Struggling with AND vs OR in my formula
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?
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):
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.
