invoiceit!Pro
Products Import Guide in detail
invoiceit!Pro
- importing Products
allows updating of existing products!
invoiceit!Pro
allows import of contacts, products and debtors. This is accessed
from Setup - Import/Export button at the bottom.
if you are familiar with Excel, the procedures below will appear overly
detailed, but they are written for the inexperienced. Bear with us,
please.
The method described below makes a backup of the original import template
(so you can use it again later) and copies the content of your existing
data spreadsheet into a worksheet (the tab at the bottom) called 'OldData'.
Finally, you map the fields by creating a series of formulae, which copy
the data from your original to the appropriate column in the Import
template.
Alternative: paste directly into the appropriate column in Import
section of spreadsheet |
Import
contacts debtors
click
on image to enlarge
|
How to
import products
this guide is as detailed as possible to enable novice users to follow the
steps.
Prepare the data:
Data Import/Export screen in invoiceit:
1) from the 'Select Data Type' dropdown, select Spreadsheet
2) from the green dropdown, select 'Products/Service Charges'
3) double-click the large panel at the bottom to open the client import
template (it is located in your program folder
...\invoiceit!5\import).
Import template ImportProducts.xls:
(to use CSV file refer the entry bottom of page)
4) Delete the sample entries (Line2 and below)
5) from File Menu, select 'Save as' and save a copy of this template in
another folder (as backup).
6) From File Menu, select Open and locate your old data spreadsheet and
double-click it.
7) Press Ctrl+A (to select all), then Ctrl+C (to copy all)
8) switch to the worksheet 'OldData' on the import template
(the tab at the bottom), click on
cell A1 and press Ctrl+V (to paste all).
You now have both the import template and your old data in the same
spreadsheet.
Map the fields:
1) click on the 'Import' worksheet (tab at the bottom) and select cell A2 (the first
empty cell at top left).
let's assume your old product ID is in column C of the worksheet 'OldData'
and the first row contains your field headers.
2) in cell A2 on the 'Import' worksheet, type =OldData!C2
and press Enter. You will see the old product ID displayed in cell A2 of
the template.
Repeat the same with other columns - Example: if your OLD product name is
on 'OldData', column A, row 2, type =OldData!A2 and
press Enter. Always use the row number of your old data worksheet which
contains the FIRST entry.
3) continue with other columns until all columns from worksheet 'OldData'
have found a corresponding column in worksheet 'Import' (see complete
column list and their expected content below).
4) when you have completed this, select Row 2, click on the black dot in the bottom right corner of
W2 and drag
this down as far as required. Example: if you have 250 products, drag as
far as Row 251.
All entries from the old data worksheet are now 'copied' to the template
worksheet.
5) from the File Menu, select 'Save' (or press Ctrl+S).
Close the spreadsheet and switch back to invoiceit. Click on the Import
button and it's done.
Product columns in detail - those in red are required entry
fields:
Number fields: do not use currency
or percent symbols
Fields marked Y in column "update" will be
updated, when Import finds that this product ID already
exists.
You can therefore use the Import function to update your product
list periodically from external sources
Price fields will be updated in all cases. If you leave a price
blank, it means the existing price will be deleted, except for
ListPrice, which will default to 0 if left blank.
Cost field will only be updated when you have entered a value.
Blank cost fields will be ignored.
| Column |
Field
Name |
update |
Type/Size |
Comment |
| A |
ProductID |
|
Text/15 |
unique
charge code for products/services; Required Entry |
| B |
PType |
|
Number |
Charge
Type code
1=product/inventory (used as default, if left blank)
2=service charge |
| C |
PName |
Y |
Text/150 |
description of product/service;
Required Entry |
| D |
GroupID |
|
Text/3 |
Charge
Group: use ID as per list
#12; (Setup > List selector at far left) |
| E |
LocationID |
|
Text/5 |
Product Location;
use ID as per list #9; (Setup > List selector at far left) |
| F |
VendorID |
|
Number |
Supplier/Vendor ID
use the number from contact import
if ID not found by Import, this field will not
transfer |
| G |
ListPrice |
Y |
Number |
price as per top price level of charge item;
blanks will be converted to 0 |
| H |
Price2 |
Y |
Number |
Price Level 2,
as per Setup p.6
leave blank if not used |
| I |
Price3 |
Y |
Number |
Price Level
3, as per Setup p.6
leave blank if not used |
| J |
Price4 |
Y |
Number |
Price Level
4, as per Setup p.6
leave blank if not used |
| K |
Price5 |
Y |
Number |
Price Level
5, as per Setup p.6
leave blank if not used |
| L |
Cost |
Y |
Number |
not
used if PType=1 and Monitor=TRUE (monitored inventory) |
| M |
TaxRate1 |
|
Number |
NOT USED in USA/Canada.
Enter tax rate that applies to this item (without % sign) |
| N |
TaxRate2 |
|
Number |
not used in value-added tax countries
US: state sales tax
Canada: GST/TPS
other countries: First tax
Enter tax rate that applies to this item (without % sign) |
| O |
TaxRate3 |
|
Number |
not used in value-added tax countries
US: county sales tax
Canada: HST
other countries: Second tax
Enter tax rate that applies to this item (without % sign) |
| P |
TaxRate4 |
|
Number |
not used in value-added tax countries
US: city sales tax
Canada: PST/TVQ
other countries: Third tax
Enter tax rate that applies to this item (without % sign) |
| |
|
|
|
Inventory/Stock
Control |
| Q |
Monitor |
|
Text/5 |
monitor
levels
if item is monitored: -1 (or TRUE or YES or Y)
if not monitored: 0 (or FALSE or NO or N) - remaining fields
will be ignored |
| R |
MinStock |
|
Number |
minimum
level |
| S |
MaxStock |
|
Number |
maximum
level |
| T |
CurrentQty |
|
Number |
Current quantity on hand |
| U |
CurrentValue |
|
Number |
Current
total value on hand (quantity on hand * unit cost) |
|
