Issue
How to ensure a Reference field with Auto-complete enabled will always display results to a user
Description
Any list in Servicenow processes ACLs for each record as it displays for a user to determine whether the record will be visible or not to that logged in user..
This can result in the Number of rows removed from this list by security constraints message being displayed on the list being viewed.
For a normal list view, this is sufficient to understand what has prevented records being returned, however sometimes it is not obvious that a list is being returned and you do not see the error message above.
This is the case for Reference fields with Auto-complete enabled.
A user can click on a reference field which will evaluate the first 20 results returned, if no records pass the ACLs then regardless of whether there are records that pass the ACL conditions further on in the list or not you will be presented with the message "No matches found". If you then type in the first few letters you will see the results start to appear as the records filter down and the result set fits in the 20 record limit here.
To avoid this situation, you must consider using an alternative to ensure that fewer records are returned to be processed by the Reference Qualifier (especially if there is no Reference Qualifier for the Reference field).
The best option for this is a Before Query Business Rule.
This has a couple of benefits:
- Less results will be returned from the server, so the time taken to process ACLs for the entire list will be reduced resulting in a performance improvement in the loading time for that list.
- You will not encounter the "No matches found" when you click into a Reference field with auto-complete enabled due to the first 20 records not passing ACLs for the destination table.
Procedure
There are many different ways to implement your Before Query Business Rule as it depends on your business needs and requirements. It may be as simple as copying the script in the ACL that brought you to this page in the first place.
Our documentation has some detail on the Before Query Business Rule specifically:
https://docs.servicenow.com/csh?topicname=business-rule-examples.html&version=latest
And further detail on Business Rules in general can be found starting from here:
https://docs.servicenow.com/csh?topicname=c_BusinessRules.html&version=latest
The Community is also a great source of information where you can ask specific customisation and implementation questions to discuss and create solutions with your peers around the globe. Here is a great example of the support you receive from a similar question:
https://community.servicenow.com/community?id=community_question&sys_id=32c3c7a5dbd8dbc01dcaf3231f961961
Applicable Versions
All versions of Servicenow can be enhanced by using Business Rules where possible instead of ACLs.
Additional Information
As this behaviour is by design, the above is the best avenue to ensure the right data is presented to your users at the right time.
