Skip to main content
Solved

Are there docs for the filterByFormula options when using fetch?

  • July 6, 2021
  • 7 replies
  • 71 views
D

I am trying to figure out how to translate the in-airtable-ui filters into a filterByFormula method in the request via fetch. I can’t seem to find any docs and some related questions kind of seem like people are figuring these out somehow so I feel like I’m missing something.

For my exact example, I’m trying to replicate "where fieldName contains string" in the Airtable UI. I’ve seen some conflicting opinions between using FIND and SEARCH, but neither have worked for me yet. I’m hoping to find something that outlines those available options, similar to how there examples for CURL (and an unofficial CodePen for generating them)

Best answer by Justin_Barrett

This helps a lot for understanding why it’s not explicitly documented. Do you happen to know if it is not an option to filterByFormula on linked record fields and/or lookup fields?

If I run a plain text Search on a regular field, I can get a result as expected. However, I cannot on linked records or lookup fields.

Given this filter, I’d expect this to work as you mentioned

const getCaseHowYouCanHelpTest = async (slug) => {
  const records = await caseHowYouCanHelpTable
    .select({
      filterByFormula: `SEARCH("${slug}",{lookupCaseSlug})`,
    })
    .all();
  return minifyHowYouCanHelpRecords(records);
};

But logging it with console.log(slug, test); results in brandon-lawson []


Linked record fields should work because Airtable auto-converts the contents to a comma-separated string when read by a formula. However, lookup fields need some extra attention. Even when the related linked record field is only targeting a single record, lookup fields most often return arrays, not single values. The SEARCH() function requires single strings as arguments, so comparing against a lookup field’s array isn’t going to work. Thankfully the fix is easy: concatenate the field contents with an empty string, which force-converts the output to be a string. That would turn your code into this:

const getCaseHowYouCanHelpTest = async (slug) => {
  const records = await caseHowYouCanHelpTable
    .select({
      filterByFormula: `SEARCH("${slug}",{lookupCaseSlug} & "")`,
    })
    .all();
  return minifyHowYouCanHelpRecords(records);
};

I created a base to help demystify the output of lookup fields in various situations:

7 replies

D
  • Dan_Denney
  • Author
  • New Participant
  • July 6, 2021

This one is a great example of why I’m asking. I’m guessing that this must be documented and I’m thinking it could be close to my issue. I’m trying to pass in a variable for a slug to look for. FilterbyFormula not working with template literals - #2 by Justin_Barrett

I’ve been trying to pass strings for now just to get a passing version.
.select({ filterByFormula: SEARCH(“brandon-lawson”,{lookupCaseSlug}) })

The reason that this is important is that we have a collection of pages with linked data. Right now, I’m having to request every entry in each table that we access, then filter after the request in static props.

Justin_Barrett
Forum|alt.badge.img+21

This one is a great example of why I’m asking. I’m guessing that this must be documented and I’m thinking it could be close to my issue. I’m trying to pass in a variable for a slug to look for. FilterbyFormula not working with template literals - #2 by Justin_Barrett

I’ve been trying to pass strings for now just to get a passing version.
.select({ filterByFormula: SEARCH(“brandon-lawson”,{lookupCaseSlug}) })

The reason that this is important is that we have a collection of pages with linked data. Right now, I’m having to request every entry in each table that we access, then filter after the request in static props.


This should work with a variable:

.select({ filterByFormula: `SEARCH("${variableName}",{airtable_field_name})` })
Justin_Barrett
Forum|alt.badge.img+21

Long story short: the formula that you pass to filterByFormula just needs to be a valid formula that returns True or False. It’s not about matching what the UI filter field does. Valid formulas can be much more complex than what the interface’s filter option allows. As long as it’s built correctly, it should work.

kuovonne
D
  • Dan_Denney
  • Author
  • New Participant
  • July 6, 2021

Long story short: the formula that you pass to filterByFormula just needs to be a valid formula that returns True or False. It’s not about matching what the UI filter field does. Valid formulas can be much more complex than what the interface’s filter option allows. As long as it’s built correctly, it should work.


This helps a lot for understanding why it’s not explicitly documented. Do you happen to know if it is not an option to filterByFormula on linked record fields and/or lookup fields?

If I run a plain text Search on a regular field, I can get a result as expected. However, I cannot on linked records or lookup fields.

Given this filter, I’d expect this to work as you mentioned

const getCaseHowYouCanHelpTest = async (slug) => {
  const records = await caseHowYouCanHelpTable
    .select({
      filterByFormula: `SEARCH("${slug}",{lookupCaseSlug})`,
    })
    .all();
  return minifyHowYouCanHelpRecords(records);
};

But logging it with console.log(slug, test); results in brandon-lawson []

D
  • Dan_Denney
  • Author
  • New Participant
  • July 6, 2021

This helps a lot for understanding why it’s not explicitly documented. Do you happen to know if it is not an option to filterByFormula on linked record fields and/or lookup fields?

If I run a plain text Search on a regular field, I can get a result as expected. However, I cannot on linked records or lookup fields.

Given this filter, I’d expect this to work as you mentioned

const getCaseHowYouCanHelpTest = async (slug) => {
  const records = await caseHowYouCanHelpTable
    .select({
      filterByFormula: `SEARCH("${slug}",{lookupCaseSlug})`,
    })
    .all();
  return minifyHowYouCanHelpRecords(records);
};

But logging it with console.log(slug, test); results in brandon-lawson []


And just to be sure that it’s not a regular code error, if I flip it to “Title” and pass in a string that works, the response is expected.

.select({
      filterByFormula: `SEARCH("${slug}",{Title})`,
    })

brandon-lawson [
  {
    buttonText: 'View Now',
    case: [ 'rechNgq5lMlVdUDNX' ],
    longerDescription: "Visit the Facebook page to stay up to date on Teekah's case.",
    lookupCaseSlug: [ 'teekah-lewis' ],
    title: 'Visit the Help Find Missing Teekah Lewis Facebook page',
    type: 'Explore',
    url: 'https://www.facebook.com/Help-find-Missing-Teekah-Lewis-100247185413648'
  },
  {
    buttonText: 'Listen Now',
    case: [ 'receYGC3uWAIQWWG1' ],
    longerDescription: 'Listen to the 911 call to help authorities uncover what was actually said.',
    lookupCaseSlug: [ 'brandon-lawson' ],
    title: 'Help Decipher the 911 Call',
    type: 'Listen ',
    url: ' https://youtu.be/j9Nr0qdc624'
  },
  {
    buttonText: 'Donate Now',
    case: [ 'recRkGBIoVGVc9qdI' ],
    longerDescription: 'Help Vivianne raise money to engage an Attorney and Private Investigator.',
    lookupCaseSlug: [ 'joseph-smedley' ],
    title: "Help Joseph's Sister Raise Money",
    type: 'Share',
    url: 'https://www.gofundme.com/f/attorney-amp-private-investigator-for-joseph-smedley'
  }
]
Justin_Barrett
Forum|alt.badge.img+21

This helps a lot for understanding why it’s not explicitly documented. Do you happen to know if it is not an option to filterByFormula on linked record fields and/or lookup fields?

If I run a plain text Search on a regular field, I can get a result as expected. However, I cannot on linked records or lookup fields.

Given this filter, I’d expect this to work as you mentioned

const getCaseHowYouCanHelpTest = async (slug) => {
  const records = await caseHowYouCanHelpTable
    .select({
      filterByFormula: `SEARCH("${slug}",{lookupCaseSlug})`,
    })
    .all();
  return minifyHowYouCanHelpRecords(records);
};

But logging it with console.log(slug, test); results in brandon-lawson []


Linked record fields should work because Airtable auto-converts the contents to a comma-separated string when read by a formula. However, lookup fields need some extra attention. Even when the related linked record field is only targeting a single record, lookup fields most often return arrays, not single values. The SEARCH() function requires single strings as arguments, so comparing against a lookup field’s array isn’t going to work. Thankfully the fix is easy: concatenate the field contents with an empty string, which force-converts the output to be a string. That would turn your code into this:

const getCaseHowYouCanHelpTest = async (slug) => {
  const records = await caseHowYouCanHelpTable
    .select({
      filterByFormula: `SEARCH("${slug}",{lookupCaseSlug} & "")`,
    })
    .all();
  return minifyHowYouCanHelpRecords(records);
};

I created a base to help demystify the output of lookup fields in various situations:

H
D
  • Dan_Denney
  • Author
  • New Participant
  • July 6, 2021

Linked record fields should work because Airtable auto-converts the contents to a comma-separated string when read by a formula. However, lookup fields need some extra attention. Even when the related linked record field is only targeting a single record, lookup fields most often return arrays, not single values. The SEARCH() function requires single strings as arguments, so comparing against a lookup field’s array isn’t going to work. Thankfully the fix is easy: concatenate the field contents with an empty string, which force-converts the output to be a string. That would turn your code into this:

const getCaseHowYouCanHelpTest = async (slug) => {
  const records = await caseHowYouCanHelpTable
    .select({
      filterByFormula: `SEARCH("${slug}",{lookupCaseSlug} & "")`,
    })
    .all();
  return minifyHowYouCanHelpRecords(records);
};

I created a base to help demystify the output of lookup fields in various situations:


You have saved many more hairs on my head and the Airtable API so many bits. I was literally requesting 100-1200 entries per table, across 6 tables, for what was usually 3-10 matches. You are my hero of the day!

Justin_Barrett
Terms & ConditionsAccessibility statement