...
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 an Excel Spreadsheet
...
Below is an example of the questions but this explanation should be read in conjunction with a working Layout.xlsx:
****Note These characters cannot be used in the text of options: / | ’
...
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. | |||
| UNITCOST | Cost Price | |||
AMT | Price after standard discounts. | |||
| CODEPART1 | Get Code of Material selected | Mostly used for Options that depend on the Material chosen e.g. Rail Road Y/N option only available for some fabrics. | ||
| CODEPART2 | Get Code of Material Type selected | Restrict Options available based on the Material Type selected in the same way as Options that depend on a previous option selected. | ||
| CODEPART3 | Get Code of Colour selected | As with CODEPART1 and CODEPART2 used for Options that depend on the Colour selected. Note not everyone uses Colour for colour, it might be Hinged or Sliding. | ||
| DESCNPART1 | Get description of Material selected | Functions the same as CODEPART except use the full Material Description. | ||
| DESCNPART2 | Get description of Material Type selected | Functions the same as CODEPART except use the full Material Type Description. | ||
| DESCNPART3 | Get description of Colour selected | Functions the same as CODEPART except use the full Colour Description. | ||
| INSTALLATIONJOB#NOTE | Dispatch Notes | Dispatch notes to appear per line item | ||
Rows |
|
| ||
1 | This is the Key Word (Name) for this column (Option). | So KEYWORD (not case sensitive) is the Key Word (Name) for this option and must be unique for this tab (Product Type/Inventory Group) | ||
2 | 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. Typical usage is Freight Product Code here, AMT as Field and the response is the price. | ||
3 | The column Key Word that which provides the product code for pricing which can be this column. | So if this is the quantity for a previously selected option this tells the system which option it applies to. | ||
4 | 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. | ||
5 | Add value to Saved Field (Y) else Clear and then add (N or blank). | This applies to text as well as numbers. | ||
6 | The Look Back column that the option relates to. | The column that restricts the options available are dependent on (restricted by). | ||
| 7 | The question to display to the user. | e.g. Location |
Row 8 is the command that sets the data type, required, see table below for command details.
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 | example fo entry -50 |
N | Notes | |
H | Heading | Heading only so options can appear grouped. |
F | File Upload | Uploads file to attached files for that order |
Row | Function | Details |
|---|---|---|
9 | Is Required | Enter Y if Option must be answered if available (enabled). |
| 10 | Can Mass Update | This is for future functionality. |
| 11 | Clear value when Copied to new item | Enter Y if the Option is to be cleared when copied from another line item. |
| 12 | Is Not for Online Wholesale Ordering | These Options are for internal use e.g.manufacture or buy-in |
| 13 | Use First valid Answer as default | Enter Y if the first available answer is to be displayed by default. |
| 14 | Help Message | Text of a message to be displayed when the ? at the end of the line is clicked, ? is not displayed if there is no message. |
| 15 | Picture URL | Future option to display Display a picture specific to the option such as a technical drawing of a bracket. See how to here> |
Rows 16 and below list options for both ‘L’ and Look-Back commands. These can consist of up to 3 sections separated the vertical line (Known as the Pipe character| ) The parameters are explained using the following example:- LEFT CHAIN|Stainless Steel|OPCHASSTL
V Options | Lookup options |
| |
|---|---|---|---|
LEFT CHAIN | This means that this option is only to be shown if the value selected in the Look-Back 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. |
...
| BUZ Unique Option Name as at 9/8/13 | PELTYPE | PELWIDTH | PELRETURN | PELRETURNSIZE | |||||||
| Inventory Code for Pricing | |||||||||||
| Value applies to Price from Related Option Name | PELTYPE | ||||||||||
| Related Column Data Field | ITEMWIDTH | ||||||||||
| Related Column is Add to else Set to | |||||||||||
| Look Back @ BUZ Column Name | Look Back @ BUZ Column Name | PELRETURN | |||||||||
| Question Heading | Pelmet | Pelmet Width | Pelmet Return | Return Size | |||||||
| Option Type | L | & | L | & | |||||||
| Is Required | Y | ||||||||||
| 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 Answers from here down | Yes | Yes | Both | BOTH| | |||||||
| Answers from here down | No | Left Only | LEFT ONLY| | ||||||||
| Answers from here down | Right Only | RIGHT ONLY| | |||||||||
| None | |||||||||||
Example of dependent Options
A | Y | Z | AA | AB | AC | AD | ||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| BUZ Unique Option Name as at | ANGLE COLOUR | SPECIAL COLOUR ANGLE | PARTS1 | SIZE1 | PARTS2 | SIZE2 | ||||||
| Inventory Code for Pricing | ||||||||||||
| Value applies to Price from Related Option Name | ANGLE COLOUR | ANGLE COLOUR | ||||||||||
| Related Column Data Field | ITEMWIDTH | ITEMWIDTH | ||||||||||
| Related Column is Add to else Set to | FALSE | FALSE | TRUE | Look TRUE | ||||||||
| Look Back @ BUZ Column Name | ANGLE REQUIRED | ANGLE COLOUR | PARTS1 | PARTS2 | ||||||||
| Question Heading | Angle Colour | Special Colour | Parts | Size | Parts | Size | ||||||
| Option Type | L | L | L | L | L | L | ||||||
| Is Required | Y | Y | Y | Y | ||||||||
| Can Mass Update | ||||||||||||
| Clear value when Copied to new item | ||||||||||||
| Is Not for Online Wholesale Ordering | ||||||||||||
| Use First valid Answer as default | ||||||||||||
| Help Message | ||||||||||||
| Help Message | ||||||||||||
| Picture URL | ||||||||||||
| Answers from here down | Picture URL | |||||||||||
| Answers from here down | YES|APO|OPANGAPO | SPECIAL COLOUR|Admiralty 51017 | 12*12 | 12*12|1000|OPP12/12/10 | 12*12 | 12*12|1000|OPP12/12/10 | ||||||
| Answers from here down | YES|Birch White|OPANGBW | SPECIAL COLOUR|Anotec Dark Grey 51275 | 20*12 | 12*12|2160|OPP12/12/21 | 20*12 | 12*12|2160|OPP12/12/21 | ||||||
| Answers from here down | YES|Black|OPANGBLACK | 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 |
...
| PELWIDTH | PELRETURN | PELRETURNSIZE | PELRETURNSIZE2 | ||
| PELCOLOUR | PELCOLOUR | PELCOLOUR | |||
| ITEMWIDTH | ITEMWIDTH | ITEMWIDTH | |||
| Y | Y | ||||
| PELTYPE | PELRETURN | PELRETURN | PELRETURN | ||
| Pelmet Width | Pelmet Return | Left Return Size | Right Return Size | ||
| & | L | & | & | ||
| Y | |||||
| LINEA VALANCE 98MM NO INSERT| | Both | BOTH| | BOTH| | ||
| LINEA VALANCE 140MM NO INSERT| | Left Only | LEFT ONLY| | RIGHT ONLY| | ||
| LINEA VALANCE 140MM WITH INSERT| | Right Only | ||||
| LINEA VALANCE 140MM WITH INSERT| | None |
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.
| BUZ Unique Option Name as at | FREIGHTMETHOD | FREIGHTCOMPANY | ||
| Inventory Code for Pricing | ||||
| Value 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 | FREIGHTMETHOD | |||
| Question Heading | Sent by | Freight Destination | ||
| Option Type | L | L | ||
| Is Required | Y | Y | ||
| 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 | Courier | COURIER|NSW|OPFRNSW | ||
| Answers from here down | Own Carrier | COURIER|Qld|OPFRQLD | ||
| Answers from here down | Pickup | COURIER|Vic|OPFRVIC | ||
| COURIER|SA|OPFRSA |
Extras -Adding to Unit Cost and RRP
Column F has an Option Code of OPEXCST and to get the Unit cost and the RRP to use this 1 Option code for pricing you must set it up as below
Colunm F
- Unique name - COST
- Inventory Code for Pricing - OPEXCST
- Related Data Field - UNITCOST
Column H
- Unique name - EXTRACHARGE
- Value applies to price from related Option Name - COST (this links it back to the Option code in the COST column)
- Related Data Field - RRP
If either Cost or RRP are left blank BUZ will only apply the cost or the RRP but if both are entered then both will be applied
InvoiceExtras Sheet
This is THE SAME format as the Extras sheet and is used as a single set of options when finalizing an order when creating the invoice. This is usually used by wholesale companies. The FREIGHT and DISCOUNT columns below are explained below the example.
| BUZ Unique Option Name as at 24/03/2013 | FREIGHTMETHOD | FREIGHT | DISCOUNT | |||
| Inventory Code for Pricing | OPDISCOUNT | |||||
| Value applies to Price from Related Option Name | FREIGHTMETHOD | |||||
| Related Column Data Field | RRP | RRP | ||||
| Related Column is Add to else Set to | ||||||
| Look Back @ Look Back @ BUZ Column Name | FREIGHTMETHOD | |||||
| Question Heading | Sent by | Freight | Discount | |||
| Option Type | L | # | D | |||
| Is Required | Y | |||||
| 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 | Toll Ipec|NSFRTOLL | TOLL IPEC | ||||
| Answers from here down | By Our Van|NSFRVAN | BY OUR VAN | ||||
| Answers from here down | Own Carrier | LOCAL TRANSPORT | ||||
| Local Transport|NSFRLOC | ||||||
| Goods Collected |
V 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) | |
Row 3: FREIGHTMETHOD | This is the column that the Inventory Code for pricing is . | 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. | |
Row 4: RRP | The field for the option Item that that answer to this is to be saved to.. Possible fields are DESCN, ITEMWIDTH, ITEMHEIGHT, ITEMDEPTH, QTY, RRP, AMT | Format is pipe character “|” followed by the field that the value is to be saved as. This is the third (last) parameter on the line. | |
| DISCOUNT | This is the Key Word for this column (Option). | ||
| Row 2: OPDISCOUNT | Inventory Code for pricing. | Here we see a product code OPDISCOUNT to apply the value to rather than change the line item values as with discounts given at the quote stage. | |
| Row 4: 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 |
Marketing Sheet
Marketing is also in a similar format and contains questions relating to current marketing campaigns and demographics.
It is found in the same output spreadsheet as above.
***Note: For the Lead Sources reports to work the first 2 column must be TYPE and COMPTYPES
Below is the top section of a typical Marketing sheet.
| BUZ Unique Option Name as at 1/10/2013 | TYPE | COMPTYPES | TYPEOTHER | OTHERINFO | |||||||||
| 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 | TYPE | TYPE | |||||||||||
| Question Heading | Survey | Company Types | Other Description | Other Info | |||||||||
| Option Type | L | L | $ | $ | |||||||||
| Is Required | Y | Y | Y | ||||||||||
| Can Mass Update | |||||||||||||
| Clear value when Copied to new item | |||||||||||||
| Is Not for Online Wholesale Ordering | |||||||||||||
| Use Use First valid Answer as default | |||||||||||||
| Help Message | |||||||||||||
| Picture URL | |||||||||||||
| Answers from here down | Radio | RADIO|2CH | OTHER| | ||||||||||
| Answers from here down | Newspaper | RADIO|2 Day FM | |||||||||||
| Answers from here down | Newspaper | RADIO|2 Day FM | Answers from here down | Letter Box | RADIO|2GB | Letter Box | RADIO|2GB |
Picture URL using Dropbox
First the folder your images are in on drop box need to be public, to do this follow the below;
- Right-click on a folder you want to publish.
- Select "Dropbox -- Share link".
- On the following dropbox website, click "Get link". Now your folder became public and anyone can access it with the folder link.
Now to upload images and add URL;
- Upload images to this drop box folder
- Right click on the image within dropbox and select Copy image address (see below)
3. In your questions layout exported from BUZ, paste this link in row 16 “Picture URL”
4. Import the layout back into BUZ
Import Errors
Typical Error "Error in sheet: AWZS - Sheet Name: AWZS,Inventory Code: OPRLBRKT Column #:6 Row #:13"
Option Product Codes are validated as existing during the import and the above message indicates the Inventory Code was not found. If the Code is not a Code this indicates the column is not formatted correctly.
Important Price Calculation considerations
Zero Quantity required
When calculating the prices most involve quantity(Qty), ItemWidth, ItemHeight and ItemDepth. Often these are not all set using the Options so BUZ sets them to 1 (one), if you have an Option that includes a response of 0(zero) you may end up with 1 flowing through, this mainly effects quantity. So if you have an Option that asks, for example, the quantity of brackets and a valid answer is 0 then the Options needs to structured so that the resultant charge is not for 1.
Price Grid returns zero
When pricing both the main product and the add-ons and Price Grid is used if the Price Grid returns zero price, this includes sizes greater than the maximum setup in the grid, are considered "not available" so when this happens unexpectedly don't forget to check the Options as well as the main product.
Generic layouts to download
Products
| View file | ||||
|---|---|---|---|---|
|
Extras
| View file | ||||
|---|---|---|---|---|
|
Invoice Extras
| View file | ||||
|---|---|---|---|---|
|