Related List Queries
A way to easily join a related table into an existing query
What are they?
A way to easily join a related table into an existing query
Some examples
- Users with 1+ Open Incident
- /sys_user_list.do?sysparm_query=RLQUERYincident.caller_id,>=1^active=true^ENDRLQUERY
- Users with 0 Active Incidents
- /sys_user_list.do?sysparm_query=RLQUERYincident.caller_id,=0^active=true^ENDRLQUERY
- Users with >1 Computer Assigned to Them
- /sys_user_list.do?sysparm_query=RLQUERYcmdb_ci_computer.assigned_to,>1^ENDRLQUERY
How do I get to them?
I’m not as familiar with the structure of them yet, so instead of writing them from memory, I usually build them in the Reporting module first and then copy and paste it wherever I need them.
- Go to a list view, and jump from that query into the reporting module by clicking “Bar Chart” or “Pie Chart”
- Once you’re there, expand the filter to show the “Related List Conditions” section
- Run the report
- Click on a bar to jump back into the list view
- Right-click and copy the query and paste wherever you need it
Right click a field header to jump straight from a list view to the reporting module, Click the Filter and then expand the Related List Condition section, All active users with one or more active Incident
active=true^RLQUERYincident.caller_id,>=1^active=true^ENDRLQUERY^active=1
How are they structured?
RLQUERY${RELATED_TABLE}.${RELATED_FIELD_NAME},${OPERATOR}${LIMIT}^ENDRLQUERY
Example:
RLQUERYincident.caller_id,>=1^active=true^ENDRLQUERY
RELATED_TABLE: The related table you’re looking to join from (ex: incident)
RELATED_FIELD_NAME: The table you’re looking to query from, this should point to the table you’re querying from (ex: caller_id)
OPERATOR: >, >= , =, <, <=
LIMIT: Can be any number
Do they work everywhere?
Kind of. They work via the URL, and they work in scripts. I’ve even used them in Performance Analytics Indicators and Indicator Sources.
However, I’ve had mixed success using them in Reference Qualifiers. As it turns out, if you’re using them in a Reference Qualifier you can ONLY use the Related List Query.
For example, this won’t work as a reference qualifier for the sys_user table. This would instead show all active users.
active=true^RLQUERYincident.caller_id,>=1^active=true^ENDRLQUERY
But this will work as a reference qualifier for the sys_user table
RLQUERYincident.caller_id,>=1^active=true^ENDRLQUERY^EQ