How to Loop using Split In Batches Node

Use Case:

Today’s Date Function will generate the Date and with the help of MySQL Node, I’m getting the email address matching today’s date.

Actually, there are multiple emails, Not only one.

Then Updating the Contact with a Tag in Automizy Node.

The Problem is. It’s updating only the first data got from Database.

I want to loop through this and update all the emails in Automizy which I got from MySQL Node.

Output of MySQL node:

image

Workflow:

{
  "nodes": [
    {
      "parameters": {
        "triggerTimes": {
          "item": [
            {
              "hour": 23,
              "minute": 58
            }
          ]
        }
      },
      "name": "Cron",
      "type": "n8n-nodes-base.cron",
      "typeVersion": 1,
      "position": [
        590,
        320
      ]
    },
    {
      "parameters": {
        "functionCode": "var today = new Date();\nvar dd = String(today.getDate()).padStart(2, '0');\nvar mm = String(today.getMonth() + 1).padStart(2, '0'); //January is 0!\nvar yyyy = today.getFullYear();\n\ntoday = yyyy + '-' + mm + '-' + dd;\nitems[0].json.todayDate = today;\nreturn items;"
      },
      "name": "Today's Date",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        770,
        320
      ]
    },
    {
      "parameters": {
        "operation": "executeQuery",
        "query": "=select email FROM api_key where erd=\"{{$node[\"Today's Date\"].json[\"todayDate\"]}}\""
      },
      "name": "MySQL1",
      "type": "n8n-nodes-base.mySql",
      "typeVersion": 1,
      "position": [
        950,
        320
      ],
      "credentials": {
        "mySql": "Singapore DB"
      }
    },
    {
      "parameters": {
        "batchSize": 1,
        "options": {
          "reset": true
        }
      },
      "name": "SplitInBatches",
      "type": "n8n-nodes-base.splitInBatches",
      "typeVersion": 1,
      "position": [
        1130,
        320
      ]
    },
    {
      "parameters": {
        "operation": "update",
        "email": "={{$node[\"SplitInBatches\"].json[\"email\"]}}",
        "updateFields": {
          "tags": [
            "For Renewal"
          ]
        }
      },
      "name": "Automizy",
      "type": "n8n-nodes-base.automizy",
      "typeVersion": 1,
      "position": [
        1290,
        320
      ],
      "credentials": {
        "automizyApi": "Personalizery Automizy"
      }
    }
  ],
  "connections": {
    "Cron": {
      "main": [
        [
          {
            "node": "Today's Date",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Today's Date": {
      "main": [
        [
          {
            "node": "MySQL1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "MySQL1": {
      "main": [
        [
          {
            "node": "SplitInBatches",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "SplitInBatches": {
      "main": [
        [
          {
            "node": "Automizy",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

Hey @mcnaveen!

The documentation for the Split In Batches node covers what you’re looking for. To give a brief, you have to connect the output of the Automizy node to the input of the Split In Batches node. This way, after the Automizy node gets executed, it will trigger the Split In Batches node. The Split In Batches node will fetch the new batch, pass that information to Automizy. This loop will continue till the Split In Batches node fetches all the information.

Also, you can execute the same workflow without using the Split In Batches node. The Automizy node will update all the emails.

Split in Batches node is a bit confusing for me bro. Do I need to set Batch Size as 1

Why I’m asking this because I can’t predict how much data will come.

How this can be done?

@mcnaveen it makes sense to use the Split In Batches node if you’re not sure about the amount of the data that will be returned. Below is the workflow that might help

{
  "nodes": [
    {
      "parameters": {
        "triggerTimes": {
          "item": [
            {
              "hour": 23,
              "minute": 58
            }
          ]
        }
      },
      "name": "Cron",
      "type": "n8n-nodes-base.cron",
      "typeVersion": 1,
      "position": [
        638,
        296
      ]
    },
    {
      "parameters": {
        "functionCode": "var today = new Date();\nvar dd = String(today.getDate()).padStart(2, '0');\nvar mm = String(today.getMonth() + 1).padStart(2, '0'); //January is 0!\nvar yyyy = today.getFullYear();\n\ntoday = yyyy + '-' + mm + '-' + dd;\nitems[0].json.todayDate = today;\nreturn items;"
      },
      "name": "Today's Date",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        818,
        296
      ]
    },
    {
      "parameters": {
        "operation": "executeQuery",
        "query": "=select email FROM api_key where erd=\"{{$node[\"Today's Date\"].json[\"todayDate\"]}}\""
      },
      "name": "MySQL1",
      "type": "n8n-nodes-base.mySql",
      "typeVersion": 1,
      "position": [
        998,
        296
      ],
      "credentials": {
        "mySql": "mysql-support"
      }
    },
    {
      "parameters": {
        "batchSize": 1,
        "options": {}
      },
      "name": "SplitInBatches",
      "type": "n8n-nodes-base.splitInBatches",
      "typeVersion": 1,
      "position": [
        1200,
        300
      ]
    },
    {
      "parameters": {
        "operation": "update",
        "email": "={{$node[\"SplitInBatches\"].json[\"email\"]}}",
        "updateFields": {
          "tags": [
            "reviewer"
          ]
        }
      },
      "name": "Automizy",
      "type": "n8n-nodes-base.automizy",
      "typeVersion": 1,
      "position": [
        1310,
        150
      ],
      "credentials": {
        "automizyApi": "automizy"
      }
    }
  ],
  "connections": {
    "Cron": {
      "main": [
        [
          {
            "node": "Today's Date",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Today's Date": {
      "main": [
        [
          {
            "node": "MySQL1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "MySQL1": {
      "main": [
        [
          {
            "node": "SplitInBatches",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "SplitInBatches": {
      "main": [
        [
          {
            "node": "Automizy",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Automizy": {
      "main": [
        [
          {
            "node": "SplitInBatches",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

Finally got it working & understood.

2 Likes