Apply formulas to correct data in the base product catalog

Applying formulas allows you to set logical conditions for checking the data stored in the fields of the base catalog, fields 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, including product attributes. 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 "Base catalog products" subsystem


The procedure for compiling the formula:

1. Select the field for which the formula is being compiled
2. Select a field name macro substitution from the list on the right
3. Specify logical validation of values (if necessary)
4. 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. The screen will display a list of all available fields in the base catalog, including product attributes. You can create a new field or select an existing one. Each field is highlighted with a specific color.


In the list on the right, functions are available for creating logical conditions and correcting data


Getting values from products from price lists.
When using macro substitutions to obtain data from products from price lists, it is necessary to take into account that the value will be taken from the product from the price list, from which the price for the base catalog product was taken, taking into account the fact that there are several identical products in the prices. If there is only one product, then the relationship is established only with it. That is, if you want to take a value from a product from the price list, then the program will select only one product.
If a product from the base catalog is not matched with a product from the price list and the formula contains a macro substitution of the form {Price list: field_name}, then the value for this macro substitution will be NULL, this should be taken into account when compiling logical conditions, for example:
IF( {Price list: RRP Price}=0 OR {Price list: RRP Price} IS NULL, 0, 1)
in this example, if the RRP from the price is equal to zero or there is no connection with the product from the price (NULL), then the output will be 0, otherwise it will be 1.


Formula examples.

Formula for obtaining product values from the specified counterparty, where N is the ID of the counterparty (specified in the counterparty directory)
[[ОтКонтрагента_N{Price list: Price}]]

Formulas for obtaining product values from the specified account group, where N is the account group ID (specified in the directory of account groups)
[[ОтГруппыКонтрагента_N{Price list: Price}]]
[[ОтГруппыКонтрагента_N MIN({Price list: Price})]]
[[ОтГруппыКонтрагента_N MIN(IF({Price list: Price}=0, NULL, {Price list: Price}))]]

The formula for getting the values of products from price lists from the specified row, if there are several offers of the product in the base catalog.
Where N is the line number + field name where you want to take the value from.
For products from the group of contractors "Suppliers"
[[ОтПоставщикаСтрока_N{Price list: Price}]]
For products from the group of counterparties "Competitors"
[[ОтКонкурентаСтрока_N{Price list: Price}]]

Formula for specifying the exchange rate, where EUR is the code of the required currency
{CURRENCY_RATE_EUR}

Formula for getting values from the main product of an option
[[ОтГлавногоТовара{Товар БК: Наименование}]]

Formula for rounding RRP to a multiple of 10
FLOOR(({BC item: RRP price} + 10-0. 01) / 10) * 10
Price example: 30522. 45, after applying the formula, the price will be 30530.

The CASE function checks the truth of a set of conditions and, depending on the result of the check, can return one or another result.

CASE WHEN условие_1 THEN результат_1 WHEN условие_2 THEN результат_2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . WHEN условие_N THEN условие_N [ELSE альтернативный_результат] END

Example.

CASE WHEN ProductCount = 1 THEN 'Товар заканчивается' WHEN ProductCount = 2 THEN 'Мало товара' WHEN ProductCount = 3 THEN 'Есть в наличии' ELSE 'Много товара' END

The IF function returns one of two values depending on the result of the conditional expression.

IF(условие, значение_1, значение_2)

Example.

IF(ProductCount > 3, 'Много товара', 'Мало товара')

Using values from the manufacturer's reference, macro substitutions are available

• m. name - manufacturer's name
• m. url - link to the manufacturer's website
• m. warranty
• md. description_short - short description
• md. description_full - full description
• md. address - address
• md. address_service - service center address

Curly braces are not required, an example

It is possible to use values from the category directory, macro substitution is available

• c. delivery_cost - delivery cost

Curly braces are not required, an example


New number generator
[[НайтиМаксДобавить1{Товар БК: Артикул (внутренний)}]]

Monitoring the prices of competitors on the Internet

Save a link to this article