Is there a way to use filterByFormula in Airtable to filter by a specific field value by date without it defaulting to the first entry in the table?

Question

I am trying to filter by date to get one specific record, of which the Name field should = 8/01/2022. (I used the Name field in Airtable to place the dates. I set the field type to date.) The issue I'm having is that although it seems to work fine, it basically ignores the specification for the date and instead returns the first value in the table.

This is what I have for getting the data from airtable.

  let isoDate = new Date("08/01/2022").toISOString().slice(0, 10);
  
  const base = new Airtable({ apiKey: apiKey }).base(baseID);

  base("tabledata")
    .select({
      filterByFormula: `"DATESTR({Name})='${isoDate}'"`,
      view: "Grid view",
    })
    .eachPage(
      function page(records, fetchNextPage) {
        records.forEach(function (record) {
          let newEl = {
            date: record.get("Name"),
            game: record.get("games"),
          };
          setData(newEl);
        });
        try {
          fetchNextPage();
        } catch {
          return;
        }
      },
      function done(err) {
        if (err) {
          console.error(err);
          return;
        }
      }
    );

and this is the record that is retrieved:

{date: '2022-07-29', game: Array(6)}
date: "2022-07-29"
game: Array(6)

'2022-07-29' is the name of the first field in my table. when I print the ISOString at any point I get 8/01/2022.

score 2 · Answer 1 · answered Aug 01 '22 at 05:58

It turned out the issue was with this line

filterByFormula: `"DATESTR({Name})='${isoDate}'"`

I ended up changing the data type in airtable to short text since it doesn't really matter when it comes to the way I'm using it. So DATESTR was no longer needed, but I was still dealing with the issue no matter what I did.

Here is my new isoDate variable declaration:

let isoDate = new Date().toLocaleDateString();

It took me a while before I realized that it would make sense to try this line and see if it would work: "{Name})='8/01/2022'" And it did! So clearly the issue was with the isoDate string. After searching for a while I found this setup which worked:

filterByFormula: `{Name} = "${isoDate}"`

The main difference is the double quotes around the variable and no extra quotes around the full string.

Is there a way to use filterByFormula in Airtable to filter by a specific field value by date without it defaulting to the first entry in the table?

1 Answers1