AQL "cross asset" queries

Question

Hi,

I'm tearing my hair out trying to get some form of AQL to work somehow - and test it.

I have 2 assets in my schema: "Billing Accounts" and "Users".

"Billing Accounts":

* Name (default label)

"Users":

* Name
* Email (default label)
* Billing Account: Object linking to "Billing Accounts"

This way I have a link between a given user and their corresponding billing account name.

I want to run an AQL query where I can select the a "Billing Accounts" object where that object is linked to a Users object where the Email is equal to a known value.

From the Asset lists, I can write an AQL "advanced query" it fails if I try to reference another object in my schema than the one where I'm trying to run it on.

For example, in the "Billing Accounts" asset the AQL query:

"Billing Accounts"."Name" = "My Company"

Returns the My Company asset object as expected.

But

"Billing Accounts"."Name" = "Portal Users"."Billing Account" AND "Portal Users"."Email" = "me@yopmail.com"

Returns an error, despite there being an object with the email me@yopmail.com, and that object is linked against an asset.

I can and often have written SQL joins all day, and even JQL for advanced reporting, but I cannot find a way after 2 days of throwing everything against the wall of getting an AQL query to link 2 objects together...

I've tried the same queries, setting up the Asset Object as a custom field, : The field accepts both :

objectType="Billing Accounts" AND objectType = "Users"

and even

schemaType="Customer Support Related Data"

As a valid Filter Scope, but whatever I try in the Filter Issue Scope is always rejected.

I've tried playing with the inboundReferences, outboundReferences, there has to be a way to query and join 2 assets together, otherwise why bother having references, but I just cannot find any way to get a list of objects that are linked to another object where that antother object has a field with a value of xyz...

Help...? Please? I can't see where I'm going wrong, and can't find any similar use case discussed or explained on line...

kate.brenden · Answer

@Daniel you might be able to accomplish this using the 'object HAVING outboundReference() functionality. I'm not familiar enough with it to recommend a solution in your context, but I was able to accomplish something similar in a JIra form where the custom fields listed are selections a user made earlier in the form:

object HAVING outboundReferences("Application Name" = ${customfield_12639.label}) AND object HAVING outboundReferences(object having inboundReferences("Short Name" = ${customfield_12640.label}, referenceType IN (Value)))

In this circumstance, I have 3 object types, A, B, and C. A has an attribute link to B, and C has a attribute link to B. This field will return all objects in C that are matched to A via B. For example,

A = Jira; B = Access to a Role; C = Request a new Jira Role. If I pick Jira, this field will return Request a new Jira role even though there's no direct link between the two objects.

Anyway, I know that is a *completely* different scenario from what you have outlined, but hopefully you can scrap together pieces of it to get what you are looking for. I've found outbound/inbound reference to be a VERY helpful AQL query because it gives you access to everything in the 'linked objects' box. Good luck!

Victor Feria · Answer

Hi Daniel,

I have now the same issue as you with join in AQL, did you find a solution?
I believe that AQL does not support it, you can only filter attributes with related objects.

Thank you