Airtable list with a filter with an expression

Questions
1

I’m trying to implement upsert operation for Airtable.

I have an Airtable List node which uses expression in the filter field.
So far I’ve been only using expression to read the configuration - baseId and tableId, this time my node is supposed to take the value of the primary field and return any matching records from the table.

So I have something like
“filterByFormula”: “={Name} = ‘{{$json[“Name”]}}’”

I have a number of data items in the input, and after the first run they are all inserted to the table,
but when I run the workflow the second time, all the names should be found in the table, but only one item is actually returned.

It looks like to expression in the filter formula is evaluated only once.

What am I doing wrong?

Please share the workflow

‘search by external_id’ is causing the problems

Share the output returned by the last node

Information on your n8n setup

  • n8n version: 0.149.0
  • Database you’re using (default: SQLite): mariadb
  • Running n8n with the execution process [own(default), main]: own
  • Running n8n via [Docker, npm, n8n.cloud, desktop app]: docker

Cheers

2

Welcome to the community @mattesilver !

Yes, that is currently sadly an inconsistency in n8n that many of those read operations execute just once instead of once per item. We are currently thinking of ways of how we can fix that in the future.

In the meantime, you would have to use a Split In Batches node with a batch size of 1 and so iterate over them to make it work.

3

Thank you @jan for your answer.

Any way to collect the items back, so I can use them in the merge node in ‘removeKeyMatches’ mode?

4

Yes, is possible, sadly not at all obvious if you do not know how.

Here an example:

5

:thinking:

I’ve put my airtable call between split and IF, changed the function so it caches the output of the AT node but it behaves weirdly.
It complains that all items must be wrapped in a {json:{}} but then if I do that, the json objects appears in the items (in the preview)

6

It was creating a list of lists, so I added this to the collector function:

return newItems.flat(1).map(i=>({json:i}));

whole code:

const newItems = [];

let runIndex = 0;
do {
  try {
    newItems.push($items("Search by external_id", 0, runIndex++));
  } catch (e) {
    break;  
  }
} while(true);

return newItems.flat(1).map(i=>({json:i}));

Only now ‘merge1’ node never gets called if the table is empty (inserts to fresh table)
gaaa

7

I was able to construct a single formula for all my items with a trivial function node

{json:{formula:“OR(”+values.map(v=>formula).join(’, ')+")"}}