When consolidating data from disparate sources, the resulting list views may not always be optimal. A specific scenario involves Business Central Item Attributes, which are often structured with attribute and value IDs requiring resolution. This can lead to a fragmented view where a single item’s attributes are spread across multiple records.
Popdock offers a solution to this multi-record item attribute challenge through its calculated fields functionality. By defining these fields and then summarizing the list, you can group data by item number, presenting all attributes for each item within a single, easily readable record with dedicated columns.
Let’s show you how to change the results from this query to a list with each attribute in its own column and a single record for each item number.
Building Your Query Builder List in Sandbox
To get the results from the above screenshot you will need a query that joins the Item Table to the Item Attribute Value Mapping Table by joining on the fields No. to No. Then you will need to join the Item Attribute Value Mapping Table to the Item Attribute Value Table by joining on Item Attribute Value ID to ID.
Once you have these fields (and any additional fields added), publish this query to your Sandbox environment. If you are not familiar with publishing your Query Builder list, please see this article: Publishing Query Builder Lists and Widgets to Business Central (Sandbox).
Promoting Your Query Builder Extension to Production
Now that your Query Builder list is published to your Sandbox environment, we will need to promote it to your Production environment to be able to start building the calculated fields needed to move attributes to columns. This article will help with that process if you are not familiar: Promoting an Extension from a Business Central Sandbox to Production.
Adding Calculated Fields to Your Production List
At this point, you should have your Query Builder extension promoted to Production and your Query Builder list should show in Production. If you run the list for a quick test, you should see something similar to the screenshot at the top of the article of the demo data.
1. From the main menu, go to Connectors.
2. Find your Business Central Production environment and click the pencil icon (Edit) next to it.
3. Next in the left navigation menu, click on Lists.
4. Locate your new list and click the pencil icon (Edit) next to it.
5. Now from the left navigation menu, click on Calculated fields.
6. Here you should not have any so click the link to add a calculated field.
7. In the Add calculated field window that appears, give the field a name for your first attribute. In my demo data ID 1 = Color so I will name this field Color. The field type is just a simple String and for the field language we are going to choose or leave it on the default Popdock script.
8. Next is the formula which is going to be the same for each attribute you have with the exception of the ID that you will change every time you create a new calculated field. For this first one the script is:
If({Item Attribute ID}=1,{Value},"")What this script does is basically an If/Else statement: If the field Item Attribute ID is equal to 1 then I want the value from the value field to appear here else leave the field blank. Looking at the below record from the list, this new column Color will look at the column Item Attribute ID and if it equals 1, it will grab the data that shows in the Value column and add it under the Color column else it is going to leave the Color column blank.
9. Continue to repeat the above steps for each attribute that your items may have building a calculated field for each attribute name and changing the formula to represent each ID. For example, my next calculated field would be Depth with the script being:
If({Item Attribute ID}=2,{Value},"")Eventually as you get all your calculated fields added it will start to look something like this, I only completed five attributes for this example:
If you test your list now and add the calculated fields, it will look a little strange like this, but the final step will bring everything together to a single record for each item:
Building Your Summary Custom List
1. From the main menu, go to Connectors.
2. Find your Business Central Production environment and click the pencil icon (Edit) next to it.
3. Next in the left navigation menu, click on Custom lists.
4. Click the add custom list drop down, then select Summarize a list.
5. Give your list a name, ex. Items With Attributes Summary.
6. The base list will be your Query Builder list.
7. Choose what Group you would like the list to appear under.
8. Group by field, this is where you want to group by the Item No. so that all those attributes get rolled up under a single record for the Item No.
9. Summary method is None and no need to check the box to add the base list as a detail.
10. Click Add list.
11. From the left navigation window, click on Fields.
12. Next click on the bullet option at the top to select Summary fields.
13. Then click the link to add summary fields.
14. In the Add summary fields, select Maximum (this is so that it returns the only value that should be in the column for a single item number) for the Summary method.
15. Then select each field that you want to see the data for in your list with the exception of the item number because that one is already there because of the grouping.
16. Click Add fields.
The summary fields get added with the Max of added before the field name so I would just edit each field and delete that off.
Now you are ready to set your default fields and test the list. You should see the results you want with a single record for each item number, along with each attribute in its own column.
If you run into any issues or have any questions please feel free to reach out to our Support Team at support@eonesolutions.com.
