Sending Configurations via Spreadsheet

Like the Formatted Price Files for OASIS, a simple spreadsheet file may be used to move configuration information from a manufacturer to a sales agency. This document defines the file formatting.

Please note that the file format does not take advantage of all the OASIS series features

The File

Version of OASIS

OASIS V2.6.3 W655A1.

File Format

The file must be formatted as a Microsoft Excel .xls, Microsoft Excel .xlsx, or ASCII text file in tabular format. 

Line 1: Instruction line (typically "Configurations”)

The remainder of the file contains instructions for creating the price file

Columns

Each line is composed of a required 11 columns

• A - The instruction as defined below
• B - Level used to group like attributes (whole number only)
• C - The attribute or segment of the catalog number
• D - Description of the attribute (No special characters allowed)
• D - Sell price (typically lowest authorized for sale) (number and decimal only; no $ signs)
• F - Sell commission (whole number only; 10 represents 10%)
• G - Overageable price - the price where overage may be added to the product (number and decimal only; no $ signs)
• H - Overageable Comm - The commission rate at the overageable price (whole number only; 12 represents 12%)
• I - Overage split - the amount of the overage kept by the sales agency (whole number only; 90 represents 90%)
• J - Effective end date - when the price must be refreshed
• K - Notes (ignored by the process).

Note: any new lines/carriage returns, or special characters in a field can cause the process to fail.  Use plain ASCII text only.

Instructions

Instructions include:

• Products - used to create a sections of products defined by a series an may have common attributes.
• Series - a product series and must have a unique set of characters beginning the series catalog number
• Attribute - a required attribute (e.g. finish, color temp, etc)
• Option - an attribute that may be optionally selected or not (e.g. an emergency ballast).
• Dash - a dash formatting character for one level in the catalog number.

Each Product instruction may enclose one or more Series definitions. Any attributes and options are shared between the series when created.

Mixing of attributes, options or dashes at the same level is not allowed.

Example

Consider the following spreadsheet. Note the instructions in the first column and the Level values being used to group like attributes. The pricing information is fully implemented only on the series instruction. The pricing may be used or omitted on the attribute or option instruction.

Configuration Spreadsheet.xlsx (40 KB download)

How Agents upload the file once they receive it

The file is loaded by selecting the Manufacturers -> Pricing, then selecting Tools -> Update Configuration (with file):

Here is an example of the SCL2 configuration. (The SCL series is similar as it was created within the same product group.)

Removal of a series

This is a relativity simple process. To remove an old series, simply list it with an ending date prior to "today". The Update will automatically remove any series with the effective end date prior to today.

Article attachments: