Skip to main content
Solved

filterByFormula Syntax using the OR logic in Node.js

  • August 12, 2019
  • 1 reply
  • 1 view
M

Hi,
I am new to Airtable and am trying to build a query that will act like a global search across multiple columns.

Our members table has the following columns {‘Full Name’,‘Phone’,‘Email’} that I would like to search.

I want to have a single search field on our site to look up members by either their Full Name, Phone, or Email.

This is the query that I am using, but it doesn’t work, and I have tried many incantations to no avail…

record = await table.select({
filterByFormula: OR(‘Full Name’=searchString, ‘Phone’=searchString, ‘Email’=searchString),
fields: [‘Member’, ‘Full Name’, ‘Phone’, ‘Email’, ‘Status’, ‘Member Photo’]
}).firstPage().then((records) => {
rec = records[0][“fields”];
return rec;
}).catch((err)=>{
console.error(err)
});

The above code works great if I use a simple filterByFormula such as:
filterByFormula: 'Member=' + member_id

Thanks in advance for any assistance!!

Mike

Best answer by Mike_Kramer

I figured this out myself. Here is the filterByFormula JS code I used to make a nested AND inside of an OR in case someone else can find it useful:

//Get the query parameter
var searchString = new String(ctx.query["search"]);//Get the Search Query string
//console.log("ctx.query['search'] =", JSON.stringify(searchString));

//Make an array of the input parameters
var firstLast = searchString.trim().split(" ");
//filterByFormula to search for Full Name, Email, Phone
var filterByFormula = "OR(AND({First Name}='" + firstLast[0] + "', {Last Name}='" + firstLast[1] + "'), {Email}='" + firstLast[0] + "', {Phone}='" + firstLast[0] + "')";
View original
Did this topic help you find an answer to your question?

1 reply

M
  • Mike_Kramer
  • Author
  • New Participant
  • 1 reply
  • Answer
  • August 13, 2019

I figured this out myself. Here is the filterByFormula JS code I used to make a nested AND inside of an OR in case someone else can find it useful:

//Get the query parameter
var searchString = new String(ctx.query["search"]);//Get the Search Query string
//console.log("ctx.query['search'] =", JSON.stringify(searchString));

//Make an array of the input parameters
var firstLast = searchString.trim().split(" ");
//filterByFormula to search for Full Name, Email, Phone
var filterByFormula = "OR(AND({First Name}='" + firstLast[0] + "', {Last Name}='" + firstLast[1] + "'), {Email}='" + firstLast[0] + "', {Phone}='" + firstLast[0] + "')";
V
V
Justin_Barrett
4 people like this
  • V
    vpego
  • V
    VTL_Dev
  • Justin_Barrett
    Justin_Barrett
  • B
    Brucehum

Reply


Most helpful members this week

Terms & Conditions