Airtable list with a filter with an expression

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

{
  "name": "Apply templates variant 0",
  "nodes": [
    {
      "parameters": {},
      "name": "Start",
      "type": "n8n-nodes-base.start",
      "typeVersion": 1,
      "position": [
        -60,
        -210
      ]
    },
    {
      "parameters": {
        "values": {
          "string": [
            {
              "name": "airtable.baseId",
              "value": "---redacted---"
            },
            {
              "name": "airtable.tableId.source",
              "value": "---redacted---"
            },
            {
              "name": "airtable.tableId.target",
              "value": "---redacted-----"
            },
            {
              "name": "airtable.tableId.toilet",
              "value": "---redacted---"
            }
          ],
          "number": [
            {
              "name": "templateVariant"
            },
            {
              "name": "daysForward",
              "value": 7
            }
          ]
        },
        "options": {}
      },
      "name": "Configuration",
      "type": "n8n-nodes-base.set",
      "typeVersion": 1,
      "position": [
        140,
        -210
      ],
      "executeOnce": true,
      "color": "#FF0000"
    },
    {
      "parameters": {
        "functionCode": "var __create = Object.create;\nvar __defProp = Object.defineProperty;\nvar __getOwnPropDesc = Object.getOwnPropertyDescriptor;\nvar __getOwnPropNames = Object.getOwnPropertyNames;\nvar __getProtoOf = Object.getPrototypeOf;\nvar __hasOwnProp = Object.prototype.hasOwnProperty;\nvar __markAsModule = (target) => __defProp(target, \"__esModule\", { value: true });\nvar __reExport = (target, module2, desc) => {\n  if (module2 && typeof module2 === \"object\" || typeof module2 === \"function\") {\n    for (let key of __getOwnPropNames(module2))\n      if (!__hasOwnProp.call(target, key) && key !== \"default\")\n        __defProp(target, key, { get: () => module2[key], enumerable: !(desc = __getOwnPropDesc(module2, key)) || desc.enumerable });\n  }\n  return target;\n};\nvar __toModule = (module2) => {\n  return __reExport(__markAsModule(__defProp(module2 != null ? __create(__getProtoOf(module2)) : {}, \"default\", module2 && module2.__esModule && \"default\" in module2 ? { get: () => module2.default, enumerable: true } : { value: module2, enumerable: true })), module2);\n};\n\n// src/service-plan-v0/main.ts\nvar date = __toModule(require(\"date-fns\"));\n\n// src/timeConstraints.ts\nfunction toDuration(down, hours, minutes) {\n  return {\n    hours,\n    minutes,\n    days: hours || minutes ? void 0 : down ? void 0 : 1\n  };\n}\nfunction parseTimeConstraints(input) {\n  if (!input || input.trim() == \"\") {\n    return {\n      timeAfter: {},\n      timeBefore: { days: 1 }\n    };\n  }\n  const reMatch = /^(?:([01]?[0-9]|2[0-3])(?::([0-5][0-9]))?)?-?(?:([01]?[0-9]|2[0-3])(?::([0-5][0-9]))?)?$/.exec(input);\n  if (reMatch) {\n    const timeAfterHrs = reMatch[1] ? Number.parseInt(reMatch[1]) : void 0;\n    const timeAfterMin = reMatch[2] ? Number.parseInt(reMatch[2]) : void 0;\n    const timeBeforeHrs = reMatch[3] ? Number.parseInt(reMatch[3]) : void 0;\n    const timeBeforeMin = reMatch[4] ? Number.parseInt(reMatch[4]) : void 0;\n    return {\n      timeAfter: toDuration(true, timeAfterHrs, timeAfterMin),\n      timeBefore: toDuration(false, timeBeforeHrs, timeBeforeMin)\n    };\n  } else {\n    throw new TypeError(\"Could not parse time constraints from '\" + input + \"'\");\n  }\n}\n\n// src/service-plan-v0/main.ts\nfunction createTaskName(item, cat, day) {\n  return cat[0] + date.format(day, \"RRRRMMdd\") + \" \" + item.toilet[\"location name\"];\n}\nfunction mkServicePlan(item, day) {\n  const result = [];\n  const dateDeploy = item.toilet[\"Contract start\"] ? date.parseISO(item.toilet[\"Contract start\"]) : null;\n  const dateCollect = item.toilet[\"Contract end\"] ? date.parseISO(item.toilet[\"Contract end\"]) : null;\n  const todayDeploy = dateDeploy ? date.isEqual(dateDeploy, day) : false;\n  const todayCollect = dateCollect ? date.isEqual(dateCollect, day) : false;\n  const todayTemplates = item.templates.filter((tmpl) => tmpl.Dow == date.getDay(day));\n  if (todayDeploy || todayCollect) {\n    if (todayDeploy) {\n      result.push({\n        Category: \"Deploy\",\n        Toilet: [item.toilet.id],\n        complete_after: day,\n        complete_before: date.addDays(day, 1),\n        Contact: item.toilet.Contact,\n        Name: createTaskName(item, \"Deploy\", day)\n      });\n    }\n    if (todayCollect) {\n      result.push({\n        Name: createTaskName(item, \"Collect\", day),\n        Category: \"Collect\",\n        Toilet: [item.toilet.id],\n        Contact: item.toilet.Contact,\n        complete_after: day,\n        complete_before: date.addDays(day, 1)\n      });\n    }\n  } else {\n    const constraints = parseTimeConstraints(item.toilet[\"Time constraints\"] || \"\");\n    todayTemplates.forEach((tmpl) => result.push({\n      Name: createTaskName(item, \"Service\", day),\n      Category: \"Service\",\n      Toilet: tmpl.Toilet,\n      Contact: item.toilet.Contact,\n      Template: [tmpl.id],\n      complete_after: date.add(day, constraints.timeAfter),\n      complete_before: date.add(day, constraints.timeBefore)\n    }));\n  }\n  return result;\n}\nfunction mapGetOrDefault(map, key, def) {\n  if (!map.has(key)) {\n    const result = def(key);\n    map.set(key, result);\n    return result;\n  }\n  return map.get(key);\n}\nfunction mergeToiletTemplates(items2) {\n  const tmplByToiletId = new Map();\n  items2.forEach((i) => {\n    const factory = () => {\n      return { templates: [] };\n    };\n    if (\"toilet\" in i) {\n      mapGetOrDefault(tmplByToiletId, i.toilet.id, factory).toilet = i.toilet;\n    } else if (\"template\" in i) {\n      mapGetOrDefault(tmplByToiletId, i.template.Toilet[0], factory).templates.push(i.template);\n    } else {\n      throw new TypeError(\"Missing toilet or template\");\n    }\n  });\n  return Array.from(tmplByToiletId.values());\n}\nfunction main() {\n  const myItems = mergeToiletTemplates(items.map((i) => i.json));\n  const config = $node.Configuration.json;\n  const today = date.startOfToday();\n  const result = [];\n  for (let i = 0; i < config.daysForward; i++) {\n    const day = date.add(today, { days: i + 1 });\n    result.push(...myItems.filter((item) => !!item.toilet).flatMap((item) => mkServicePlan(item, day)).map((i2) => {\n      return { json: i2 };\n    }));\n  }\n  return result;\n}\n\n  return main();\n\n"
      },
      "name": "Create Service Plan",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        940,
        -220
      ]
    },
    {
      "parameters": {
        "operation": "list",
        "application": "={{$json.airtable.baseId}}",
        "table": "={{$json.airtable.tableId.source}}",
        "additionalOptions": {
          "filterByFormula": "={Variant} = {{$json.templateVariant}}"
        }
      },
      "name": "Get all template entries",
      "type": "n8n-nodes-base.airtable",
      "typeVersion": 1,
      "position": [
        340,
        -120
      ],
      "credentials": {
        "airtableApi": {
          "id": "1",
          "name": "Airtable account"
        }
      },
      "notes": "currently selecting route templates for days of week before today (Sunday = first DOW)"
    },
    {
      "parameters": {
        "operation": "list",
        "application": "={{$node[\"Configuration\"].json[\"airtable\"][\"baseId\"]}}",
        "table": "={{$node[\"Configuration\"].json[\"airtable\"][\"tableId\"][\"toilet\"]}}",
        "additionalOptions": {
          "filterByFormula": "=and(\nnot(is_before(dateadd(today(),{{$json[\"daysForward\"]}},'days'), {Display contract start})),\nnot(is_after(dateadd(today(),1,'days'), {Display contract end}))\n)"
        }
      },
      "name": "Get all toilets",
      "type": "n8n-nodes-base.airtable",
      "typeVersion": 1,
      "position": [
        340,
        -330
      ],
      "credentials": {
        "airtableApi": {
          "id": "1",
          "name": "Airtable account"
        }
      }
    },
    {
      "parameters": {},
      "name": "Merge",
      "type": "n8n-nodes-base.merge",
      "typeVersion": 1,
      "position": [
        740,
        -220
      ],
      "notes": "Not doing any kind of merge - merge by key either drops toilets without route templates (but with deploy/collect date) or drops all route templates of toilet apart from the first"
    },
    {
      "parameters": {
        "keys": {
          "key": [
            {
              "currentKey": "fields",
              "newKey": "toilet"
            },
            {
              "currentKey": "id",
              "newKey": "toilet.id"
            }
          ]
        }
      },
      "name": "Create toilet",
      "type": "n8n-nodes-base.renameKeys",
      "typeVersion": 1,
      "position": [
        540,
        -330
      ]
    },
    {
      "parameters": {
        "keys": {
          "key": [
            {
              "currentKey": "fields",
              "newKey": "template"
            },
            {
              "currentKey": "id",
              "newKey": "template.id"
            }
          ]
        }
      },
      "name": "Create template",
      "type": "n8n-nodes-base.renameKeys",
      "typeVersion": 1,
      "position": [
        550,
        -120
      ]
    },
    {
      "parameters": {
        "operation": "list",
        "application": "={{$node[\"Configuration\"].json[\"airtable\"][\"baseId\"]}}",
        "table": "={{$node[\"Configuration\"].json[\"airtable\"][\"tableId\"][\"target\"]}}",
        "returnAll": false,
        "limit": 1,
        "additionalOptions": {
          "fields": [
            "Name"
          ],
          "filterByFormula": "={Name} = '{{$json[\"Name\"]}}'"
        }
      },
      "name": "Search by external_id",
      "type": "n8n-nodes-base.airtable",
      "typeVersion": 1,
      "position": [
        1140,
        -120
      ],
      "credentials": {
        "airtableApi": {
          "id": "1",
          "name": "Airtable account"
        }
      }
    },
    {
      "parameters": {
        "operation": "append",
        "application": "={{$node[\"Configuration\"].json[\"airtable\"][\"baseId\"]}}",
        "table": "={{$node[\"Configuration\"].json[\"airtable\"][\"tableId\"][\"target\"]}}",
        "options": {
          "bulkSize": 10,
          "typecast": true
        }
      },
      "name": "Create service plan entry",
      "type": "n8n-nodes-base.airtable",
      "typeVersion": 1,
      "position": [
        1540,
        -400
      ],
      "credentials": {
        "airtableApi": {
          "id": "1",
          "name": "Airtable account"
        }
      }
    },
    {
      "parameters": {
        "operation": "update",
        "application": "={{$node[\"Configuration\"].json[\"airtable\"][\"baseId\"]}}",
        "table": "={{$node[\"Configuration\"].json[\"airtable\"][\"tableId\"][\"target\"]}}",
        "id": "={{$json[\"id\"]}}",
        "updateAllFields": false,
        "fields": [
          []
        ],
        "options": {
          "bulkSize": 10,
          "typecast": true
        }
      },
      "name": "Update service plan entry",
      "type": "n8n-nodes-base.airtable",
      "typeVersion": 1,
      "position": [
        1540,
        -120
      ],
      "credentials": {
        "airtableApi": {
          "id": "1",
          "name": "Airtable account"
        }
      }
    },
    {
      "parameters": {
        "functionCode": "console.log(item);\n\nreturn item;"
      },
      "name": "FunctionItem",
      "type": "n8n-nodes-base.functionItem",
      "typeVersion": 1,
      "position": [
        1740,
        -400
      ]
    },
    {
      "parameters": {
        "conditions": {
          "boolean": [
            {
              "value1": "={{$json.id != undefined}}",
              "value2": true
            }
          ]
        }
      },
      "name": "IF",
      "type": "n8n-nodes-base.if",
      "typeVersion": 1,
      "position": [
        1340,
        -120
      ]
    },
    {
      "parameters": {
        "mode": "removeKeyMatches",
        "propertyName1": "Name",
        "propertyName2": "fields.Name"
      },
      "name": "Merge1",
      "type": "n8n-nodes-base.merge",
      "typeVersion": 1,
      "position": [
        1340,
        -400
      ]
    }
  ],
  "connections": {
    "Start": {
      "main": [
        [
          {
            "node": "Configuration",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Configuration": {
      "main": [
        [
          {
            "node": "Get all toilets",
            "type": "main",
            "index": 0
          },
          {
            "node": "Get all template entries",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Get all template entries": {
      "main": [
        [
          {
            "node": "Create template",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Get all toilets": {
      "main": [
        [
          {
            "node": "Create toilet",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Merge": {
      "main": [
        [
          {
            "node": "Create Service Plan",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Create toilet": {
      "main": [
        [
          {
            "node": "Merge",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Create template": {
      "main": [
        [
          {
            "node": "Merge",
            "type": "main",
            "index": 1
          }
        ]
      ]
    },
    "Create Service Plan": {
      "main": [
        [
          {
            "node": "Merge1",
            "type": "main",
            "index": 0
          },
          {
            "node": "Search by external_id",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Search by external_id": {
      "main": [
        [
          {
            "node": "IF",
            "type": "main",
            "index": 0
          },
          {
            "node": "Merge1",
            "type": "main",
            "index": 1
          }
        ]
      ]
    },
    "Create service plan entry": {
      "main": [
        [
          {
            "node": "FunctionItem",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Merge1": {
      "main": [
        [
          {
            "node": "Create service plan entry",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "IF": {
      "main": [
        [
          {
            "node": "Update service plan entry",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  },
  "active": false,
  "settings": {
    "timezone": "Europe/Vienna",
    "saveExecutionProgress": true,
    "saveManualExecutions": true
  },
  "id": 1
}

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

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.

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?

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

Here an example:

: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)

{
  "name": "Apply templates variant 0",
  "nodes": [
    {
      "parameters": {},
      "name": "Start",
      "type": "n8n-nodes-base.start",
      "typeVersion": 1,
      "position": [
        -60,
        -210
      ]
    },
    {
      "parameters": {
        "values": {
          "string": [
            {
              "name": "airtable.baseId",
              "value": "-"
            },
            {
              "name": "airtable.tableId.source",
              "value": "-"
            },
            {
              "name": "airtable.tableId.target",
              "value": "-"
            },
            {
              "name": "airtable.tableId.toilet",
              "value": "-"
            }
          ],
          "number": [
            {
              "name": "templateVariant"
            },
            {
              "name": "daysForward",
              "value": 7
            }
          ]
        },
        "options": {}
      },
      "name": "Configuration",
      "type": "n8n-nodes-base.set",
      "typeVersion": 1,
      "position": [
        140,
        -210
      ],
      "executeOnce": true,
      "color": "#FF0000"
    },
    {
      "parameters": {
        "functionCode": "var __create = Object.create;\nvar __defProp = Object.defineProperty;\nvar __getOwnPropDesc = Object.getOwnPropertyDescriptor;\nvar __getOwnPropNames = Object.getOwnPropertyNames;\nvar __getProtoOf = Object.getPrototypeOf;\nvar __hasOwnProp = Object.prototype.hasOwnProperty;\nvar __markAsModule = (target) => __defProp(target, \"__esModule\", { value: true });\nvar __reExport = (target, module2, desc) => {\n  if (module2 && typeof module2 === \"object\" || typeof module2 === \"function\") {\n    for (let key of __getOwnPropNames(module2))\n      if (!__hasOwnProp.call(target, key) && key !== \"default\")\n        __defProp(target, key, { get: () => module2[key], enumerable: !(desc = __getOwnPropDesc(module2, key)) || desc.enumerable });\n  }\n  return target;\n};\nvar __toModule = (module2) => {\n  return __reExport(__markAsModule(__defProp(module2 != null ? __create(__getProtoOf(module2)) : {}, \"default\", module2 && module2.__esModule && \"default\" in module2 ? { get: () => module2.default, enumerable: true } : { value: module2, enumerable: true })), module2);\n};\n\n// src/service-plan-v0/main.ts\nvar date = __toModule(require(\"date-fns\"));\n\n// src/timeConstraints.ts\nfunction toDuration(down, hours, minutes) {\n  return {\n    hours,\n    minutes,\n    days: hours || minutes ? void 0 : down ? void 0 : 1\n  };\n}\nfunction parseTimeConstraints(input) {\n  if (!input || input.trim() == \"\") {\n    return {\n      timeAfter: {},\n      timeBefore: { days: 1 }\n    };\n  }\n  const reMatch = /^(?:([01]?[0-9]|2[0-3])(?::([0-5][0-9]))?)?-?(?:([01]?[0-9]|2[0-3])(?::([0-5][0-9]))?)?$/.exec(input);\n  if (reMatch) {\n    const timeAfterHrs = reMatch[1] ? Number.parseInt(reMatch[1]) : void 0;\n    const timeAfterMin = reMatch[2] ? Number.parseInt(reMatch[2]) : void 0;\n    const timeBeforeHrs = reMatch[3] ? Number.parseInt(reMatch[3]) : void 0;\n    const timeBeforeMin = reMatch[4] ? Number.parseInt(reMatch[4]) : void 0;\n    return {\n      timeAfter: toDuration(true, timeAfterHrs, timeAfterMin),\n      timeBefore: toDuration(false, timeBeforeHrs, timeBeforeMin)\n    };\n  } else {\n    throw new TypeError(\"Could not parse time constraints from '\" + input + \"'\");\n  }\n}\n\n// src/service-plan-v0/main.ts\nfunction createTaskName(item, cat, day) {\n  return cat[0] + date.format(day, \"RRRRMMdd\") + \" \" + item.toilet[\"location name\"];\n}\nfunction mkServicePlan(item, day) {\n  const result = [];\n  const dateDeploy = item.toilet[\"Contract start\"] ? date.parseISO(item.toilet[\"Contract start\"]) : null;\n  const dateCollect = item.toilet[\"Contract end\"] ? date.parseISO(item.toilet[\"Contract end\"]) : null;\n  const todayDeploy = dateDeploy ? date.isEqual(dateDeploy, day) : false;\n  const todayCollect = dateCollect ? date.isEqual(dateCollect, day) : false;\n  const todayTemplates = item.templates.filter((tmpl) => tmpl.Dow == date.getDay(day));\n  if (todayDeploy || todayCollect) {\n    if (todayDeploy) {\n      result.push({\n        Category: \"Deploy\",\n        Toilet: [item.toilet.id],\n        complete_after: day,\n        complete_before: date.addDays(day, 1),\n        Contact: item.toilet.Contact,\n        Name: createTaskName(item, \"Deploy\", day)\n      });\n    }\n    if (todayCollect) {\n      result.push({\n        Name: createTaskName(item, \"Collect\", day),\n        Category: \"Collect\",\n        Toilet: [item.toilet.id],\n        Contact: item.toilet.Contact,\n        complete_after: day,\n        complete_before: date.addDays(day, 1)\n      });\n    }\n  } else {\n    const constraints = parseTimeConstraints(item.toilet[\"Time constraints\"] || \"\");\n    todayTemplates.forEach((tmpl) => result.push({\n      Name: createTaskName(item, \"Service\", day),\n      Category: \"Service\",\n      Toilet: tmpl.Toilet,\n      Contact: item.toilet.Contact,\n      Template: [tmpl.id],\n      complete_after: date.add(day, constraints.timeAfter),\n      complete_before: date.add(day, constraints.timeBefore)\n    }));\n  }\n  return result;\n}\nfunction mapGetOrDefault(map, key, def) {\n  if (!map.has(key)) {\n    const result = def(key);\n    map.set(key, result);\n    return result;\n  }\n  return map.get(key);\n}\nfunction mergeToiletTemplates(items2) {\n  const tmplByToiletId = new Map();\n  items2.forEach((i) => {\n    const factory = () => {\n      return { templates: [] };\n    };\n    if (\"toilet\" in i) {\n      mapGetOrDefault(tmplByToiletId, i.toilet.id, factory).toilet = i.toilet;\n    } else if (\"template\" in i) {\n      mapGetOrDefault(tmplByToiletId, i.template.Toilet[0], factory).templates.push(i.template);\n    } else {\n      throw new TypeError(\"Missing toilet or template\");\n    }\n  });\n  return Array.from(tmplByToiletId.values());\n}\nfunction main() {\n  const myItems = mergeToiletTemplates(items.map((i) => i.json));\n  const config = $node.Configuration.json;\n  const today = date.startOfToday();\n  const result = [];\n  for (let i = 0; i < config.daysForward; i++) {\n    const day = date.add(today, { days: i + 1 });\n    result.push(...myItems.filter((item) => !!item.toilet).flatMap((item) => mkServicePlan(item, day)).map((i2) => {\n      return { json: i2 };\n    }));\n  }\n  return result;\n}\n\n  return main();\n\n"
      },
      "name": "Create Service Plan",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        940,
        -220
      ]
    },
    {
      "parameters": {
        "operation": "list",
        "application": "={{$json.airtable.baseId}}",
        "table": "={{$json.airtable.tableId.source}}",
        "returnAll": false,
        "limit": 20,
        "additionalOptions": {
          "filterByFormula": "={Variant} = {{$json.templateVariant}}"
        }
      },
      "name": "Get all template entries",
      "type": "n8n-nodes-base.airtable",
      "typeVersion": 1,
      "position": [
        340,
        -120
      ],
      "credentials": {
        "airtableApi": {
          "id": "1",
          "name": "Airtable account"
        }
      },
      "notes": "currently selecting route templates for days of week before today (Sunday = first DOW)"
    },
    {
      "parameters": {
        "operation": "list",
        "application": "={{$node[\"Configuration\"].json[\"airtable\"][\"baseId\"]}}",
        "table": "={{$node[\"Configuration\"].json[\"airtable\"][\"tableId\"][\"toilet\"]}}",
        "additionalOptions": {
          "filterByFormula": "=and(\nnot(is_before(dateadd(today(),{{$json[\"daysForward\"]}},'days'), {Display contract start})),\nnot(is_after(dateadd(today(),1,'days'), {Display contract end}))\n)"
        }
      },
      "name": "Get all toilets",
      "type": "n8n-nodes-base.airtable",
      "typeVersion": 1,
      "position": [
        340,
        -330
      ],
      "credentials": {
        "airtableApi": {
          "id": "1",
          "name": "Airtable account"
        }
      }
    },
    {
      "parameters": {},
      "name": "Merge",
      "type": "n8n-nodes-base.merge",
      "typeVersion": 1,
      "position": [
        740,
        -220
      ],
      "notes": "Not doing any kind of merge - merge by key either drops toilets without route templates (but with deploy/collect date) or drops all route templates of toilet apart from the first"
    },
    {
      "parameters": {
        "keys": {
          "key": [
            {
              "currentKey": "fields",
              "newKey": "toilet"
            },
            {
              "currentKey": "id",
              "newKey": "toilet.id"
            }
          ]
        }
      },
      "name": "Create toilet",
      "type": "n8n-nodes-base.renameKeys",
      "typeVersion": 1,
      "position": [
        540,
        -330
      ]
    },
    {
      "parameters": {
        "keys": {
          "key": [
            {
              "currentKey": "fields",
              "newKey": "template"
            },
            {
              "currentKey": "id",
              "newKey": "template.id"
            }
          ]
        }
      },
      "name": "Create template",
      "type": "n8n-nodes-base.renameKeys",
      "typeVersion": 1,
      "position": [
        540,
        -120
      ]
    },
    {
      "parameters": {
        "operation": "list",
        "application": "={{$node[\"Configuration\"].json[\"airtable\"][\"baseId\"]}}",
        "table": "={{$node[\"Configuration\"].json[\"airtable\"][\"tableId\"][\"target\"]}}",
        "additionalOptions": {
          "fields": [
            "Name"
          ],
          "filterByFormula": "={Name} = '{{$json[\"Name\"]}}'"
        }
      },
      "name": "Search by external_id",
      "type": "n8n-nodes-base.airtable",
      "typeVersion": 1,
      "position": [
        1390,
        -100
      ],
      "credentials": {
        "airtableApi": {
          "id": "1",
          "name": "Airtable account"
        }
      }
    },
    {
      "parameters": {
        "operation": "append",
        "application": "={{$node[\"Configuration\"].json[\"airtable\"][\"baseId\"]}}",
        "table": "={{$node[\"Configuration\"].json[\"airtable\"][\"tableId\"][\"target\"]}}",
        "options": {
          "bulkSize": 10,
          "typecast": true
        }
      },
      "name": "Create service plan entry",
      "type": "n8n-nodes-base.airtable",
      "typeVersion": 1,
      "position": [
        2290,
        -390
      ],
      "credentials": {
        "airtableApi": {
          "id": "1",
          "name": "Airtable account"
        }
      }
    },
    {
      "parameters": {
        "operation": "update",
        "application": "={{$node[\"Configuration\"].json[\"airtable\"][\"baseId\"]}}",
        "table": "={{$node[\"Configuration\"].json[\"airtable\"][\"tableId\"][\"target\"]}}",
        "id": "={{$json[\"id\"]}}",
        "updateAllFields": false,
        "fields": [
          []
        ],
        "options": {
          "bulkSize": 10,
          "typecast": true
        }
      },
      "name": "Update service plan entry",
      "type": "n8n-nodes-base.airtable",
      "typeVersion": 1,
      "position": [
        2290,
        -110
      ],
      "credentials": {
        "airtableApi": {
          "id": "1",
          "name": "Airtable account"
        }
      }
    },
    {
      "parameters": {
        "functionCode": "console.log(item);\n\nreturn item;"
      },
      "name": "FunctionItem",
      "type": "n8n-nodes-base.functionItem",
      "typeVersion": 1,
      "position": [
        2490,
        -390
      ]
    },
    {
      "parameters": {
        "conditions": {
          "boolean": [
            {
              "value1": "={{$json.id != undefined}}",
              "value2": true
            }
          ]
        }
      },
      "name": "IF",
      "type": "n8n-nodes-base.if",
      "typeVersion": 1,
      "position": [
        2090,
        -110
      ]
    },
    {
      "parameters": {
        "mode": "removeKeyMatches",
        "propertyName1": "Name",
        "propertyName2": "fields.Name"
      },
      "name": "Merge1",
      "type": "n8n-nodes-base.merge",
      "typeVersion": 1,
      "position": [
        2090,
        -390
      ]
    },
    {
      "parameters": {
        "batchSize": 1,
        "options": {}
      },
      "name": "SplitInBatches",
      "type": "n8n-nodes-base.splitInBatches",
      "typeVersion": 1,
      "position": [
        1150,
        -100
      ]
    },
    {
      "parameters": {
        "functionCode": "const newItems = [];\n\nlet runIndex = 0;\ndo {\n  try {\n    newItems.push($items(\"Search by external_id\", 0, runIndex++));\n  } catch (e) {\n    break;  \n  }\n} while(true);\nreturn newItems; \n"
      },
      "name": "Function1",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        1830,
        -110
      ]
    },
    {
      "parameters": {
        "conditions": {
          "boolean": [
            {
              "value1": true,
              "value2": "={{$node[\"SplitInBatches\"].context[\"noItemsLeft\"]}}"
            }
          ]
        }
      },
      "name": "IF1",
      "type": "n8n-nodes-base.if",
      "typeVersion": 1,
      "position": [
        1710,
        140
      ]
    }
  ],
  "connections": {
    "Start": {
      "main": [
        [
          {
            "node": "Configuration",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Configuration": {
      "main": [
        [
          {
            "node": "Get all toilets",
            "type": "main",
            "index": 0
          },
          {
            "node": "Get all template entries",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Get all template entries": {
      "main": [
        [
          {
            "node": "Create template",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Get all toilets": {
      "main": [
        [
          {
            "node": "Create toilet",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Merge": {
      "main": [
        [
          {
            "node": "Create Service Plan",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Create toilet": {
      "main": [
        [
          {
            "node": "Merge",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Create template": {
      "main": [
        [
          {
            "node": "Merge",
            "type": "main",
            "index": 1
          }
        ]
      ]
    },
    "Create Service Plan": {
      "main": [
        [
          {
            "node": "Merge1",
            "type": "main",
            "index": 0
          },
          {
            "node": "SplitInBatches",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Search by external_id": {
      "main": [
        [
          {
            "node": "IF1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Create service plan entry": {
      "main": [
        [
          {
            "node": "FunctionItem",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Merge1": {
      "main": [
        [
          {
            "node": "Create service plan entry",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "IF": {
      "main": [
        [
          {
            "node": "Update service plan entry",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "SplitInBatches": {
      "main": [
        [
          {
            "node": "Search by external_id",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "IF1": {
      "main": [
        [
          {
            "node": "Function1",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "SplitInBatches",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Function1": {
      "main": [
        [
          {
            "node": "IF",
            "type": "main",
            "index": 0
          },
          {
            "node": "Merge1",
            "type": "main",
            "index": 1
          }
        ]
      ]
    }
  },
  "active": false,
  "settings": {
    "timezone": "Europe/Vienna",
    "saveExecutionProgress": true,
    "saveManualExecutions": true
  },
  "id": 1
}

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

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(’, ')+")"}}