When working with Electronic Reporting (ER) configurations, grouping records is often essential. The LISTJOIN function enables you to consolidate data from multiple lists, which is especially useful when managing data from various sources with common fields. Here’s how to make the most of the LISTJOIN function in your ER configurations.
Using LISTJOIN in Electronic reporting
When displaying a repeating section in a template, you typically work with a single list (e.g., showing all lines of a sales invoice on a document). However, sometimes you need to combine records from multiple lists. This is helpful when data is spread across different sources that share common fields, like transactions in a project invoice. In this scenario, a project invoice might include multiple types of transactions, such as expenses, fees, items, and hourly charges.
Different lists may contain distinct fields, as seen in the example below.
- The Expenses container has fields for Amount, Description, Quantity, and Type.
- The Items container has fields for Amount, Description, ItemId, Quantity, and Unit.
Below is the formula which allows us to join our two lists in our ER format’s mapping in a new calculated field:
LISTJOIN(@.Expenses, @.Items)The resulting list will only include the fields that are common across both lists, in this case, Amount, Description, and Quantity.
Field Requirements
To join records from different lists, ensure that any fields you want in the final, consolidated list exist in all the lists you’re combining. If a field is missing from even one of the lists, it won’t appear in the final joined list, potentially leaving gaps in your data.
Handling Missing Fields
If some lists lack the required fields, you can add those fields to make the list join work seamlessly. Ensure the new fields have the same name and type as in the other lists. You can set placeholders in your model mapping for these new fields to prevent errors from empty fields.
Steps to Implement the Solution
- Identify Missing Fields: Determine which fields are missing in each list.
- Create Missing Fields: Add these fields with the same names and types to lists where they are missing. In our example, Expenses list is missing the ItemId and Unit fields, while the Items list is missing the Type field.
- Set Placeholders: Use placeholders in your model mapping for these fields to handle errors caused by empty fields.
The result is a consolidated list with all the required fields from both lists.
By following these steps, you can use the LISTJOIN function in an ER configuration effectively. This approach ensures your data is easily combined in single data source and is easy to display.