Search bar algorithm not perfect

bbelo · November 4, 2022, 2:42pm

Hi Community.

The Search Bar algorithm in the list block is not perfect. It will find patterns of multiple strings if they are coming in perfectly “as is” from the selected Airtable columns. However, it won’t find partial strings (chopped by words), strings mixed in sequence, strings with commas in between, or added words in between (like “in” or “at”) etc…

Let’s say I have an Airtable of people (person’s name, business name, address)
Typical use cases:
Find me a person’s name at business name
Find me a person’s name, business name
Find me a business name, person’s name
Find me a person’s name, address
Find me a business name, address
Find me a person’s name at address
Find me a business name at address
Find me … etc, etc, etc…

It just won’t do that.

In other words, this isn’t your Google search
Ideally, Softr should update their search algorithm. Short of that, I’ve decided to create common permutations of search patterns as formula columns in Airtable with CONCATENATE() function, and add all of those columns in the Softr list block search bar’s “search by” field.

I’ve got about 100 or so search pattern permutations. So, 100 fields.
Question: how many Airtable fields can I add to the list block search bar’s “search by” field before it starts crippling performance? How well does it scale?

Thank you
Boris

Rekrt · March 2, 2023, 9:54am

I second this issue.

The search bar feature of Softr only searches for “perfect” matches and not by single keywords. This basically makes the search bar unusable in cases where I want to allow users to search across multiple fields (a feature that is actually offered by Softr).

artur · March 2, 2023, 4:20pm

Currently it’s using exact match with contains logic over multiple fields. For more advanced cases the data needs to be taken out from airtable and put into specific engines that provide advanced search functionalities.

Rekrt · March 2, 2023, 6:20pm

Absolutely understand this can’t be the same as a proper search engine. But in my opinion the logic just doesn’t make sense the way it is now. Let me give you an example:

Data structure:
Name (string) | Skills (tags) | Residence (string)

Content:
Jon Doe | [Java][SQL] | Berlin

You’re displaying this data in a list view. The list view block allows you to add a search bar and select the fields that are to be searchable. Let’s assume you select all three fields as searchable.

Search results:
‘Jon’ →
‘Jon Doe’ →
‘Doe Jon’ →
‘Jon Berlin’ →
‘SQL’ →
‘SQL Java’ →

Even though I understand the options are limited without a proper search engine, I think the basic text search could be improved. Perhaps a configuration option could be added to the search bar ‘Only exact matches’.

artur · March 2, 2023, 9:27pm

This is mostly related the way we interact with Airtable as a datasource. We do work with it real time and use their API (we don’t copy their data over to our storage and maintain) and currently the filtering is not giving us flexibility. What other datasources would you consider ?

bbelo · March 3, 2023, 9:28pm

Xano, of course! But generally speaking, I can’t wait for the day Softr will access an API data source. It will put Softr on another level.

artur · March 5, 2023, 12:52pm

@bbelo working on it
Re XANO could you pls share if you use it as more of an API or more of a database with tables ?

Rekrt · July 17, 2023, 8:28am

@artur I would like to come back to this topic.

As you can see from my example above, the search is quite imperfect at the moment to the extent that our users feel that it’s broken (they are reporting it to us as a bug).

Airtable’s APIs allow the use of formulas in search queries which I’m sure you’re aware. By using RegEx it should be possible to achieve at least a search matching that doesn’t require the matched terms to be in the right order. That would already fix a lot of cases, such as searching for tags (which currently must be in the right order).

What do you think?

bbelo · July 17, 2023, 2:21pm

@artur , We use Xano as database to store all of content simply because Airtable can’t scale beyond 50K records or regular plan on 250K records on an enterprise plan (which is ridiculously expensive).

So, the main reason is database scale.

We build some pages that retrieve data from Xano for display and then we embed this as an iframe in Softr. Clunky, but works.

bbelo · July 17, 2023, 2:27pm

@Rekrt , The search is in fact imperfect. We found a work-around for that by creating a very very very long Airtbale formula that accounts for every possible permutation of search query word placements and retunrs the results. Seems to work great on a small scale. Will see how well it performs when the table starts to grow.

But it depends on the use case, too. Ours is fairly simple - Name, Title, Business Name, Address. There is only so many placement permutations of those.

Long term, frankly, I don’t think this will matter as much due to the advent of LLMs. We will be able to train an LLM on all of our data in Airtable and build a query bot that will understand query in any permutation of word placements. Natural language and follow-up questions will be, of course, the cherry on top!

Rekrt · July 17, 2023, 2:56pm

Thanks. Agree that LLMs should be able to significantly improve full-text search in general.

In the meantime, do you mind sharing the Airtable formula you are using to create the permutations?

bbelo · July 17, 2023, 3:16pm

@Rekrt , now brace yourself…

"|",
CONCATENATE({Business Type (from Business Type ID)}," ",{State or Province}),
"|",
CONCATENATE({Business Type (from Business Type ID)},", ",{State or Province}),
"|",
CONCATENATE({Business Type (from Business Type ID)}," in ",{State or Province}),
"|",
CONCATENATE({Business Type (from Business Type ID)}," ",{State or Province Full}),
"|",
CONCATENATE({Business Type (from Business Type ID)},", ",{State or Province Full}),
"|",
CONCATENATE({Business Type (from Business Type ID)}," in ",{State or Province Full}),
"|",
CONCATENATE({Business Type (from Business Type ID)}," ",{Zip or Postal Code}),
"|",
CONCATENATE({Business Type (from Business Type ID)},", ",{Zip or Postal Code}),
"|",
CONCATENATE({Business Type (from Business Type ID)}," in ",{Zip or Postal Code}),
"|",
CONCATENATE({Business Type (from Business Type ID)}," ",{City}," ",{State or Province}),
"|",
CONCATENATE({Business Type (from Business Type ID)},", ",{City}," ",{State or Province}),
"|",
CONCATENATE({Business Type (from Business Type ID)}," in ",{City}," ",{State or Province}),
"|",
CONCATENATE({Business Type (from Business Type ID)},", ",{City},", ",{State or Province}),
"|",
CONCATENATE({Business Type (from Business Type ID)}," in ",{City},", ",{State or Province}),
"|",
CONCATENATE({Business Type (from Business Type ID)}," ",{City}," ",{State or Province Full}),
"|",
CONCATENATE({Business Type (from Business Type ID)},", ",{City}," ",{State or Province Full}),
"|",
CONCATENATE({Business Type (from Business Type ID)}," in ",{City}," ",{State or Province Full}),
"|",
CONCATENATE({Business Type (from Business Type ID)},", ",{City},", ",{State or Province Full}),
"|",
CONCATENATE({Business Type (from Business Type ID)}," in ",{City},", ",{State or Province Full}),

"|-|",

CONCATENATE({City}," ",{Business Type (from Business Type ID)}),
"|",
CONCATENATE({City},", ",{Business Type (from Business Type ID)}),
"|",
CONCATENATE({City}," ",{State or Province}," ",{Business Type (from Business Type ID)}),
"|",
CONCATENATE({City},", ",{State or Province}," ",{Business Type (from Business Type ID)}),
"|",
CONCATENATE({City}," ",{State or Province Full}," ",{Business Type (from Business Type ID)}),
"|",
CONCATENATE({City},", ",{State or Province Full}," ",{Business Type (from Business Type ID)}),
"|",
CONCATENATE({Zip or Postal Code}," ",{Business Type (from Business Type ID)}),
"|",
CONCATENATE({Zip or Postal Code},", ",{Business Type (from Business Type ID)})
)

bbelo · July 17, 2023, 3:17pm

@Rekrt , sorry the code above didn’t copy/paste correctly. Here is the correct version.

CONCATENATE(
CONCATENATE({First and Last Name}," ",{Business Type (from Business Type ID)}),
"|",
CONCATENATE({First and Last Name},", ",{Business Type (from Business Type ID)}),
"|",
CONCATENATE({First and Last Name}," ",{Business Name}),
"|",
CONCATENATE({First and Last Name},", ",{Business Name}),
"|",
CONCATENATE({First and Last Name}," ",{City}),
"|",
CONCATENATE({First and Last Name},", ",{City}),
"|",
CONCATENATE({First and Last Name}," in ",{City}),
"|",
CONCATENATE({First and Last Name}," ",{State or Province}),
"|",
CONCATENATE({First and Last Name},", ",{State or Province}),
"|",
CONCATENATE({First and Last Name}," in ",{State or Province}),
"|",
CONCATENATE({First and Last Name}," ",{State or Province Full}),
"|",
CONCATENATE({First and Last Name},", ",{State or Province Full}),
"|",
CONCATENATE({First and Last Name}," in ",{State or Province Full}),
"|",
CONCATENATE({First and Last Name}," ",{Zip or Postal Code}),
"|",
CONCATENATE({First and Last Name},", ",{Zip or Postal Code}),
"|",
CONCATENATE({First and Last Name}," in ",{Zip or Postal Code}),
"|",
CONCATENATE({First and Last Name}," ",{City}," ",{State or Province}),
"|",
CONCATENATE({First and Last Name},", ",{City}," ",{State or Province}),
"|",
CONCATENATE({First and Last Name}," in ",{City}," ",{State or Province}),
"|",
CONCATENATE({First and Last Name},", ",{City},", ",{State or Province}),
"|",
CONCATENATE({First and Last Name}," in ",{City},", ",{State or Province}),
"|",
CONCATENATE({First and Last Name}," ",{City}," ",{State or Province Full}),
"|",
CONCATENATE({First and Last Name},", ",{City}," ",{State or Province Full}),
"|",
CONCATENATE({First and Last Name}," in ",{City}," ",{State or Province Full}),
"|",
CONCATENATE({First and Last Name},", ",{City},", ",{State or Province Full}),
"|",
CONCATENATE({First and Last Name}," in ",{City},", ",{State or Province Full}),

"|-|",

CONCATENATE({First and Last Name}," ",{Business Type (from Business Type ID)}," ",{City}),
"|",
CONCATENATE({First and Last Name},", ",{Business Type (from Business Type ID)}," ",{City}),
"|",
CONCATENATE({First and Last Name},", ",{Business Type (from Business Type ID)},", ",{City}),
"|",
CONCATENATE({First and Last Name}," ",{Business Type (from Business Type ID)},", ",{City}),
"|",
CONCATENATE({First and Last Name}," ",{Business Type (from Business Type ID)}," in ",{City}),
"|",
CONCATENATE({First and Last Name},", ",{Business Type (from Business Type ID)}," in ",{City}),
"|",
CONCATENATE({First and Last Name}," ",{Business Type (from Business Type ID)}," ",{State or Province}),
"|",
CONCATENATE({First and Last Name},", ",{Business Type (from Business Type ID)}," ",{State or Province}),
"|",
CONCATENATE({First and Last Name},", ",{Business Type (from Business Type ID)},", ",{State or Province}),
"|",
CONCATENATE({First and Last Name}," ",{Business Type (from Business Type ID)},", ",{State or Province}),
"|",
CONCATENATE({First and Last Name}," ",{Business Type (from Business Type ID)}," in ",{State or Province}),
"|",
CONCATENATE({First and Last Name},", ",{Business Type (from Business Type ID)}," in ",{State or Province}),
"|",
CONCATENATE({First and Last Name}," ",{Business Type (from Business Type ID)}," ",{State or Province Full}),
"|",
CONCATENATE({First and Last Name},", ",{Business Type (from Business Type ID)}," ",{State or Province Full}),
"|",
CONCATENATE({First and Last Name},", ",{Business Type (from Business Type ID)},", ",{State or Province Full}),
"|",
CONCATENATE({First and Last Name}," ",{Business Type (from Business Type ID)},", ",{State or Province Full}),
"|",
CONCATENATE({First and Last Name}," ",{Business Type (from Business Type ID)}," in ",{State or Province Full}),
"|",
CONCATENATE({First and Last Name},", ",{Business Type (from Business Type ID)}," in ",{State or Province Full}),
"|",
CONCATENATE({First and Last Name}," ",{Business Type (from Business Type ID)}," ",{Zip or Postal Code}),
"|",
CONCATENATE({First and Last Name},", ",{Business Type (from Business Type ID)}," ",{Zip or Postal Code}),
"|",
CONCATENATE({First and Last Name},", ",{Business Type (from Business Type ID)},", ",{Zip or Postal Code}),
"|",
CONCATENATE({First and Last Name}," ",{Business Type (from Business Type ID)},", ",{Zip or Postal Code}),
"|",
CONCATENATE({First and Last Name}," ",{Business Type (from Business Type ID)}," in ",{Zip or Postal Code}),
"|",
CONCATENATE({First and Last Name},", ",{Business Type (from Business Type ID)}," in ",{Zip or Postal Code}),

"|-|",

CONCATENATE({First and Last Name}," ",{Business Type (from Business Type ID)}," ",{City}," ",{State or Province}),
"|",
CONCATENATE({First and Last Name},", ",{Business Type (from Business Type ID)}," ",{City}," ",{State or Province}),
"|",
CONCATENATE({First and Last Name},", ",{Business Type (from Business Type ID)},", ",{City}," ",{State or Province}),
"|",
CONCATENATE({First and Last Name}," ",{Business Type (from Business Type ID)},", ",{City}," ",{State or Province}),
"|",
CONCATENATE({First and Last Name}," ",{Business Type (from Business Type ID)}," in ",{City}," ",{State or Province}),
"|",
CONCATENATE({First and Last Name},", ",{Business Type (from Business Type ID)}," in ",{City}," ",{State or Province}),
"|",
CONCATENATE({First and Last Name}," ",{Business Type (from Business Type ID)}," ",{City},", ",{State or Province}),
"|",
CONCATENATE({First and Last Name}," ",{Business Type (from Business Type ID)}," ",{City},", ",{State or Province}),
"|",
CONCATENATE({First and Last Name},", ",{Business Type (from Business Type ID)}," ",{City},", ",{State or Province}),
"|",
CONCATENATE({First and Last Name},", ",{Business Type (from Business Type ID)},", ",{City},", ",{State or Province}),
"|",
CONCATENATE({First and Last Name}," ",{Business Type (from Business Type ID)},", ",{City},", ",{State or Province}),
"|",
CONCATENATE({First and Last Name}," ",{Business Type (from Business Type ID)}," in ",{City},", ",{State or Province}),
"|",
CONCATENATE({First and Last Name},", ",{Business Type (from Business Type ID)}," in ",{City}," ",{State or Province}),
"|",
CONCATENATE({First and Last Name},", ",{Business Type (from Business Type ID)}," in ",{City},", ",{State or Province}),
"|",
CONCATENATE({First and Last Name},", ",{Business Type (from Business Type ID)}," in ",{City}," ",{State or Province}),

"|-|",

CONCATENATE({First and Last Name}," ",{Business Type (from Business Type ID)}," ",{City}," ",{State or Province Full}),
"|",
CONCATENATE({First and Last Name},", ",{Business Type (from Business Type ID)}," ",{City}," ",{State or Province Full}),
"|",
CONCATENATE({First and Last Name},", ",{Business Type (from Business Type ID)},", ",{City}," ",{State or Province Full}),
"|",
CONCATENATE({First and Last Name}," ",{Business Type (from Business Type ID)},", ",{City}," ",{State or Province Full}),
"|",
CONCATENATE({First and Last Name}," ",{Business Type (from Business Type ID)}," in ",{City}," ",{State or Province Full}),
"|",
CONCATENATE({First and Last Name},", ",{Business Type (from Business Type ID)}," in ",{City}," ",{State or Province Full}),
"|",
CONCATENATE({First and Last Name}," ",{Business Type (from Business Type ID)}," ",{City},", ",{State or Province Full}),
"|",
CONCATENATE({First and Last Name}," ",{Business Type (from Business Type ID)}," ",{City},", ",{State or Province Full}),
"|",
CONCATENATE({First and Last Name},", ",{Business Type (from Business Type ID)}," ",{City},", ",{State or Province Full}),
"|",
CONCATENATE({First and Last Name},", ",{Business Type (from Business Type ID)},", ",{City},", ",{State or Province Full}),
"|",
CONCATENATE({First and Last Name}," ",{Business Type (from Business Type ID)},", ",{City},", ",{State or Province Full}),
"|",
CONCATENATE({First and Last Name}," ",{Business Type (from Business Type ID)}," in ",{City},", ",{State or Province Full}),
"|",
CONCATENATE({First and Last Name},", ",{Business Type (from Business Type ID)}," in ",{City}," ",{State or Province Full}),
"|",
CONCATENATE({First and Last Name},", ",{Business Type (from Business Type ID)}," in ",{City},", ",{State or Province Full}),
"|",
CONCATENATE({First and Last Name},", ",{Business Type (from Business Type ID)}," in ",{City}," ",{State or Province Full}),

"|-|",

CONCATENATE({First and Last Name}," ",{Business Name}," ",{City}," ",{State or Province}),
"|",
CONCATENATE({First and Last Name},", ",{Business Name}," ",{City}," ",{State or Province}),
"|",
CONCATENATE({First and Last Name}," at ",{Business Name}," ",{City}," ",{State or Province}),
"|",
CONCATENATE({First and Last Name},", ",{Business Name},", ",{City}," ",{State or Province}),
"|",
CONCATENATE({First and Last Name}," at ",{Business Name},", ",{City}," ",{State or Province}),
"|",
CONCATENATE({First and Last Name}," ",{Business Name},", ",{City}," ",{State or Province}),
"|",
CONCATENATE({First and Last Name}," at ",{Business Name},", ",{City}," ",{State or Province}),
"|",
CONCATENATE({First and Last Name}," ",{Business Name}," in ",{City}," ",{State or Province}),
"|",
CONCATENATE({First and Last Name},", ",{Business Name}," in ",{City}," ",{State or Province}),
"|",
CONCATENATE({First and Last Name}," at ",{Business Name}," in ",{City}," ",{State or Province}),
"|",
CONCATENATE({First and Last Name}," ",{Business Name}," ",{City},", ",{State or Province}),
"|",
CONCATENATE({First and Last Name}," at ",{Business Name}," ",{City},", ",{State or Province}),
"|",
CONCATENATE({First and Last Name}," ",{Business Name}," ",{City},", ",{State or Province}),
"|",
CONCATENATE({First and Last Name}," at ",{Business Name}," ",{City},", ",{State or Province}),
"|",
CONCATENATE({First and Last Name},", ",{Business Name}," ",{City},", ",{State or Province}),
"|",
CONCATENATE({First and Last Name},", ",{Business Name},", ",{City},", ",{State or Province}),
"|",
CONCATENATE({First and Last Name}," ",{Business Name},", ",{City},", ",{State or Province}),
"|",
CONCATENATE({First and Last Name}," at ",{Business Name},", ",{City},", ",{State or Province}),
"|",
CONCATENATE({First and Last Name}," ",{Business Name}," in ",{City},", ",{State or Province}),
"|",
CONCATENATE({First and Last Name}," at ",{Business Name}," in ",{City},", ",{State or Province}),
"|",
CONCATENATE({First and Last Name},", ",{Business Name}," in ",{City},", ",{State or Province}),
"|",
CONCATENATE({First and Last Name},", ",{Business Name}," in ",{City}," ",{State or Province}),

"|-|",

CONCATENATE({First and Last Name}," ",{Business Name}," ",{City}," ",{State or Province Full}),
"|",
CONCATENATE({First and Last Name},", ",{Business Name}," ",{City}," ",{State or Province Full}),
"|",
CONCATENATE({First and Last Name}," at ",{Business Name}," ",{City}," ",{State or Province Full}),
"|",
CONCATENATE({First and Last Name},", ",{Business Name},", ",{City}," ",{State or Province Full}),
"|",
CONCATENATE({First and Last Name}," at ",{Business Name},", ",{City}," ",{State or Province Full}),
"|",
CONCATENATE({First and Last Name}," ",{Business Name},", ",{City}," ",{State or Province Full}),
"|",
CONCATENATE({First and Last Name}," at ",{Business Name},", ",{City}," ",{State or Province Full}),
"|",
CONCATENATE({First and Last Name}," ",{Business Name}," in ",{City}," ",{State or Province Full}),
"|",
CONCATENATE({First and Last Name},", ",{Business Name}," in ",{City}," ",{State or Province Full}),
"|",
CONCATENATE({First and Last Name}," at ",{Business Name}," in ",{City}," ",{State or Province Full}),
"|",
CONCATENATE({First and Last Name}," ",{Business Name}," ",{City},", ",{State or Province Full}),
"|",
CONCATENATE({First and Last Name}," at ",{Business Name}," ",{City},", ",{State or Province Full}),
"|",
CONCATENATE({First and Last Name}," ",{Business Name}," ",{City},", ",{State or Province Full}),
"|",
CONCATENATE({First and Last Name}," at ",{Business Name}," ",{City},", ",{State or Province Full}),
"|",
CONCATENATE({First and Last Name},", ",{Business Name}," ",{City},", ",{State or Province Full}),
"|",
CONCATENATE({First and Last Name},", ",{Business Name},", ",{City},", ",{State or Province Full}),
"|",
CONCATENATE({First and Last Name}," ",{Business Name},", ",{City},", ",{State or Province Full}),
"|",
CONCATENATE({First and Last Name}," at ",{Business Name},", ",{City},", ",{State or Province Full}),
"|",
CONCATENATE({First and Last Name}," ",{Business Name}," in ",{City},", ",{State or Province Full}),
"|",
CONCATENATE({First and Last Name}," at ",{Business Name}," in ",{City},", ",{State or Province Full}),
"|",
CONCATENATE({First and Last Name},", ",{Business Name}," in ",{City},", ",{State or Province Full}),
"|",
CONCATENATE({First and Last Name},", ",{Business Name}," in ",{City}," ",{State or Province Full}),

"|-|",

CONCATENATE({Business Type (from Business Type ID)}," ",{First and Last Name}," ",{City}," ",{State or Province}),
"|",
CONCATENATE({Business Type (from Business Type ID)},", ",{First and Last Name}," ",{City}," ",{State or Province}),
"|",
CONCATENATE({Business Type (from Business Type ID)},", ",{First and Last Name},", ",{City}," ",{State or Province}),
"|",
CONCATENATE({Business Type (from Business Type ID)}," ",{First and Last Name},", ",{City}," ",{State or Province}),
"|",
CONCATENATE({Business Type (from Business Type ID)}," ",{First and Last Name}," in ",{City}," ",{State or Province}),
"|",
CONCATENATE({Business Type (from Business Type ID)},", ",{First and Last Name}," in ",{City}," ",{State or Province}),
"|",
CONCATENATE({Business Type (from Business Type ID)}," ",{First and Last Name}," ",{City},", ",{State or Province}),
"|",
CONCATENATE({Business Type (from Business Type ID)}," ",{First and Last Name}," ",{City},", ",{State or Province}),
"|",
CONCATENATE({Business Type (from Business Type ID)},", ",{First and Last Name}," ",{City},", ",{State or Province}),
"|",
CONCATENATE({Business Type (from Business Type ID)},", ",{First and Last Name},", ",{City},", ",{State or Province}),
"|",
CONCATENATE({Business Type (from Business Type ID)}," ",{First and Last Name},", ",{City},", ",{State or Province}),
"|",
CONCATENATE({Business Type (from Business Type ID)}," ",{First and Last Name}," in ",{City},", ",{State or Province}),
"|",
CONCATENATE({Business Type (from Business Type ID)},", ",{First and Last Name}," in ",{City}," ",{State or Province}),
"|",
CONCATENATE({Business Type (from Business Type ID)},", ",{First and Last Name}," in ",{City},", ",{State or Province}),
"|",
CONCATENATE({Business Type (from Business Type ID)},", ",{First and Last Name}," in ",{City}," ",{State or Province}),

"|-|",

CONCATENATE({Business Type (from Business Type ID)}," ",{First and Last Name}," ",{City}," ",{State or Province Full}),
"|",
CONCATENATE({Business Type (from Business Type ID)},", ",{First and Last Name}," ",{City}," ",{State or Province Full}),
"|",
CONCATENATE({Business Type (from Business Type ID)},", ",{First and Last Name},", ",{City}," ",{State or Province Full}),
"|",
CONCATENATE({Business Type (from Business Type ID)}," ",{First and Last Name},", ",{City}," ",{State or Province Full}),
"|",
CONCATENATE({Business Type (from Business Type ID)}," ",{First and Last Name}," in ",{City}," ",{State or Province Full}),
"|",
CONCATENATE({Business Type (from Business Type ID)},", ",{First and Last Name}," in ",{City}," ",{State or Province Full}),
"|",
CONCATENATE({Business Type (from Business Type ID)}," ",{First and Last Name}," ",{City},", ",{State or Province Full}),
"|",
CONCATENATE({Business Type (from Business Type ID)}," ",{First and Last Name}," ",{City},", ",{State or Province Full}),
"|",
CONCATENATE({Business Type (from Business Type ID)},", ",{First and Last Name}," ",{City},", ",{State or Province Full}),
"|",
CONCATENATE({Business Type (from Business Type ID)},", ",{First and Last Name},", ",{City},", ",{State or Province Full}),
"|",
CONCATENATE({Business Type (from Business Type ID)}," ",{First and Last Name},", ",{City},", ",{State or Province Full}),
"|",
CONCATENATE({Business Type (from Business Type ID)}," ",{First and Last Name}," in ",{City},", ",{State or Province Full}),
"|",
CONCATENATE({Business Type (from Business Type ID)},", ",{First and Last Name}," in ",{City}," ",{State or Province Full}),
"|",
CONCATENATE({Business Type (from Business Type ID)},", ",{First and Last Name}," in ",{City},", ",{State or Province Full}),
"|",
CONCATENATE({Business Type (from Business Type ID)},", ",{First and Last Name}," in ",{City}," ",{State or Province Full}),

"|-|",

CONCATENATE({Business Type (from Business Type ID)}," ",{First and Last Name}),
"|",
CONCATENATE({Business Type (from Business Type ID)},", ",{First and Last Name}),

"|-|",

CONCATENATE({Business Name}," ",{Business Type (from Business Type ID)}),
"|",
CONCATENATE({Business Name},", ",{Business Type (from Business Type ID)}),
"|",
CONCATENATE({Business Name}," ",{City}),
"|",
CONCATENATE({Business Name},", ",{City}),
"|",
CONCATENATE({Business Name}," in ",{City}),
"|",
CONCATENATE({Business Name}," ",{State or Province}),
"|",
CONCATENATE({Business Name},", ",{State or Province}),
"|",
CONCATENATE({Business Name}," in ",{State or Province}),
"|",
CONCATENATE({Business Name}," ",{State or Province Full}),
"|",
CONCATENATE({Business Name},", ",{State or Province Full}),
"|",
CONCATENATE({Business Name}," in ",{State or Province Full}),
"|",
CONCATENATE({Business Name}," ",{Zip or Postal Code}),
"|",
CONCATENATE({Business Name},", ",{Zip or Postal Code}),
"|",
CONCATENATE({Business Name}," in ",{Zip or Postal Code}),
"|",
CONCATENATE({Business Name}," ",{City}," ",{State or Province}),
"|",
CONCATENATE({Business Name},", ",{City}," ",{State or Province}),
"|",
CONCATENATE({Business Name}," in ",{City}," ",{State or Province}),
"|",
CONCATENATE({Business Name},", ",{City},", ",{State or Province}),
"|",
CONCATENATE({Business Name}," in ",{City},", ",{State or Province}),
"|",
CONCATENATE({Business Name}," ",{City}," ",{State or Province Full}),
"|",
CONCATENATE({Business Name},", ",{City}," ",{State or Province Full}),
"|",
CONCATENATE({Business Name}," in ",{City}," ",{State or Province Full}),
"|",
CONCATENATE({Business Name},", ",{City},", ",{State or Province Full}),
"|",
CONCATENATE({Business Name}," in ",{City},", ",{State or Province Full}),

"|-|",

CONCATENATE({Business Type (from Business Type ID)}," ",{City}),
"|",
CONCATENATE({Business Type (from Business Type ID)},", ",{City}),
"|",
CONCATENATE({Business Type (from Business Type ID)}," in ",{City}),
"|",
CONCATENATE({Business Type (from Business Type ID)}," ",{State or Province}),
"|",
CONCATENATE({Business Type (from Business Type ID)},", ",{State or Province}),
"|",
CONCATENATE({Business Type (from Business Type ID)}," in ",{State or Province}),
"|",
CONCATENATE({Business Type (from Business Type ID)}," ",{State or Province Full}),
"|",
CONCATENATE({Business Type (from Business Type ID)},", ",{State or Province Full}),
"|",
CONCATENATE({Business Type (from Business Type ID)}," in ",{State or Province Full}),
"|",
CONCATENATE({Business Type (from Business Type ID)}," ",{Zip or Postal Code}),
"|",
CONCATENATE({Business Type (from Business Type ID)},", ",{Zip or Postal Code}),
"|",
CONCATENATE({Business Type (from Business Type ID)}," in ",{Zip or Postal Code}),
"|",
CONCATENATE({Business Type (from Business Type ID)}," ",{City}," ",{State or Province}),
"|",
CONCATENATE({Business Type (from Business Type ID)},", ",{City}," ",{State or Province}),
"|",
CONCATENATE({Business Type (from Business Type ID)}," in ",{City}," ",{State or Province}),
"|",
CONCATENATE({Business Type (from Business Type ID)},", ",{City},", ",{State or Province}),
"|",
CONCATENATE({Business Type (from Business Type ID)}," in ",{City},", ",{State or Province}),
"|",
CONCATENATE({Business Type (from Business Type ID)}," ",{City}," ",{State or Province Full}),
"|",
CONCATENATE({Business Type (from Business Type ID)},", ",{City}," ",{State or Province Full}),
"|",
CONCATENATE({Business Type (from Business Type ID)}," in ",{City}," ",{State or Province Full}),
"|",
CONCATENATE({Business Type (from Business Type ID)},", ",{City},", ",{State or Province Full}),
"|",
CONCATENATE({Business Type (from Business Type ID)}," in ",{City},", ",{State or Province Full}),

"|-|",

CONCATENATE({City}," ",{Business Type (from Business Type ID)}),
"|",
CONCATENATE({City},", ",{Business Type (from Business Type ID)}),
"|",
CONCATENATE({City}," ",{State or Province}," ",{Business Type (from Business Type ID)}),
"|",
CONCATENATE({City},", ",{State or Province}," ",{Business Type (from Business Type ID)}),
"|",
CONCATENATE({City}," ",{State or Province Full}," ",{Business Type (from Business Type ID)}),
"|",
CONCATENATE({City},", ",{State or Province Full}," ",{Business Type (from Business Type ID)}),
"|",
CONCATENATE({Zip or Postal Code}," ",{Business Type (from Business Type ID)}),
"|",
CONCATENATE({Zip or Postal Code},", ",{Business Type (from Business Type ID)})
)

Rekrt · July 23, 2023, 9:17am

Thanks for sharing. I’ve ended up using the following JS script in an Airtable automation to create various possible permutations and store them in a hidden search field:

let specialChars = ['+', '-', '&', ',', '.', '(', ')', 'and', 'or', 'of', 'for'];

// Function to generate permutations of all words in a sentence
function calculatePermutations(sentence) {
    console.log('Generating permutations for: ', sentence);

    // Stores all words in the sentence
    let lis = sentence.split(" ");

    // Clean special chars
    lis = lis.filter(word => specialChars.indexOf(word) < 0);
 
    // Reduce to 5 words max
    lis = lis.slice(0, 5);

    // Stores all possible permutations of words in this list
    return permuteGenerator(lis);
}
 
// Function to generate permutations using generators
function* permuteGenerator(arr, n = arr.length) {
    if (n <= 1) yield arr.slice();
    else
        for (let i = 0; i < n; i++) {
            yield* permuteGenerator(arr, n - 1);
            const j = n % 2 ? 0 : i;
            [arr[n - 1], arr[j]] = [arr[j], arr[n - 1]];
        }
}

MelissaM · August 6, 2023, 6:25pm

I’m interested in this as well. I’d really like to pull data from Algolia. My dream is to replace my hand coded app (curlsbot.com) with something like softr. I’d need to be able to have a larger search field that takes multiple comma separated queries. It would be so awesome to have the results be from the Airtable record that I use as a source for Algolia.

I’d also consider using the formula shown here since it’s similar enough to what I do in my hand coded app, but I still need to be able to do multiple searches at once.

softrsimon · September 5, 2023, 9:27am

I am running into the same problem with climesumer.com.

An accurate search is a must. I’ve tried to use a tags database with synonym words, but a more effective search would cure all these workarounds.

E.g. I have a product with the tag “shoe”. If a user searches for “shoe” it works, but only a small change like “running shoe” already doesn’t work.
Even if I had “running shoe” in my database if a user types “shoe for running”, it would not work again!

Since listing pages are a core feature of Softr, I think a powerful search should be high up the prior list. Many thanks @artur.

Rekrt · March 29, 2024, 10:10am

Hey @artur , I wanted to follow-up on this.

Still today one of the main issues we have with Softr is its lack of proper search functionality. In this thread we have learned that one workaround is to create all possible term permutations in a separate Airtable field. However, this isn’t feasible for content that exceeds more than just a few terms.

I am wondering if

a) you are planning to add any form of advanced search functionality, or
b) can suggest any other workarounds.

Thanks!

artur · March 29, 2024, 10:38pm

In order to perform more advanced search on top of Airtable data we need to copy your data into our database and be able to perform those… Data copying and persisting is something we are avoiding as much as we can…

How big is the data set in question in general ?

Rekrt · March 30, 2024, 7:55am

We’re building a job platform with Softr. Each candidate has a job title, usually consisting of 1-5 terms (Airtable text field). In addition, each candidate has a list of approx. 10 skills associated (Airtable tag field). Now, we want to enable our users to use the search bar to search for job title & skill combinations, e.g. “react developer”. This should find someone who has “Senior Frontend Developer” as job title and “React” as one of their skills.

An added level of complexity here is that for a full-text search you wouldn’t normally expect multiple search terms to be connected with a hard “AND”. Instead, a full text search would simply score results which include the most search terms higher.

So I guess search is indeed a more comprehensive feature to build, however, one I believe should be a key capability of a platform like Softr.

Rekrt · March 30, 2024, 8:09am

By the way, here’s a hint of how this could be achieved through Airtable’s API:

filterByFormula=OR(FIND("term", Field1), FIND("term", Field2))