Struggling with AND vs OR in my formula

[Trey_Donovan]

Hi All, I could use a little insight into what's wrong with my formula.

It's scripted in my API, using Airtable.js with Next.js.

The following works with only the second two fields, which are multi-select, but ignores (or seems to ignore) the text input field "Title."

const records = await table

.select({

filterByFormula: `OR(

FIND(LOWER("${req.query.title}"), Title) > 0,

FIND("${req.query.module}", module) > 0,

FIND("${req.query.lessonprogram}", program) > 0

)`,

sort: [{ field: "RecordId", direction: "asc" }],

})

.all();

If I try to use AND, I get nothing:

const records = await table

.select({

filterByFormula: `AND(

FIND(LOWER("${req.query.title}"), Title) > 0,

FIND("${req.query.module}", module) > 0,

FIND("${req.query.lessonprogram}", program) > 0

)`,

sort: [{ field: "RecordId", direction: "asc" }],

})

.all();

However, through the REST API I get the JSON results I want, using AND (even if Title is an empty string):

https://api.airtable.com/v0/<appID>/<table>?filterByFormula=AND(FIND("", Title),FIND("Program+string", program),FIND("Module+string", module))&sort%5B0%5D%5Bfield%5D=RecordId&sort%5B0%5D%5Bdirection%5D=asc&api_key=<api_key>

 Is there some way I can write the formula using scripting in the Next.js API that will return results on all three fields?  

Thanks a million for any help!

[Ben_Young1]

Hey @Trey_Donovan! 

Try this snippet out:

let filterFormula = `AND(
    FIND(LOWER("${req.query.title}"), LOWER({Title})),
    FIND("${req.query.module}", {module}),
    FIND("${req.query.lessonprogram}", {program})
)`;

const records = table.select({
    filterByFormula: filterFormula,
    sort: [{ field: "RecordId", direction: "asc" }]
})
    .all();

I just tested this quickly and it works as intended.
I believe the problem was originating from this line in your original snippet:

FIND(LOWER("${req.query.title}"), Title) > 0,

If I had to take a guess, you forgot to also wrap the value of the Title field in a LOWER() function which meant that instead of comparing this:

FIND("recordtitle", "recordtitle")

You were actually comparing this:

FIND("recordtitle", "RECORDTITLE")

Let me know if that still doesn't work as intended for you.

[Trey_Donovan]

Thanks @Ben_Young1 , that's a start, as I wasn't aware that LOWER needed to effect the column Title also, but alas, this is returning an empty array, as it was before.

I tried various iterations, but no dice. 

I tried again, to put OR as the operator, and it returns all the records that match the program and module columns, but AND is not working at all.

[Ben_Young1]

Interesting. Here's the full context of my testing.

Here are the records we're querying against.

Here's the actual snippet I used in my test and a screenshot of the result:

import Airtable from "airtable";
const { AIRTABLE_KEY } = dotenv.config().parsed;
const base = new Airtable({ apiKey: AIRTABLE_KEY }).base("myTestBaseId");
const filterValues = {
    title: "Record 1",
    module: "moduleValue",
    program: "programValue"
};

const filterFormula = `AND(
    FIND(LOWER("${filterValues.title}"), LOWER({Title})),
    FIND("${filterValues.module}", {module}),
    FIND("${filterValues.program}", {program})
)`;

const records = base("myTableId").select({
    filterByFormula: filterFormula,
    sort: [{ field: "Record ID", direction: "asc" }]
})
    .all();

await records
    .then(query => query.map(record => record.fields))
    .then(data => console.log(data))

Could you post a scrubbed, updated snippet from your recent attempt?

[Trey_Donovan]

Thanks @Ben_Young1 for posting your test.

Here's my search component:

import {
  table,
  getMinifiedRecords,
  findRecordByFilter,
  filterByFormula,
} from "./utils/Airtable";
import { LessonsContext } from "../../contexts/LessonsContext";

const searchLessons = async (req, res) => {

    let filterFormula = `AND(
      FIND(LOWER("${req.query.title}"), LOWER({Title})),
      FIND("${req.query.module}", {module}),
      FIND("${req.query.lessonprogram}", {program})
  )`;

    const records = await table
      .select({
        filterByFormula: filterFormula,
        sort: [{ field: "RecordId", direction: "asc" }],
      })
      .all();
    const minifiedRecords = getMinifiedRecords(records);
    res.statusCode = 200;
    res.json(minifiedRecords);
};

export default searchLessons;

So the context passes the query to this like so:

const findLessons = async ({ searchTerms }) => {
    try {
      const querystring = require("querystring");
      let queryString = querystring.stringify(searchTerms);
      const res = await fetch(`/api/searchLessons?${queryString}`, {
        method: "GET",
        headers: { "Content-Type": "application/json" },
      });
      const lessons = await res.json();
      setLessons(lessons);
    } catch (error) {
      console.error("Error performing search", error);
    }
  };

It might be worth noting that the Title column is not in the first position in my Airtable, as in your example.  Not sure if that matters.  

Here's my console, while trying with AND (showing the state updating while I'm typing):

[Ben_Young1]

It might be worth noting that the Title column is not in the first position in my Airtable, as in your example.  Not sure if that matters.  

---

The visible field order doesn't matter since the field is structurally just a property of the record object.

In your shoes, I would try the following.

First, grab the value of filterFormula, create a formula field in your table, and then insert the value of the variable into the field. Logging the value of the variable to the console gave me the following:

AND(
    FIND(LOWER("Record 1"), LOWER({Title})),
    FIND("moduleValue", {module}),
    FIND("programValue", {program})
)

One of three things happens. Either the formula is invalid and the field will not accept the formula, none of the records return a value of true, or the record that you intended to return in the query returns a value of true in the new formula field.

If the formula is invalid, then the problem is with the formula.
If the formula is valid, but no records return true, then there's something about that first expression in the AND() function that fails to return true.
If you get a result similar to the screenshot above, then the problem is with the code.

I get a feeling that everything is actually working correctly since using the OR() function returns records as expected, which means that the second scenario is most likely and no records will return true.

Look for anything that might cause the two strings you're evaluating aren't matching. Whitespace, special characters, etc.

[Trey_Donovan]

Thanks for the test method, @Ben_Young1 

Yes, so this turned up a successful search in the Airtable, so the problem is with my code, evidently.

I appreciate the leg up, here.

[Trey_Donovan]

FWIW, anyone who's having this issue, my solution, (thanks to @Ben_Young1 for halping to debug it) was this:

    const records = await table
      .select({
        filterByFormula: `AND(
        FIND("${req.query.module}", {module}),
        FIND("${req.query.lessonprogram}", {program}),
        SEARCH(LOWER("${req.query.title}"), LOWER({Title}))
      )`,
        sort: [{ field: "RecordId", direction: "asc" }],
      })
      .all();

I changed FIND to SEARCH, and then after doing this, found that my component was being passed %20 encoded spaces, which gummed up the works.  I changed my stringify function thusly:

let queryString = querystring.stringify(searchTerms).replace(/%20/g, "+");

Everything started to work properly after I updated the code to pass parameters with +'s instead of %20 spaces.   This reconciled my confusion from seeing my Postman queries work while my scripted queries did not.