Hi @Prem4253 ,
Great! I'm sharing with you a quick idea about how to make it work. Let me know if you need any additional clarification in any step.
List overview
As I mentioned before, the idea here is to create a SharePoint List to track who last hosted the meeting. In my case, I decided to record the last date that each employee hosted a meeting, but you can do it in some different ways:
The idea here is to return the employee that has the oldest 'Las hosted at' date and assign this task to them. Inside the flow, we will also update the 'Last hosted at' date for the assigned employee, setting it for today's date.
Step 1 - Recurrent trigger
Create a flow with a recurrent trigger. In the flow edit page, set the interval to 1 week (green), set your time zone (yellow), select the days of week when you want the flow to run (blue) and choose the best hour/minute of its days to the flow run (pink):
Step 2 - Pull oldest 'Last hosted at' from SharePoint
Use the 'Get items' action to return the records from your SharePoint List. After populate the action with the list details, click in 'Show advanced options' and set the 'Oder By' as '[yourColumnName] asc' (highlighted in yellow) and the 'Top Count' to 1 (highlighted in green):
Make sure to put the correct logical name of your column in the 'Oder By'. You can identify the logical name of the column by running the 'Get items' action without the 'Oder By' and accessing its raw outputs (if you don't know how to access the raw outputs, check this article: http://digitalmill.net/2023/06/29/how-to-access-the-raw-outputs-in-power-automate/)
In this step, you are asking Power Automate to order the List Records by 'Last hosted at' column in ascending order, and to return only the first record after it (it means, the oldest one).
Step 3 - Sending notifications
Now we will send the notifications. Add an 'Apply to each' loop and pass the 'value' property from 'Get items' as input. Even that you have only one record, it is still stored into an array, so you need to access this element by using the 'Apply to each' (which is easier) or by a direct reference to the first element:
Inside the 'Apply to each' block, insert a 'Post message in a chat or channel', from Teams connector. Set the configuration as the following:
- Post as: Flow bot (highlighted in green)
- Post in: Chat with Flow bot (highlighted in yellow)
- Recipient: dynamic content of the 'email' column from your 'Get items' action
- Message: your message content
You can follow the same logic for the email, but using the Outlook connector and the 'Send email' action (I'm sorry for not sharing an image from this specific action, but my Microsoft Developer Account does not have access to Outlook connector).
Step 4 - Updating the 'Last hosted at' in SharePoint List
After notify the employee, we need to update the SharePoint List, to make sure that this user won't be notified again tomorrow. So, still inside the 'Apply to each' block, add an 'Update item' action and populate it with your List details. After populating it, some new inputs will be displayed, matching to each List column. Make sure to fill the 'ID' input with the dynamic content from 'ID' property from the 'Get items' action (highlighted in yellow) and any other mandatory field. Also, fill the 'Last hosted at' input with an expression that formats today's date to the same date format that you have in the SharePoint List (highlighted in green), which in my case is month/day/year:
Expression: formatDateTime(utcNow(), 'MM/dd/yyyy')
Output
After the flow run, the user will receive the following message in Teams:
And the record from 'John S', which had the oldest 'Las hosted at' date (Aug 8th), will have this column updated with today's date (Aug 22nd):
When the flow run tomorrow, it will send the notifications to 'Harry P', and update its 'Last hosted at' for Aug 23rd.
Let me know if it works for you or if you need any additional help!
-------------------------------------------------------------------------
If this is the answer for your question, please mark the post as Solved.
If this answer helps you in any way, please give it a like.
http://digitalmill.net/
