Replace From Table Formula

REPLACE_FROM_TABLE formula allows to retrieve specific data from a related record, instead of merely showing the related record's ID or reference

In Sales Layer, the catalog data is organized into tables:

• The products table
• The variants table
• Or custom entities (e.g: marketing content (brochures, campaigns), technical or quality certificates, user manuals, regulations, additional information about variants (such as color or material details)

When elements are being linked between any of these tables via a related item type field (for example, a product that includes several manuals), Sales Layer stores that relationship using a reference (typically an internal ID).

Formula usage:

When a field is referenced to another item (such as a related item type field), by default, it will export the ID or reference of it.

Instead, if the objective is to extract specific information from the related item, such as its name or any other attributes such as:  PDF file URL, image URL, etc. you will need to use the REPLACE_FROM_TABLE formula.

Pseudocode:

REPLACE_FROM_TABLE({field}, “table”, “table field related”, “table field conversion”, “default value”, modifier, “separator”, “outseparator”,”outlang”)

• {field}: the reference field that will be used to search on other tables.
• table: the table where the data to be substituted will be collected.
• table field related:  reference field of the table in which we are looking for the data.
• table field conversion: the field to get the data from.
• default value:  in case no data is found, this will be the output of our formula (e.g., "-", "" or any symbol or word).
• modifier: Optional, modifier for the data search, it can be CASE_SENSITIVE or UNCASE_SENSITIVE (By default).
• separator: Optional, the character separating multiple values, in case of having more than one associated reference, it will be the character by which the different references will be separated.
• outseparator: Optional, how to separate results in the output.
• outlang:  Optional, export values in a specific language like "en" or "es".
  • GET_LANG() formula can be used here in order to get the languages dynamically, no parameters required.

It is important to know that REPLACE_FROM_TABLE can work with several elements. That is, if several items are assigned in the {field} additional parameters like "default value", "modifier" and "separator" will be required as explained in the second example.

Example:

In the following example the process to retrieve the name of an item from a related table is detailed. For it the “{related_field}” in the first table is required. 

In other words: in order to retrieve the name of a custom entity from its table, we will be using the formula in the product table inside a channel.

The relation is based on a custom entity related to a product.

Here we have our custom entity created:

When relating it through the related type of field to a product it will look similar to this:

Note the default value parameter:  “-”  will be returned if the “name” field to get would be empty.

Use cases:

1. Use of the  REPLACE_FROM TABLE to get the image Name or URLs stored in a specific field within the Custom Entities table related to the Products table.

The Custom Entity table is configured to get the URLs using the formula and separating the possible several URLs with commas.

In this case, we added an image field called {Custom image} to the previously showed Custom Entity table:

The result will be: 

2. Nested REPLACE_FROM_TABLE: to collect the value of a specific field from the Custom Entities related to Products by applying the formula in the Variants table.

Three levels need to be taken in account: Starting from the current table - for example Variants (first table), go to the related item field within the Products table (second table) and get values from Custom Entities (third table).

To retrieve the correct result, a nested formula construction is required and to achieve this, we have to reach the product level from the variant table, this can be done with the first REPLACE_FROM_TABLE, in this case, we will search for the Custom Entity field recently created, and then we are going to apply another REPLACE_FROM_TABLE like we were in the product level. These are the steps to follow:

Step 1: look for the field in common between variants and products- each product has a parent reference, so this will be our key to retrieve data from the products.
In the below example, this field  it would be {product_reference}

Step 2: create the first REPLACE_FROM_TABLE: we are aiming to retrieve data from the product now, the formula will look similar to this:

In this case, we used the {product_reference} field from variants to achieve the top level table, pointing at the products table and using its corresponding fields to retrieve the information.

Step 3: Next, we need to apply another REPLACE_FROM_TABLE as we were in the product table, looking for the specific fields in the Custom Entity table.

So the final nested formula will be: 

Note: If the formula cannot find the value because the specific item in that field has no content, by default, it will return the value of the reference field. To obtain an empty value, simply adjust the default value parameter using a quoted space: " ". Example: REPLACE_FROM_TABLE({product_reference}, "products", "Reference", "Description", " ").

Warning: This formula won't return all the categories is you are trying to retrieve the category reference of a product, this is caused because the Category reference field has a different behavior than the other field types.