Due to scheduled maintenance, some areas of our Shop and Manage Plan pages are currently inaccessible. Thank you for your patience.

Please reach out to sales@eonesolutions.com with any questions.

Back

Including Setup tables in a Business Central List

Published: Aug 19, 2024
Post Author Written by Ethan Sorenson

Sometimes data from a setup table in Business Central will be required when building a list. For example, pulling the Company Name from the Company Information table or the LCY Code from the General ledger Setup table. Adding these tables can be tricky because there won’t be a field to join a setup table to the rest of the data you are retrieving from Business Central.

Below are the steps we can take using a Cross Join to join a setup table to a Business Central Query Builder list.

What is a Cross Join?

A Cross Join contains rows that combine each row from the upper data item table with each row from a lower data item table. A cross join does not apply any comparisons between columns of data items.

The Microsoft documentation has an example of the output when using a Cross Join between the Sales Header and Salesperson/Purchaser tables.

If you use a Cross Join to add a table that contains more than one record, your list will return a duplicate for each record in the Cross Joined table.

Adding General Ledger Setup table to a List

For my scenario I am going to take a list of Purchase Invoices and join it to the General Ledger Setup table so I can add the Local Currency Code to my list.

  1. Create a basic list joining the Purch. Inv. Header table to the Purch. Inv. Line table.
  1. Click + Add table
  2. Add the General Ledger Setup table
  3. Add any From field and To field you want

The From and To field can be any random fields. We will remove the link in a later step.

  1. Click Add table
  2. Select LCY Code from the General Ledger Setup tab
  3. Click on the line joining the General Ledger Setup table to your query
  4. Change the Join type to Cross join
  1. Publish the list
  2. Go back to the Popdock Dashboard and run your list.
  3. Notice that the LCY Code field is the same for every Invoice in the list

Using Cross Joined data in a Calculated Field

You can stop after you have added the column, but in my scenario I want to take some action based on the field I joined. I’m going to add a new calculated field that will replace the blank currency fields with the LCY Code.

  1. Open the Query Builder list again
  2. Navigate to the Fields tab and add a new Calculated field
  3. Provide a Name for the Calculated field
  4. Set the Type to String
  5. Enter the below Formula
  6. Click Go back to List
If(IsBlank({Currency Code}), {LCY Code}, {Currency Code})
  1. Run your list again, and add your Calcuated field to the response
  2. Notice that the new Calculated field is dynamically filling in the blank currency values

If you have questions about using cross joins in Query Builder, contact support@eonesolutions.com.

Feeling stuck? Get the support and guidance you need to help you power through any data challenge

We're on your integration team. Connect with our people and let us know how we can help you.

Get support