SOLVED: Assets Custom Field AQL Filter using conditional logic

[Derek Fields _RightStar_]

Situation

I have two Object Types: Office and User. The important attributes are:

Office:

• Managed Offices -- this is an Object Reference to other Office objects

User:

• Jira User -- points to the Jira User account
• Office -- the office that the user is currently assigned to
• Superuser -- whether the user can manage ALL offices

I have a Custom Field of Assets type called "Office" that allows the Reporter to select from a list of offices that their current Office manages. 

For example, we have five Offices (A, B, C, D, E) where A manages B and C and the Reporter is in A, then they will see in the Office drop down B and C. They won't see D or E because those offices aren't managed by A

HOWEVER, If they are a Superuser, then they see all of the offices.

Problem

I can solve the first part of the requirement, which is to show only the offices that are managed by the reporter's current office. The AQL in the Issue Filter looks like this:

ObjectType = Office AND
Object HAVING inboundReferences(ObjectType = Office AND 
  Object HAVING inboundReferences(ObjectType = User AND 
  "Jira User" = currentReporter())

This works to reference through to the Offices that are managed by the Office that the Jira User is assigned to.

However, I need a way to "turn off" this filter if the user is a Superuser, in which case, they need to see all of the offices. 

I tried this version

ObjectType = Office AND
  (Object HAVING inboundReferences(ObjectType = Office AND 
    Object HAVING inboundReferences(ObjectType = User AND 
      "Jira User" = currentReporter()))
OR
(ObjectType = User and "Jira User" = currentReporter() AND Superuser = true)

This didn't work, which I assume is because I am trying to return two different object types.

Is there are way to accomplish this in AQL?

[Kevin Patterson]

Hi @Derek Fields _RightStar_ ,

I'm not 100% sure this can be done with AQL alone, but give this a try and let me know:

ObjectType = Office AND
(Object HAVING inboundReferences(ObjectType = Office AND 
Object HAVING inboundReferences(ObjectType = User AND 
"Jira User" = currentReporter()))

OR

ObjectType = "Office" AND currentReporter() in (objectType = "User" AND "Jira User" = currentReporter() AND Superuser = true)

[Derek Fields _RightStar_]

Thanks for jumping in. However, the syntax that you provided isn't legal. You can't embed an AQL within the (). It requires specifying it as an Object Having [in|out]boundReferences. 

At this point, I think that my only option is to create two Custom Fields. One that limits the selection and one that doesn't limit the selection. I will use a behavior to show the right field depending on the user's setting.

[gyorgy.rauf]

Hi @Derek Fields _RightStar_

I believe the condition is working but since you already have AND operators, you need to but them in brackets.

I would try this way:

(ObjectType = Office AND (Object HAVING inboundReferences(ObjectType = Office AND Object HAVING inboundReferences(ObjectType = User AND "Jira User" = currentReporter()))) OR (ObjectType = "Office" AND Object HAVING inR(objectType = "User" AND "Jira User" = currentReporter() AND Superuser = true))

You can also use inR() and outR() instead of the long format. :)

[Derek Fields _RightStar_]

@gyorgy.rauf Thanks for reaching out. I was ultimately able to get this working. I appreciate you jumping in after all this time to help.