Do until loop not working

[therufus]

Good afternoon all.

I have a process that uses a HTTP GET to obtain all payment data from a bank, and updates the table to be processed. I need this to get 7 days worth of transactions into the table. I've composed a "do until" loop to get todays transactions, subtract 1 from the date, then re-run to get today -1's transactions. Then get today -2's transactions, etc. Here is the flow:

Don't mind the teams posts, that's just to troubleshoot. Here is what happens:

As you can see today's transactions (15/08/2023) gets processed, the compose subtracts a day, the TargetDay variable gets updated, and it runs yesterdays transactions. However, for some reason after this iteration of getting the banking data, the Compose action is never triggered again and it keeps hitting the HTTP call with the same date.

Any ideas what this could be?

[leemager · Accepted solution]

My guess would be that condition action after the HTTP action is always returning false after the first execution, what's in that condition?

[therufus]

There is a lot in the condition. Essentially splitting out payment types, honours, dishonours, adding them to the temp payment table, etc.

My understanding is that if that condition action failed, the whole flow would fail. Am I wrong in assuming that?

[sandeepstw]

Hi There,

It sounds like there's an issue with the state of the loop or variable handling within Power Automate. You can use this approach. 

Make sure 'TargetDay' must be outside of loop. When making HTTP GET request dynamically set date parameter by 'TargetDay'. This ensures each iteration uses the updated date.After the HTTP GET action, utilize the Compose action to subtract one day from the 'TargetDay'. Something like this - 

'addDays(utcNow(), -1, 'yyyy-MM-dd')'

Then, use a "Set Variable" action to update the 'TargetDay' with the result from the Compose action. Verify your "do until" loop's exit condition. For 7 days worth of transactions, you might want to maintain a counter that increments each loop iteration and have the loop exit after the counter reaches 7.

Please give kudos and mark as solution if it helps.  

Thanks, 

Sandeep Mishra 

[leemager]

Very difficult to tell, but from what I can see from your debug messages, the second 'so now the target date...' message isn't hit despite the loops continuing. The condition action directly precedes it, so that would be where I would initially look. Have you tried going through that run itself to see which actions ran / didn't run on the first vs the second iteration?

[therufus]

Hi Sandeep.

For further context:

1. The variable "TodaysDate" is set before the loop as 

formatDateTime(convertFromUtc(utcNow(), 'AUS Eastern Standard Time' ),'yyyy-MM-dd')

2. The variable "TargetDay" is set before the loop as 

formatDateTime(addDays(variables('TodaysDate'),-1), 'yyyy-MM-dd'). The first day I'll have in the HTTP GET is for yesterdays settlement date.

3. The variable "EndDay" is set before the loop as

formatDateTime(addDays(variables('TodaysDate'), -7), 'yyyy-MM-dd'). This is the last day I will need so the loop should end here.

All dates are in yyyy-mm-dd format so the comparison within the loop should be correct.

Inside the loop, the API call is made and is successful as I can see the temp table filling up in the dataverse with all of todays payments (and yesterdays during the second loop iteration). The Compose is the input 

addDays(variables('TargetDay'),-1) which gives a full yyyy-mm-dd hh:mm:ss:000z format. I then increment the "TargetDay" variable using 

formatDateTime(outputs('Compose'), 'yyyy-MM-dd') to get it back to the correct format.

I've tried doing this all with a standard increment counter which counts from 0 (set outside the loop) to 6, but with the same results.

[sandeepstw]

Hi, 

The problem you have described suggests that the "do until" loop isn't recognising the date change properly after the second iteration.

As you have tried using a standard increment counter with the same results, consider using a "Scope" action to group your HTTP GET request, Compose, and "Set Variable" actions. This can sometimes help streamline the execution flow within Power Automate.

[therufus]

I just tried using a scope, but I get an error saying the maximum nesting limit is 8 and I have 9 levels of nesting.

[therufus]

You were right leemager!

Even though the flow didn't actually fail, turns out there was a "," in one of the fields of the CSV I was downloading through the HTTP GET request. Since the comma was interpreted as a "next column" designator, the whole thing stopped working.

The lesson learned here, if an action fails, it doesn't always fail the flow run. Sometimes it just performs in illogical ways.

[leemager]

ah, commas in CSV text fields, perhaps the leading global cause of headaches after datetime conversions 😆