Okay, I've done a bit here, that I hope will be helpful. This is all from the 2nd flow I called out in my previous post - the one that keeps track of approvals and who to notify next. (The other workflow, the 1st flow, is the one that adds lines to the "Log" I reference here.)
⭐Step 1 - I created an Excel sheet to mimic yours. I set it up with a proper Table named "Approvals", and I stored it in OneDrive.
⭐ Step 2 - I created a SharePoint list to act as our tracking log. I need a unique key between Excel & SP to track them, so I am using P.No+Indices. "Not Started" means that the Dept's column in Excel is currently blank, and, OverallStatus tells us which Department the approval task is currently with - or if the entire item is complete.
⭐ Step 3 - In a scheduled flow, the first thing I do is gather all items from our Approvals log in SharePoint. I use a view to only show me ones that are incomplete. From here, I can add the "Get a row" action for Excel. As soon as I set the Key Column to our PNoIndices, and reference the PNoIndice value from the SP list, it automatically turns into an "Apply to each" for us - which means it will iterate through all rows one at a time.
⭐ Step 4 - The first thing I will do is set some variables to ensure I have clean data so I don't mess up my current log. The current status of each department, and the current department.
⭐ Step 5 - Next, I create a Switch to operate on our "txtCurrentDept" variable. If the current Department we're waiting on is Dept1, it will go through the Dept1 branch, and no others.
⭐ Step 6 - I use the switch states to set variables only, which populate the more complex actions on the outside of the switch. It means less to update later on if something changes. Here you can see we are just using 2 conditions, and setting two main variables: what is the outcome, and, which department should go next. It would be the same for all 7 departments.
⭐ Step 7 - and, finally, when we leave the switch, we update our log and send our email. I did not include the email in mine of course, but, there is an "if" statement in each Department value so it will only populate if applicable. The formula looks like this: if(equals(variables('txtCurrentDept'),'Dept2'),variables('txtCurrentDeptResponse'),variables('txtDept2Value')) - which means, "if the current status for this row is set to Department 2 (we're waiting on Department 2 to give an outcome), then get their current response, and set it to that. If it ISN'T the current department, then we'll use the same value we started at.)
(Ignore "Row" in the last screenshot - it's just "Title" and I would've used the same value as previous, it doesn't change.)
BONUS: While after this action you would create your email to the next department, you could also make a condition such as "if the response is Rejected, use this email template when responding back to the same department - but if it's Approved and going to this other department, use the main template."
==========
So, that is the entirety of "Flow #2" .. or, I have it labeled as Excel Approval Tracking.
This method also means you can easily add reminders, if that's something you need.
I hope this was helpful.
Rhia
