when a row/rows is modified in excel mail to be triggered to certain person and apply condition for further steps

[Akshay3]

Hello Community,

Am looking for flow for my personal work, background Excel file -> mail

Step1: i will input first 3 columns in the excel

step2: newly added items (1 or more) should be trigger through mail to dept1

step3: dept1 will change status to yes -> mail trigger to dept2

same steps till dept7 and last i should receive mail from dept7 with status and comments

Flow can run daily twice and mail should include mutiple rows

Condition to be used for all depts

i tried out and flooded with lot of mails and needed to delete the comple flow to stop the trigger

anyone can help me, quick response will be highly appreciated.

Thank you.

[Rhiassuring]

Hi there, 

The main issue I see here - and why it's going to be way more complicated than it needs to be if you keep it going this way - is that they're going to be updating while you're updating, which means you also won't have real time approval progression. They'd have to wait twice daily for the information to move on. But, for the sake of this post, I'm going to pretend that is the case and there is no way around it 🙂  These things happen. 

I think this needs to be split up into two flows, though. Here's my initial thoughts...

Flow # 1 - "We have new stuff!"

⭐Scheduled flow, twice daily.

⭐Get all rows from the table in Excel.

⭐Evaluate how many it was last time you checked (you could keep this number in a list somewhere)

⭐Evaluate the total # of rows minus what it was last time (that gets you how many rows need to go out).

⭐Iterate through those rows to build a table to email out to your first department only. 

⭐Write to a log (a list somewhere, maybe named TrackingLog) the row ID, an overall status, and each departments status. (No for 2 - 7, In Progress for 1). Add a view for "In Progress", and filter it to all items that don't have an overall status of "Complete".

Flow # 2 - "Please review the stuff!"

⭐ This could be one of two triggers:  1) "When a file is modified..." if time is of the essence on approvals and it's not annoying, or 2) Scheduled, every 30m or something. 
⭐Get all items from the TrackingLog from the view In Progress. Iterate through each one, look at the row in Excel, identify if the Department is equal to "Yes" yet, and if it is, update your log, set the next Department to "In Progress", and send them the email.

There are some opportunities in here for streamlining, as well, aside from using email approvals. For example, you can make "digests" for users to give them less emails - for example, if you are only checking once a day, or twice a day - and this is a very active list - you may want to say, "check for items where it was previously Department 1, and it's now time for Department 2, and send them the requests for any associated data at the same time." 

Anywho, those are my thoughts.

Cheers,

Rhia

[Akshay3]

Thanks Rhia for your reply, actually i was looking forward for a image where flow is constructed.

That would be more useful.

the main concept is whenever a dept. changes status it should move to next irrespective when it was added means row 1 is pending while next 5 are completed and newly 5 items are added, i wanted row 1 along with newly added rows in the mail. 

Hope this gives more clarity.

[Rhiassuring]

Hi there, 

The points above achieve what you are looking for. Which part would you like to see built out? It would take quite some time to build out the entire flow but I am happy to assist with a specific piece or two that you might have?

Cheers,

Rhia

[Akshay3]

maybe for 2-3 dept you can build the flow, i will try to replicate to all others

flow:

1. first 3 columns will be inputted in excel and send to dept1

2. if dept1 approves to yes should move to dept 2 orelse need to retrigger to dept1

[Rhiassuring · Accepted solution]

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

[Akshay3]

Thank you for the quick response, let me try out the same for 2-3 depts then replicate.

looks bit complicated but can't do anything will try upon the same.

[Rhiassuring]

Good luck - let me know if you get stuck anywhere.