How to limit results returned in reference qualifier but make them all available

[Bill Claman]

We have a scenario on one of our app engine forms where under certain conditions, a lookup will attempt to return 183,000 rows for the popup selection. There are a number of other fields in that same retrieved table but none of that information will be known by the users filling out the form.

Has anyone found a way of restricting the result set to something more useable like 10,000 records?  And then if you did, how did you handle the user putting in filter information for a column but the data they were looking for was not in that group of returned records?

[Marc Mouries]

I've seen users being confused after they set filters in the table view that pops up and not realizing that the record they are looking for does not appear in the list because they have a typo in the filters or missed a "*". 

This is the reason why - especially when the list of records is large - a good practice is to add fields in the form to prompt the user for data that can be used in a dynamic reference qualifier to filter the results. 

[Bill Claman]

I agree but in this case the there are not any other fields to help filter that the users will know. After the list shows, they will likely understand some of the other values enough to help filter but by then it is too late. The delay already happened and they likely got impatient waiting on the results (because it just shows a blank screen while it it building the result list), closed the window and called somebody to complain or filled out a helpdesk ticket since the selection is required.

[Marc Mouries · Accepted solution]

In this case take a look at 

- Index suggestions for slow queries

- https://community.servicenow.com/community?id=community_article&sys_id=db724e3fdb20f014981a0b55ca961950

[Bill Claman]

the 2nd link seemed to have worked!  Thanks.

To anyone else with this same issue:  in the article linked above in the "One Solution" section there is a reference to "...add the three fields shown below" - it is referring to the fields in the blue box - use Configure Form Design to do it.  Once you check the first box, the next one will show up and then the 3rd after checking the 2nd