You may have seen Query Definitions in a Tree Grid View demo, but did you know you can also use Query Definitions (QD) to define reusable queries for your Aras method code? This blog post walks through the Identity Map community project and demonstrates how you can use Query Definitions in your own methods.
This tutorial assumes a basic familiarity with Query Builder and Query Definitions. If you’re new to these features or you just want a quick refresher, check out our previous blog post, An Introduction to Query Builder and Tree Grid View.
Getting Started
The code snippets shown below are part of the Identity Map Aras Community Project. If you want to apply the project or view the source code, find it on the Aras Labs GitHub:
ArasLabs/identity-map
identity-map – Demonstrates how to execute a Query Definition programmatically and visualize the output with D3.
Executing the Query Definition
There are three basic steps to executing a Query Definition in your method code: get the Query Definition structure, apply any configuration options or parameters to the structure, then run the qry_ExecuteQueryDefinition action on the structure.
Get the Query Definition Structure
When you get the qry_QueryDefinition item from the server, it's important to get the item's structure, not just the top level item. In the sample code below, we set the "levels" attribute to retrieve the structure without having to specify each relationship type and related item.
Apply Configuration Options & Parameters
Every Query Definition has options that can be configured at runtime, like "offset" and "fetch". The offset option indicates the first row in the results (default is 0). The fetch option indicates the maximum number of results to return. We need to add these settings to the Query Definition structure wrapped in a CDATA element. Here's the sample:
If you have parameters configured on your Query Definition, we can also pass values in the Query Definition structure. Just get the qry_QueryParameter Item(s) from the structure and overwrite the default value.
Note: The sample above uses a Query Definition with only one parameter configured. If you have multiple parameters, you will need to loop through them or be more specific with the XPath to get a single parameter.
Execute the Query
Lastly, all we need to do is set the action to "qry_ExecuteQueryDefinition", reset the levels attribute back to "0", and apply the request to the server.
Parse the Result
When you get the response back from the server, you can parse the results into any format you need. The resulting XML looks a bit like your typical AML data structure. However, it has some important differences to consider:
- The Item tag uses an "alias" attribute instead of "type".
- The "alias" attribute may or may not match an ItemType name. You can explicitly set the alias in the Query Builder GUI.
- The Item tag does not include the typical "id", "typeId", or "keyed_name" attributes.
- Item properties contain the id of the referenced item.
- Related items appear in the "Relationships" tag on the relationship item – not in the "related_item" tag.
If you want to see the result that your Query Definition generates, you can use the Execute Query action in the Query Builder UI.
Putting It All Together
Here's the full snippet that runs the Query Definition in the Identity Map community project:
Note: This post contains JavaScript sample snippets. The same concepts apply to C#/VB if you want to execute a Query Definition in your server-side code.
Additional Resources
If you're looking for more resources and information about Query Definitions, check out the content listed below:
Aras Demo Series – Query Builder & Tree Grid View: In this webinar, learn how Query and Tree Grid View Definitions can be built using a graphical interface to identify the specific elements of their data model and configure how this information is displayed to their end users.
An Introduction to Query Builder and Tree Grid View: Walk through the basics of configuring a Query Definition and Tree Grid View in Aras Innovator.
Tech Tip: Filter a Tree Grid View with Parameters: A tutorial on configuring parameters for your Tree Grid View. Parameters let end users set filters to hone in on the most meaningful data in a Tree Grid View.
Tree-grid-view-sample: This project adds a Tree Grid View to the Package Definition ItemType to help admins better understand the contents of a particular package.
Effectivity-sample-application: The Effectivity Sample Application does not focus on TGV/QB, but it does use both for viewing effectivity on a Part BOM. The documentation also includes details about how the Query Definition and Tree Grid View are configured.
LOOKING FOR MORE ARAS INSPIRATION?
Subscribe to our blog and follow @ArasLabs on Twitter for more helpful content! You can also find our latest open-source projects and sample code on the Aras Labs GitHub page.