↑↓ selectEnter openEsc close

    1. Elbuz

    How to fill price list fields with a formula

    21 min readUpdated

    Elbuz fills in price list fields missing from the supplier's file: describe the value once with a formula, and it's calculated on every import.

    When you need this:

    • Brand and model come in separate columns — the formula joins them into a name the app recognizes between imports.
    • Price must not exceed the RRP — the formula caps it at the RRP.
    • You need a price or text formula — the recipe library has ready-made examples.
    • You need a catalog value — the formula takes it from the matched product, leaving other rows intact.

    Where to start: your first formula in three steps in the “Field formulas” window.

    What formulas can do

    Your first formula in three steps

    Until a formula is selected, the right side of the window shows three steps under the heading “How to set up formulas”. Here they are in more detail.

    Step one: pick a field

    The “Add formula” button opens a field list with a search box (“Search field...”). Pick the field whose value should be calculated — it becomes the formula's target field.

    The list has two kinds of rows: all price list product fields, including the ones you created yourself, and all active product attributes. There are usually far more attributes than fields, so here people use search more often than scrolling.

    The field picker popover with a search box and a list of fields
    1Search field box
    2A field row
    1. Type here to find the field you want to add a formula for.
    2. Each row is a field with its code; clicking a row creates a formula for it.

    The target field is set once. In the formula list, its “Field name” column can't be edited: to calculate a different field, add a new formula and turn off or delete the old one.

    There are no restrictions by field type: the target can be text, a number or a price. The type only affects how the result is rounded — that has its own section.

    Step two: write the expression

    On the right, the editor opens under the heading “Field formula”, next to the selected field's name. The editor has line numbers and highlighting: field names in curly braces have their own color, and an unclosed brace stands out right away.

    Don't type operands by hand. “Fields for formulas” opens a list of all available fields; double-click a row to insert its at the cursor. Or press Ctrl+Space in the editor to see fields matching what you've typed.

    The “?” icon next to the editor shows a short syntax reference: a price list field — {Прайс: Цена}, an expression — {Прайс: Цена} * 1.18, a condition — CASE WHEN … THEN … ELSE … END.

    The formula editor panel for the RRP field
    1Target field chip
    2Fields for formulas
    3Formula text
    4Apply this formula
    1. The chip shows which field the formula writes to.
    2. Opens the reference of fields and macros you can use in a formula.
    3. The formula text with highlighted field macros.
    4. Recalculates this formula only, in all price list rows.

    Formulas have no “Save” button. The formula text goes to the server about a second after you stop typing; toggles and the note are saved immediately. After the first save in an open window, a “Saved ·” mark with the time appears above the editor — that's how you check the change went through.

    Step three: calculate

    A new formula is created already active — you don't need to turn anything on, even though the hint in the window suggests it. The “Active” toggle (it's in the editor and as a column in the list) is for the opposite: to turn a formula off without deleting it.

    While the formula field is empty, the formula is skipped. But once you finish typing an expression, it's saved a second later — and the formula is live: the next price list import will calculate it without asking you. If the expression isn't finished yet and the price list is about to import, turn off “Active”.

    You can calculate right away with two buttons:

    • “Apply formulas” in the window toolbar — all active formulas of this price list;
    • “Apply this formula” in the editor — only the current one.

    While the recalculation runs, the message “Recalculating...” stays on screen. At the end you see “Formulas applied”. If the price list's products tab is open alongside, its grid refreshes by itself and the new values show up without extra clicks; if not, open it to check the result.

    How to manage the formula list

    Besides the field name, the formula list has columns worth knowing:

    • “Note” — text for yourself; doesn't affect calculation. Six months later, it's what explains why the formula exists.
    • “Order” — a number that decides which formula is calculated first.
    • “Active” — a checkbox; turns the formula off without deleting it.
    • “Sheet name” — filled in only for formulas created from the “Import from sheets” window.
    • “Field ID” — the target field's code; you'll need it when looking for a formula by an error message.
    • “Update product uniqueness key” — a checkbox that only makes sense for the name field; see the section on attributes and photos.

    “Note”, “Order” and both checkboxes are edited with a double-click right in the list; “Field name” and “Sheet name” are not. Rows whose target is an attribute are highlighted in blue, so you can spot them without reading names. The trash can in the toolbar deletes a formula, with the usual confirmation, after which “Formula deleted” appears.

    Where a formula gets its values

    Where to start depends on where the value will come from.

    A macro looks like a field name in curly braces with a prefix: {Price list: Price}. The prefix answers the question “where from”.

    PrefixWhat it insertsFields available
    {Price list: …}a field of the same product in the price list102
    {Product: …}a field of the matched product in your catalog171
    {Contractor: …}a field of the card of the supplier who owns this price list156
    {Price list attribute: …}the value of an attribute that came in the price listper the attribute list
    {PriceCompetitorsTP: …}a product field on a marketplaceonly inside a block macro

    The field list opens with the “Fields for formulas” button. It has four columns — “Field”, “Group”, “Macro” and “Code” — and rows are grouped by the “Group” column, so you don't hunt for the price among attributes. Clicking a row shows the field's description at the bottom; double-clicking inserts the macro into the formula.

    The fields reference popover with Field, Group, Macro and Code columns
    1Macro column
    2Group column
    3Hint about double-click
    1. The Macro column shows the text in curly braces that is inserted into the formula.
    2. The Group column shows which group the field belongs to.
    3. The hint above the table explains that a double-click inserts the macro into the editor.

    Old formulas with Russian or Ukrainian field names — {Прайс: Цена} instead of {Price list: Price} — keep working: the app recognizes a name in any enabled language. You don't need to rewrite them. A double-click in the list inserts the name in your interface language.

    Fields of the price list itself and of the supplier card

    The most common case: everything you need is already in the row, just not in the right field. There's a price but no recommended price — calculate it from the price. No name, but brand and model are there — join them. There's an SKU, but with leading zeros — trim them.

    Supplier card fields are available the same way, with the {Contractor: …} prefix. This helps when the value is the same for the whole price list and is already stored in the contact card — then you don't have to repeat it in every formula.

    How to take a value from the catalog, an exchange rate or another price list

    Fields of your catalog's product

    The {Product: …} macro takes a value from the product card in your catalog — the one the price list row is matched with. For example, this is how you put your own manufacturer SKU into the price list instead of the one the supplier sent.

    This works only for rows where with the catalog is already done.

    An unmatched product has empty catalog fields, and the formula will overwrite the target with an empty value.

    How to avoid this — in the section on empty values; how to match products — Matching price list products with the catalog.

    A second condition is mentioned less often: formulas must run after matching, not before. The step order within an import is set in the order of operations. If formulas come before product matching, the {Product: …} macro runs on unmatched rows and writes an empty value, even though matching happens a step later.

    Exchange rate

    The rate is inserted by a separate macro such as {CURRENCY_RATE_USD}, where the last three letters are the currency code from the currency list. In a formula it's an ordinary number you can multiply or divide by.

    If the currency has a rate markup filled in the currency list, the rate is inserted with that markup — not the one the bank shows.

    A value from another supplier's price list

    That's what block macros are for — they're written in double square brackets and contain an ordinary field macro inside.

    • [[FromContractor_N{Price list: Price}]] — takes the value of the same product from the price list of contact number N.
    • [[FromGroupContractor_1 MIN(IF({Price list: Price}=0, NULL, {Price list: Price}))]] — takes a value from a whole contact group. Aggregate functions are allowed inside such a block: this example takes the lowest non-zero price in the group.
    • [[RowFromCompetitorTP_N{PriceCompetitorsTP: Price}]] — the value from the N-th offer on a marketplace, in ascending order.

    All three are in the field list: “Take the value from the specified contact”, “Take the value from a contact group” and “Take the value from a competitor price list row”. The list inserts them in Latin; the Russian spellings ОтГруппыКонтрагента_N and ОтКонкурентаТПСтрока_N work the same way.

    How to keep your price slightly below a competitor's and not below cost — the scenario repricing by competitor prices. How to collect competitor prices into a price list and what it gives your store — competitor price monitoring.

    What you can write in a formula

    A formula is a single expression that gives one value for each price list row. It's not a sequence of steps: if you need two steps, make two formulas and set their order.

    In an expression you can write:

    • field macros in curly braces — {Price list: Price};
    • numbers with a decimal point — 1.25, and text in single or double quotes — 'Sale';
    • arithmetic + - * / %, comparisons and logic (comparison table), CASE WHEN … THEN … ELSE … END;
    • functions from the allowlist — below;
    • comments: -- to end of line, # to end of line and /* … */. They're handy for labeling CASE branches and don't affect calculation.

    Templates of the most common functions are in the “Fields for formulas” list: a double-click inserts them into the editor. Ready-made recipes are grouped by task: price, name and description, conditions, numbers, text.

    Full function allowlist
    GroupFunctions
    NumbersROUND, CEIL, CEILING, FLOOR, TRUNCATE, MOD, ABS, SIGN, GREATEST, LEAST, POW, POWER, SQRT, EXP, LN, LOG, LOG10, LOG2, PI, RAND, trigonometry (SIN, COS, TAN…)
    ConditionsIF, IFNULL, NULLIF, COALESCE, ISNULL, CAST, CONVERT
    TextCONCAT, CONCAT_WS, REPLACE, SUBSTRING, SUBSTR, SUBSTRING_INDEX, MID, LEFT, RIGHT, TRIM, LTRIM, RTRIM, LOWER, UPPER, LCASE, UCASE, LENGTH, CHAR_LENGTH, LOCATE, INSTR, POSITION, LPAD, RPAD, REVERSE, REPEAT, SPACE, INSERT, FORMAT, FIELD, ELT, FIND_IN_SET, CHAR, ASCII, ORD, STRCMP, SOUNDEX, QUOTE, HEX, UNHEX, MD5, SHA1, SHA2, CRC32, TO_BASE64, FROM_BASE64
    Regular expressionsREGEXP_REPLACE, REGEXP_SUBSTR, REGEXP_INSTR, REGEXP_LIKE
    DateNOW, CURDATE, CURTIME, DATE, YEAR, MONTH, DAY, DAYOFWEEK, WEEKDAY, WEEK, DAYNAME, MONTHNAME, LAST_DAY, DATE_FORMAT, DATE_ADD, DATE_SUB, DATEDIFF, TIMESTAMPDIFF, UNIX_TIMESTAMP, FROM_UNIXTIME, STR_TO_DATE and other calendar functions
    JSONJSON_EXTRACT, JSON_UNQUOTE, JSON_OBJECT, JSON_ARRAY, JSON_LENGTH, JSON_CONTAINS
    AggregateMIN, MAX, AVG, SUM, COUNT, GROUP_CONCAT — only inside a block macro

    What you can't write

    A formula with any item below doesn't run: the field doesn't change, the other formulas are applied, and this one lands in the “Formula errors” window and as a “Formula skipped…” line in the “Operation log”.

    • Functions outside the allowlist and the words SELECT, UNION, INTO, VALUES, TABLE, WITH — the message names the disallowed fragment.
    • The characters ;, backticks, @, [, { outside quoted text. The app doesn't recognize a macro with a typo in the field name, so it stays in the expression with its curly braces — that's why a field name typo also shows up as a disallowed fragment “{Price list: …”.
    • Aggregate functions directly in the expression — MIN, MAX, SUM. A formula is calculated for each row separately; there's nothing for it to aggregate.
    • A decimal comma and semicolons between arguments, as in Excel: 1,1, IF(a; b; c).

    Text in quotes isn't checked, so CONCAT({Price list: Name}, " (select)") works.

    Ready-made price formulas

    Paste any recipe as is, with your own field names. Unless stated otherwise, the target is the “Price” field. Markup by price range or category is easier with pricing rules; a formula is for what rules can't express.

    A price field result is rounded again: to the kopeck, or to a whole number with “Round up” or “Round down” on. Recipes with kopecks (a price ending in .99) work only with those toggles off.

    Markup by price range

    Cheap products go up 30%, mid-range 20%, expensive ones 10%.

    CASE WHEN {Price list: Price} < 500 THEN {Price list: Price} * 1.3 WHEN {Price list: Price} < 2000 THEN {Price list: Price} * 1.2 ELSE {Price list: Price} * 1.1 END

    Result: 400 → 520, 1,000 → 1,200, 5,000 → 5,500. Branches are checked top to bottom; the first match wins.

    Lower the price by 12%

    {Price list: Price} - ({Price list: Price} * 12) / 100

    Result: 100 → 88.

    Price ending in .99

    CEIL({Price list: Price}) - 0.01

    Result: 248.30 → 248.99. Rounded up to a whole number, minus one kopeck.

    Price ending in 9 hryvnias

    FLOOR({Price list: Price} / 10) * 10 + 9

    Result: 248 → 249, 251 → 259. The price rises by at most UAH 9 and drops only if it already ends in 9 plus kopecks: 259.50 → 259. On cheap products +UAH 9 is noticeable — check the cheapest item in the price list.

    Price ending in 99 hryvnias

    FLOOR({Price list: Price} / 100) * 100 + 99

    Result: 1,234 → 1,299.

    Round to tens

    ROUND({Price list: Price}, -1)

    Result: 1,234.50 → 1,230. A negative second parameter rounds left of the point: −1 to tens, −2 to hundreds.

    Minimum margin over cost price

    At least cost price plus 15%.

    GREATEST({Price list: Price}, {Price list: Cost price} * 1.15)

    Result: price 100 and cost 95 → 109.25; price 200 and cost 95 → 200. If cost price is empty, see empty value.

    Don't sell above the recommended price

    IF({Price list: Price} > {Price list: RRP} AND {Price list: RRP} > 0, {Price list: RRP}, {Price list: Price})

    Result: price 1,100 and RRP 999 → 999; price 900 and RRP 999 → 900; RRP 0 → the price stays.

    RRP if there is one, otherwise purchase price plus markup

    IF({Price list: RRP} > 0, {Price list: RRP}, {Price list: Price} * 1.25)

    Result: RRP 999 → 999; RRP 0 and price 800 → 1,000.

    RRP minus 5%, otherwise the regular price

    CASE WHEN {Price list: RRP} > 1 THEN {Price list: RRP} - ({Price list: RRP} * 5) / 100 ELSE {Price list: Price} END

    Result: RRP 1,000 → 950; RRP 0 → the regular price. The > 1 check matters: most products usually have no RRP, and their price would become zero.

    Convert the price at an exchange rate

    {Price list: Price} * {CURRENCY_RATE_USD}

    Result: $25 at a rate of 41.50 → 1,037.50. The rate comes from the currency list, rate markup included (exchange rate).

    A price list entirely in dollars needs no formula: set the currency on the “Price” column — price list in dollars. A formula on top of it applies the rate twice.

    Cost price plus 4%, to a whole number

    ROUND({Price list: Cost price} * 1.04, 0)

    Result: 1,000 → 1,040.

    Profit percentage

    The target is a number field, not a price.

    (({Price list: Price} - {Price list: Cost price}) / {Price list: Price}) * 100

    Result: price 150 and cost 100 → 33.33. A price of 0 divides by zero, and the field becomes zero.

    Ready-made formulas for name and description

    Build a name from manufacturer and model

    CONCAT_WS(' ', {Price list: Manufacturer}, {Price list: Model})

    Result: “Samsung” and “A55” → “Samsung A55”. The first parameter is the separator between parts. If one of the columns is empty, the separator doubles: “Samsung A55” with two spaces.

    Replace a word in the name

    REPLACE({Price list: Name}, 'Smartphone', 'Phone')

    Result: “Smartphone Samsung A55” → “Phone Samsung A55”. The replacement is case-sensitive: lowercase “smartphone” won't change.

    Trim the name to 60 characters

    LEFT({Price list: Name}, 60)

    Result: the first 60 characters of the name; the rest is dropped. Shorter names don't change.

    Add a prefix or suffix

    CONCAT('Sale! ', {Price list: Name}) or CONCAT({Price list: Name}, ' (pre-order)')

    Result: “Samsung A55” → “Sale! Samsung A55” or “Samsung A55 (pre-order)”. Running it again adds the prefix again — narrow the formula with a condition.

    Strip HTML tags from a description

    The target is “Short description” or “Full description”.

    REGEXP_REPLACE({Price list: Full description}, '<[^>]*>', '')

    Result: “<p>Screen <b>6.6"</b></p>” → “Screen 6.6"”.

    Remove extra spaces

    TRIM(REGEXP_REPLACE({Price list: Name}, ' +', ' '))

    Result: “ Samsung Galaxy A55 ” → “Samsung Galaxy A55”.

    Remove leading zeros from an SKU

    TRIM(LEADING '0' FROM {Price list: Manufacturer SKU})

    Result: “000123” → “123”.

    Remove the brand name from the supplier's category

    TRIM(REPLACE({Price list: Category from price list}, CONCAT('/ ', {Price list: Manufacturer}), ''))

    Result: “Smartphones / Samsung” → “Smartphones”.

    Copy the SKU from the catalog card

    IF(IFNULL({Product: Manufacturer SKU}, '') = '', {Price list: Manufacturer SKU}, {Product: Manufacturer SKU})

    Result: matched products get the SKU from the catalog; the rest keep their own (why IFNULL is needed).

    Conditions: how to write “if … then …”

    A condition is written in SQL, not Excel: parts are separated by commas, text goes in single quotes, and there's no = sign at the start of the formula.

    Two options: IF

    IF({Price list: Quantity} > 0, 'In stock', 'Pre-order')

    Result: quantity 5 → “In stock”, 0 → “Pre-order”.

    Several options: CASE WHEN … THEN … ELSE … END

    CASE {Price list: Manufacturer} WHEN 'Apple' THEN '12 mo.' WHEN 'Samsung' THEN '24 mo.' ELSE '6 mo.' END

    Result: for the “Warranty” field, Samsung → “24 mo.”, any manufacturer other than Apple → “6 mo.”. Without ELSE, products that match no branch get an empty value.

    Comparisons

    What to checkHow to write it
    equal / not equal{Price list: Manufacturer} = 'Samsung', <> 'Samsung'
    greater, less>, <, >=, <=
    in a range{Price list: Price} BETWEEN 1000 AND 5000 — bounds included
    contains text{Price list: Name} LIKE '%Pro%'
    one of the values{Price list: Manufacturer} IN ('Apple', 'Samsung')
    several conditionsAND — both, OR — at least one, NOT — negation

    A condition inside a condition

    IF({Price list: Price} > 1000, IF({Price list: Quantity} > 0, 'Expensive, in stock', 'Expensive, pre-order'), 'Budget')

    Result: price 1,200 and quantity 5 → “Expensive, in stock”; price 500 → “Budget”. More than two levels is hard to read — use CASE WHEN with conditions joined by AND instead.

    A fallback value: IFNULL and COALESCE

    COALESCE(NULLIF({Price list: RRP}, 0), {Price list: Price})

    Result: RRP 899 → 899; RRP 0 → the regular price. NULLIF turns zero into a missing value, and COALESCE takes the first available one from the list. IFNULL(x, fallback) does the same for two values.

    Number and text functions

    Numbers

    FunctionWhat it doesExample
    ROUND(x, n)rounds to n decimal places; a negative n rounds to tens, hundredsROUND(1.298, 1) → 1.3; ROUND(1234.5, -1) → 1230
    CEIL(x)up to a whole numberCEIL(1.23) → 2
    FLOOR(x)down to a whole numberFLOOR(1.23) → 1
    TRUNCATE(x, n)drops digits after the n-th without roundingTRUNCATE(1.2100743, 2) → 1.21
    GREATEST(a, b, …)the largest value — “no less than”GREATEST({Price list: Price}, 10): 3 → 10
    LEAST(a, b, …)the smallest value — “no more than”LEAST({Price list: Price}, 1200): 1500 → 1200
    ABS(x), MOD(x, y)absolute value, remainder of divisionMOD(17, 5) → 2

    The decimal part is written with a point: 1.1. With a comma, 1,1, the app reads two numbers, and the formula ends up in “Formula errors”.

    Text

    FunctionWhat it doesExample
    CONCAT(a, b, …)joins valuesCONCAT('Samsung', ' ', 'A55') → Samsung A55
    CONCAT_WS(separator, a, b, …)joins with a separator, skips missing valuesCONCAT_WS(', ', 'Black', '256 GB') → Black, 256 GB
    REPLACE(text, from, to)replaces all occurrences, case-sensitiveREPLACE('A55 5G', ' 5G', '') → A55
    SUBSTRING(text, start, length)a piece of text from a positionSUBSTRING('iPhone 15 Pro', 8, 2) → 15
    LEFT(text, n), RIGHT(text, n)the first or last n charactersLEFT('Samsung', 3) → Sam
    SUBSTRING_INDEX(text, delimiter, n)text up to the n-th delimiterSUBSTRING_INDEX('Samsung Galaxy A55', ' ', 1) → Samsung
    TRIM, LTRIM, RTRIMremove spaces at the edgesTRIM(' A55 ') → A55
    UPPER, LOWERuppercase or lowercaseUPPER('ab-12x') → AB-12X
    CHAR_LENGTH(text)number of charactersCHAR_LENGTH('Ціна') → 4
    LOCATE(substring, text)position of a fragment, 0 if not foundLOCATE('Pro', 'iPhone 15 Pro') → 11
    REGEXP_REPLACE(text, pattern, replacement)replacement by regular expressionsee stripping HTML

    LENGTH counts bytes, not characters: for Cyrillic you get twice as much — LENGTH('Ціна') gives 8. For name length, use CHAR_LENGTH. Also: CONCAT with a missing value returns a missing value as a whole — where a part may be missing, use CONCAT_WS.

    How to narrow a formula with a condition

    Below the editor there's a collapsible block, “Apply condition (optional)”. It holds a condition builder on price list product fields: “name contains”, “price greater than”, “SKU starts with” and so on. The formula is calculated only for rows that match the condition.

    Several condition rows are joined with “OR” by default — any one of them is enough. You can switch this in the builder itself. Below the condition, the app restates it in plain words: before saving, read that summary and make sure it says what you meant.

    Expanded “Apply condition (optional)” block with an empty condition builder
    1Condition builder
    2Save
    1. The condition builder: add rules and groups combined with AND / OR.
    2. Save — stores the condition for the formula; not pressed.

    At the bottom of the block are “Clear condition” and “Save condition”; after saving, “Updated” appears. When you select a formula that has a condition, the block expands by itself.

    The condition is stored separately from the formula text, and “Clear condition” alone doesn't remove it: the button only clears the builder on screen. The empty condition reaches the formula only after “Save condition”.

    A condition is the cheapest risk-free way to try a new formula. Limit it to one SKU, click “Apply this formula”, check the result in the product grid, and only then remove the limit.

    When a formula is calculated

    Finishing a formula isn't one of these moments: editing it alone doesn't change product values.

    1. On every price list import — while the “Applying formulas to fields” step is in the price list's operations. A new price list has it, but the step can be removed, and then formulas don't run on import (order of operations).
    2. With a button in this window. “Apply formulas” — all active formulas of the price list; “Apply this formula” — only the selected one.
    3. Together with applying markup manually. “Apply markup to products” also runs formulas after recalculating prices — but only those targeting a price or a number. Text formulas are skipped.

    A formula overwrites the field, and the previous value isn't kept anywhere. There's no rollback: only a new price list import brings the original data back. Run a new formula on one product first.

    Order of application

    Within one run, formulas go by the “Order” column. This matters when one formula uses another's result: if a built name feeds a formula that derives an SKU from it, the first needs the lower order.

    The queue:

    1. formulas of this price list — by the “Order” column;
    2. formulas tied to an Excel sheet — only if the price list has per-sheet settings turned on; otherwise they aren't part of the run, and there's no message about it;
    3. formulas for all price lists — always last.

    Before the first formula of a run, the app refreshes the row's service values — category name, supplier and its group, stock status. So macros like {Price list: Category} already see fresh values.

    What happens to each formula in a run. The first fork is the syntax check: a broken formula is skipped, the rest go on. The second applies only to the name field.
    no formulasthere are formulasnoyesyesnoyesnoStart — the “Applying formulas tofields” step or a button in the windowFormulas are selected — “Active” ison and the expression isn't emptyEnd of runService values of price list rows arerefreshedNext formula by the “Order” columnDid the formula pass the syntaxcheck?Formula skipped — a line in the log;on a manual run it appears in the“Formula errors” windowThe value is written to rows thatmatch the “Apply condition”a price field is rounded at this pointIs the target the name field with“Update product uniqueness key”checked?The uniqueness key is recalculatedMore formulas?
    • Start or end
    • Action or result
    • Decision: the path depends on the answer

    Clicking “Apply formulas” a second time calculates from what's already calculated. The formula {Price list: Price} * 1.1 written to the “Price” field raises the price by 10% on every click: 100 → 110 → 121 → 133. Unlike markup, formulas have no “apply once” protection.

    If a formula reads and writes the same field, click the button once and check the result instead of clicking again “just in case”.

    Also, write decimals with a point. With a comma — * 1,1 — the app reads two numbers, and the formula never runs and lands in “Formula errors”.

    Formula vs. markup: which runs last

    If both a formula and a pricing rule write to the same field, whichever runs last wins. The formula step can go before or after product repricing; see the order for your price list in the “Order of operations” window.

    More: order of operations during price list import and pricing rules.

    “Product markup history” shows how pricing rules built the price. Formulas don't appear there — they're a separate mechanism with no history of their own.

    When the calculated value reaches your catalog

    A formula writes only to price list rows: recalculation doesn't change the product card in your catalog. The value reaches the catalog during “Updating base catalog”, and only in fields whose source in “Field links” is this price list field.

    You can run the update with the “Update” button in the update window or wait for the scheduled run (when the update runs).

    If the price list grid shows the new value but the product card the old one, either the catalog update hasn't run since, or the target field isn't a source in “Field links”.

    How to set a formula for all price lists

    If the same formula should apply to all suppliers, you don't need to add it to every price list. There's one way in: in the price lists window, select “All sources” on the left, open the “All products” tab and open “Field formulas” from the menu above the grid (tooltip “Additional Operations”).

    The sign that you're in this mode is a red line below the toolbar: “The settings for all price lists are used.” It's not an error or a warning that something broke; it's a reminder that changes here apply to all sources.

    Field formulas window in the mode for all price lists, with the notice line
    1All price lists notice
    2Apply formulas
    1. The notice that the settings for all price lists are used.
    2. Apply formulas — in this mode it runs over every price list; not pressed.

    These formulas run after the formulas of a specific price list. If they write to the same field, the global one wins: the price list formula's result is overwritten. This is the most common cause of “the formula is correct, but the value is someone else's”.

    In this mode, the “Apply formulas” button goes through all active price lists of all active contacts one by one. What you see while the recalculation runs, and what happens if it breaks off, is in the section on breaks and duration.

    What happens if a recalculation breaks off

    What's left in the price list after a break

    Formulas run one after another, and each formula writes an ordinary field to all price list rows that pass its condition at once. So after a break, the formulas that managed to run are written in full, and the following ones aren't run at all.

    Three targets are written in several steps, and a break between them leaves part of the work done:

    • an attribute — first, empty rows of this attribute are added to the price list products, then the value is written into them;
    • “Link to product photo” — first the photo list is updated, then new links are added, duplicates are removed, and only then is the field itself written;
    • the name with the “Update product uniqueness key” checkbox — first the name is written, then the key is recalculated.

    In “All sources” mode, price lists are processed one by one. A break leaves the price lists already processed recalculated, and the rest not. A manual recalculation writes to the “Operation log” only lines about formulas skipped because of errors, so you can't see there which price list it reached.

    In the window, a break looks like this: the “Recalculating...” message disappears, the “Apply formulas” button is active again, and instead of “Formulas applied” you see a red “Formulas were not applied: the server did not respond or returned an error. Try again; if it happens again, check the operations log.”

    Is it safe to click again? Clicking again calculates all active formulas from the start, including the ones that had already run before the break.

    • If a formula takes values only from other fields (for example, joins a name from brand and model), a repeat writes the same result.
    • If a formula reads and writes the same field, a repeat calculates it a second time from the calculated value, as in the price example above. In that case, instead of clicking again, import the price list again: the import takes values from the file, and formulas in the “Applying formulas to fields” step are calculated on them once.

    What you see while the recalculation runs

    There's no limit on the number of rows or price lists, and the recalculation isn't split into batches. It runs right away while the window is open, not in a background queue. In “All sources” mode, each price list in turn gets its price list formulas, sheet formulas and formulas for all price lists.

    The “Recalculating...” message stays until the server responds. Another sign that the recalculation is running is the inactive “Apply formulas” button with an indicator next to it: you can't click it again until the recalculation finishes. The end of the recalculation is the “Formulas applied” message or the “Formula errors” window.

    What happens to the result: attributes, photos, name, rounding

    Two target fields behave differently from the rest, and it's worth knowing before a formula runs. This also covers a checkbox in the formula list that matters for only one field.

    Product attribute

    When the target is an attribute, the value is written to the product's attribute, not to its row. If the product didn't have that attribute yet, the app creates it — so the formula doesn't just fill in a value, it also adds an attribute the supplier never sent.

    A formula doesn't create a new attribute in the attribute list: you can only pick an existing one as the target. Two more conditions:

    • if the attribute has no name in the interface language, the formula fills it only in the price list products that already had it;
    • the attribute is added only to products that pass the formula's apply condition.

    This is price list data: it reaches your catalog's cards like other price list fields — through the base catalog update.

    Photo link

    The “Link to product photo” field is processed twice: the result goes both into the field itself and into the product's photo list. After that, identical links are removed, so running it again won't create duplicates of one image.

    The “Update product uniqueness key” checkbox

    This checkbox is in every row of the list, but works only for the name field. For other fields it does nothing.

    Why it exists: the app uses the product name to recognize that it's the same product that came last time. If a formula rewrites the name, the recognition key “drifts”, and on the next import the same product is treated as new. The checkbox makes the app recalculate the key right after the formula — and recognition doesn't break.

    The window hint says it plainly: “The product uniqueness key is recalculated only from the “Name” field formula — on other fields this checkbox changes nothing.” Without it, the key stays based on the name from the file. Turn it on with the formula and don't toggle it on a live price list. Catalog field formulas have the same checkbox.

    How the result is rounded

    Only price fields are rounded — by the same “Round up” and “Round down” toggles as the price after markup; with both off, to the kopeck. The settings are shared: changing them for a formula changes the final prices of all price lists. More: Price rounding and rounding step.

    Text fields and fractional number fields that aren't prices aren't rounded at all. If you need rounding in such a field, do it with the ROUND function inside the formula.

    How not to wipe a field with an empty value

    Not all empties are equal, and the difference here costs data.

    • An empty price list column is an empty string or zero. Joining with it survives: CONCAT({Price list: Manufacturer}, ' ', {Price list: Manufacturer SKU}) for a product without a manufacturer gives the SKU with a leading space.
    • No value at all — the product isn't matched with the catalog, there was division by zero, a block macro found nothing. Then the whole expression becomes empty, however many other operands it has.

    Such a result doesn't leave the field as it was — it wipes it. A number or price field gets zero, a text field gets an empty string. Division by zero behaves the same: no error, just zero in the field.

    This most often happens with a {Product: …} formula on a price list where not all products are matched. Matched ones get the catalog value, and all others get zero or an empty string instead of what they had. And “what they had” is gone: formulas have no rollback.

    The protection is a check inside the formula, but it has to be written carefully. The obvious option is “if empty, take it from the price list”:

    IF({Product: Manufacturer SKU} = "", {Price list: Manufacturer SKU}, {Product: Manufacturer SKU})

    Don't do this. This check saves only a matched product whose catalog field is empty. For an unmatched one, the value isn't empty but missing, and the comparison gives neither “yes” nor “no”: the formula goes to the second branch and takes the catalog value again. The recipe silently wipes exactly the rows it was written for.

    The working version differs by one wrapper — IFNULL, which substitutes an empty string for a missing value:

    IF(IFNULL({Product: Manufacturer SKU}, "") = "", {Price list: Manufacturer SKU}, {Product: Manufacturer SKU})

    It reads: if the catalog has no SKU or it's empty, keep the SKU from the price list; otherwise take it from the catalog. A run on a price list of 160 items with 64 matched: the first version wiped 96 rows, the second none.

    Prices don't need the wrapper — there you guard against something else: a > 1 or > 0 check before dividing by that number.

    How to check a formula

    The window has no separate preview: you won't find a “Check” button or a test grid with the result here. Instead, there's this.

    Syntax check on apply. Before calculating anything, the app checks that the formula is written correctly. A formula with an error is simply skipped — the other formulas are still applied, and you get the “Formula errors” window listing what didn't work.

    Explanation from the AI assistant. The magic wand icon in the window toolbar opens the assistant with a question about a formula for the selected field. The errors window has an “Ask the AI assistant” button — it sends all broken formulas as one question and asks what's wrong and for corrected versions.

    Neither button calculates anything in the price list: they open the AI consultant window and send the text right away, without a separate confirmation. The consultant's reply is paid, like any of its replies in chat; charges are shown in your balance.

    Limiting the scope. The apply condition (section above) lets you run the formula on one product first.

    Checking the result. If the price list's products tab is open alongside, its grid refreshes by itself after applying.

    Only correct syntax is checked, not meaning. A formula where you mixed up price and cost price passes the check and is calculated — and you'll see it only in product prices. So always do the first run on a limited scope.

    Testing a new formula on one product

    You can write and test a formula in chat. Select a price list, click “✦ AI” in its header → “Price list field formula” (visible to those who have access to the price list formulas window) and describe what the field should calculate.

    The consultant runs the formula through the same mechanism as the “Apply” button without writing anything, and gives you the finished formula in a separate block with a copy button; you paste it into the formulas window yourself. Details — in the article on the AI consultant.

    Scenarios

    The supplier doesn't send a ready-made name

    Task. Manufacturer and SKU come in separate columns in the file, and there's no full product name.

    What to set up. “Add formula” → the “Name” field, expression CONCAT({Price list: Manufacturer}, ' ', {Price list: Manufacturer SKU}). In the formula list, check “Update product uniqueness key” in this row.

    What you get. The name is built in price list rows on every import, as long as the “Applying formulas to fields” step is in the price list's operations. After writing the name, the app recalculates the rows' uniqueness key, so on the next import the product is recognized as the same one.

    Your catalog's product card doesn't change: the name gets there only through “Updating base catalog”, if the field is set as a source in “Field links”.

    Don't sell above the recommended price

    Task. The supplier sends both a price and an RRP, and the price in the price list must not be higher than the RRP.

    What to set up. A formula on the “Price” field from the recipe “Don't sell above the recommended price”.

    What you get. In rows where the RRP is filled in and lower than the price, the price becomes equal to the RRP; where the RRP is zero or the price is already lower, it stays as is. The result is rounded as a price.

    Since the target is a price, the formula also runs after the “Apply markup to products” button. Its work won't be visible in “Product markup history”.

    Your catalog SKU instead of the supplier's SKU

    Task. The price list needs the manufacturer SKU from your product card, not the one the supplier sent.

    What to set up. A formula on the “Manufacturer SKU” field from the recipe “Copy the SKU from the catalog card”. In the order of operations, the formula step must come after product matching.

    What you get. Matched rows get the SKU from the catalog card; unmatched ones keep the SKU from the file. The formula doesn't match products itself: a row without matching stays unlinked to the catalog. Without the IFNULL wrapper, unmatched rows would lose their SKU — why is explained in the section on empty values.

    One rule for all suppliers

    Task. Leading zeros in SKUs need to be removed in all price lists, without adding the same formula to each one.

    What to set up. Open formulas in the mode for all price lists and add a formula on the “Manufacturer SKU” field from the recipe “Remove leading zeros from an SKU”.

    What you get. The formula runs on every price list import — after the price list's own formulas. The “Apply formulas” button in this mode goes through all active price lists of active contacts. This formula isn't visible in a single price list's formulas window, and it overwrites a price list formula on the same field.

    What to do when a value wasn't calculated

    Common problems
    What you seeWhyWhat to do
    “Formulas applied”, but product values are the same Most often: products missed the “Apply condition”; a formula for all price lists overwrote the result; or the formula is tied to a sheet with per-sheet settings off. Less often, “Active” was turned off: new formulas start enabled Check in exactly this order (when a formula is calculated)
    The field became zero or empty A value was missing: unmatched product, division by zero, or a block macro found nothing Wrap the catalog field in IFNULL; plain IF(field = "") doesn't help; re-import the price list to restore the old value (empty value)
    The “Formula errors” window with a list of fields At least one formula failed the syntax check Fix those listed — the rest are applied; for help, click “Ask the AI assistant” (checking a formula)
    In the log: “Formula skipped: field “…”, rule ID … (…). Check the formula syntax — the remaining formulas have been applied.” Same syntax error, caught on import In this price list's “Field formulas”, find the rule by the number from the message
    “Formula skipped: field “…”, rule ID … (…). It contains a disallowed fragment “SELECT”…” A word triggered the protection: a SELECT subquery or a database service function. Allowed are fields, numbers, quoted text, operators and functions like IF, CASE, CONCAT, ROUND, REPLACE Find the rule by ; the parentheses show whose formula it is: “price list formula”, “global formula, applied to the price list” (all price lists mode) or “catalog product field formula” (catalog formulas). Rewrite it without the forbidden word (what you can write)
    The error message says “global formula, applied to the price list” Broken — checked on every price list, so it repeats in each Edit it in all price lists mode, not in this source's window (formulas for all price lists)
    The error message ends with “sheet “…”” The formula is tied to an Excel sheet and hidden in the regular formulas window Open formulas from “Import from sheets” for that sheet (ways in)
    The price grows with every click of “Apply formulas” The formula reads and writes the same field, so it builds on its own result Write to another field or click once per run (order of application)
    A formula with {Product: …} worked only on some products The other rows aren't matched, so they have no catalog fields Match the products and repeat; meanwhile, wrap the formula in IFNULL (empty value, product matching)
    “Recalculating...” stays on for a long time It covers every product of the price list, or of every active source in all price lists mode Wait; next time narrow it with a condition (apply condition)
    A red line “The settings for all price lists are used.” Opened from the “All products” tab, in all price lists mode For one price list's formulas, open the window from its card (formulas for all price lists)
    A red “Error:” message with text Not saved — connection dropped or session expired Click “Refresh”, repeat the change, watch the “Saved ·” mark
    “You have no access to this window. Contact your administrator.” after “Apply formulas” The “Field formulas” permission was removed while the window was open: nothing was calculated Ask an administrator to restore it (who has access)
    The formula was there, but it's not in the list Sheet formulas show only when opened from “Import from sheets”; formulas for all price lists — only in that mode Open the window the way you created the formula (ways in)

    FAQ

    Where's the “Save” button for formulas?

    There isn't one. The formula text saves itself about a second after you stop typing, and toggles and the note save immediately. The “Saved ·” mark with the time above the editor shows that the change went through.

    I changed a formula — why are product values the same?

    The formula and the values live separately. Recalculation happens on a price list import or with a button: “Apply formulas” — all active formulas, “Apply this formula” — just one (when a formula is calculated).

    The formula was skipped with “It contains a disallowed fragment “SELECT””. What's wrong?

    The expression has a subquery, a function outside the allowlist or an unrecognized macro, and the protection kicked in. Only this formula was skipped; the rest were applied. Find the rule by the ID in the message and fix the fragment (what you can't write).

    Do old formulas with Russian field names need rewriting?

    No. {Прайс: Цена} and {Price list: Price} work the same: the app recognizes a name in any enabled language (macros).

    How do I get the lowest price among several suppliers?

    With a block macro for a contact group: MIN and other aggregate functions are allowed inside it (a value from another supplier's price list).

    Can a formula fill in a product attribute?

    Yes, attributes are in the list of target fields alongside regular fields. If the product didn't have that attribute, the app creates it (attributes and photos).

    If I change the name with a formula, is the product uniqueness key recalculated?

    Yes, if the “Update product uniqueness key” checkbox is on for this formula. Without it, the key stays calculated from the name in the file, and on the next import the product is treated as new (the checkbox).

    Can I see what value the field had before the formula?

    No: a formula overwrites the field and doesn't keep values. What's kept are versions of the formula text itself — the “Formula change history” button in the window toolbar, which works as in catalog formulas.

    Where to find it and who has access

    The window has no menu item or shortcut: it opens with a button from five places. All of them lead to the same window; the only difference is which price list it opens for.

    From the list of price lists
    select a source in the list on the left → the menu in the source header → the “Prices and markups” group → “Field formulas”
    From the price list card
    the “Additional” section → the “Field formulas” button
    From the card's “Columns” section
    the “Formulas” button in the toolbar above the field grid
    From the price list's products tab
    the menu above the grid, tooltip “Additional Operations” → “Field formulas”. This is also the only way into formulas for all price lists at once
    From the “Import from sheets” window
    the sheet's operations menu → “Field formulas”. Formulas created from here are tied to that sheet
    Access rights
    the “Field formulas” permission in group permissions — covers viewing, editing and applying together
    Prepare
    a price list that has been imported at least once, with column addresses set up

    A formula takes values from fields that have already been parsed, not from the raw file. There are no finer permissions inside the window and no separate module: formulas are available on any plan.

    Price list source header with the burger menu open and the “Prices and markups” group expanded
    1Field formulas
    1. Field formulas — the menu item in the “Prices and markups” group that opens the formulas window.

    The window is split in two: on the left, this price list's formula list; on the right, the editor for the selected formula. You can drag the border between them with the mouse — the width is remembered for next time.

    The Field formulas window with the toolbar, the list of formulas on the left and the editor panel on the right
    1Add formula
    2Apply formulas
    3List of formulas
    4Divider between the list and the editor
    1. Add formula — opens the field picker to create a formula for a field.
    2. Apply formulas — recalculates the fields in all rows of the price list.
    3. The list on the left shows the fields that already have a formula.
    4. Drag the border between the list and the editor with the mouse to resize them.

    Related topics

    Was this article helpful?

    Missing a feature? Tell us.

    We will add it for free if other users need it too.

    If only you need it, we will agree on it separately.

    Article contents

    Suggest an improvement

    Try Elbuz free