↑↓ selectEnter openEsc close

    1. Elbuz

    How to set up price list columns for customers

    12 min readUpdated

    Elbuz builds a price list for customers and dealers from columns set once: name, price, stock, attributes, or your own formula. Values come fresh from the catalog.

    When you need this:

    • A customer wants their own columns — add them from the field palette, reorder with arrows.
    • No ready-made field — a formula returns the RRP or regular price, joins name and SKU, converts currency.
    • The customer sees “4123 USD” — pick the right price column for “Currency”.
    • A column is empty — a “Check” test run shows each column's value first.

    Where to start: “Price list (XLSX, CSV)” template card → “Select fields” tab → “Add field to export”.

    What price list columns can do

    • Columns from three sources — catalog fields, price list row fields, and nine macros: photos, categories, attributes, your own formula.
    • Your own header — the customer sees in the first row exactly what you typed in “Field name”.
    • Clean cells — prices are rounded to two decimals, line breaks are stripped, and an empty value gives an empty cell.
    • A formula in a column — a value that no field contains.
    • Test run — shows the result on one product and sends nothing.
    • Any number of prices — catalog prices and prices from supplier price lists side by side.

    How to add columns and headers

    The file's columns are set on the “Select fields” tab. The “Selected fields” grid is the customer's future table: as many rows as the grid has, as many columns the file will have.

    There are three exceptions, covered below: the “Product attributes” macro and the “Show values per supplier” checkbox spread one grid row across many columns, and the “Export only products from price lists” mode drops all base catalog fields from the file.

    The “Add field to export” button opens the field palette with a “Search field...” box (if nothing matches, the palette says “Nothing found”). Fields in the palette are grouped by three sources:

    • “Products from the base catalog” — every column of the product grid, including attributes shown in the grid. These fields come from your ;
    • “Products from price lists” — every column of the supplier price list row grid;
    • “Macros” — nine special fields that the grids don't have.
    Field palette opened by Add field to export, with source groups
    1Search field
    2Products from price lists group
    3Macros group
    1. The search box filters the palette by field name.
    2. The Products from price lists group lists fields taken from supplier price list rows.
    3. The Macros group lists computed values such as custom formula, category names and photo links.

    Here is what each in the third group gives you:

    Field in the paletteWhat the column contains
    Links to product photosall product photos in one line, separated by the link separator
    Product video linksall videos, the same way
    All category codes of the productcategory s separated by commas
    All category codes for the product (full ID hierarchy)the ID path from the tree root
    All category names of the productcategory names separated by the category separator
    Category name, level Nthe name of the category at the given level, as a separate column
    Product attributesspreads into a separate column for each attribute
    Product attributes from price listthe same for attributes from supplier price lists
    Custom formulaa placeholder for your own expression. It is not added empty: “Formula” gets the sample CONCAT_WS(" ", "String1", "String2", "..."), and if you don't rewrite it, the file will get the line String1 String2 ...

    You can add “Custom formula” and “Category name, level N” as many times as you like — each time a new field is created. Other fields can't be added twice: a repeat is silently rejected, and the palette shows the field as already added.

    How to name and order columns

    The first row of the file is the “Field name” grid column and nothing else. Not the field name from the directory, not the system name: when you add a field, “Field name” gets the palette name, and then you edit it right in the cell.

    The customer sees exactly what is written there. An empty “Field name” gives an empty header in the file, and that is how tables with an unlabeled column happen.

    The other grid columns control how the column looks:

    • “Order” — the column's position in the file. You can change it two ways: with the arrows in the row itself and with the toolbar arrows (“Move selected column up” / “Move selected column down”);
    • “Width” — the column width in , 15 characters by default. Not used in ;
    • “Source” — where the field comes from, with a colored dot;
    • “Formula” — the expression that calculates the cell value;
    • “Product sorting” — this field's position in the file's sort order;
    • “Show values per supplier” — spreads one column into a column for each supplier;
    • “Int. field name” — the system name, read-only.
    Template card, Select fields tab: toolbar and the grid of selected columns
    1Add field to export
    2Move column arrows
    3Field name
    4Formula
    5Order
    1. Add field to export opens the palette of fields you can add as a column.
    2. The arrows move the selected column up or down in the file.
    3. Field name is the column heading in the output file; you write it yourself.
    4. Formula holds a calculation for the column, here the price taken from the recommended retail price when it is set.
    5. Order sets the position of the column in the file.

    The trash icon in a row removes the column, but asks first: “Remove the column from the template? Its formula and settings will be lost.” The formula can't be restored afterward — if it's complex, copy the text before deleting.

    How many columns to use is up to you; there's no limit. A typical customer price list is a small table of five to eight columns, not a catalog export with every field.

    What goes into a cell

    A field value goes through several transformations, and they're worth knowing because the customer sees the result.

    1. There are no gaps. Every field is wrapped so that instead of “no value” the file gets an empty cell, not the word NULL.
    2. Prices are rounded to two decimals, numeric fields to a whole number. This applies to fields of the “price” and “number” type in the field directory: “Price”, “Cost price”, “RRP”, “Old price”, “Sale price”, “Price from the price list”. In XLSX any of these columns is written as a number, not as text, so Excel sums and sorts it as a number right away.
    3. Line breaks and tabs are stripped from text fields — that's why a long product description doesn't split a CSV into several lines.
    4. Long text in XLSX is cut off at 32,767 characters: that's a limit of the format itself, not of the app.

    A formula column bypasses all these rules: the formula result goes into the file exactly as the database calculated it, without rounding. So put rounding inside the formula itself.

    How to calculate a value with a formula

    You need a formula when no field has the value: give the RRP, or the regular price if there's none; join the name with the SKU; convert the price at a rate. Write it right in the “Formula” column of the field grid, without square brackets (XML templates wrap formulas in [[ … ]] — not here).

    Fields are inserted with macros of the form {‹grid name›: ‹field name›}. A real example from a working template — a price column:

    IF({Product: RRP} > 0, {Product: RRP}, {Product: Price})

    It reads as “if the is filled in, take it; otherwise take the regular price”. Macros for price list rows ({Price list: Price}) and for product attributes work the same way — {Catalog attribute: Color} outputs one attribute as a separate column.

    The exchange rate is inserted with the {CURRENCY_RATE_USD} macro; it has two synonyms — Russian {КУРС_ВАЛЮТЫ_USD} and Ukrainian {КУРС_ВАЛЮТИ_USD}. All three work the same in formula columns and in custom values. The rate is taken from the currency list rounded to two decimals — for currencies with a small rate, this noticeably distorts the conversion.

    The column comes out empty, and the run continues. Every formula goes through a safety check, which looks for keywords in the text by simple substring match, regardless of where they appear.

    If the check fails, the app leaves that column empty, builds the rest of the file as usual, and logs a line with the column name: “The formula of column “‹name›” was rejected by the safety check — the column was exported empty”. The loss is visible, but only in the run log.

    People trip over ordinary English words inside quotes: CONCAT("Updated: ", p.name) is blocked because of update, REPLACE(p.name, "Insert", "") because of insert, and CONCAT("Reunion ", p.name) because of union. Rewrite such formulas without these words.

    The check applies to base catalog columns and macros. In columns from the “Products from price lists” grid, the formula goes into the query unchecked: there such a word won't blank the column, but it can break the whole run.

    Two different failures with different consequences: one leaves you without a file, the other without one column. Mixing them up is costly, because you have to look in different places
    NoYesYesNoFormula written in the columnIs the syntax intact?commas in IF, paired bracketsThe file isn't built at all:the log shows “Error gettingproducts!”,the operation card is redSafety check:does the text contain an SQLkeyword —update, insert, union?The column comes out empty,the rest of the file is built as usual,the log has a line with the columnnameThe value goes into the cell
    • Action or result
    • Decision: the path depends on the answer

    How to see what you'll get

    The flask button “Check” in the card header does a test run on one product and shows the result as “column = value” pairs. It writes to a separate file, sends nothing, and doesn't touch the live link — it's the only safe way to see the result of an edit.

    If the run is clean, the bottom shows “No problems found: the export was built correctly and every macro was substituted.” For XLSX, your columns may be followed by several hundred empty pairs — a trace of the Excel library's test mode, covered below.

    Export template check window with the result for the first matching product
    1Product ID
    2Result summary
    3Column = value pairs
    1. Leave Product ID empty to test on the first suitable product, or enter an ID to test a specific one.
    2. The summary line says whether the export was built correctly and every macro was substituted.
    3. Each line shows a column and the value the template produced for the product, including values calculated by formulas.

    A syntax error in a formula stops the whole file. A missing comma in IF or an unclosed bracket breaks not one column but the whole database query: the file isn't built at all. The log shows a red line “Error getting products!”, and the operation card is marked as failed.

    The final line of the operation log will be “Data export NOT completed: the file was not generated”, and the last run date in the list won't change. To avoid catching this on a live run, test every formula edit with the “Check” button.

    Which prices and currency to give the customer

    How many prices to give is up to you, with no limit: every price column you add goes into the file. The base catalog offers “Price”, “RRP”, “Cost price”, “Old price”, “Sale price”, and “Currency”. From price list rows — the same plus “Price from the price list”, “Min price”, “Average price”, “Max price”, “Exchange rate”, “Profit”, and “Profit %”.

    Two fields with price-like labels aren't prices. “Fixed price” is a flag meaning “the price is locked, don't update it from price lists”, and it goes into the file as 0 or 1. “Price change” is a percentage change, rounded to a whole number. Both exist only in the base catalog; the price list row grid doesn't have them.

    No markup is applied at the export stage: the file gets the value stored in the field. If the customer needs a different price, either calculate it with a formula in the column or set it earlier — with pricing rules when the catalog is updated from price lists.

    The “Price” and “Currency” columns taken from a price list describe different things. Here is a real supplier row from the demo account: “Price from the price list” — 92.00, “Currency” — USD, “Exchange rate” — 41.55, “Cost price” — 3,823.00, “Price” — 4,123.00.

    So the “Price” field is already in hryvnias (converted at the rate and marked up), while the “Currency” field still holds the supplier's currency. Put these two columns side by side, and the customer sees “4123 USD”.

    The only correct partner for the “Currency” column is “Price from the price list”: that's the price in the supplier's currency. The app doesn't warn about this or check anything; you only catch it by looking at the file.

    Base catalog fields don't have this trap: in an export, a product's “Currency” is always replaced with the code of the account's main currency, so it always matches the price next to it.

    The “Export the minimum price for identical products per supplier” checkbox calculates fairly: it compares the cost price, and the cost price is already converted to the account currency, so different currencies are never mixed here.

    The decimal separator in prices is always a period — it doesn't depend on the server or its settings. If the customer needs a comma, they have to set it on their side: in their import settings or in Excel's regional settings.

    Scenarios

    Give the customer the RRP, or the regular price if there's none

    • What to set up: a “Custom formula” column with the expression IF({Product: RRP} > 0, {Product: RRP}, {Product: Price}) and a header the customer will understand.
    • What you get: products with the RRP filled in show the RRP in the column, the rest show the regular price. Add rounding to the formula: the formula result goes into the file without it.

    The customer needs the price in the supplier's currency

    • What to set up: from price list rows — “Price from the price list” and “Currency” next to it.
    • What you get: the amount and the currency code describe the same thing. Don't use the “Price” + “Currency” pair from the price list: the price there is already in your currency (why).

    The customer needs a different price than on the website

    • What to set up: a formula in the price column, or pricing rules when the catalog is updated.
    • What you get: the file gets the calculated value. There's no separate markup at the export stage.

    What to do when a column in the file is wrong

    Common problems
    What you seeWhyWhat to do
    “Error getting products!” — a red line in the run log The selection query broke — almost always because of a syntax error in the “Formula” of one of the columns. An empty “Formula” doesn't stop the run: that column simply comes out empty Click “Check” in the card header — it shows the error text. No file was built: the final log line is “Data export NOT completed: the file was not generated”, the operation card is marked as failed, and the link keeps the previous file (about formulas)
    “One of the [[ ... ]] formulas in the template has a mistake. Check the commas between IF arguments and the pairing of brackets and quotes.” The test run found a syntax error. The mention of [[ ]] doesn't apply to price lists: formulas here are written without brackets Check the commas between IF arguments and the pairing of brackets and quotes in the “Formula” column
    “The formula of column “‹name›” was rejected by the safety check — the column was exported empty” The formula contained a forbidden substring — select, update, insert, union — and the safety check blanked that column Rewrite the formula without these words, even if they're inside quotes (about formulas)
    “No file was produced. Fix the problem above first — while it is there, no products get into the export.” The test run didn't get as far as building the file because of the error above Fix the error named in the line above it in the check window
    A column without a header in the file This column has an empty “Field name” — that's where the first row comes from Type the header right in the “Field name” cell (about headers)
    The file has a price, but the currency next to it is wrong The “Price” and “Currency” columns from a price list describe different things: the price is already converted to your currency, while the currency code is still the supplier's Put the “Price from the price list” column next to “Currency”, or don't take “Currency” from the price list (about price and currency)
    “Remove the column from the template? Its formula and settings will be lost.” Confirmation of column deletion The formula can't be restored after deletion — copy its text if it's complex

    FAQ

    The column in the file is empty, although the formula is written. Why?

    The formula failed the safety check: the text contains update, insert, union, or select — even inside quotes. The run continues, and the log names the column. Rewrite the formula without these words (about formulas).

    How many prices can I give the customer in one file?

    As many as you like: every price column you add goes into the file. “Fixed price” isn't a price — it's a flag, and in the file it will be 0 or 1 (about prices).

    Can I put the product photos themselves into the price list?

    No. The “Links to product photos” column outputs links to photos; the images themselves won't be in the file. The “Export photo” checkbox is for uploading photo files to , and the cloud version doesn't have it.

    How do I change the decimal separator in prices to a comma?

    Not in the template: prices always use a period. The customer sets the comma in their own import or in Excel's regional settings.

    Why can't I add the same field to the template a second time?

    A repeated field is silently rejected, and the palette shows it as already added. Only “Custom formula” and “Category name, level N” can be added more than once.

    Where to find it and who has access

    Menu
    Directories → Operations → Data export → pencil in the template row → “Select fields”
    Access right
    the “Data export” window — one right covers the list, the card, and “Check”
    Prepare
    a “Price list (XLSX, CSV)” template — how to create one

    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