Help

Pass variable in filterByFormula (Node.js)

Topic Labels: API
3431 4
cancel
Showing results for 
Search instead for 
Did you mean: 
ronald_vonk_bin
4 - Data Explorer
4 - Data Explorer

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]
    })
}
4 Replies 4
Erin_OHern
6 - Interface Innovator
6 - Interface Innovator

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!

ronald_vonk_bin
4 - Data Explorer
4 - Data Explorer

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:
Image 2020-08-26 at 7.23.28 am

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!