Microsoft SQL Loop - Not Iterating Correctly For Me

Questions
1

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.

image
image1189×437 48.7 KB

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.

2

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.

3

@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?

image
image1192×454 48.6 KB

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

Please clarify…thanks!

4

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.

5

@Jon,

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

1 Like
6

That is good to hear, Happy Automating :tada: