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. From the main menu, select Connectors.
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 Query Builder.
4. To start a new Query Builder list, select Click here to add a list.
5. Enter a Name for your list and then press Tab on your keyboard. The API endpoint will populate. Select a Group for your list. Then select Add.
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 Query editor pane on the right. In the Query editor, select whether to make this table a Summary table or not. Then select Fields and select which fields to populate from this table by selecting the box next to each field. In this example, the following fields are selected: 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 Query editor pane 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. In the Query editor pane, select Restrictions. To add a restriction, select Click here to add a restriction. 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 Join to 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 Type field is being used in both the From field and To field.
17. With the tables and fields selected, select Add.
18. The new table will populate within the visual builder pane. By selecting the arrow between the two tables, you can open the Query editor pane. Within this pane, 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 Query editor pane 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. By default, Summary method is not displayed. Select Select columns and then then select Summary method to display the column.
23. Select Default fields from the left navigation pane. Then 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 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 the connector name in the breadcrumb.
Then select Publish in the navigation pane.
27. Select Publish and then select your Query Builder list. Select Publish.
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.