Is there any wildcards available in search?

[Swoopy]

As shown in this picture, I am about to see rows containing texts like,

“Happy New Year”
“very happy this year”
“So happy those good years. I love you dear.”

,by using wildcards in combination with characters which normally available in RDBMS query.

Is there any wildcards like this in search?

Regards,

[Steve]

Search is a simple text match; there are no wildcards.

[Swoopy]

Is it available somewhere else in AppSheet?

[Steve]

Nope.

[Swoopy]

I am thinking about creating an advanced search form. Is it possible to do?

[Steve]

It is, but there is some complexity if your app has more than one user.

[Phil8055]

Hi Steve

can you search for any non-empty row?
My DB has a "comment" column. How do I search or filter for all non-empty rows?
Thanks

[Swoopy]

The mixture of:

• 2 new 1-row tables containing dereference expression in Valid-If
• 2 new detail views
• 2 new actions (for each view)
• A dashboard for the 2 detail views

I have got a poor man’s Advanced Search. It’s good enough for me.

Using these searches in combination with the AppSheet’s built-in search (at the top band & inside each column’s dropdown), user can dig down into what they want to search in each column.

[Josephine]

For anybody else trying to use a wildcard, I think I’ve found a way…
I have a table of invoices which may be awaiting a purchase order to be generated (usual practice being PO generated prior to invoice received). Rather than checking them again each day, I’ve created a slice which checks the invoices marked as awaiting PO against a list of PO’s which reference said invoice.

FIND(
UPPER([InvID]),
UPPER(LIST(PO Lines[ItemDescription]))
)>0

I now have a slice of Invoice IDs (which have their ID within the item description of POs), that I can trigger a bulk action on, to send them on their merry way to process and check for any discrepancies before payment.

[Swoopy]

Thank you for the tip but I don’t think that’s an aspect of using wildcards.

[Josephine]

@Swoopy
Valid, it gave me what I needed but may not translate to others. However, as is common with AppSheets - if the functionality isn’t there, build it / extrapolate what others have done.
I’ve manage to create a wildcard search:

This is a dashboard with a search at the top, and lines at the bottom matching ‘beef’ or ‘dice’. Obviously it could be tweaked to be ‘and’ instead of ‘or’, expand the number of potential searches (I only set it for two) etc.
It’s somewhat slow but this is tapped on to an extensive app I’ve built, and, while proving this functionality, I haven’t concentrated on speed for the formulas or making them the ‘smartest’, so I am certain others on here could make it much better. Regardless, in essence it is:

1. Table to store search term: SearchTest
2. Slice to get maxrow of SearchTest: [id]=MAXROW(“SearchTest”,"_RowNumber")
3. Table with lines to search, added two virtual columns:
   vSearchOne
   CONTAINS([ItemDescription],
   UPPER(
   INDEX(
   SPLIT(
   TOP(
   LIST(MaxSearchRow[SearchOne]),1),""),1)))
   vSearchTwo
   CONTAINS([ItemDescription],
   UPPER(
   INDEX(
   SPLIT(
   TOP(
   LIST(MaxSearchRow[SearchOne]),1),""),2)))
4. Slice with results from table searched: OR([vSearchOne]=TRUE,[vSearchTwo]=TRUE)

[1minManager · Accepted solution]

I have a client that wants this implimenting too. I was going to user an ENUMLIST to create a list of wildcard words and then use INDEX() and CONTAINS() to do the searching

[Swoopy]

@Josephine
@1minManager

Thanks to both of you for elaborations.

These hint me to re-think (after a very long time) of how to mimic the wildcard search.

[Luke_H]

I know this is older, but this worked really well for me. I created a view based off an inventory table called Materials with a concatenated column called search that has all the searchable text in one column (Materials[Search]). Then I set up a 6 word search in a different table called Search with a search column called text1 that will search and use any words in any order seperated by a spaces. This was the slice formula below

AND(CONTAINS([Search],INDEX(SPLIT(Search[Text1],’ ‘), 1))
,CONTAINS([Search],INDEX(SPLIT(Search[Text1],’ ‘), 2))
,CONTAINS([Search],INDEX(SPLIT(Search[Text1],’ ‘), 3))
,CONTAINS([Search],INDEX(SPLIT(Search[Text1],’ ‘), 4))
,CONTAINS([Search],INDEX(SPLIT(Search[Text1],’ ‘), 5))
,CONTAINS([Search],INDEX(SPLIT(Search[Text1],’ '), 6)))

[Swoopy]

Unfortunately I can’t download the search results which are form CONTAINS() expression. AppSheet just download all rows in the related table.

[Rifad]

Search multiple columns with different criteria Tips & Tricks 💡

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([_THISR…