Docentric Query Browser for D365FO

Docentric Query Browser is an extension of the built-in SysQuery framework, including the Query form (SysQueryForm), introduced by Docentric. It is designed to support building AX queries from scratch with Visual Studio-like capabilities and a Preview option. This tool allows you to create queries and perform ad-hoc reporting directly within Dynamics 365 for Finance and Operations (D365FO).

Everything described in this article is included starting from v3.4.9 in Docentric AX Free Edition as part of the FREE Productivity Tools for D365FO!

Understanding the standard query form

Let’s start with a quick refresher on the standard Query form to better understand Docentric Query Browser improvements.

In D365FO, we will take the All Customers form as an example. We just need to go to the Options tab and click on the Advanced filter or sort button and the standard Query form will open, showing these tabs:

Range: Add filter values to show specific records.
Sorting: Change the default grid order.
Date options: Filter records by effective dates (when the data source table property Valid Time State Field Type is enabled).
Joins: Add joins to the existing tables.

Depending on the query prompt options, other tabs can appear:

Aggregate: Add an aggregate field with the Average, Sum, Minimum, Maximum, or Count functions.
Group by: Add a grouping field.
Having: Add additional filter fields based on aggregate criteria.
Company range: Filter on a company basis if the query allows cross-company data.

Introducing Docentric Query Browser

To use the Docentric Query Browser, your user must have the DocSysQueryForm privilege assigned. Docentric Query Browser can be accessed from all the forms in D365FO, but for the following steps you'll be opening it from the All customers form.

The menu item on the Options tab will be enabled in v3.4.9.1.

Here’s how to access it:

Go to Accounts receivable > Customers > All customers.
Go to the Options tab on the menu.
Click Docentric Query Browser.

You can also access Docentric Query Browser via Docentric Table Browser.

When opened, we start building ad-hoc query with the root data source table or view and immediately see enhancements like Copy, Export/Import, and Preview buttons.

These are nice, right? Let's learn about all the features we have built!

Key features of Docentric Query Browser

So, what does Docentric Query Browser has to offer? Here's the complete feature list:

Ad-hoc query building:
- Build and save queries per user.
- Select specific table fields and display methods to fine-tune results.
- Use the Dynamic fields option for flexible querying (e.g., new fields added to the source table automatically appear in the results).
Enhanced joins:
- Add related tables with configurable join types like InnerJoin, OuterJoin, ExistsJoin, and NoExistsJoin.
- Change join types for existing tables.
- View localized and technical names for data sources, tables, join types, fields, and display methods.
- Change the name of your data source.
Instant query preview:
- Instantly preview query results based on your user roles in a tabular format.
- Export query results directly to Excel for further analysis or reporting.
Export / Import queries:
- Share queries seamlessly between users and environments.
SQL copying:
- Copy the SQL statement behind the query using the Copy button in Docentric Query Browser, and use it for debugging, troubleshooting, or sharing with your technical team (by using Copy text to the clipboard functionality).
Custom solutions:
- Integrate the Docentric Query Browser into your custom solutions to enhance ad-hoc reporting capabilities.
- Use it to simplify data extraction and analysis, making your solutions more dynamic and user-friendly.
Location:
- Access the Docentric Query Browser from any form in D365FO via Options > Page options > Docentric Query Browser or through the Docentric Table Browser.
- Coming in v3.5.0: Access to some features (e.g., Copy SQL, Export/Import, Preview) of Docentric Query Browser in Options > Page options > Advanced filter or sort, Records to include on the dialog invoked by SysOperationController classes, conditions configured via the SysQuery framework such as those used in Print management, Alert rules and Document routing, and more.

You can use Docentric Query Browser to edit Docentric data source queries for License plate labels layouts. In the future, we will support other report data sources. Learn more >>

Let’s learn more about some features!

Select table fields and methods

Like creating a query in Visual Studio, we can select fields dynamically or manually, and decide which ones will be included in the query.

When we click the Select table fields/methods button, this dialog will open:

Here, we can disable the Dynamic fields options and select each individual field that will be returned to the query results.

This also works with display methods if the table has any.

Enhanced joins

The Joins tab is packed with many improvements. Continuing with the All customers form, where we have the Customers table, and we’ve already added the Global address book table:

We’re going to add another table join to the Customers node. Select it and click the Add table join button:

Select the Customer bank accounts table, change the join type to InnerJoin, and click the Select button:

You can see the join has been created with the InnerJoin type we selected.

If you want to change the join mode, you can do it by selecting the joined table and using the Change table join mode button:

Instant query preview and Export to Excel

Thanks to the preview functionality we’re able to build a query and see the results in a grid. This also means we can export the data in the grid to an Excel spreadsheet.

Let’s see how we can do it. Open the query browser from the All customers form and add a range for the customer group number 10:

Add sorting on the customer's name:

Change to the Joins tab, and for the Customers data source, select the fields Customer account and Customer group and the methods custGroup (which returns the customer group description) and name (which returns the customer's name).

Finally, click the Preview button to display the results:

And after previewing it, you can also export it to Excel by right-clicking on the grid and selecting the Export all rows option.

Thanks to this functionality, you’re able to build a complex query in the user interface, and export data easily. This is one of the key benefits of Docentric Query Browser!

Export / Import queries between users and environments

After building a query in, for example, the sandbox environment, where you’ve changed ranges and added some other table joins, it’s time to move it to production.

Usually, you would repeat all the process manually in the production environment, but we’ve added export and import capabilities to the query form:

When you export your modified query using the Export query button, it will download a file to your PC.

You can use that file and import it using the Import query button in your target environment and forget about doing work twice!

Calling Docentric Query Browser from code

Developers can easily integrate the Docentric Query Browser into any form in D365FO to provide users with streamlined access for creating ad-hoc reports via Excel. Here is an example of how you can achieve this:

[Control("MenuFunctionButton")] 
class DocSysQueryForm 
{ 
    /// <summary> 
    /// Called when the DocSysQueryForm menu button is clicked. 
    /// </summary> 
    public void clicked() 
    { 
        // Get the root form data source 
        FormDataSource fds = element.dataSource(); 

        // Create a new instance of the query for the root form data source table 
        Query query = new Query(); 

        query.addDataSource(fds.table()); 

        SysQueryRun queryRun = new SysQueryRun(query); 

        queryRun.title("@DocentricAX5:DocSysQueryForm"); 

        // Set standard prompt options for the SysQueryFom form 
        queryRun.promptAllowAddDataSource(true); 
        queryRun.promptAllowAddRange(QueryAllowAdd::AllFields); 
        queryRun.promptAllowAddSorting(QueryAllowAdd::AllFields); 
        queryRun.promptAllowSave(true); 
        queryRun.promptSaveQueryPrUser(true); 
        queryRun.promptShowSorting(true); 
        queryRun.promptShowAggregate(true); 
        queryRun.promptAllowQueryFilters(true); 
        queryRun.promptLoadLastUsedQuery(false); 
        queryRun.promptShowCompany(true); 
        queryRun.promptShowQuerySelect(true); 
        queryRun.promptShowReset(true); 

        // Set Docentric prompt options for the SysQueryForm form 
        queryRun.parmDocentricExtensionEnabled_DC(true); 
        queryRun.parmTableJoinsParam_DC(DocSysQueryFormTableJoinsParam::CanUpdate); 
        queryRun.parmAllowEditDataSourceNames_DC(false); 
        queryRun.parmTableFieldsParam_DC(DocSysQueryFormTableFieldsParam::CanUpdate); 
        queryRun.parmTableMethodsParam_DC(DocSysQueryFormTableMethodsParam::CanUpdate); 
        queryRun.parmPreview_DC(true); 
        queryRun.parmAllowExportImportQuery_DC(true); 

        // Open the SysQueryForm form 
        queryRun.prompt(); 
    } 
}

[Control("MenuFunctionButton")]

class DocSysQueryForm

{

/// <summary>

/// Called when the DocSysQueryForm menu button is clicked.

/// </summary>

public void clicked()

{

// Get the root form data source

FormDataSource fds = element.dataSource();

// Create a new instance of the query for the root form data source table

Query query = new Query();

query.addDataSource(fds.table());

SysQueryRun queryRun = new SysQueryRun(query);

queryRun.title("@DocentricAX5:DocSysQueryForm");

// Set standard prompt options for the SysQueryFom form

queryRun.promptAllowAddDataSource(true);

queryRun.promptAllowAddRange(QueryAllowAdd::AllFields);

queryRun.promptAllowAddSorting(QueryAllowAdd::AllFields);

queryRun.promptAllowSave(true);

queryRun.promptSaveQueryPrUser(true);

queryRun.promptShowSorting(true);

queryRun.promptShowAggregate(true);

queryRun.promptAllowQueryFilters(true);

queryRun.promptLoadLastUsedQuery(false);

queryRun.promptShowCompany(true);

queryRun.promptShowQuerySelect(true);

queryRun.promptShowReset(true);

// Set Docentric prompt options for the SysQueryForm form

queryRun.parmDocentricExtensionEnabled_DC(true);

queryRun.parmTableJoinsParam_DC(DocSysQueryFormTableJoinsParam::CanUpdate);

queryRun.parmAllowEditDataSourceNames_DC(false);

queryRun.parmTableFieldsParam_DC(DocSysQueryFormTableFieldsParam::CanUpdate);

queryRun.parmTableMethodsParam_DC(DocSysQueryFormTableMethodsParam::CanUpdate);

queryRun.parmPreview_DC(true);

queryRun.parmAllowExportImportQuery_DC(true);

// Open the SysQueryForm form

queryRun.prompt();

}

This code snippet demonstrates how to set up a button to open the Docentric Query Browser with customized options, enabling users to create, preview, and export queries easily.

Limitations

As amazing as it is, Docentric Query Browser also has some limitations:

It's not as powerful as creating a query in Visual Studio.
The Copy SQL to clipboard functionality copies the query without the literals in the where clause.
When opened from Docentric Table Browser, renaming the data sources is not available. This funcionality is only available when using Query Browser to edit data source queries for Docentric reports (e.g., License plate labels layouts).
A system administrator must enable access to the Docentric Query Browser for regular users by adding the DocSysQueryForm privilege to the specified security role to which the specified regular user is assigned.
When adding new data sources to a query, and according to D365FO security, users will only be able to add tables where they have rights based on their roles.

Although the Docentric Query Browser may not match the full capabilities of Visual Studio for complex queries, it excels in providing an intuitive, user-friendly solution for most day-to-day needs.

Why use Docentric Query Browser?

Docentric Query Browser is a game-changer for ad-hoc query building, debugging, troubleshooting, and previewing data in D365FO. Designed for both functional users and developers, it enables ad-hoc reporting directly within the D365FO app. Whether you're fine-tuning query performance or quickly analyzing data, this tool empowers you to work smarter and more efficiently.

We encourage you to explore Docentric Query Browser and unlock the potential for smarter, more dynamic querying within your D365FO environment – all for FREE.

Give it a try and let us know your thoughts. We’d love to hear from you!

Docentric Query Browser for D365FO

Understanding the standard query form

Introducing Docentric Query Browser

Key features of Docentric Query Browser

Select table fields and methods

Enhanced joins

Instant query preview and Export to Excel

Export / Import queries between users and environments

Calling Docentric Query Browser from code

Limitations

Why use Docentric Query Browser?

See also

Leave a Reply Cancel reply