The Vendor Part List:
Understanding Pricebook Items and Vendor Items
Reviewing Results and Fixing Failures
Before the process of importing vendor lists is described, an understanding of the Pricebook items and vendor part list items is necessary. Vendor Part Lists relate SuccessWare21 Pricebook Items to a list of a vendor parts consisting of a vendor part number, price and list price as illustrated below.
More than one vendor item may be related to a singled Pricebook item. If the part #TUBE-14 (1/4 inch tubing) is purchase from three different vendors: ABC, Inc., Jones Bros. Supplies, and XYZ Company, we might see the follow relationship in SuccessWare21:
To process a vendor part list import…
1) Prepare your import file (described below).
2) Access the vendor part view of the PO Manager by right clicking in the PO Manager and selecting ‘View | Vendor Part Lists’.
3) Right click again and select ‘Import Vendor Part List...’
4) The ‘open file’ dialog will pop up. Select the file you wish to import and click ‘OK’.
5) Select the appropriate import options and click ‘OK’ (described below).
6) SuccessWare21® will attempt to import the file.
7) Review results and fix failed rows. After importing, SuccessWare21® will report on the number of rows that succeeded and failed. The original text file will contain a ‘Status’ column after the import attempt. You can open the file and examine this field to determine the cause of failure if any rows failed. Based on this information you can make adjustment to the import file and run the import again (described below).
Preparing your import file
The import file must be a tab delimited file containing the following columns. Different columns are required in this file based upon the operation you wish to perform when importing.
The
‘Vendor Part List Import’ feature in SuccessWare21 can be used to…
a)
Update
Price and ListPrice on existing vendor part items.
b)
Create
new vendor part items.
c) Create new Pricebook items and vendor part items.
d) Update the description on existing Pricebook items. (added feature; 1.6.200.48)
These processes a, b, c and d correspond to the required columns a, b, and c/d in the chart below. i.e., all fields with ‘a’ in the ‘a’ column are required for process ‘a’, those with ‘b’ in the ‘b’ column are required for process ‘b’, and those with ‘c’ in the ‘c/d’ column are required for process ‘c and/or d’.
At a minimum, the columns VendorNo, VendorItemNo, and Price are required for all processes a, b, and c. If you only want to (a) Update Price and ListPrice on existing vendor part items, then these are the only columns required. Notice that ListPrice is an optional column for all processes.
In addition, ItemNo is required if you wish to (b) Create new vendor part items.
If you wish to (c) Create new Pricebook items or (d) Update Pricebook items during the import and vendor part items, then you must include the columns Add and Description. Counted, Valued, and Serialized only apply to process (c) and are optional. They only need only be included if you want to set these fields to a value other than false.
|
Required |
|
|
|
||
|
a |
b |
c/d |
Default |
Column Name |
Description |
|
a |
b |
c |
|
VendorNo |
Corresponds to and must match an existing SuccessWare21 vendor number. |
|
a |
b |
c |
|
VendorItemNo |
The vendors item number (optional column). May not contain special characters. May contain letters, numbers, hyhen ‘-‘, or slash ‘/’. |
|
a |
b |
c |
|
Price |
The price you pay when purchasing from this part from this vendor. This may be different from this list price if you receive special pricing from the vendor. |
|
|
|
|
|
ListPrice |
The standard price this vendor charges for this part. |
|
|
b |
c |
|
ItemNo |
Corresponds to then SuccessWare21 Pricebook item number. May not contain special characters. May contain letters, numbers, ‘-‘, or ‘/’. |
|
|
|
c |
false |
Add |
If this column contains an ‘x’, ‘yes’, or ‘true’, then SuccessWare21 will create an item in the Pricebook for this line using the ‘ItemNo’, ’Description’, ‘Counted’, ‘Valued’, and ‘Serialized’ column values. If the ‘ItemNo’ is already in use, the item will not be added and the import for this line will fail. Note that any Pricebook part items created will be ‘uncategorized’. This means they will not be assigned to a category or group within your Pricebook. Within the Pricebook you can multi-select and drag the new parts into appropriate part groups. This will assign the related sale types, expense types, and pricing formulas to the new parts. |
|
|
|
c |
|
Description |
Corresponds to the ‘description’ field for the item in SuccessWare21. This value is only required if the ‘Add’ column is true. |
|
|
|
|
false |
Counted |
Corresponds to the ‘counted’ setting on SuccessWare21 Pricebook items. A value ‘X’, ‘yes’, ‘true’ will set this option when creating items during import. |
|
|
|
|
false |
Valued |
Corresponds to the ‘valued’ setting on SuccessWare21 Pricebook items. A value ‘X’, ‘yes’, ‘true’ will set this option when creating items during import. |
|
|
|
|
false |
Serialized |
Corresponds to the ‘serialized’ setting on SuccessWare21 Pricebook items. A value ‘X’, ‘yes’, ‘true’ will set this option when creating items during import. |
|
|
|
|
|
import_Status |
This column is automatically added to your import file and will contain the value ‘OK’ if the line imported successfully, or the value ‘ERROR’ if the line encountered an error during import. When an error occurs, check the ‘import_StatusMessage’ column to determine the cause of the error. |
|
|
|
|
|
import_StatusMessage |
This column is automatically added to your import file and will contain information about the success or failure for the import of the particular item. |
Selecting Import Options
The Import Options Dialog, offers you options for processing when importing vendor part lists. Based on the columns contained in your import file you will have the following options:
- Allow
import processing…
- Update existing vendor part items—this
is the basic operation and will always be available.
- Create new vendor part items when
necessary—this option will be available if you have included the ‘ItemNo’ columns in your import file.
- Create new Pricebook
part items when necessary—this option will be available if you have
included the ‘Add’, ‘ItemNo’, and ‘Description’
Columns in your import file. Note
that any Pricebook part items created will be
‘uncategorized’. This means they
will not be assigned to a category or group within your Pricebook.
Within the Pricebook you can
multi-select and drag the new parts into appropriate part groups. This will assign the related sale
types, expense types, and pricing formulas to the new parts.
- Update existing pricebook
items (description)—This option will be
available when option ‘c’ above is available. Only items from the import file marked
with an ‘x’ in the add column will be updated. This will only update the descriptions
for existing items in the pricebook.
- When creating new Pricebook items, create them as…
- Counted—if
option c is available, and you have not included the column ‘Counted’ in
your import file, you can set this checkbox to control the value of
‘counted’ for Pricebook items created.
- Valued—if option c is available, and
you have not included the column ‘Valued’ in your import file, you can
set this checkbox to control the value of ‘valued’ for Pricebook items created.
- Serialized—if option c is available, and you have not included the column ‘Serialized’ in your import file, you can set this checkbox to control the value of ‘serialized’ for Pricebook items created.
- Setting vendor supplier preference…
- Make the vendor the ‘primary’ supplier for the part (bump current primary to secondary)
- Make the vendor the ‘primary’ supplier for the part (replace the current primary).
- Make the vendor the ‘secondary’ supplier for the part.
- Do not make any supplier preference changes.
Once you have reviewed/selected your import options, click ‘OK’. SuccessWare21® will attempt to import the file.
Reviewing Results and Fixing Failures
After importing, SuccessWare21® will report on the number of rows that succeeded and failed, how many pricebook items and/or vendor part items were created/updated and supplier preference changes made. The original text file will contain an ‘import_Status’ column after the import attempt. You can open the file and examine this field to determine which lines succeeded and which lines failed. The file will also contain an ‘import_StatusMessage’ column which will contain information/results for the row and any error messages.
Based on this information you can make adjustment to the import file and run the import again.
Notice that the first row in each example contains column names. The naming/spelling of the column names must match those above.
Update Price and ListPrice on existing vendor part items
In this example VendorNo, VendorItemNo and Price are included. If the VendorItemNo is found for the VendorNo, the Price (and ListPrice where included) will be updated for that vendor part item. If the VendorItemNo for the VendorNo is not found, the operation will fail for that line.
|
VendorNo |
VendorItemNo |
Price |
ListPrice |
|
100066 |
9999520 |
$6.04 |
6.50 |
|
100066 |
9999466 |
$5.33 |
5.95 |
|
100066 |
E-06 |
$9.75 |
10.95 |
|
100066 |
J-32 |
$5.40 |
8.00 |
|
100066 |
J-23 |
$96.00 |
112.00 |
|
100066 |
D-65 |
$43.00 |
47.00 |
|
100066 |
E-26 |
$2324.00 |
|
|
100066 |
M-52 |
$242.00 |
|
|
100066 |
M-51 |
$34.00 |
|
|
100066 |
C-135 |
$4.00 |
5.00 |
|
100066 |
C-136 |
$0.23 |
0.50 |
|
100066 |
C-137 |
$4.00 |
5.00 |
|
100066 |
J-15 |
$3.00 |
3.99 |
|
100066 |
M-41 |
$20.00 |
20.00 |
Create new vendor part items.
In this example VendorNo, VendorItemNo and Price are included. If the VendorItemNo is found for the VendorNo, the Price (and ListPrice where included) will be updated for that vendor part item. Because the ItemNo is included, if the VendorItemNo for the VendorNo is not found, a new vendor part item will be created with Price (and ListPrice where included).
|
VendorNo |
VendorItemNo |
Price |
ListPrice |
ItemNo |
|
100066 |
9999520 |
$6.04 |
6.50 |
TS9B |
|
100066 |
9999466 |
$5.33 |
5.95 |
TS4W |
|
100066 |
E-06 |
$9.75 |
10.95 |
RSPT |
|
100066 |
J-32 |
$5.40 |
8.00 |
PG980 |
|
100066 |
J-23 |
$96.00 |
112.00 |
PG979 |
|
100066 |
D-65 |
$43.00 |
47.00 |
PG735 |
|
100066 |
E-26 |
$2324.00 |
|
PG665 |
|
100066 |
M-52 |
$242.00 |
|
PG566-568 |
|
100066 |
M-51 |
$34.00 |
|
PG512-513 |
|
100066 |
C-135 |
$4.00 |
5.00 |
PG501 |
|
100066 |
C-136 |
$0.23 |
0.50 |
PG501 |
|
100066 |
C-137 |
$4.00 |
5.00 |
PG501 |
|
100066 |
J-15 |
$3.00 |
3.99 |
PG496-497 |
|
100066 |
M-41 |
$20.00 |
20.00 |
PG487-489 |
Create new Pricebook items and vendor part items.
In this example VendorNo, VendorItemNo and Price are included. If the VendorItemNo is found for the VendorNo, the Price (and ListPrice where included) will be updated for that vendor part item. Because the ItemNo is included, if the VendorItemNo for the VendorNo is not found, a new vendor part item will be created with Price (and ListPrice where included).
Before updating/adding the vendor part item, because rows 3 through 14 have an ‘x’ in the Add columns, an attempt will be made to add a Pricebook part item with the specified ItemNo and Description. AverageCost and StandardCost for the item will be set to ‘Price’. If the ItemNo is already in use, the operation will fail, the Pricebook item will not be added and the vendor part item will not be created. The ItemNo must be unique to all items in the pricebook (parts, labor, tasks, and overhead).
|
VendorNo |
VendorItemNo |
Price |
ListPrice |
ItemNo |
Add |
Description |
Counted |
Valued |
Serialized |
|
100066 |
9999520 |
$6.04 |
6.50 |
TS9B |
|
|
|
|
|
|
100066 |
9999466 |
$5.33 |
5.95 |
TS4W |
|
|
|
|
|
|
100066 |
E-06 |
$9.75 |
10.95 |
RSPT |
x |
Some desc.. |
|
|
|
|
100066 |
J-32 |
$5.40 |
8.00 |
PG980 |
x |
Some desc.. |
x |
|
|
|
100066 |
J-23 |
$96.00 |
112.00 |
PG979 |
x |
Some desc.. |
x |
|
|
|
100066 |
D-65 |
$43.00 |
47.00 |
PG735 |
x |
Some desc.. |
x |
|
|
|
100066 |
E-26 |
$234.00 |
|
PG665 |
x |
Some desc.. |
x |
|
|
|
100066 |
M-52 |
$242.00 |
|
PG566-56 |
x |
Some desc.. |
x |
x |
x |
|
100066 |
M-51 |
$34.00 |
|
PG512-51 |
x |
Some desc.. |
x |
x |
x |
|
100066 |
C-135 |
$4.00 |
5.00 |
PG501 |
x |
Some desc.. |
x |
x |
|
|
100066 |
C-136 |
$0.23 |
0.50 |
PG501 |
x |
Some desc.. |
x |
x |
|
|
100066 |
C-137 |
$4.00 |
5.00 |
PG501 |
x |
Some desc.. |
x |
x |
|
|
100066 |
J-15 |
$3.00 |
3.99 |
PG496-47 |
x |
Some desc.. |
x |
x |
x |
|
100066 |
M-41 |
$20.00 |
20.00 |
PG487-48 |
x |
Some desc.. |
x |
x |
x |