An Introduction to Query Builder and Tree Grid View

Two powerful solutions were introduced into the Aras Platform in 11.0 SP10: Query Builder (QB) and Tree Grid View (TGV). QB is a tool for building powerful, reusable AML queries using a graphical user interface. TGV displays the results of one of these queries either in a relationship grid or on-demand through an action. In this blog post, we’ll be covering the basics of these new solutions by re-creating the Part MultiLevel BOM relationship with a little extra information.

Creating Your First Query Using QB

These reusable queries are stored as a new ItemType in innovator. We’ll start by navigating to Administration > Configuration > Query Definitions and following the steps below to create our reusable query.

  1. Create a new Query Definition
  2. Fill in the following information
    • Name: Parts and Owners
    • Context Item Type: Part
    • Description: Return a list of Child parts as well as they’re Owner Identities
  3. Save the Query Definition
    • After saving, you should notice a new button in the sidebar becomes visible
  4. Click the “Show Editor” button in the sidebar

The Query Builder Editor

The first thing you’ll see in the editor is the root element of our query on the right and several icons on the left. To begin with, we’ll add some additional items to our query by right-clicking on the root element.

We want to get the Identity that owns this part. This information is stored in the owned_by_id property of the Part ItemType which we can add to the query by selecting Add Related Item > Using Item Property… . In the resulting dialog, we can search for owned_by_id, select it, and click Add.

Now that we have the owner, we want to go ahead and also get any child parts as part of this query. We can do this by selecting Add Related Item > Using Relationship… and selecting BOM. We want to make sure that Include Related box is checked, so that we’ll also add the related Part to the query and not just the Part BOM relationship item.

Great! We can see now that we’ll also get any child parts with this query. However, this doesn’t account for the situation where a child part has children itself. Fortunately, Query Builder allows us to easily define a recursive query for just such a situation. We can do this by right-clicking on the recently added child part and selecting Reuse Query Element Definition. Pick the top-level Part in the resulting dialog. After running this, your query should look something like the screenshot below.

Now that we seem to have our query in place, we can execute it to make sure that the results are what we expect.  To do this, we can run the Execute Query action from the Actions menu. This action will bring up a dialog that will let you pass any additional parameters into the query such as a specific id or item_number. For this example, we’ll just click the blue Execute button at the bottom. Your results should be something like below.

That’s not quite right. We’re getting the items we expect, but there’s no additional information about them. This is because we haven’t selected any properties to be returned. If we right-click on the top-level Part element of the query, we can select Edit Query Element > Selected Properties… we can choose the properties that should be returned similar to editing the select attribute of an AML query. In this example, we’re going to choose Part NumberNameGeneration, and Revision.

Notice that any properties we select for the top level Part are also applied to the child because it’s reusing the query definition. We’re also going to select some properties for the Owner Identity. Let’s do this a little differently and instead click on the Properties icon to bring up the selection dialog.

If we execute this query again, we’ll see that the query now returns the properties that we’ve selected as we expect. However, we still need a way to translate the AML returned by the query into a more readable format for the end user. This is where Tree Grid View comes in.

Displaying Your Query WIth Tree Grid View

We’ll first navigate to Administration > Configuration > Tree Grid Views and create a new item with the following information.

  • Name: Parts and Owners
  • Query Definition: Parts and Owners
  • Description: Return a list of Parent and Child parts as well as they’re Owner Identities

Let’s save this item and click the “Show Editor” button in the side bar. You should see a simple grid with all of the cells initially blanked out. For both of the Part cells and the Identity Cell, we’ll right-click on them and choose Map Element in order to add them to this grid.

Now that all of our elements are mapped there are a few different things that we need to do. To begin with, we’ll need to add 3 more columns to this grid. We can do this by right-clicking on the column header and selecting Add New Column. After doing that, we’ll need to change the column names to match the properties that we’re going to display in them. When finished, your grid should look something like this.

The last step we’ll need to configure is what data is going to be displayed in each cell. To do this, we’ll right-click and chose Cell Display Settings. This will bring up a dialog where you can map properties into the cell templates. For each column, map the corresponding property until your grid matches the one below.

With that finished, all we need to do is add this grid to our Part ItemType. The easiest way to do this is to use the Set Tree Grid View Usage action from the Actions menu and follow the steps below.

  1. Choose Relationship Tab as the Target Usage
    1. Click Next
  2. Choose to create a New relationship
    1. Click Next
  3. Set the Label to Parts and Owners
    1. Click Next
  4. Leave the Starting Condition as is and click Next
    • This starting condition will use whatever Part you are currently viewing as the root of the query
  5. Click Generate

Now let’s go to one of our assemblies and see the finished product!

We can see that our TGV relationship has been automatically added to Part and displays all of the information that we expect with no extra code required!

Conclusion

The example in this blog post was intentionally simple and just scratches the surface of the power that Query Builder provides. One of the more powerful use cases that we may cover in a later blog post is how to easily and quickly perform a reverse look up query similar to what is currently supported with the Reverse Items Grid, but with the added benefit of being easily customizable. If you are using 11.0 SP10+, we highly recommend exploring some of the capabilities of Query Builder.

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. In particular, check out the Tree Grid View Sample project that Eli put out recently for a helpful example of how you can use QB/TGV to more easily manage your exportable packages!

4 thoughts on “An Introduction to Query Builder and Tree Grid View

  1. Eric Domke

    It would also be worth mentioning that with versionable item types (like Part), the query builder does not restrict the results set to is_current = 1 records like AML does by default. Therefore, you need to add a condition for is_current = 1 to your query builder definition yourself. If you don’t, you will see all generations of every part.

  2. AngelaIp

    Will the tree grid view also work for Federated ItemTypes?
    I have an idea for displaying full or mixed federated data from other system (like ERP) in Tree Grid style.

    Also I haven´t tried it by myself yet. As far as I see, AML has no trouble to access Federated Data. But maybe there is something to consider?

Leave a Reply