Skip to main content

Pass variable in filterByFormula (Node.js)

R

Hi, would anybody know how to pass the second variable (airtableField) in below function? The third variable (shopifyId) is accepted! The purpose is to return just 1 record.

Thanks in advance, Ronald

let shopifyId = '2418417139848'
checkAirtableId('Orders','Order id',shopifyId)

function checkAirtableId(airtableBase,airtableField,shopifyId) {
    let recordFound = base(airtableBase).select({
        filterByFormula: `${airtableField} = ${shopifyId}`
    }).firstPage(function(err,records) {
        if(err) {
            console.log(err);
            return;
        }
        return records[0]
    })
}

E
  • Erin_OHern
  • Participating Frequently
  • August 18, 2020

Hi Ronald,

Thanks for your question!

When using the filterByFormula parameter, you’ll want Airtable field names to be surrounded with curly braces in your formula. Assuming your airtableField variable is a field name, try adding those curly braces like so:

        filterByFormula: `{${airtableField}} = ${shopifyId}`

Let me know if that works, or if you’re still having issues!

T
1 person likes this
  • T
    Tony_Duchars1
R

Thank you Erin, that works!
The only thing is…it only works if the shopifyId is a number (within quotation marks…so this is a string after all…), if I change this in a name like ‘ronald’, it doesn’t work anymore (which is unpractical for another application). The ‘Order id’ field is textfield, so this should not matter, right?

E
  • Erin_OHern
  • Participating Frequently
  • August 26, 2020
ronald_vonk_bin wrote:

Thank you Erin, that works!
The only thing is…it only works if the shopifyId is a number (within quotation marks…so this is a string after all…), if I change this in a name like ‘ronald’, it doesn’t work anymore (which is unpractical for another application). The ‘Order id’ field is textfield, so this should not matter, right?


Hey @ronald.vonk_binthout!

Let’s take a step back and look at the Airtable formulas that are generated in both cases. When shopifyId is a number, the formula parameter becomes:
filterByFormula: {Order id} = 2418417139848

This formula {Order id} = 2418417139848 is a valid Airtable formula, and will return the value 1 for a record whose Order id field has the value 2418417139848.

If we change the code and set shopifyId to a text value, like this:
let shopifyId = 'ronald',
our Airtable formula then becomes {Order id} = ronald. This is not a valid Airtable formula, since it’s trying to treat “ronald” as a field name (but we don’t have a field named “ronald”), so we get an error. You can test out formulas in the Airtable UI to see how they behave:

In this case, we’ll need to change the formula to have quotes (single or double quotes, doesn’t matter) around the string we are trying to match:
{Order id} = "ronald"

You should be able to add these quotes in your template string like so:

filterByFormula: `{${airtableField}} = "${shopifyId}"`

And the good news is these quotes will work for both text and number strings! You don’t need them with numbers, but having them there won’t hurt.

R
P
Justin_Barrett
3 people like this
  • R
    Ronan_Camus
  • P
    Prashanth_Kumar
  • Justin_Barrett
    Justin_Barrett
B
Forum|alt.badge.img+4
  • Binthout_BV
  • Participating Frequently
  • August 31, 2020
Erin_OHern wrote:

Hey @ronald.vonk_binthout!

Let’s take a step back and look at the Airtable formulas that are generated in both cases. When shopifyId is a number, the formula parameter becomes:
filterByFormula: {Order id} = 2418417139848

This formula {Order id} = 2418417139848 is a valid Airtable formula, and will return the value 1 for a record whose Order id field has the value 2418417139848.

If we change the code and set shopifyId to a text value, like this:
let shopifyId = 'ronald',
our Airtable formula then becomes {Order id} = ronald. This is not a valid Airtable formula, since it’s trying to treat “ronald” as a field name (but we don’t have a field named “ronald”), so we get an error. You can test out formulas in the Airtable UI to see how they behave:

In this case, we’ll need to change the formula to have quotes (single or double quotes, doesn’t matter) around the string we are trying to match:
{Order id} = "ronald"

You should be able to add these quotes in your template string like so:

filterByFormula: `{${airtableField}} = "${shopifyId}"`

And the good news is these quotes will work for both text and number strings! You don’t need them with numbers, but having them there won’t hurt.


That’s a very clear explanation, thank you very much Erin!

E
1 person likes this
  • E
    Erin_OHern

Reply


Most helpful members this week

Terms & Conditions