Glide’s search works fairly well, but it relies on having a collection that is displaying (on one or more pages) each of the rows you want to make available for searching.
I wanted to create a way for users to search my main sheet without having to display a collection that is then filtered down by the search. Rather I wanted a page with just a search box displayed, not a list of items. Then when the search term is entered it returns one or more items that match the search term. I have a preliminary approach but curious to hear if folks have ideas on how to make it work a little better.
My Approach
What I did was create a Glide table with just one row. In it I created a text column called “Search Term”, and then a relation column that relates that column to the main sheet. That relation column logic says "when the text in “Search Term” matches the value in a specified column in the main sheet, return that row. I then choose match multiple.
On the page, I put a text input box that inputs text to the Search Term column in my Glide Table, and then below it a collection that has as its source the relation column. Functionally and design wise it works great. Until there is text in the text input box, there are no items displayed. Once text is entered into the box, one or more items appear in the collection like search results.
The limitations
There are a few limitations to this. The biggest one is that for the search to work, the user must enter the term exactly as it appears in the column being looked up, which for my application is not as limiting as it sounds. For two of the most common searches in my app, the search terms are mostly well defined like a person’s last name or a number. But one problem there is that capitalization matters. For example, one use is to look up the name of authors. If they fail to capitalize the author’s last name–a defect that would not matter in the built-in search–the relation will not be made and the “search” will yield no results. Similarly, this approach only works if there is an exact match so, “Smith” will not match to “Bob Smith”
Questions
Is there some easy way to overcome the capitalization issue? Essentially transform the input from the user to the proper capitalization (I.e. capitalize the first letter)? Alternately, I thought of creating a duplicate column in the main sheet for each column that will be searched that takes the term and removes the capitalization and relate both columns (capitalized and lowercase) to the “Search Term” column and then have a collection for each relation column. That would work if I could figure out how to create the lowercase column. Any suggestions there?
The bigger challenge is how to implement some sort of fuzzy search for those search terms that aren’t as straightforward as a known person’s name. Is there any way that folks can think of to be able to relate one column to another using SOME of the words in that column, such that including too many words (Bob Smith) would work as well as including too few (Smith, instead of Bob Smith).
Thanks in advance for any ideas.
