Query

Query is a new feature we are introducing to the lookup formula to enable more precise control over how results are filtered based on what the user types in the autocomplete field.

In standard lookups, we have a limit of 200 results, which means that the lookup function first returns only the first 200 rows from the table and only then filters them based on the text that the user enters in the autocomplete field. If there are more than 200 rows in the table, the lookup will not search through all the data, but only the first 200, and will display the matching results based on those. This means that if the relevant data is outside the first 200, the user may simply not see it.

When we use the query() function, the situation is different. Instead of limiting itself to the first 200 results, the lookup function searches the entire table or list of data and returns only the first 50 matching results. As the user types more letters into the autocomplete field, the number of results returned by the function gradually decreases, depending on how closely the entered term matches the data in the table. This gives the user full control over the filtering of results.

Example: lookup(SYSUSER, NAME) vs lookup(SYSUSER, NAME=query(), NAME)

Let's assume that we have a SYSUSER table with user data (e.g. their first and last names). We want to implement autocomplete in a cell to help the user search for users from this table.

Formula without query()

=lookup(SYSUSER, NAME)

When the user starts typing text in the autocomplete field, e.g. “a”, lookup first retrieves only the first 200 rows from the SYSUSER table, regardless of whether they contain the letter “a”. After retrieving these 200 rows, lookup filters them, selecting only those that contain the letter “a” in the name (i.e. those that match the text entered by the user). Therefore, regardless of how many users start with the letter ‘a’, lookup will only display those who are in the first 200 rows of the table.

Formula with query()

=lookup(SYSUSER,NAME=QUERY(),NAME)

In this case, when the user starts typing text, e.g. the letter ‘a’, the lookup function searches the entire SYSUSER table instead of limiting itself to the first 200 records. The results are then filtered based on the text entered, and the user will see the first 50 matching results.