Microsoft Flows has been a great contribution by Microsoft and has been really helping and assisting individuals to automate the process with no code.
Recently, while working over one of the scenarios, I was stuck with a business requirement and it is really great to share that specific business scenario.
Business Scenario
The business has a GST Compliance scenario, in which a business needs to submit a GST report on the GST portal every month by the 10th and for the same, they need to send a reminder to a list of vendors over email asking to submit the GST reports. Post that reminder, there are multiple internal processes which need to be done on specific time gap post-delivery of the reminder.
Let's say the business sends a reminder on the 22nd of the month, and considering their planning, they need to send another event after 5 days for that specific month, let's say on 27th of that month. So, they need a way to keep the flow in a waiting condition until a specific date and once that date is reached, the second step will take place
Approach
Based upon the inputs from business, we created an Excel sheet with all the dates for the entire year considering the weekend holidays and public holidays and along with a variable in another worksheet.
The Excel file which has been created by us is as shown in the below image.
- Created an Excel file Online – saved the same on OneDrive.
- Excel has two columns.
- Created a flow which will extract these dates from Excel Online.
Step to read the data from OneDrive
Incorporated the steps to read the data from OneDrive, which includes creating a variable and then using the component “Get a row”, to read the variable value from business Excel Online from OneDrive.
Condition to check whether the extracted date is equal to “Today”
Since the flow has been scheduled to be executed every day, based upon the variable, it will extract a row from business Excel, which will have multiple columns, and we need to check if the date extracted from Excel is matching today's date or not, to ensure that the reminder to the vendor needs to be triggered or not.
Formula for the same -
- formatDateTime(addDays('1900-01-01T00:00:00Z',sub(int(body('Get_a_row')?['Date']),2)),'dd/MM/yyyy')
Need to wait until another specific date
Now, post successful delivery of the email reminder to the vendor, we need to wait considering the public holidays and weekends, to ensure for a specific date and then, we need to proceed further for undertaking the subsequent actions in the system.
Formula for the same -
- concat(formatDateTime(addDays('1900-01-01T00:00:00Z',sub(int(body('Get_a_row')?['Date 2']),2)),'yyyy-MM-dd'),'T','14:45','Z')
End Result
The flow is scheduled to execute every day in the morning, and now if the date of execution is equal to today, it performs some specific operation including sending email to vendor creation of tasks, events, etc.
The vendor gets a reminder email generated from flows, and then waits for subsequent actions until the other date is achieved in flow!!