Blog

Search - Applying multiple AND/OR conditions to the Search function- Workaround

The search function performs a partial match against multiple fields based on a single search term. Where we want to combine additional search terms with a logical 'And' or 'Or', there's no way to do this alone with the search function. This post describes a workaround to perform this task.

The search function is very powerful, particularly against Dataverse and SQL Server data sources where this function is delegable. The purpose of the Search function is to perform partial matches against one or more fields based on a single search term. There is, however, no way to append multiple search terms using the 'and' or 'or' operators.

This post describes this behaviour in more detail and offers a workaround for the latter requirement

How to match a single search term against multiple fields

To demonstrate the basic use of the search function, let's take the example of a list of properties. We can search for records that contain the partial text "east" in the fields "address1" and "city" with the following syntax.

Search(Property, 
       "east",
       "Address1"
       "City"
)
The screenshot below illustrates the output. Notice the occurrences of the partial text "east" in either the Address1 or City fields.


Here's where the search function falls short. Let's suppose we now want to extend this example to return all records that contain the partial text "east" in either the "address1" or "city" fields, OR the partial text "uda" in the country" fields.

Because the search function accepts only a single term and provides no way to define additional search terms against specific fields, it isn't possible to fulfil this requirement.

Workaround - How to match multiple search terms against multiple multiple fields

The workaround for this limitation is to call the filter function and to apply the 'in' operator instead of calling Search. This formula highlights how we can specify multiple additional search terms with the 'or' (||) operator. 

Filter(
    Property,
    (
       "east" in Address1 ||
       "east" in City
     ) ||
    "uda" in Country
)

If instead, we want to match multiple search terms with a logical 'and', we can replace the '||' operator with the '&&' operator.

The screenshot beneath illustrates how this technique would appear on a search screen. Notice how the results include those records where "east" appears in Address1 or City, OR "udan" appears in the country field.

Conclusion

The search function does not support the ability to match multiple search terms against multiple multiple fields. We can workaround this limitation by applying the filter/in combination, and this post demonstrated this technique.
Previous
Next
Related posts