Reverse Lookups with Query Builder

Reverse Lookups with Query Builder

Long time users of Aras Innovator may be familiar with configuring an empty relationship to display data using the ReverseItemsGrid.html file included with the PE application. This custom grid lets users easily see the parent items in a database that is using a given item (e.g. the parent Parts that a CAD item is related to). This feature was great, but it took time to configure and wasn't easily customizable since it existed as a file in the codetree. Fortunately, there's a better way to do these kinds of reverse lookups in newer versions of Aras Innovator: Query Definitions.

Configuring the Query Definition

In this blog, we'll be creating a reverse lookup query that lets us see what Group Identities our users belong to. The benefit to this approach is that we can verify our reverse lookup is correct by checking the standard Identity Membership report. We'll start by creating a new Query Definition, setting the Context Item Type to Identity, and opening up the editor.

Use Referencing Item

Once we're in the editor, we can start building out our query. We can right-click on the root Identity node of our definition and select Add Related Item > Using Referencing Item... .

Selecting this will bring up a dialog that contains a list of all of the item properties with the Identity ItemType as the data source. For our example, we'll be looking for the item labeled Member (Has Member). This means that we'll be adding a reverse lookup to the Member that exists between a Group Identity and the Identities that make it up. The name in parentheses, Has Member, is the label of the property. In this case, Has Member is the label of related_id.

Add a Link to the Parent Item

Now that we have the reverse lookup to our relationship, we'll need to add a link to the source item of this relationship. Since the source_id is just an item property, we can do this using the Add Related Item > Using Item Property... action.

Loop this Query

In the intro blog post, we used the Reuse Query Element Definition action to make our query look all the way down a Part BOM structure. We can also do the exact same thing to make our query search all the way up a reverse lookup structure as well. Let's right-click on our parent Identity and make it reuse the definition of our root-level identity. 

Finish the Query

Now that we have the bulk of the query in place, we can put the finishing touches on our query. We'll select the Name property to be returned from both our root Identity as well as its parents, and we'll also select the From Date and End Date properties from our Member relationship.

Test the Query

With our query finished, we can test it out before building and publishing our Tree Grid View. As stated before, we can validate that our query is returning all the correct information by using the Identity Membership report. In my test environment, this report indicates that one of my users, Mike Miller, belongs to a number of different Group Identities.

To test our query, we can run Actions > Execute Query from the toolbar of our Query Definition tab. This will bring up a dialog that lets me enter some additional criteria to filter the query. I'll choose to run this query just against Mike Miller, so I can more easily compare the results to what my membership report returned.

The result of the query will be in raw XML, but I can still see that all of the Group Identities have been successfully returned by my reverse lookup.

Choose How to Display this Data

The beauty of having our reverse lookup defined as a Query Definition is that we now get a number of different options to display this data to the user. We could add a new relationship tab to display this data in a Tree Grid View. We could set up a Graph View that would open in a new tab when the user runs an action. We could even build an entirely custom view like the Identity Map community project. Even better might be to implement all of these to give users options and let them see the information in the way that works best for them. 

Query Definitions are powerful, easily configurable, and will only become more reusable as different views are added. As a testament to their versatility of Query Definitions, all of the existing views that used the old ReverseItemsGrid.html for their reverse lookups have been updated to use Query Definitions instead.

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.