If we want to filter and show data in a table based on a reference field which is bringing data from another table, the simplest approach which comes to our mind is to dot-walk to the reference field’s table and form a query.
For example, let’s say we have a table called Tickets which has a reference field to a group table.If we want to filter records in the tickets table based on the values of groups & build a complex query with it, the simplest approach would be to use group.name is one of {a list of values} or group.sys_id is one of {a list of values} or anything else based on requirement. This may be done directly from filters or even in a query business rule where you can call methods and get values returned to further form your query.
This approach works fine but an issue is that the values being returned get appended to the URL.
A sample URL with only 4 entries being returned looks like:
https://dev_MY_INSTANCE.service-now.com/u_ticket_list.do%3Fsysparm_query%3Du_user_group.sys_idINxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx%xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx%xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx%xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
This is a big risk when records are huge in number. In such cases, the URL will never load.
A simple solution to this is to create a dynamic filter option for the reference field (group, in our example).
Make sure to use the correct field type and Reference table. Here, in the example stated, I’ve made a call to a script include to return a few records based on which I want to further build a query.
You can use this dynamic filter option in a filter for a table, as a reference qualifier or even in the query business rules. This is also a better approach if you do not want to implement Read ACLs to avoid the security constraint message.
This shortens the URL to something like this:
https://dev_MY_INSTANCE.service-now.com/u_ticket_list.do%3Du_user_groupDYNAMICxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx%26sysparm_first_row%3D1%26sysparm_view%3D
