Search multiple columns with different criteria

GreenFlux
Participant V

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

2X_2_2d3ae1a1236b9a010b24cb3fc5c5414872d50aa3.png

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.

2X_0_096603f4b7204d0ab7ed68efba584e905a7b0f64.gif

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.

41 24 7,994
24 REPLIES 24

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.

-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.

Yesโ€ฆpleaseโ€ฆwe shall continue there.!
Thank you.

Jonathan_S
Participant V

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

Hello! Iโ€™m pretty new to Appsheet & not a programmer at all. Iโ€™ve been trying to create a way to search two different columns - oneโ€™s a drop-down list & the otherโ€™s a date. (I have 70 columns altogether.) Iโ€™m just using one table.

Do I need to create a โ€œsearchโ€ table?
How can I write it so we can scan for all the dates within a certain time period.

Iโ€™m really lost - Thanks in advance for any help!
Diane

Hi @Diane_Peterson,

Iโ€™d recommend learning a few of the basic before trying to build the search dashboard from this example.

Here are a few of the support docs that will help with filtering data.


Thanks for your response. If Iโ€™m understanding Slices right, me as the app creator would be the only one who could see the filtered data, right? That wonโ€™t work as I need to make a multiple search column that any of my users can use.

Have your considered using the native search features, or even the assistant is pretty neat.

TyAlevizos
Participant V

Love this thread. Iโ€™ve been meaning to build one of these as a dashboard. I loaded 10,000 fake records into the following app and used many of the concepts that @GreenFlux laid out. Thanks for that!

This is a bit on the deeper end of the Appsheet swimming pool. But well worth it if you need some advanced search built into your app.

https://www.appsheet.com/samples/A-peruser-filtering-dashboard?appGuidString=8957235e-f62e-4da4-9b93...

Hi @TyAlevizos , this link is not working. Could you share the updated app as I am in need of the app for search view.

Guillermo
Participant V

Hi @GreenFlux, how can I make a video like yours and link it to a post here in the community?
Thanks in advance!!

Hi @Guillermo,

I used Gifox to make the GIF in the original post, and then just inserted the image into the post by dragging the file into the post editor. It will upload image (including GIFโ€™s), but not videos.

For full videos, you have to upload it somewhere else first, like Google Drive, set it as public, and then paste a link, or the <embed> code.

Are you interested in making tutorial videos?

Thank you so much for your tip! I have already a free tutorial in spanish on YouTube and a complete course on my website, TailorSheet!

Thanks again @GreenFlux

First of all, thank you for your tutorial. It was EXTREMELY helpful.

Thereโ€™s only one thing I couldnโ€™t do yet:

How can I remove the Save/Cancel buttons?

In advance, thank you for the support.

Regards;

Fรกbio Nabuco Correia

To my knowledge, you canโ€™t remove those from a Form view. (You can do some trickery to hide them, but I donโ€™t think thatโ€™s what youโ€™re looking for hereโ€ฆ

Your screenshot is of a Form view, @GreenFlux created this visual with a Detail view with quick edits.

Hello !

I am trying to launch a similar search, using the detail view, but as a tab within a Dashboard view. I am struggling with creating a new search record for the logged in user when launching this view.  
Could someone help with this please?

Rifad
Participant V
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]  )
  )
)

Based on the above condition, [Status] appears to be a type ENUM. It is searching over ENUMLIST(FilterTable) to ENUM(Results table).

Does anyone know if we can use the same search function to search one ENUMLIST for another ENUMLIST? As of right now, it shows the result when ENUMLIST matches its position in the filter table with ENUMLIST in the result table. By searching this community, I wasn't able to find a better solution. Any information would be greatly appreciated. Thanks.

@Rifad ,

Just for clarification. Could you clarify if you mean

1) How to reconfigure the filter expression if the [Status] column in the "Orders" table in the above example is enumlist instead of an enum

2) If so, would you like the record in the "Orders" table to appear in search results if any one or more values from the enumlist in "Orders" table match the enumlist values in the search table?

 

1. Yes you are right. Both search column and orders table column is an ENUMLIST.

 

2. Both search and orders can have multiple items.

ex: 
Search: a , c
Order: a , b , c

Thank you , then maybe this?

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

 

Edit: I think original expression shared by @GreenFlux had a small typo in the second sub expression, the [Status] was inadvertently repeating in the [Name] related sub-expression as well. I have changed it to [Name] and highlighted.

I have used intersect before but it was giving me this error.

Screenshot 2023-06-19 at 4.27.37 PM.png

Screenshot 2023-06-19 at 4.27.52 PM.png

Could you share column types for [Status] columns in the two tables? And the error as shown with the expression assistant with the full expression?

Just wondering where a list of list is getting created and why INTERSECT() should fail on two lists, if the [Status] columns are list type in both filter and data ( Order table in the example) table

Top Labels in this Space