Airtable API - Check if Record Contains Each Value of POST Request Body

[Bryan_Wilkinson]

Hello,

I’m able to successfully make post requests to my Airtable base via the api using a netlify function. My POST request body contains four values (we can call them w, x, y, z). What my function currently does is create a new record and adds each value to their respective field.

What I’d like to do, before creating a new record, is to first check if any records in my base have all 4 identical values for each field provided in the post request. If a record does, I’ll return a success response, if not I’ll create a new record with the POST values. I see that the select() function may be a good start but am having trouble understanding how to check if a record matches each value in a post request. For reference I’m using node.js to send this request here’s my create function that is working correctly:

let fields = JSON.parse(event.body)
const { w, x, y, z} = fields
// This is where I'd like to check if my table already has a record
// with each value in the post request as a field
await table
      .create({
        w,
        x,
        y,
        z,
      })
      .then((records) => {
        console.log("Successfully inserted into airtable")
      })
      .catch((err) => {
        throw err
      })
    return {
      statusCode: 202,
      body: JSON.stringify({ message: "Authentic" })

Is there an easy way to do this?

Thanks for the help!

[kuovonne]

Welcome to the Airtable community!

Bryan_Wilkinson:

Is there an easy way to do this?

Airtable does not have an “upsert” function that will create a record only if it doesn’t exist yet. Thus your code must check if the record exists and then decide if it should create the record or not based on the return result.

To check if there is a matching record, you should do a GET request using filterByFormula. When building your formula, first test out your formula using a formula field in your actual base.

[Bryan_Wilkinson]

Ok so I’ll do a GET request first, passing my values as parameters to a new function, does something like this make sense? Also what does the filterByFormula function return, a true/false or the actual record that matches the request? For my use case, a true/false would be perfect.

const checkRecords = async (w, x, y, z) => {
try {
    const records = await table
      .select({
        filterByFormula: `FIND(w = '$(w)', x = '$(x)', y = '$(y)', z = '$(z)')`,
      })
      .all();
    console.log(records)
    if (records = true) {
             do something
    }
    else {
       await table.create(.....
  } catch(err => {
console.log(Error.err)
};

[kuovonne]

Bryan_Wilkinson:

does something like this make sense?

Your formula doesn’t make sense. You need to pass a valid Airtable formula. To do that, first test your formula in an Airtable formula field.

Bryan_Wilkinson:

Also what does the filterByFormula function return, a true/false or the actual record that matches the request?

filterByFormula filters the results of the query to records that match the filter. So if a record (or records) match, you get that record. If there is no matching record, you get no records.

You can read more about filterByFormula in the REST API documentation. It can be a bit tricky to get the formula right, so testing is important.

[Bryan_Wilkinson]

kuovonne:

Your formula doesn’t make sense. You need to pass a valid Airtable formula. To do that, first test your formula in an Airtable formula field.

What would make sense? I have no clue, this is literally my 3rd day using it. Where do I test formulas in Airtable? Is there a clear example using filterByFormula to look for a record that matches one or more fields from an API request? Not be rude but I don’t even see filterbyformula being used in the REST API docs?

I found this format, does this make sense? And can this be adapted to multiple fields?

filterByFormula: `FIND("${w}", x)'

[kuovonne]

Bryan_Wilkinson:

I have no clue, this is literally my 3rd day using it. Where do I test formulas in Airtable?

Your third day using Airtable or your third day using the REST API? If you haven’t used Airtable, you should get familiar with the Airtable app itself.

Bryan_Wilkinson:

Where do I test formulas in Airtable?

In Airtable itself. Create a new formula field in the base.

Bryan_Wilkinson:

Not be rude but I don’t even see filterbyformula being used in the REST API docs?

Which docs are you looking at? It’s there under the docs for listing records.

REST API - Airtable

REST API - Airtable

After you’ve created and configured the schema of an Airtable base, use the API to create, read, update, and destroy records.

[Bryan_Wilkinson]

This is what I see in the docs:

I don’t see how filterbyFormula is used in the provided example. I can click the formula link and learn all about what can be included in a formula, but it would be nice if it showed its use in a literal example with javascript.

[Bryan_Wilkinson]

I appreciate you helping me, just frustrated that I have to really strain to get something that I think would be a common need to work - but clearly its all new.

[kuovonne]

Bryan_Wilkinson:

I appreciate you helping me

You’re welcome.

Bryan_Wilkinson:

just frustrated that I have to really strain to get something

The REST API has been around for a very long time in the Airtable world, and filterByFormula has been around since 2016. This particular feature of the REST API is practically unchanged since then, and is also very robust once you figure it out.

However, it was designed to be used by people who (1) were users of the Airtable app itself, and (2) were familiar with writing code.

Writing documentation that is clear and comprehensive yet also uncluttered is a tricky tasks. I think the api documentation does a decent job of walking this line. However, if you have specific examples how how to improve the documentation, feel free to send them to support. I find they are very willing to consider documentation improvements when provided with specific text.