Overview
These are the questions and available answers for each inventory Group used in Quoting.
The Options are loaded with a date that they apply from and Order Date Entered is used to indicate the Options to be used. This is done so that as products change over time with Options being added and removed old orders are still valid and new Options can be loaded in advance of the release.
Checking the Options being used at a Date.
Step 1 is to "Download current layouts" with "Date as at" the Order Entered Date in question.
In cell A1 on each tab is "BUZ Unique Option Name as at 4/11/2013" which is the used as the commencement date for these Options.
Setting up Option Questions in ProdInfo.xlsx
Inventory Group Tabs
The questions are setup in ProdInfo.xls which is saved in the data folder C:\Dropbox\BUZ\Customer Folders\CompanyName\Layouts.
Tabs are added for each Inventory Group that can be ordered and the tab must be labelled exactly as the code for the Inventory Group. E.g. ROLL = Rollers
Each column represents a question and the possible answers (Options or Attributes) except for column A which are descriptions for the row's data. The order left to right is important for example a quantity question will apply to the main product and all those to the right of it, those to the left would get the default value of 1.
Below is an example of the questions but this explanation should be read in conjunction with a working ProdInfo.xls:
****Note These characters cannot be used in the text of options: / | ’
| Text | Description |
| BUZ Unique Option Name as at 5/07/2013 | |
| Inventory Code for Pricing | |
| Value applies to Price from Related Option Name | |
| Related Column Data Field | |
| Related Column is Add to else Set to | |
| Look Back @ BUZ Column Name | |
| Question Heading | Location |
| Option Type | $ |
| Is Required | |
| Can Mass Update | |
| Clear value when Copied to new item | |
| Is Not for Online Wholesale Ordering | |
| Use First valid Answer as default | |
| Help Message | |
| Picture URL | |
| Answers from here down |
- Each column, row 1 is the Key Word for that item. Key Words must only appear once for each product and must not contain $. Below is a table of Key Words with special significance. Key Words are used to fill in the Job Sheet.
Key Word | Function | Details |
DESCN | Description for Order Line usually Room/Location | List of options listed underneath. |
ITEMWIDTH | Width required, used for price calculations | If omitted is set to one (1). |
ITEMHEIGHT | Height/Drop required, used for price calculations. | If omitted is set to one (1). |
ITEMDEPTH | If depth is required or can be used for price levels. | If omitted is set to one (1). |
QTY | Quantity of this item. | If omitted is set to one (1). |
RRP | Price before any discounts. |
|
AMT | Price after discounts. | |
Key Word Options | Example: POST QTY|AWPOST|POSTTYPE|QTY|TRUE|OTHERCOLM |
|
POST QTY | This is the Key Word (Name) for this column (Option). | So POST QTY (not case sensitive) is the Key Word (Name) for this option and must be unique for this tab (Product Type/Inventory Group) |
|AWPOST | This is the Inventory Code for pricing. It can be omitted. | To indicate the inventory code to be used to look up pricing add the pipe character "|" followed by the inventory code in capitals that is to be looked up for price. This is not validated so if price does not appear check the code is correct and exists in Inventory Maintenance. |
|POST TYPE | The column Key Word name that this refers to which can, if needed, be this column. | So if this is the quantity for a previously selected option this tells the system which option it applies to. |
|QTY | The field on the previous option’s charge record where answer is to be saved. | Format is pipe character “|” then the Type Word which can be QTY, ITEMWIDTH, ITEMHEIGHT, ITEMDEPTH, RRP, AMT. are Key Words. |
|TRUE | Add value to Saved Field else Clear and then add (False). | |
|OTHERCOLM | The Vertical Lookup column that the option relates to. | The column that restricts the options if it not the column immediately to the left. See Vertical Lookup below. |
Row 2 is the question to display to the user.
Row 3 is the command that sets the data type, required, etc see table below for command details.
The First Character | Function | Details |
L | Drop Down List | List of options listed underneath. |
$ | Text box |
|
R | Integer (no decimals) with a 2 sets of number ranges and messages | Used to warn not recommended sizes and the second set for not allowed. Not allowed is also indicated by there being no price. |
& | Integer input |
|
# | Decimal input |
|
D | Discount amount with decimals and deduct | |
N | Notes |
|
H | Heading | Heading only so options can appear grouped. |
F | File Upload |
|
Extra Characters |
|
|
! | Required | Means that the question has to answered |
V | Vertical Lookup | This means that what is selected in the option list column prior or the OTHERCOLM parameter from row 1, will restrict the options listed. To do this you need the previous value in capitals then a pipe character "|" followed by the option text. |
M | Include option in Mass Update Options | See Order Entry for details of Mass Update. |
W | Not a Web Online ordering Option | Only for internal ordering use. |
Z | Do not repeat previous value when adding new item. | When a new is added the previous item for this order’s details are repeated by default so Z indicates to clear the previous value. |
= | Default value is first item in list rather than blank. | Only works with List combos. |
4. Rows 4 and below list options for both ‘L’ and ‘V’ commands. These can consist of up to 4 sections separated the vertical line (Known as the Pipe character| ) The parameters are explained using the following example:- LEFT CHAIN|Stainless Steel|OPCHASSTL|+2
V Options | Lookup options |
|
LEFT CHAIN | This means that this option is only to be shown if the value selected in the previous option matches it. | So if LEFT CHAIN (not case sensitive) was chosen in the previous option then Stainless Steel will be in the dropdown for them to choose. |
|Stainless Steel | This is the text that will be presented in the list to the person entering the item. | |
|OPCHASSTL | Inventory Code for pricing. This is the second option for L or third for V is used. | To indicate the inventory code to be used to look up pricing add the pipe character "|" followed by the inventory code in capitals that is to be looked up for price. This is not validated so if price does not appear check the code is correct and exists in Inventory Maintenance. |
|+2 | Move ahead 2 columns instead of 1 which is normal if parameter is omitted. | Format is pipe character “|” then “+” followed by the number of columns to move ahead. Must be the last parameter on the line. The columns that are to be skipped must be V columns so that they are disabled prior to choosing that option otherwise the user can still click into them. |
In the example below the PELRETURN is activated for all answers to PELWIDTH but because the No|+4 in PELTYPE causes it to skip 4 columns the PELRETURN will not be available if No is selected. Note that the options under PELRETURN all have | with nothing to the left of it so the V uses any answer provided it doesn’t skip this column.
PELTYPE | PELWIDTH | PELRETURN |
Pelmet | Pelmet Width | Pelmet Return |
L! | &V | L!V |
Yes | Yes | |Both |
No|+4 | |Left Only | |
|Right Only | ||
|None |
A more complex example.
Y | Z | AA | AB | AC | AD |
ANGLE COLOUR | SPECIAL COLOUR ANGLE | PARTS1 | SIZE1||ANGLE COLOUR|ITEMWIDTH|FALSE | PARTS2 | SIZE2||ANGLE COLOUR|ITEMWIDTH|TRUE |
Angle Colour | Special Colour | Parts | Size | Parts | Size |
L!V | L!V | L | L!V | L | L!V |
YES|APO | SPECIAL COLOUR|Admiralty 51017 | 12*12 | 12*12|1000|OPP12/12/10 | 12*12 | 12*12|1000|OPP12/12/10 |
YES|Birch White | SPECIAL COLOUR|Anotec Dark Grey 51275 | 20*12 | 12*12|2160|OPP12/12/21 | 20*12 | 12*12|2160|OPP12/12/21 |
YES|Black | SPECIAL COLOUR|Anotec Mid Bronze 51014 | 20*25 | 12*12|2600|OPP12/12/26 | 20*25 | 12*12|2600|OPP12/12/26 |
YES|Special Colour|OPANGSPECOL | SPECIAL COLOUR|Anotec Natural Pearl 89119 | 20*40 | 12*12|3000|OPP12/12/30 | 20*40 | 12*12|3000|OPP12/12/30 |
SPECIAL COLOUR|Anotec Off White 51271 | 12*40 | 12*12|4000|OPP12/12/40 | 12*40 | 12*12|4000|OPP12/12/40 | |
SPECIAL COLOUR|Anotec Silver Grey 51272 | Patio Bolt|OPPATBOL | 12*12|4500|OPP12/12/45 | Patio Bolt|OPPATBOL | 12*12|4500|OPP12/12/45 |
Column Y ANGLE COLOUR, List, required that only shows if YES was selected in the previous and Special Colour is a surcharge on the following options and the price comes from product OPANGSPECOL.
Column Z SPECIAL ANGLE COLOUR, List, Required that only shows if SPECIAL COLOUR was selected in the previous column.
Column AA PARTS1, List, optional and will always show.
Column AB SIZE1, Required, options depend on what was selected in PARTS1 column. Selected value will SET ITEMWIDTH of ANGLE COLOUR charge record, which is for product OPANDSPECOL. It will also generate a charge to one of several different products depending on option selected, the first one to OPP12/12/10.
Column AC PARTS2, List optional and will always show.
Column AD SIZE2, Required, options depend on what was selected in PARTS2 column. Selected value will ADD to ITEMWIDTH of ANGLE COLOUR charge record, which is for product OPANDSPECOL. It will also generate a charge to one of several different products depending on option selected, the first one to OPP12/12/10.
Extras Sheet
This is a similar format to the Inventory Group sheets and is used as a single set of options that apply to the whole Quote/Order. Like the option lines row one can be made of up to 4 parameters. This is particularly useful for the Extras sheets. The parameters are explained using the following FREIGHT|NSFREIGHT|DESCN|RRP
V Options | Lookup options |
|
FREIGHT | This is the Key Word for this column (Option). | So FREIGHT (not case sensitive) is the Key Word (Name) for this option and must be unique for this tab (Product Type/Inventory Group) |
|NSFREIGHT | Inventory Code for pricing. This is the second option for L or third for V is used. | To indicate the inventory code to be used to look up pricing add the pipe character "|" followed by the inventory code in capitals that is to be looked up for price. This is not validated so if price does not appear check the code is correct and exists in Inventory Maintenance. |
|DESCN | References another column by its key word. | |
|RRP | The field for the option Item that that answer to this is to be saved to.. Possible fields are DESCN, ITEMWIDTH, ITEM HEIGHT, QTY, RRP | Format is pipe character “|” then “+” followed by the number of columns to move ahead. Must be the last parameter on the line. The columns that are to be skipped must be V columns so that they are disabled prior to choosing that option otherwise the user can still click into them. |
InvoiceExtras Sheet
This is THE SAME format as the Extras sheet and is used as a single set of options when finalizing an order prior to invoicing.
Marketing Options Workbook
MarketingOptions.xls is also in a similar format and contains questions relating to current marketing campaigns and demographics.
It is found in the same folder as DrOEProdInfo.xls