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":
"Users":
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...
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
@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!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.