Iteration using airtable List

Hi there,

I am trying to update an airtable table with data coming off a function array, so it iterates through and adds a record in airtable for each object in the array.

It works fine, so if I have, say 6 objects in the array, I get 6 new lines in my table.
However, I now need to check if each entry is in the airtable already, and update it instead of appending it if it is present.
I have inserted an airtable list node with a formula to check if the entry already exists, however it breaks my iteration.
If it finds the entry it continues, however if it cannot find an entry in airtable it stops, and it stops all further iterations. My plan was to do an IF node to send it to either an update airtable or append airtable node. I have the “Continue On Fail:” setting on, but that hasn’t helped.

Ideally I would like it if the Airtable LIST node outputted an empty JSON if it can’t find an entry, and then continues the iteration.

Does anyone know how I would achieve that?

Thanks

Did you try the setting “Always Output Data”?

Yes, I have Always Output Data set to on.

I fear I am getting the concept wrong. I am used to using the iterator on integromat, so maybe I am letting that influence my thinking.

I am feeding an array of 6 objects (containing an id) from the function. From there I send myself a Slack message with the id I am looking up, and I receive 6 messages with each id (as expected).
I feed the same thing to the airtable LIST node, and from there to Slack, and I receive only the first one that matches the ‘filter by formula’. If I turn off the ‘filter by formula’ I receive every row in the table. My thinking is that when I turn off that filter, I should receive every row in the table 6 times.

Any guidance would be most appreciated.

Thanks.

Hey @stuwad!

Can you share your workflow here (select the nodes and copy them with CTRL+C or CMD+C and paste it there)? This will help me re-create your workflow and find a solution for you. :slightly_smiling_face:

hopefully I am doing this right…

{
  "nodes": [
    {
      "parameters": {
        "operation": "list",
        "application": "appqBIbn4U9bd6it3",
        "table": "Table 1",
        "additionalOptions": {
          "filterByFormula": "={ID} = \"{{$json[\"id\"]}}\""
        }
      },
      "name": "check if exists in airtable",
      "type": "n8n-nodes-base.airtable",
      "typeVersion": 1,
      "position": [
        790,
        300
      ],
      "alwaysOutputData": true,
      "credentials": {
        "airtableApi": "Airtable diedododa"
      },
      "continueOnFail": true
    },
    {
      "parameters": {
        "conditions": {
          "string": [
            {
              "value1": "={{$json[\"fields\"][\"id\"]}}",
              "operation": "notContains",
              "value2": "\"\""
            }
          ]
        }
      },
      "name": "IF variant does exits in airtable",
      "type": "n8n-nodes-base.if",
      "typeVersion": 1,
      "position": [
        1010,
        300
      ]
    },
    {
      "parameters": {
        "keepOnlySet": true,
        "values": {
          "string": [
            {
              "name": "Name",
              "value": "newvaluegoes here"
            }
          ]
        },
        "options": {}
      },
      "name": "Set",
      "type": "n8n-nodes-base.set",
      "typeVersion": 1,
      "position": [
        1230,
        240
      ]
    },
    {
      "parameters": {
        "keepOnlySet": true,
        "values": {
          "string": [
            {
              "name": "Name",
              "value": "newvaluegoes here"
            },
            {
              "name": "id",
              "value": "thenewid"
            }
          ]
        },
        "options": {}
      },
      "name": "Set1",
      "type": "n8n-nodes-base.set",
      "typeVersion": 1,
      "position": [
        1230,
        390
      ]
    },
    {
      "parameters": {
        "operation": "update",
        "application": "appqBIbn4U9bd6it3",
        "table": "Table 1",
        "id": "={{$node[\"check if exists in airtable\"].json[\"id\"]}}",
        "options": {}
      },
      "name": "airtable - update record",
      "type": "n8n-nodes-base.airtable",
      "typeVersion": 1,
      "position": [
        1450,
        200
      ],
      "credentials": {
        "airtableApi": "Airtable diedododa"
      }
    },
    {
      "parameters": {
        "operation": "append",
        "application": "appqBIbn4U9bd6it3",
        "table": "Table 1",
        "options": {}
      },
      "name": "Airtable - add new record",
      "type": "n8n-nodes-base.airtable",
      "typeVersion": 1,
      "position": [
        1450,
        440
      ],
      "credentials": {
        "airtableApi": "Airtable diedododa"
      }
    },
    {
      "parameters": {
        "functionCode": "return items[0].json.variants.map((item) => {\n return {\n     json: item  }\n     });\n"
      },
      "name": "Get Variants - Iteration",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        580,
        300
      ]
    },
    {
      "parameters": {},
      "name": "Start",
      "type": "n8n-nodes-base.start",
      "typeVersion": 1,
      "position": [
        250,
        300
      ]
    },
    {
      "parameters": {
        "functionCode": "var items = [\n{\"json\":{\n\"variants\":[\n{\n\"id\":\"1000-is-in-the-table\",\n\"Name\":\"Name for 1000-is-in-the-table\"\n},\n{\n\"id\":\"2000-is-in-the-table\",\n\"Name\":\"Name for 2000-is-in-the-table\"\n},\n{\n\"id\":\"6000-NOT-in-the-table\",\n\"Name\":\"Name for 6000-is-in-the-table\"\n}\n]\n}}];\nreturn items;\n"
      },
      "name": "Function",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        410,
        300
      ]
    }
  ],
  "connections": {
    "check if exists in airtable": {
      "main": [
        [
          {
            "node": "IF variant does exits in airtable",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "IF variant does exits in airtable": {
      "main": [
        [
          {
            "node": "Set",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Set1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Set": {
      "main": [
        [
          {
            "node": "airtable - update record",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Set1": {
      "main": [
        [
          {
            "node": "Airtable - add new record",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Get Variants - Iteration": {
      "main": [
        [
          {
            "node": "check if exists in airtable",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Start": {
      "main": [
        [
          {
            "node": "Function",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Function": {
      "main": [
        [
          {
            "node": "Get Variants - Iteration",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

Yes “list” in the Airtable node does sadly currently not execute once per item, it only executes once for the first item. For it to work would you have to use the SplitInBatches-Node.
That is currently sadly also true for similar nodes and operations like the Google Sheet Node in “Read” mode.

Thanks a lot…I have now managed to get it working using the Split in Batches nose. Cheers.

1 Like

@stuwad would you mind sharing your solution? I can’t seem to make it working, even with split in batches, it only gets run once and I can’t see what I’m doing wrong.

ok, I figured it out, if someone else has a problem understanding the split in batches thing, I hope this helps.

{
  "nodes": [
    {
      "parameters": {
        "operation": "executeQuery",
        "query": "SELECT account, stuff FROM some_table where do_i_care='yes';"
      },
      "name": "MySQL",
      "type": "n8n-nodes-base.mySql",
      "typeVersion": 1,
      "position": [
        340,
        430
      ],
      "credentials": {
        "mySql": "mysql"
      }
    },
    {
      "parameters": {
        "operation": "append",
        "application": "someApp",
        "table": "Some Table",
        "options": {}
      },
      "name": "Add data to airtable",
      "type": "n8n-nodes-base.airtable",
      "typeVersion": 1,
      "position": [
        1200,
        570
      ],
      "alwaysOutputData": true,
      "credentials": {
        "airtableApi": "My Airtable"
      }
    },
    {
      "parameters": {
        "values": {
          "string": [
            {
              "name": "Data",
              "value": "={{$node[\"SplitInBatches\"].json[\"account\"]}}"
            }
          ]
        },
        "options": {}
      },
      "name": "Set",
      "type": "n8n-nodes-base.set",
      "typeVersion": 1,
      "position": [
        1010,
        570
      ]
    },
    {
      "parameters": {
        "batchSize": 1,
        "options": {
          "reset": false
        }
      },
      "name": "SplitInBatches",
      "type": "n8n-nodes-base.splitInBatches",
      "typeVersion": 1,
      "position": [
        520,
        430
      ]
    },
    {
      "parameters": {
        "conditions": {
          "boolean": [
            {
              "value1": true,
              "value2": "={{$node[\"SplitInBatches\"].context[\"noItemsLeft\"]}}"
            }
          ]
        }
      },
      "name": "IF",
      "type": "n8n-nodes-base.if",
      "typeVersion": 1,
      "position": [
        1360,
        570
      ]
    },
    {
      "parameters": {
        "operation": "list",
        "application": "SomeApp",
        "table": "Some Table",
        "additionalOptions": {
          "fields": [
            "Trunk",
            "Daily Credit"
          ],
          "filterByFormula": "=({Account} = '{{$json[\"account\"]}}')"
        }
      },
      "name": "get data from Airtable",
      "type": "n8n-nodes-base.airtable",
      "typeVersion": 1,
      "position": [
        680,
        430
      ],
      "alwaysOutputData": true,
      "credentials": {
        "airtableApi": "My Airtable"
      }
    },
    {
      "parameters": {
        "conditions": {
          "boolean": [
            {
              "value1": "={{!!Object.keys($node[\"get data from Airtable\"].data).length}}",
              "value2": true
            }
          ]
        }
      },
      "name": "data exists",
      "type": "n8n-nodes-base.if",
      "typeVersion": 1,
      "position": [
        870,
        430
      ]
    }
  ],
  "connections": {
    "MySQL": {
      "main": [
        [
          {
            "node": "SplitInBatches",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Add trunk to airtable": {
      "main": [
        [
          {
            "node": "IF",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Set": {
      "main": [
        [
          {
            "node": "Add trunk to airtable",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "SplitInBatches": {
      "main": [
        [
          {
            "node": "get data from Airtable",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "IF": {
      "main": [
        null,
        [
          {
            "node": "SplitInBatches",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "get data from Airtable": {
      "main": [
        [
          {
            "node": "data exists",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "data exists": {
      "main": [
        [
          {
            "node": "SplitInBatches",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Set",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}
2 Likes