# Use the "search" function in the "filter by formula" of Airtable plugin with a lookup record

**URL:** <https://community.weweb.io/t/use-the-search-function-in-the-filter-by-formula-of-airtable-plugin-with-a-lookup-record/4421>\
**Category:** Ask us anything\
**Tags:** binding\
**Created:** [September 18, 2023, 4:27pm UTC](https://community.weweb.io/t/use-the-search-function-in-the-filter-by-formula-of-airtable-plugin-with-a-lookup-record/4421 "2023-09-18T16:27:15Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![MaxenceP](https://avatars.discourse-cdn.com/v4/letter/m/5e9695/32.png) [@MaxenceP](https://community.weweb.io/u/MaxenceP)\
**Post date:** [September 18, 2023, 4:27pm UTC](https://community.weweb.io/t/use-the-search-function-in-the-filter-by-formula-of-airtable-plugin-with-a-lookup-record/4421/1 "2023-09-18T16:27:15Z")

</div>

Hi,

I have two tables: Clients and Employees. Employees is linked to Clients and a column “client\_id” displays the related airtable client id.  
I would like to use the _filter by formula_ in the Airtable plugin to find all records in the Employees table that have the related airtable id.

What can I use ?

---

<div class="post-metadata">

**Author:** ![Joyce](https://sea2.discourse-cdn.com/flex016/user_avatar/community.weweb.io/joyce/32/13232_2.png) [@Joyce](https://community.weweb.io/u/Joyce)\
**Post date:** [September 19, 2023, 7:57am UTC](https://community.weweb.io/t/use-the-search-function-in-the-filter-by-formula-of-airtable-plugin-with-a-lookup-record/4421/2 "2023-09-19T07:57:03Z")

</div>

Hi @MaxenceP 👋

Here are the docs on the subject: [Airtable data source | WeWeb documentation](https://docs.weweb.io/plugins/data-sources/airtable-data.html#filters-by-formula)

The tricky part is getting the quotation marks right but the use case presented in the article seems to match very closely to what you’re trying to do

---

<div class="post-metadata">

**Author:** ![MaxenceP](https://avatars.discourse-cdn.com/v4/letter/m/5e9695/32.png) [@MaxenceP](https://community.weweb.io/u/MaxenceP)\
**Post date:** [September 19, 2023, 11:51am UTC](https://community.weweb.io/t/use-the-search-function-in-the-filter-by-formula-of-airtable-plugin-with-a-lookup-record/4421/3 "2023-09-19T11:51:55Z")

</div>

Hi Joyce and thank you for your answer.

I’ve already used the search function of this doc for a more basic approach: a simple column in a table and it works great.  
Here it doesn’t work, probably because the column I’m doing my search is an array.  
The forumula I tried is this one `'SEARCH("'+weweb_variable_for_entreprise_id+'",entreprise_id)'` where entreprise\_id is the lookup field of my clients table.

---

<div class="post-metadata">

**Author:** ![Joyce](https://sea2.discourse-cdn.com/flex016/user_avatar/community.weweb.io/joyce/32/13232_2.png) [@Joyce](https://community.weweb.io/u/Joyce)\
**Post date:** [September 20, 2023, 4:34pm UTC](https://community.weweb.io/t/use-the-search-function-in-the-filter-by-formula-of-airtable-plugin-with-a-lookup-record/4421/4 "2023-09-20T16:34:06Z")

</div>

Alright!

It took me a while. Here’s the whole process: [Maxence - filterByFormula with list in Airtable — Tella](https://tella.video/maxence-filterbyformula-with-list-in-airtable-49ij)

Want to skip ahead?

- use the [`ARRAYJOIN` formula](https://support.airtable.com/docs/formula-field-reference#array-functions) in Airtable to turn your array into a string
- this will allow you to use Airtable’s [`FIND` or `SEARCH` formulas](https://support.airtable.com/docs/formula-field-reference#text-operators-and-functions) (which can only search through strings) when setting up your collection in WeWeb

Thanks for the inspiration @MaxenceP! Even though it’s an Airtable limitation, it’s a great topic for a future WeWeb tutorial.

---

<div class="post-metadata">

**Author:** ![MaxenceP](https://avatars.discourse-cdn.com/v4/letter/m/5e9695/32.png) [@MaxenceP](https://community.weweb.io/u/MaxenceP)\
**Post date:** [September 21, 2023, 7:08am UTC](https://community.weweb.io/t/use-the-search-function-in-the-filter-by-formula-of-airtable-plugin-with-a-lookup-record/4421/5 "2023-09-21T07:08:40Z")

</div>

Wow great work, you saved me a lot of time here…  
Thanks a lot

---

<div class="post-metadata">

**Author:** ![benFortunato](https://avatars.discourse-cdn.com/v4/letter/b/6de8d8/32.png) [@benFortunato](https://community.weweb.io/u/benFortunato)\
**Post date:** [February 26, 2024, 11:19am UTC](https://community.weweb.io/t/use-the-search-function-in-the-filter-by-formula-of-airtable-plugin-with-a-lookup-record/4421/6 "2024-02-26T11:19:12Z")

</div>

You can also use conditionals along with the RECORD\_ID()=client\_ID. Just string all the client\_ID’s that you want to match together. If you want to find an Employee who has all the clients you should use AND, if you would like to find an employee who has any of the clients use OR.

I think this is a bit simpler than using the search function.

The syntax would just be:

OR (  
RECORD\_ID()=client\_ID,  
RECORD\_ID()=client\_ID,  
RECORD\_ID()=client\_ID  
)

You would create this OR / AND with the help of a little javascript although I’m sure you would also be able to do it with the loop feature in the workflow.

// Array of record IDs a weweb formula inserted into your javascript editor  
var recordIds = variables[‘9c13e8ec-bbaf-414b-9e8e-d85bb6df20f3’];

// Initialize an empty string to store the OR formula  
var orFormula = ‘’;

// Iterate through the recordIds array to construct the OR formula  
for (var i = 0; i \< recordIds.length; i++) {  
// Add the condition for the current record ID to the OR formula  
orFormula += “RECORD\_ID() = '” + recordIds[i] + “'”;  
// Add “,” if it’s not the last element  
if (i \< recordIds.length - 1) {  
orFormula += ", ";  
}  
}

// Construct the final formula with the OR function  
var finalFormula = “OR(” + orFormula + “)”;  
return finalFormula

This entire dynamically created formula could go into your filter for your collection.

 ![image](https://us1.discourse-cdn.com/flex016/uploads/weweb/original/2X/8/81fdf3598cc684e9788b62251e929b11e0810bb9.png)

---

<div class="post-metadata">

**Author:** ![MaxenceP](https://avatars.discourse-cdn.com/v4/letter/m/5e9695/32.png) [@MaxenceP](https://community.weweb.io/u/MaxenceP)\
**Post date:** [March 1, 2024, 1:50pm UTC](https://community.weweb.io/t/use-the-search-function-in-the-filter-by-formula-of-airtable-plugin-with-a-lookup-record/4421/7 "2024-03-01T13:50:11Z")

</div>

Hi @benFortunato and thank you for this interesting way of doing stuff.

While @Joyce answer as enough for me at the time, it no longer is, and maybe you can help:

→ The _SEARCH_ behaves as a CONTAINS which is a problem for me.

Would you know a way to make this formula more robust so that it behaves as a _IS_ and not a _contains_ ?

if not I’ll create a post on the topic 😉

As a reminder, the formula right now looks like this` ('SEARCH("'+variable containing user id+'",id_airtable)'`

---

<div class="post-metadata">

**Author:** ![benFortunato](https://avatars.discourse-cdn.com/v4/letter/b/6de8d8/32.png) [@benFortunato](https://community.weweb.io/u/benFortunato)\
**Post date:** [March 1, 2024, 3:58pm UTC](https://community.weweb.io/t/use-the-search-function-in-the-filter-by-formula-of-airtable-plugin-with-a-lookup-record/4421/8 "2024-03-01T15:58:06Z")

</div>

Instead of search just use a simple equality statement, Record\_ID()=‘SearchID’ or you could just check for any other value {airtableFieldName}=‘searchTxt’. If you need to search for multiple values then use OR and/or AND depending on what kind of functionality you want.

That is what I did in the script I wrote so I can input an array of search terms and then create an OR function that includes all the terms within that array. You could motify the script to add a SEARCH formula so that you could search for any record that contains one of the search values.

Essentially the filter formula in the API is looking for a boolean value. Your formula should generate a true false value or 0,1. Anything that generates that result will work.

---

<div class="post-metadata">

**Author:** ![benFortunato](https://avatars.discourse-cdn.com/v4/letter/b/6de8d8/32.png) [@benFortunato](https://community.weweb.io/u/benFortunato)\
**Post date:** [March 4, 2024, 2:12pm UTC](https://community.weweb.io/t/use-the-search-function-in-the-filter-by-formula-of-airtable-plugin-with-a-lookup-record/4421/10 "2024-03-04T14:12:41Z")

</div>

If you are just looking for a simple search of a field the formula is “{yourAirtableFieldName}=‘IdToMatch’” Make sure that the idtomatch is in quotes. If you want to use multiple fields just use “OR({yourAirtableFieldName}=‘IdToMatch’,{yourAirtableFieldName2}=‘IdToMatch2’)”

The way i set it up was to create a seperate function that generates the airtable search field text with the field that you want to match, and the array of ID values as input parameter. That way you can resuse this in any workflow that you might have. You could add anouther parameter to change from AND to OR if you need that.

 ![image](https://us1.discourse-cdn.com/flex016/uploads/weweb/original/2X/3/31d2e3836f29be96fd23b36117b8b92aa0de2559.png)

 ![image](https://us1.discourse-cdn.com/flex016/uploads/weweb/original/2X/1/1497217f97495630517f98733a8ece7bb3be9c4f.png)

---

<div class="post-metadata">

**Author:** ![MaxenceP](https://avatars.discourse-cdn.com/v4/letter/m/5e9695/32.png) [@MaxenceP](https://community.weweb.io/u/MaxenceP)\
**Post date:** [March 4, 2024, 2:24pm UTC](https://community.weweb.io/t/use-the-search-function-in-the-filter-by-formula-of-airtable-plugin-with-a-lookup-record/4421/11 "2024-03-04T14:24:19Z")

</div>

Ok got it thank you ! had to work around a bit so I can have my id to look for as a variable but it works now.  
You rock !