Using Relations between Tables

Often, users need to obtain information across fields which belong to different tables, for example: 

• "I need to download a CSV file of my products and retrieve the file source of a field from a table in Custom Entity".
• "I want an Excel file of all my products with a field that contains the concatenated values of its variants".
• "We would like to obtain, at the category level, the names of the principal items from the Custom Entity table".

Requests of these types are often made to our support team and therefore require special mention; their solution generally requires the use of two particular functions.

1. REPLACE_FROM_TABLE: This allows us, via the link between two different fields in different tables, to replace the values from the source field with those of another field in the destination table. Let’s suppose that we have a table in Custom Entity for assigning colours to Variants, which we use so that we don’t have to translate the name of the colour every time we add a new variant.  

If we try to export our variants directly we end up with the ID of the colour, used to link the Variants table with the Colours table in Custom Entity being exported.

However, this is most likely not what we are looking for, ideally, we’d want to export the names of the colours. To do this we use the formula: REPLACE_FROM_TABLE(field, table, table field related, table field conversion, default value, modifier, separator).

• Field: this defines the field from which we obtain the connection, the one which contains the reference to the other table. In this case, we have two options, we can either use {THIS} if the field we are applying the formula to is the one that contains the references to the external table, or we can use the {field name} if the result is going to be transferred across from a different field. For our example, we can use {THIS} as the formula is going to be executed on the field “colour” from the Variants table in an Excel export connector.

• Table: this parameter defines the destination table, where the information we want to extract is stored. In our case, this will be the table “Colours” from Custom Entity.
• Table field related: this defines the name of the field in the destination table which contains the value that is referenced in the source table. For us, this is the field “reference” from the Colours table in Custom Entity.
• Table field conversion: this defines the field, from the destination table, whose values we want to obtain, the ones to be exported. In our case, the field “Name” contains the name of the colour. 
• Default value: an optional parameter, which, by default, is an empty string: here we can define a default value to be exported for any cases where a full link between the two tables cannot be established. We will leave it as the default empty string.
• Modifier: an optional parameter, to define whether, when comparing values we consider the case of each using CASE_SENSITIVE (by default it is set to UNCASE_SENSITIVE).
• Separator: an optional parameter, which should be used when various values exist per element. This is necessary so that each different value in a field can be identified on its own and not treated as a unique value. In our case as we have some variants that have more than one colour, with each value being separated by a comma, we must use this parameter, without it the formula would only run correctly for those incidences where the variant has just one colour value:

To avoid this type of problem we recommend always using all six parameters, even if you just specify the separator parameter as being a comma:,. The benefits of doing so can be seen in the above example, if we only use the four obligatory arguments, then the function won’t always work correctly. However, by making sure we always include the separator parameter, then the functions will work regardless if there is only one value or many. This issue often comes up when the field we are working with is of Field List type.

If we execute the function as shown in the image below, with all the arguments included, we obtain the correct result, with the names of all the colours for each variant being exported correctly.

2. GET_VARIANTS_VALUES: This allows us, via the relationship between two fields, to obtain a single field with multiple values concatenated together. This is useful when we want to obtain, in just one field, all the values from another field which belong to another table, for example the “Variants” table.

Imagine a scenario where we want to export an Excel file with all the dresses we sell (products), with a field in each line that contains the different sizes each dress is available in (variants). As you can see in the following image, each product reference has a number of variants linked to it, each one with its own distinct size value. If we export these variants directly then we will end up with the product information being repeated across as many lines as there are different sizes.

To avoid this data duplication we can use the following formula: 

GET_VARIANTS_VALUES(key field, variant parent field, variant related field, output separator, dependent table, add empty values, unique values) with the following parameters:

• Key Field: this defines the field from which we want to obtain the connection, that one which contains the reference (the link to) of the other table. In this case, we have two options, we can either use {THIS} if the field we are applying the formula to is the one that contains the references to the external table or the {field name} if the result is going to be transferred across from a different field. In our case, we will use {product reference} on the field Test Sizes as defined in the Products table of the export connector.

• Variant parent field: this field defines the field name from the external table which contains the link. In our case, it is “product_reference” from the variants table.
• Variant-related field: this defines the field from which we want to take the values to be concatenated into a single field, the field containing the values to be exported; in our example: “size”.
• Output separator: optional parameter (the default value is,) which defines how we want to separate the concatenated values. We will be using the character | as the separator.
• Dependent table: optional parameter, which by default is the table Variants, where we can define the table where the formula should look for the fields from the parameters Variant parent field and Variant related field. In our case, these fields are in the variants table so we will be left this parameter empty.
• Add empty values: optional parameter (the default value is “true”) where we specify if we want to include any items which don’t have a link to the external table. We don’t want to see these products so we set this parameter to “false”.
• Unique values: optional parameter ( the default value is “true”) where we specify if we want any duplicate values to be repeated in the output String. For example, it could be that we have two dresses which are the same product, are of the same size, but of different colours. With this parameter set to “true”, the duplicate size value of both these dresses would only be inserted into the String once. In our example, we will set the parameter to false.