We are working hard to make updates to the Shop and Account Management portal.

Please note that many of these changes will roll out this Saturday, June 1st and will require downtime. We are working to ensure minimal disruption for our eOne community and really appreciate your patience as we push out these changes. Please reach out to sales@eonesolutions.com with any questions.

How to Create a Query Builder List

How to Create a Query Builder List

Query Builder allows you access the Business Central tables and to write queries at the database level without having to write a single line of AL code. Upon creating your query, you can publish it as an extension directly into your Business Central Sandbox. This article will show you how to create a Sales by Item query.

1. Select Connectors from the left navigation to open the Connectors page.

Main menu in Popdock with Connectors highlighted.

2. Locate your Dynamics 365 Business Central Sandbox connector and then select the Edit icon to the right of the connector.

3. In the left settings menu, select Lists.

4. Scroll down to the Query builder section below your lists. To start a new Query Builder list, select Click here to add a new list with the query builder.

5. Enter a Name for your list and then press Tab on your keyboard. The Item name, Item singular, API endpoint, and Group will populate. Complete these available fields.

6. Select Query builder in the left Settings menu.

7. You can use the visual builder to begin putting together your query. Select Click here to add a table and select your first table.

8. From the Table dropdown, select your table. In this example, the Sales Line table is being used.

9. Once you have selected the table, select Add table.

10. You will see the table you selected populated inside of the visual builder pane.

11. Select the table name, in this case Sales Line. This will open the Edit table window. Under the Edit table window, select whether to make this table a Summary table or not. Then select which Fields to populate from this table by selecting the box next to each field. In this example, Summary table is selected, along with he following fields: Amount, Amount Including Tax, Document Type, No., Quantity, and Type.

Notice as you select each field, it will display under the table within the visual builder pane.

12. You can further optimize the query by selecting Summary Methods for the fields and placing Restrictions on them. To customize a field, select the field within the visual builder pane, this will display the Edit field window on the right side of the page. In this example, the Summary method dropdown of Sum is set for the following fields: Amount, Amount including Tax, and Quantity.

13. If you want to add restrictions to your fields, you can select the field(s) you want to add a restriction to. This will open the Edit field window. To add a restriction, select Click here to add a restriction under Restrictions. In this example, a restriction is placed on the Document Type field to equal Invoice and the Type field to equal Item. You will notice a small Filter icon will display next to the field once a restriction is placed.

14. You can join another table to the Sales Line table and select additional fields. To add a new table, select Add table.

15. Your initial table will be populated in the Join from table dropdown. Using the Table dropdown, select the table you want to join. In this example, the Item table is being joined.

16. Use the From field and To field to select the fields from the respective tables that will be joined. In this example, the No. field is being used in both the From field and To field.

17. With the tables and fields selected, select Add table.

18. The new table will populate within the visual builder pane. By selecting the arrow between the two tables, you can open the Edit join window. Within this window, you can customize the Join type using the available drop down (all five join types are available: Inner, Left outer, Right outer, Full outer, and Cross join).

19. You can now select the fields you want to include from the Item table. By selecting Item within the visual builder pane, the Edit table window will open. You can select the following fields from this table: Blocked, Description, and Type. Once selected, the fields will populate under the table name in the visual builder pane.

20. At this point your query is complete.

Optional: By selecting </> Show code, you can view the AL code for this extension.

21. Select Fields in the left Settings menu.

22. Here you can make any changes to the fields you selected within your query, such as changing the Name of the field, the Field type, or the Summary method. In this example, no changes need to be made to the fields. Scroll down on the page to the Default fields section and select Show on the right side of the page.

23. Select Click here to add default fields.

24. You can check the box next to the fields that you want to be the Default fields of the list. For this example, the following fields are selected: Amount, Description, No., and Quantity. With the fields selected, select Add.

25. The selected fields will display in the Default fields section. You can use the drag handle dots to the left of each field name to drag and drop the fields in the order you want them presented.

26. With your default fields in place, your query is ready to publish. Select Publish under the left Actions menu.

27. The Publishing window will populate.

28. If successful, the Publish successful window will display. The query is now published as an extension into your Business Central Sandbox and a Popdock list is now available. Select OK.

29. Navigate back to the Popdock homepage and add a new tab to display your Query Builder list.

To learn how to promote your Query Builder extension from your Sandbox to Production environment. Visit Promoting an Extension from a Business Central Sandbox to Production.

If you have questions on how to create a Query Builder list, contact support@eonesolutions.com.

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

Reset Filters