Why Are My Business Central Dimensions Not Working in Popdock?

Popdock includes a built-in method for bringing dimensions into a list using the Dimension Set ID field. While this approach works well in most scenarios, performance issues or missing dimension data may occur when the number of dimensions and their combinations exceed a certain threshold.

If you encounter these issues, you can use an alternative method. The steps below outline how to build a dimension summary list that can be joined to any list containing a Dimension Set ID field. This approach ensures your dimension data is returned successfully.

Business Central Dimensions

Prerequisites:

• Popdock Business Subscription or higher (Query Builder feature required)
• Business Central Connector (Sandbox environment to build Query Builder lists)
• Business Central Connector (Production environment to promote the Query Builder list to)

Build The Dimension Set Entries Query Builder List

1. From the main menu, go to Connectors.
2. Find your Business Central Sandbox connector and click the pencil icon next to it to edit the connector.
3. Next in the left navigation menu, click on Query builder.
4. Now click on +Add list to create a new Query builder list.
5. Give your list a name, choose a Group to put the list under, then click the Add button.
6. The new list will open, in the left navigation choose Query builder.
7. Then click on the link to add a table.
8. From the drop down, click on the search box and start typing “dimension set”.
9. In the list of tables you should now see a Dimension Set Entry, click on it. Then click the Add button.
10. From the right hand side bar, click on the Fields bullet option. Choose the following fields by placing a check next to them.
11. Once the fields are added, you can now publish Query Builder to your Sandbox and then promote it to your Production connector.

12. The new list will open, in the left navigation choose Query builder.
13. Then click on the link to add a table.
14. From the drop down, click on the search box and start typing “dimension set“.
15. In the list of tables you should now see a Dimension Set Entry, click on it. Then click the Add button.

16. From the right hand side bar, click on the Fields bullet option. Choose the following fields by placing a check next to them.

17. Once the fields are added, you can now publish Query Builder to your Sandbox and then promote it to your Production connector.

If you need help on how to publish your Query Builder list, please use this article here.

Next you will need to promote the list to your Business Central Production connector so we can complete the following steps so you have a working dimensions list to join to in your production connector. If you need help on how to promote the Query Builder to production, please use this article here.

Add Calculated Fields To Place Dimension Values Into Their Own Column

After your new Business Central Dimensions Query Builder list is promoted to Production, you can edit the list to add your calculated fields. This puts your dimension values into their own column to prepare them to be summarized and consolidated to a single record.

1. Edit your Business Central Production connector, find the new list you just published and click the pencil icon to edit the list.
2. Next in the left navigation, click on Calculated fields.
3. Here you will want to add a calculated field for each of your dimensions. In my demo data, I have a total of 6 dimensions, this is what each calculated field will be:

If({Dimension Code}="AREA",{Dimension Value Name},"")
If({Dimension Code}="BUSINESSGROUP",{Dimension Value Name},"")
If({Dimension Code}="CUSTOMERGROUP",{Dimension Value Name},"")
If({Dimension Code}="DEPARTMENT",{Dimension Value Name},"")
If({Dimension Code}="SALESCAMPAIGN",{Dimension Value Name},"")
If({Dimension Code}="SALESPERSON",{Dimension Value Name},"")

You can modify this IF statement to change the dimension code to equal what your dimension code is. So if the dimension code equals what you have in quotes, the output for that field will be the Dimension Value Name, if you want this to be something else just change it to a different field like the Dimension Value Code. Then the empty double quotes at the end just means if it does not equal the Dimension Code, then just leave the value blank.

4. In the calculated field settings name the column to be your Dimension Name, so for example here I have it labeled AREA and my formula is looking for the Dimension Code to equal AREA:
5. After you have added all your calculated fields for each dimension you want to be available on your lists, then you can move on to the next section.

Building A Summarized Dimension Set Entries List

Now that you have the Dimension Set Entry Query Builder list and you have added the calculated fields for each of your Business Central dimensions, you can run the list and see that you now have a list that has duplicate Dimension Set IDs with each ID showing your dimension value in their assigned column. Here is an example:

New is summarizing the list so that the Dimension Set IDs are grouped together with each dimension assigned to that ID showing on a single line.

1. From the main menu, go to Connectors.
2. Find your Business Central Production connector, click the pencil icon to edit it.
3. In the left navigation menu, select Custom lists.
4. Click the + Add custom list button and choose Summarize a list.
5. Give the list a name and group.
6. Then choose your Dimension Set Entry list as the base list.
7. Group by field is going to be the Dimension Set ID.
8. Summary method in this scenario is None because its the ID field which doesn’t need to be a sum or any other method.
9. Then the checkbox option is not needed to add the base list as a detail.

10. Click the Add list button.
11. In the left navigation menu, click on Fields.
12. Here find the bullet button next to Summary fields and click on it.
13. Click the link to add summary fields.
14. From the Summary method dropdown, you will want to choose Distinct values, this will get us the dimension calculated fields we added.
15. Check each dimension field you want to add to the list.

16. Then click the Add fields button.
17. The Summary fields will get “Distinct values of…” added to the name, I don’t want to see this on my column headings so I delete them to be just the name of the dimension.
18. This completes the Summary list for Business Central Dimensions, and now you can join any list with a Dimension Set ID field to this list and grab the dimensions that are assigned to that ID.

If you are needing help with your join list please go to this article here on how to build a join list.

If you have any questions on any of the steps in this article regarding Business Central dimensions, please email support at support@eonesolutions.com.