Still iterate array

Good morning,
I still have my problem with my process. Small explanation
I have an excel file as input, I read each line of the file to extract the value of the reference1 field, this value is then used in an sql query. My problem is that sometimes the value of the reference1 field is an accumulation of 2 values separated by -. I can’t read my file, split the field if necessary and iterate on the split to use my query then continue the process, read the next line.

{
  "nodes": [
    {
      "parameters": {},
      "name": "Start",
      "type": "n8n-nodes-base.start",
      "typeVersion": 1,
      "position": [
        -1040,
        -170
      ]
    },
    {
      "parameters": {
        "functionCode": "return [\n  {\n    json: {},\n    binary: {\n        data: {\n\t\t\t\tdata: items[0].json.file,\n\t\t\t\tmimeType: $node[\"Webhook\"].json[\"query\"][\"type\"], // Optional but should be set if possible (optional)\n\t\t\t\tfileName: $node[\"Webhook\"].json[\"query\"][\"filename\"], // Optional but should be set if possible (optional)\n\t\t\t}    }\n  }\n]"
      },
      "name": "Function",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        -470,
        270
      ]
    },
    {
      "parameters": {
        "fileName": "=usr/local/ETL/CRP/Travaux/Transport/Input/Matrice_Transport.xlsx"
      },
      "name": "Write Binary File",
      "type": "n8n-nodes-base.writeBinaryFile",
      "typeVersion": 1,
      "position": [
        -230,
        260
      ]
    },
    {
      "parameters": {
        "values": {
          "string": [
            {
              "name": "file",
              "value": "={{$node[\"Webhook\"].json[\"body\"][\"name\"][\"data\"].replace('data:'+$node[\"Webhook\"].json[\"query\"][\"type\"]+';base64,','')}}"
            }
          ]
        },
        "options": {}
      },
      "name": "Set2",
      "type": "n8n-nodes-base.set",
      "typeVersion": 1,
      "position": [
        -770,
        270
      ]
    },
    {
      "parameters": {
        "authentication": "headerAuth",
        "httpMethod": "POST",
        "path": "65d59178-e043-45fc-a57f-264915a7daf4",
        "options": {}
      },
      "name": "Webhook",
      "type": "n8n-nodes-base.webhook",
      "typeVersion": 1,
      "position": [
        -1030,
        270
      ],
      "webhookId": "65d59178-e043-45fc-a57f-264915a7daf4",
      "credentials": {
        "httpHeaderAuth": {
          "id": "17",
          "name": "CorepHeaderAuth"
        }
      }
    },
    {
      "parameters": {
        "query": "=SELECT DISTINCT(vp.NUCMDC),vp.CLIC ,v07.RSCLT AS RSCLTCLICDE, v07.CPAYS AS PAYSCLICDE, vp.NULIV FROM COREPFIC.VM010P10 vp  INNER JOIN COREPFIC.VM007P10 v07 ON vp.CLIC = v07.CLI WHERE vp.CMOMOD <> 'A' AND vp.NULIV<>'' AND vp.NUCMDC = '000{{$node[\"Function_Split\"].json[\"values\"].join(', ')}}'"
      },
      "name": "IBM i JTOpen",
      "type": "CUSTOM.ibmi",
      "typeVersion": 1,
      "position": [
        1270,
        250
      ],
      "alwaysOutputData": true,
      "credentials": {
        "ibmiJdbc": {
          "id": "33",
          "name": "IBM i Toolbox for Java JDBC properties account"
        }
      }
    },
    {
      "parameters": {
        "batchSize": 1,
        "options": {
          "reset": false
        }
      },
      "name": "SplitInBatches_lines",
      "type": "n8n-nodes-base.splitInBatches",
      "typeVersion": 1,
      "position": [
        390,
        260
      ]
    },
    {
      "parameters": {
        "query": "=SELECT DISTINCT(COREPFIC.VM010P10.NUCMDC),COREPFIC.VM010P10.CLIL, COREPFIC.VM007P10.RSCLT, COREPFIC.VM007P10.CPAYS, COREPFIC.VM010P10.NULIV FROM COREPFIC.VM010P10 INNER JOIN COREPFIC.VM007P10 ON COREPFIC.VM010P10.CLIL = COREPFIC.VM007P10.CLI WHERE COREPFIC.VM010P10.CMOMOD <> 'A' AND COREPFIC.VM010P10.NULIV <> '' AND COREPFIC.VM010P10.NUCMDC ='000{{$node[\"SetRef\"].json[\"values\"]}}'"
      },
      "name": "IBM i JTOpen1",
      "type": "CUSTOM.ibmi",
      "typeVersion": 1,
      "position": [
        1900,
        -20
      ],
      "credentials": {
        "ibmiJdbc": {
          "id": "33",
          "name": "IBM i Toolbox for Java JDBC properties account"
        }
      }
    },
    {
      "parameters": {
        "options": {}
      },
      "name": "SPF",
      "type": "n8n-nodes-base.spreadsheetFile",
      "typeVersion": 1,
      "position": [
        70,
        250
      ]
    },
    {
      "parameters": {
        "values": {
          "string": [
            {
              "name": "numerocde",
              "value": "={{$node[\"IBM i JTOpen\"].json[\"NUCMDC\"]}}"
            },
            {
              "name": "clientcde",
              "value": "={{$node[\"IBM i JTOpen\"].json[\"CLIC\"]}}"
            },
            {
              "name": "rscltclicde",
              "value": "={{$node[\"IBM i JTOpen\"].json[\"RSCLTCLICDE\"]}}"
            },
            {
              "name": "paysclicde",
              "value": "={{$node[\"IBM i JTOpen\"].json[\"PAYSCLICDE\"]}}"
            },
            {
              "name": "clientliv",
              "value": "={{$node[\"IBM i JTOpen1\"].json[\"CLIL\"]}}"
            },
            {
              "name": "rscltcliliv",
              "value": "={{$node[\"IBM i JTOpen1\"].json[\"RSCLT\"]}}"
            },
            {
              "name": "payscliliv",
              "value": "={{$node[\"IBM i JTOpen1\"].json[\"CPAYS\"]}}"
            },
            {
              "name": "transporteur",
              "value": "={{$node[\"Webhook\"].json[\"query\"][\"transporteur\"]}}"
            },
            {
              "name": "mois",
              "value": "={{$node[\"Webhook\"].json[\"query\"][\"mois\"]}}"
            },
            {
              "name": "numerolivr",
              "value": "={{$node[\"IBM i JTOpen1\"].json[\"NULIV\"]}}"
            }
          ],
          "number": [
            {
              "name": "couttransp",
              "value": "={{$node[\"Split_reference_lines\"].json[\"Montant TTC\"]}}"
            }
          ]
        },
        "options": {}
      },
      "name": "Set",
      "type": "n8n-nodes-base.set",
      "typeVersion": 1,
      "position": [
        2120,
        -20
      ]
    },
    {
      "parameters": {
        "table": "cout_transport",
        "columns": "numerocde,clientcde,rscltclicde,paysclicde,clientliv,rscltcliliv,payscliliv,transporteur,couttransp,mois,numerolivr",
        "additionalFields": {}
      },
      "name": "Postgres1",
      "type": "n8n-nodes-base.postgres",
      "typeVersion": 1,
      "position": [
        2380,
        -20
      ],
      "credentials": {
        "postgres": {
          "id": "8",
          "name": "postgres_appsmith"
        }
      }
    },
    {
      "parameters": {
        "conditions": {
          "string": [
            {
              "value1": "={{$node[\"IBM i JTOpen\"].json[\"NULIV\"]}}",
              "operation": "isEmpty"
            }
          ]
        }
      },
      "name": "IF",
      "type": "n8n-nodes-base.if",
      "typeVersion": 1,
      "position": [
        1600,
        250
      ]
    },
    {
      "parameters": {
        "values": {
          "string": [
            {
              "name": "transporteur",
              "value": "={{$node[\"Webhook\"].json[\"query\"][\"transporteur\"]}}"
            },
            {
              "name": "numero_recepisse",
              "value": "={{$node[\"Function_Split\"].json[\"values\"][0]}}"
            }
          ],
          "number": [
            {
              "name": "mois",
              "value": "={{$node[\"Webhook\"].json[\"query\"][\"mois\"]}}"
            }
          ]
        },
        "options": {}
      },
      "name": "Set1",
      "type": "n8n-nodes-base.set",
      "typeVersion": 1,
      "position": [
        1920,
        260
      ]
    },
    {
      "parameters": {
        "table": "rejet_cout_transport",
        "columns": "transporteur,mois,numero_recepisse",
        "additionalFields": {}
      },
      "name": "Postgres",
      "type": "n8n-nodes-base.postgres",
      "typeVersion": 1,
      "position": [
        2120,
        260
      ],
      "credentials": {
        "postgres": {
          "id": "8",
          "name": "postgres_appsmith"
        }
      }
    },
    {
      "parameters": {
        "functionCode": "const results = [];\nconst text = $node[\"SplitInBatches_lines\"].json[\"Référence 1\"].trim().split('-');\nfor(item of items){\n     results.push({json: {values: text} });\n}\nreturn results;\n\n"
      },
      "name": "Function_Split",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        660,
        260
      ]
    },
    {
      "parameters": {
        "conditions": {
          "boolean": [
            {
              "value1": "={{$node[\"Split_reference_lines\"].context[\"noItemsLeft\"]}}",
              "value2": true
            }
          ]
        }
      },
      "name": "IF3",
      "type": "n8n-nodes-base.if",
      "typeVersion": 1,
      "position": [
        700,
        -310
      ]
    },
    {
      "parameters": {
        "conditions": {
          "boolean": [
            {
              "value1": "={{$node[\"Split_reference_lines\"].context[\"noItemsLeft\"]}}",
              "value2": true
            }
          ]
        }
      },
      "name": "IF4",
      "type": "n8n-nodes-base.if",
      "typeVersion": 1,
      "position": [
        1350,
        720
      ]
    }
  ],
  "connections": {
    "Function": {
      "main": [
        [
          {
            "node": "Write Binary File",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Write Binary File": {
      "main": [
        [
          {
            "node": "SPF",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Set2": {
      "main": [
        [
          {
            "node": "Function",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Webhook": {
      "main": [
        [
          {
            "node": "Set2",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "IBM i JTOpen": {
      "main": [
        [
          {
            "node": "IF",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "SplitInBatches_lines": {
      "main": [
        [
          {
            "node": "Function_Split",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "IBM i JTOpen1": {
      "main": [
        [
          {
            "node": "Set",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "SPF": {
      "main": [
        [
          {
            "node": "SplitInBatches_lines",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Set": {
      "main": [
        [
          {
            "node": "Postgres1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Postgres1": {
      "main": [
        [
          {
            "node": "IF3",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "IF": {
      "main": [
        [
          {
            "node": "Set1",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "IBM i JTOpen1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Set1": {
      "main": [
        [
          {
            "node": "Postgres",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Postgres": {
      "main": [
        [
          {
            "node": "IF4",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Function_Split": {
      "main": [
        [
          {
            "node": "IBM i JTOpen",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "IF3": {
      "main": [
        null,
        [
          {
            "node": "SplitInBatches_lines",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "IF4": {
      "main": [
        null,
        [
          {
            "node": "SplitInBatches_lines",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

this is the screenshot of my process. j’ai fait le lien entre le node Functioon_Split et le node IBM mais c’est ici que cela doit ce passer (j’ai testé beaucoup de choses sans reels résultats)

Thanks for your help

Hi @fporta, I’m sorry to hear you’re having trouble. However, based on your description it’s not 100% clear to me what exactly is not working. Your workflow doesn’t seem to se a reference1 field, so I don’t see where it’s being used.

Maybe you can provide a short and simple workflow including some example data that shows the problem?

@MutedJam
When I reread it, I saw that I was not very clear in my explanations, I would have needed the input file. In any case I found the problem, it is the IBM node which does not work with the iteration. I tested with a postgres node that works. So I’m going to split my process into 2 parts so I don’t need the Item Lists node with the IBM node
Thanks anyway for taking the time to read me

1 Like

Awesome, glad to hear you sorted it out and thanks so much for confirming!