Price Matrix - Wizard (Import Price List)
Note:
For Products, you will have to enter a line for each product selection, just as you do when creating the price list using the wizard. When creating the second an subsequent lines for a product, make sure you set the Reset Price List column to 0 or you will just keep overwriting the first line.
Price lists can only be imported from a file that is in csv delimited format
The easiest way to create this kind of file is to build a Microsoft Excel spreadsheet and save it as delimited csv format. A template , APM_PriceList_Import_layout.xlsx, is provided and can be found in the installation Extras folder.
The file can contain header rows, however they are ignored by the import.
The Customer and Sub Analysis selection criteria are entered as a string, as in the example. The structure of the example is described below.
S|0|HOTEL~2|0|NOTTINGHAM~
The columns of the spread sheet are described below:
Columns | Detail | Type |
Reset price list |
1 or 0
Set to 1 to overwrite a price list of the same name.
Set to 0 to amend a price list and for additional product lines.
| A |
Price list name | Example: Access Sample Price List | A |
Effective from date | Example: 02/01/2008 | A |
Effective to date | Example: 31/01/2008 | A |
Price list type |
SPL = Standard Price List
WOD = Whole Order Discount
BGF = Buy One Get One Free
| A |
Crystal Report Name |
Enter the report name as seen in the wizard window.
Example: Standard Price List
| A |
Currency | Enter the currency symbol. Example: £ | A |
Active | 1 = Active; 0 = Not Active | A |
Priority | 1 to 99 as you would have entered in the wizard. | A |
Sort Key | Leave this column empty. | A |
Notes | Enter the notes as you would have entered them in the wizard. | A |
Sub Analysis Filter | 1 = Yes; 0 = No | A |
Sub analysis string |
A string to define the selection criteria: Number of Sub Analysis field | Value ~ Number of Sub analysis field | Value ~
Example: 1|NOT~2|L1~3|R3~
| A |
Customer list |
A string to define the selection criteria: Key field | Include/Exclude | Value ~
Where the Key field is: C for Code; S for Sort Key; 1 for User Sort 1; 2 for User Sort 2; 3 for User Sort 3, and 0 = Include; 1 = Exclude.
Example: S|0|HOTEL~2|0|NOTTINGHAM~
| A |
Product Cat Type |
The key field for the product.
C for Code; S for Sort Key; 1 for User Sort 1; 2 for User Sort 2; 3 for User Sort 3.
| A |
Product Cat |
The value of the key field.
Example: ZINFANDEL
| A |
Exclude Product | 1 = Yes; 0 = No | A |
Single/qty | 0 = Single; 1 = Quantity | S |
Qty from | Leave blank for single otherwise enter the quantity for the beginning of the range. | S |
Qty to | Leave blank for single otherwise enter the quantity for the end of the range. | S |
Method | F = Fixed Price; L = Line Discount; P = Price Discount | S |
Value | Enter the Fixed Price value (Standard and BOGOF using Fixed method) | ‡ |
Disc % | Enter the Discount percentage (Standard and BOGOF using Discounted method) | ‡ |
Cash Discount | Enter the Discounted value | S |
Chained discount |
Chained Discount is available as an additional discount for Fixed and Price Discount methods and is applied as a Line Discount.
0 = No, or enter the Discount %.
| A |
Apply to Sales orders |
YES or NO
*Note that this different than other columns
| A |
Apply to Credit notes |
YES or NO
*Note that this different than other columns
| A |
Match all customers |
YES or NO
*Note that this different than other columns
| A |
Match all products |
YES or NO
*Note that this different than other columns
| A |
Apply to all customers |
YES or NO
*Note that this different than other columns
| A |
Apply to all products |
YES or NO
*Note that this different than other columns
| A |
Gross prices |
YES or NO
*Note that this different than other columns
| A |
Buy one get one free type |
0 = Free; 1 = Fixed; 2 = Discounted
| B |
Buy Quantity | Enter the quantity to be purchased. This will be the quantity charged at full price. | B |
Free Quantity |
Free type lists: Enter the quantity that will be free in the order.
Fixed type lists: Enter the quantity that will be charged at the fixed price.
Discounted type lists: Enter the quantity that will be discounted.
| B |
Max Allowance | Enter that maximum number of offer items. | B |
Min Qty | Enter the minimum order quantity required to qualify for the offer. | B |
Same or diff product |
0=No, 1=Yes
| B |
Diff product type |
C=Code, S=Sort Key, 1=User Sort 1, 2= User Sort 2, 3=User Sort 3
| B |
Diff product | The value of the key field for the discounted different product. | B |
Include/add free items | 0=include, 1=add | B |
WOD - Qual Type | 0 = Order Value; 1 = Order Quantity |
W
|
WOD - Qual Value | For Order Value type price lists, enter the qualifying value. | W |
WOD - Qual Qty | For Order Quantity type price lists, enter the qualifying quantity. | W |
WOD - Fixed Disc | Enter the discount value. | W |
WOD - Disc % | Enter the discount percentage. | W |
WOD - Price Code | Enter the code of the Price Record that will be used to post the discount. | W |
WOD - Single or multiple |
0=single, 1 multiple
| W |
WOD - From |
From value(only applicable for the Multiple Method)
| W |
WOD - To |
To value (only applicable for the Multiple Method)
| W |
WOD - Offer type | 0=discount, 1=free item | W |
WOD - Product type |
Product category type of free item:
C=code, S=Sort key, 1=User sort 1, 2=User sort 2, 3=User sort 3.
| W |
WOD - Product | The value of the key field for the WOD free item | W |
WOD - free qty |
The quantity of the free item
| W |
Key:
A=All
S=Standard Price Lists
‡=Buy One Get One Free (and Standard) Lists
B=Buy One Get One Free Lists
W=Whole Order Discount Lists