Apply formulas to correct data in price lists
The use of formulas allows you to set logical conditions for checking data stored in fields loaded from price lists and other subsystems in order to set the values you need depending on the conditions.
For example, you can replace the value in the "Price" field based on the "RRP" field, if it is filled in for the product, thereby uploading the value from the "Price" field to the site, taking into account the RRP control. It is also possible to combine different values from different fields, copy values to other fields. The use of formulas is limited only by your imagination and allows you to realize any of your ideas. To open the "Formulas" subsystem, select this menu item in the "Price lists" subsystem for the price list you need.
You can also set a single formula for all price lists, for this you need to select the item on the left "Open products from all counterparties".
The procedure for compiling the formula:
- Select the field for which the formula is being compiled
- Select a field name macro substitution from the list on the right
- Specify logical validation of values (if necessary)
- Set up a filter to apply the formula to certain products only (if needed)
To select the field for which you want to create a formula, click the "+" button. A list of available fields will appear on the screen.
In the list on the right, functions are available for creating logical conditions and correcting data
In formulas, you can use values from product attributes loaded from the price list. For this, a macro substitution of the form {Attribute price: attribute name} is used, for example {Attribute price: Manufacturer}
You can create a global formula for all price lists if you want to apply the same conditions to all products. To do this, open products from all counterparties, then open the "Field Formulas" subsystem
Price rounding.
If the assignment field with the "Price" type is selected for the formula, then the result of the formula is automatically rounded according to the program settings
By default, the value in the "RRP Price" field is not rounded, if it is required to be rounded based on the program settings, then add the formula in this form
Formula examples.
Formula: how to subtract 12 percent from the price
{Price list: Price} - ({Price list: Price} * 12) / 100
Formula: how to get a percentage of profit from the sale price based on the purchase price (cost)
(({Price list: Price} - {Price list: Cost price}) / {Price list: Price}) * 100
Formula: how to specify the RRP if the markup in the price field is higher than the RRP
IF({Price list: Price} > {Price list: RRP price} AND {Price list: RRP price}>0, {Price list: RRP price}, {Price list: Price})
Formula: how to strip leading zeros at the beginning of text
For example, the product SKU in the price list looks like 005799347, the task is to get the value 5799347.
TRIM(LEADING '0' FROM {Price list: Manufacturer SKU})
Formula for getting product values from sellers from the specified row (when receiving data from marketplaces).
Where N is the line number + field name where you want to take the value from
[[RowFromCompetitorTP_N{PriceCompetitorsTP: Price}]]
TRIM(REPLACE({Price list: Category from price}, CONCAT('/ ', {Price list: Manufacturer}), ''))