Microsoft SQL Loop - Not Iterating Correctly For Me

I am attempting to update the number of email attempts in my Microsoft SQL database each day. I have built the following workflow and it does iterate as expected. However, the output of the “Update Email Attempts” node uses the data from the first item for each execution. Hence, only a single record in the database gets updated…and it gets updated multiple times with the data depending on the total number of events returned from the “Retrieve Completed Events” node.

{
  "nodes": [
    {
      "parameters": {
        "triggerTimes": {
          "item": [
            {
              "hour": "={{10}}"
            }
          ]
        }
      },
      "name": "Cron",
      "type": "n8n-nodes-base.cron",
      "typeVersion": 1,
      "position": [
        -460,
        40
      ]
    },
    {
      "parameters": {
        "fromEmail": "[email protected]",
        "toEmail": "={{$node[\"Retrieve Completed Events\"].json[\"userEmail\"]}}",
        "subject": "=Demo Event Followup - {{$node[\"Retrieve Completed Events\"].json[\"eventName\"]}}",
        "text": "=Event Name: {{$node[\"Retrieve Completed Events\"].json[\"eventName\"]}}\nEvent Date: {{$node[\"Retrieve Completed Events\"].json[\"eventDate\"]}}\n\nWhen this event was created the number of anticipated attendees was unknown. \n\nNow that the event has occurred, please update the number of qualified participants who attended the demo.  \n\nYou can access the Demo Schedule app at the following link:\n\nhttps://portal.danleysoundlabs.com/app_3e083c261dd444098e4f0ce0bd92fd84",
        "options": {
          "allowUnauthorizedCerts": true
        }
      },
      "name": "Send Email",
      "type": "n8n-nodes-base.emailSend",
      "typeVersion": 1,
      "position": [
        -40,
        -100
      ],
      "credentials": {
        "smtp": {
          "id": "1",
          "name": "Email Account"
        }
      }
    },
    {
      "parameters": {
        "operation": "executeQuery",
        "query": "=SELECT recordId, eventName, eventlocation, CONVERT(varchar(10),eventStart,101) eventDate, emailAttempts, userEmail\nFROM demoSchedule\nWHERE participants = 0\nAND (emailAttempts is NULL OR emailAttempts <= 3) \nAND eventEnd < GETUTCDATE()"
      },
      "name": "Retrieve Completed Events",
      "type": "n8n-nodes-base.microsoftSql",
      "typeVersion": 1,
      "position": [
        -260,
        40
      ],
      "notesInFlow": false,
      "credentials": {
        "microsoftSql": {
          "id": "4",
          "name": "SqlExpress"
        }
      }
    },
    {
      "parameters": {
        "operation": "executeQuery",
        "query": "=UPDATE demoSchedule\nSET emailAttempts ={{$node[\"Retrieve Completed Events\"].json[\"emailAttempts\"]}} + 1\nWHERE recordId = ( {{$node[\"Retrieve Completed Events\"].json[\"recordId\"]}});"
      },
      "name": "Update Email Attempts",
      "type": "n8n-nodes-base.microsoftSql",
      "typeVersion": 1,
      "position": [
        180,
        140
      ],
      "executeOnce": false,
      "credentials": {
        "microsoftSql": {
          "id": "4",
          "name": "SqlExpress"
        }
      }
    },
    {
      "parameters": {
        "conditions": {
          "number": [
            {
              "value1": "={{$runIndex}}",
              "value2": "={{$position()}}"
            }
          ]
        }
      },
      "name": "IF",
      "type": "n8n-nodes-base.if",
      "typeVersion": 1,
      "position": [
        420,
        140
      ]
    },
    {
      "parameters": {},
      "name": "NoOp",
      "type": "n8n-nodes-base.noOp",
      "typeVersion": 1,
      "position": [
        600,
        80
      ]
    },
    {
      "parameters": {
        "batchSize": 1,
        "options": {}
      },
      "name": "SplitInBatches",
      "type": "n8n-nodes-base.splitInBatches",
      "typeVersion": 1,
      "position": [
        -40,
        140
      ]
    }
  ],
  "connections": {
    "Cron": {
      "main": [
        [
          {
            "node": "Retrieve Completed Events",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Retrieve Completed Events": {
      "main": [
        [
          {
            "node": "Send Email",
            "type": "main",
            "index": 0
          },
          {
            "node": "SplitInBatches",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Update Email Attempts": {
      "main": [
        [
          {
            "node": "IF",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "IF": {
      "main": [
        [
          {
            "node": "NoOp",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "SplitInBatches",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "SplitInBatches": {
      "main": [
        [
          {
            "node": "Update Email Attempts",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

An example JSON of completed events looks like this:

[
  {
    "recordId": 43,
    "eventName": "Older Event 2",
    "eventlocation": "UGA Stadium",
    "eventDate": "12/02/2021",
    "emailAttempts": 1,
    "userEmail": "[email protected]"
  },
  {
    "recordId": 44,
    "eventName": "Participants Test 1",
    "eventlocation": "Who KNows",
    "eventDate": "12/07/2021",
    "emailAttempts": 0,
    "userEmail": "[email protected]"
  },
  {
    "recordId": 61,
    "eventName": "outer space",
    "eventlocation": "outer most rim",
    "eventDate": "12/05/2021",
    "emailAttempts": 0,
    "userEmail": "[email protected]"
  }
]

The expected output would be to have each of the individual records (identified by their recordId) updated with their respective calculations (incremented by 1 over their existing value). Currently, if three events are returned, the first event gets its email attempts incremented by 1 (and the new value gets written as many times as there are events / batches).

Any guidance as to what I am missing would be appreciated.

Hey @chadgriff,

It looks like you are using the Node output from before the split in batches, If you use the output from the Split node instead it should give you what you are after.

@jon,

Thanks for the quick reply. However, I’m not sure I understand your response. If I set the workflow per what I think you are saying I get an infinite loop. Is this what you had in mind?

There were only 3 items in the output form the SplitInBatches node. I manually stopped the workflow after 25 iterations.

Please clarify…thanks!

Hey @chadgriff,

On your original workflow go to the SQL update query and change {{$node["Retrieve Completed Events"].json["emailAttempts"]}} to use the output from Split in batches which would be something like {{$json["emailAttempts"]}}

You will need to do the same thing for the other value as well and that should sort it out.

@jon,

Well…that makes total sense! Works as expected now. Thanks!

1 Like

That is good to hear, Happy Automating :tada: