Alternative Search Bar Approach

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”

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.


You have the right idea here, and you can lowercase both the search term and the main sheet column(s) using this plugin.

However, I would use JavaScript here to save 2 columns, because I believe we should trim the whitespace as well.

It would look like this.

return p1.toLowerCase().trim()

Then, what I would do is casting the search term to the main table using a single value column and set it to “first”, pointing to the lowercased & trimmed search term.

Finally, I would use an if then else column to retrieve a “Visible” boolean for my records.

If search term is empty then return null (because you don’t want anything to be displayed until you put in a search term), if search term is included in Column1 then true, do that for any number of columns you want, but make sure you also lowercase & trim all of them.

Then, in the collection, filter the list by visible is true.

I would not use a relation here, since there have been many cases where I experience that a relation based on a user-specific column does not “refresh” as expected when I change the user-specific column’s value. That might have something to do with a choice component and not a text entry, but I would still be careful.

1 Like

So do you mean if you have “Smith” in your data, searching Bob Smith would yield a result as well?

That could be accomplished if Glide would give us a “wildcard” character in matches. Typically, “*” is used as a wildcard character in (programming languages) that I have used.

This is great advice. I wasn’t using a user-specific column because that didn’t seem necessary. But now that you said that, I’m realizing that (of course) the value last entered by a user will be the next user’s default value. Doh. Your Javascript example is cleaner and the trim, I agree, is a good move, and yet another thing I wasn’t sure how to implement. Let me play with this.

That’s the result I want, yes. And also the reverse. If the data has Bob Smith, searching “Smith” should also yield a result. In my existing approach, since neither is an exact match of the other, the relation fails and so too the search…

I agree…is that in the offing? Would upvote a feature request if it’s out there.

You can do regex style matching with the Check Text Matches plugin.

Not familiar with Regex and have to say after testing it a bit I’m stumped. If I wanted to use it to say, for example, "when the user enters ‘Bob Smith’ ignore the Bob part and just match “smith”, what would that expression look like? Since Bob Smith would be entered into the “Search Term” field, I’m assuming I may need to use a template column to combine the value of the search term column with the value of a regex column to produce the expression that returns only the second word? I tested that and couldn’t make it work…

That use case seems a little odd. What if the user really was looking for “bob smith”? Which would be a reasonable assumption given that’s what they typed. Sure, you can craft a regular expression to ignore the first word. But should it always be ignored? That wouldn’t make sense. So, under what circumstances should it be ignored? And would you ever have a situation where you want to ignore the second word and just search for the first word? Or search for both? How would Glide know the difference? And what if the user types 3 words, or 4, or 5?

Whatever behaviour you’re looking for, it’s probably doable. But you need to start by having a clear definition of the expected result, including any edge cases. And then build logic around that.

Normally with a text search, typing more characters wil progressively narrow the list of search results. But it seems this is not the behaviour you are looking for?

The value in the column is just “Smith”. So since the “search” relies on a relation to that column, the term “Bob Smith” would not match anything. So being able to trim it would control for the user supplying extraneous information that makes the search not work.

Okay, but again - how does Glide know which of the two words entered is needed to form the relation?

As @ThinhDinh pointed out earlier, using relations here is probably not the best approach.

It occurred to me that maybe what you are looking for is essentially an OR search. That is, if a user enters “Bob Smith”, then return any results that contain either “Bob” or “Smith”.

Is that what you need?
If yes, I can describe a way to do that.

1 Like

I’m not sure if this is what you’re looking for, but anyway…

Copyable demo:


Really cool, Darren. This could give a fuzzy-type search. For example, you don’t need to care about capitalization or the order of the search keys. Name could be stored, last, first or first last and it would still find it.

1 Like

Thanks :slight_smile:
I started thinking about a way to do this yesterday, and then eventually decided to build it out just to prove to myself that it would work the way I expected it to work. And it does.
I was rather pleased that I was able to find a way to express all that logic in a single if-then-else column :wink:

1 Like

@Darren_Murphy I apologize for taking so long to reply to this. Got distracted on something else. Very very helpful, example. This gives me a lot of ideas that I will now play around with. And your points are of course well taken about the limits of my approach.

1 Like

Beautiful approach, thank you.
However, would anyone be able to explain to me why, when I enter text into the pseudo search box, it doesn’t go to the first row of the database, but fills in a random one? This problem does not allow me to make this search method work.

It should be based on whichever row your screen is attached to. Does your detail screen use filters, or do you use row owners?

The details screen doesn’t have filters, but the list screen does: it has the filter used for searching, but it clearly doesn’t work. I use a sorting with distance instead, from nearest to farthest.

Your reply to Jeff is a little confusing. When you say…

What exactly do you mean?
Are you trying to apply my method to a List Layout?
That’s not possible. It can only be used where you have an Inline List on a Details Layout.

1 Like