Improve exact search

[JA003]

Hello everyone!

Is there a way to ignore selectively characters inputted in the search field? (or some other workaround).

I have a column-type TEXT with data that can have numbers, letters, and dashes. When users try to find a record input in the search field using dashes and the record has the same characters BUT without the dashes, AppSheet can't find a match, a single dash ruins the search.

What is happening now is that users that input the data add it without dashes e.g.: "123ABC"
And the person who manages those records when in need to find records uses the key found in PDF documents where it's perfectly written WITH DASHES, so input that number using copy (from PDF) / paste (into AppSheet search bar) like "123-ABC" getting no matches, forcing to remove dashes.

As a side note: The "numbers" are very different from each other, having from 4 to 15 characters, including one or more dashes, because of that there is not a simple way to just take the user input in the column and add the missing dashes in a VC.

Is there a way for me to ignore the dashes from the search field so even if inputted like "123-ABC" AppSheet also search for non-exact matches and find "123ABC"?

Thanks in advance for your kind reply!

[WillowMobileSys]

Unfortunately, no, you can't modify the search function in a way to search with special characters or without.

However, you can add an additional column to help find these records when searching.  The idea is to have a column that is HIDDEN/NOT SHOWN, is included in the set of columns of the view and is set as Searchable. 

When a user inputs a value, assign an App Formula to the hidden column that removes any inserted special characters leaving just the significant alphanumeric characters.  When a search is performed, it can find the row by either matching "abc-123" value in the user entered column OR by matching the modified value of "abc123" in the hidden column.

I hope this helps!

[JA003]

Thanks a lot for your reply, unfortunately, the main issue I am experiencing now would not be solved like this since data is saved already without dashes but the search is performed with dashes (for the ease of just copying and pasting). Any extra column used to eliminate dashes will contain the same info as the original column, when searching with dashes will not find any match.

Regards,

[Joseph_Seddik]

You are making a wrong assumption.  Please implement the solution rather than jumping to conclusions. 

[WillowMobileSys]

As @Joseph_Seddik implies,  you can supply a hidden column that removes the hyphens after the fact.  there are 2 ways:

1)  Insert a "normal" column and then in your data for all the existing rows, perform a one time retroactive population of this new column that removes the dashes with a temporary sheet formula or , if using a database, a db SET function.  Then set a column expression that will remove the dashes for any NEW entries going forward.

2)  Create a hidden Virtual Column, set the expression that will remove the dashes from the original column, Save and Sync.  All existing data will be updated and any new entries will be updated as they are entered or updated.

The drawback of the second approach is that the Virtual Column will be re-calculated on EVERY Sync.  Not a big problem unless you have many Virtual Columns that add up or the table is HUGE and/or grows rapidly (meaning it will become HUGE in time).