Search multiple columns with different criteria

The built in search is very easy to use, but it only allows a single search term across all (searchable) columns.

36%20AM

A multi-term search can be built by constructing a Virtual Column of type List with elements of type Reference. The formula should return a list of keys from the table you want to search, and should update the list based on your search terms.

FILTER("Order",  
  AND(
    OR(  ISBLANK([_THISROW].[Status])   ,   IN([Status] ,[_THISROW].[Status]) ),
    OR(  ISBLANK([_THISROW].[Status])   ,   CONTAINS([Name] ,[_THISROW].[Name]) ),
    OR(  ISBLANK([_THISROW].[DateStart])   ,   [Date] >= [_THISROW].[DateStart]  ),
    OR(  ISBLANK([_THISROW].[DateEnd])   ,   [Date] <= [_THISROW].[DateEnd]  )
  )
)

The basic idea is to have a matching column in your Search table for every column you want to search in the other table (Orders in this example). Then your formula matches records if you enter a search term, and ignores that term if you leave it blank.

The result is a list of related records that updates instantly (without syncing) based on your search terms.

ezgif-5-20de95c65b82

Sample HERE

Note that each user should have their own row in the Search table to ensure users do not edit each others searches. This could also be done using the USERSETTINGS() but I find a regular table more flexible.

22 Likes

Hi @GreenFlux,
This is awesome. Thank you for this.
I am trying to create a similar search view where I need to show the dates, between the two selected dates, that have no data.
How to achieve this?

Thank you.

2 Likes

-that have no data in what column(s)?

It sounds like you will need to combine the date range search with ISBLANK([Data_to_check]).

Or maybe make a Slice that only shows the rows with blank values in the desired column, then perform your search on that Slice.

In the whole table

Do you mean that you want to return a list of dates with no matching rows? Or in other words, find the dates where no entry was made?

For that you would need another table with ALL dates that relates back to your main table. From there you can COUNT() the [Related Records] to your main table and find the dates with zero related records.

EDIT- just saw your other post. I’ll continue the thread there if you have other questions.

2 Likes

Yes…please…we shall continue there.!
Thank you.

This is Beautiful. Truly useful. Great Tip :bow_and_arrow: Nailed it.

1 Like