Minute Read
What was the business problem?
The client had an excel with all reminders that she/he had to check , basically the spreadsheet had all the flat files names( business files) and related info like when they are supposed to uploaded to AWS S3 bucket, who uploads it, for which country's data and so on , if the file does not arrive at the right time client had to email responsible people reminding them to do so. If the file did arrive, an automated process (python/glue jobs) would kick off loading the data to destination data warehouse for further downstream processing. So this manual checks was something client had to do every month. This was something we wanted to fix and automate it.
What did we propose ?
We proposed Sharepoint lists rather than maintaining a excel spreadsheet and we used
power automate to send reminder emails. This eliminated client intervention .
The below shows the excel structure imported into sharepoint list.
How did we implement it?
In sharepoint list, you have the days in "Expected date" column but you then created a calculated column "Actual Date" "=DATE(YEAR(TODAY()),MONTH(TODAY()),1)+[Expected Date]-1" which adds days "Expected date" to the last day of last month. Now all you need to do is ensure your flow logic detects those rows which is 2 days ahead of current day. However the problem is you cannot use calculated columns in your filter queries, so you will need to use filter arrays to get around this. That's it, once you have those rows, you add it in a html table, style it and roll it to the end users via email.
Output
The email output sent to reps would be in this form:-
Links
Inspiration from Richard Burdes youtube channel-> https://www.youtube.com/watch?v=ytyLBhwGadQ&t=185s
Filter Array logic ->
Stlying HTML Table->
Love to hear your comments...Happy blogging!!