The Excel pricing file can control the properties displayed to the shopper in the Storefront:
· Whether a property is visible or not
· Which property options are displayed
· What is the selected value of the property
For example, selecting a paper type that cannot be laminated sets the lamination property to have No Lamination as the only option, or completely hides the property.
This creates a dependency between the properties, i.e. the selection of one property affects the visibility or display options of another.
The property's options, value and visibility may be controlled by the "Property Output" section in the Excel.
As these may override the property's configuration in the back office, uStore will not allow to change the configuration of a property using the output fields of the Excel, if the property is also set with:
· Sub Property of
· Dependent Dropdown List
· Take Values form Data Source
· File Attachment input control
In a single product, some properties may have the above mentioned configurations and others may use the Property Output in the excel.
In case a pricing Excel is affecting a property that has one of the above definitions applied, an error will be shown in the following cases:
1. When trying to upload the Excel file
2. When trying to save the relevant product
Note that if the Excel is set to the store or product-profile level, you may not see the mentioned error on the product, until going to the misconfigured product's page.
Included Options and Excluded options columns control which options of a multi-option control (e.g. Dropdown list, Gallery list) will be shown in the Storefront.
You can write a formula in these fields that will return the included/excluded options according to the current selections in the ordered product, e.g. quantity and selected property options.
Included Options
Write a formula in the "Included Options" column to return to uStore all applicable options of the property, separated by a semi-colon, and denoted by their option's value (not text). This will filter out all other options that are not listed.
A blank value, returned from a formula or by a cell which
was left blank without a formula, denotes that all the property options
are applicable. You do not need to list them all.
Adding more options for the property via the back office configuration
at a later stage, will carry them over as well when a blank value is returned.
%none% denotes that there are no applicable values, resulting with an error in the Storefront.
Values that are not preconfigured for the property in the back office will be ignored.
The values are case sensitive.
Excluded options
Write a formula in the "Excluded Options" column to list all non-applicable options of the property, separated by a semi-colon, and denoted by their option's value (not text). This will filter out these value.
A blank value or %none% denotes that no options are excluded.
"Exclude Options" works in conjunction with "Included Options", removing the excluded options from the included options list.
If there is a blank value in "Included Options" and a list of values in "Excluded Options", the excluded values will be filtered out and the rest of the options will be applicable.
Values that are not preconfigured from the property in the back office will be ignored.
The values are case sensitive.
A property value set in the Storefront can be overridden by the "Selection" column in the Excel.
To override a value of a property, return the value from a formula.
In case of a multi-option control, the value must be one of the available options, and not excluded by the "Included Options" nor the "Excluded Options" fields.
A Datetime picker control gets a value according to its configuration:
· For a date and time: YYYY-MM-DD hh:mm
· For a date only: YYYY-MM-DD
· For a time only: hh:mm
Note that the Excel needs to return a text representation for the date, and not a date object (which is read as a number). Use the TEXT function as follows: =TEXT(D9, "YYYY-MM-DD hh:mm").
A Checkbox's value is denoted by the values that are assigned for Value When Checked and Value When Unchecked.
The File attachment control is not supported by the "Selection" column.
Note that when an overridden property is set by the Excel
to a specific value, even if the customer changes this value, the Excel
will calculate the same overriding value and the selection will revert
back to the value set by the Excel, to the shopper's surprise. Hence,
you should override values for hidden properties only.
In addition, when shoppers change a value of a text input property to an
invalid value, they will see a validation error message and not the value
coming from the "Selection"column of the Excel (if exists).
The visibility of the property can be controlled by the "Visibility" column in the Excel.
Write a formula that returns 1 in order to show the property, and 0 (zero) to hide it.
Leaving the field blank will take the visibility configuration from the back office.
Excel results are cached for performance improvement.
This may cause inaccuracy when using the NOW() or Today() functions in the Excel, which rely on recalculation of the Excel more often.
You cannot prevent the Excel from being cached, however you can set the purging interval as low as 1 minute using the ExcelCacheExpirationMinutes configuration key.
Customizing the Excel pricing
template
Excel product pricing best
practices