Hi @Apilotti ,
I answered a similar post some days ago, so I'm taking the same response and making some small changes to adapt it to your use case 🙂
First of all, I would recommend you to have a data source to store data about when was the last time that the employee had the task assigned to them (it can be also useful for the future, in the case of you include/remove employees in this process). As data source, you can use an Excel table or a SharePoint List. in the following example, we are using a SP List.
Also, I don't know how exactly do you intend to assign these tasks (you can create a task in planner, send an email, send a teams message, create a card in Trello....), so feel free to provide us with some additional details on your process.
Finally, it is probable that some of these steps don't match perfectly to your use case, so do not hesitate in asking for help if you need any assistance in adjusting them.
Example overview
For this solution, I will work with the following List, that stores data about employees (name, email, and date when they performed the task for the last time) and try to extract the email from the user with the oldest "LastSent" date, which in this case is "Frank S":
Your flow must be very straight forward: you just need a "Get items" action (which will return all records from a SharePoint List), and then you can use a "Send email", "Create task", or any other action that you intend to integrate in the flow to assign the task to the employee.
Finally, we update the List record to ensure that the "LastSent" date is equal to today. From my end, instead of a "Send email" or "Create task", I'm using a "Compose" action, just to display the output of the expression used to extract Frank's email:
Further reading
Before we start the step-by-step, I will share some additional content related to the topics discussed in this answer, as it can be helpful for you or for future readers that face the same issues.
Step 1 - Getting List records sorted by date
Let's start!. In Power Automate, modify your "Get items" action to include a Order By query that will sort all of the records, in ascending order, by your date column. To achieve it, the query must be [YourColumnName] asc, just like I did below (highlighted in yellow):
As mentioned before, this small change will return all list records sorted in ascending order by date, so we need to capture only the email address from the first record, as it has the oldest date.
Step 2 - Get email from oldest record
To get the email of the first record returned by SharePoint, you will need to use the following expression:
outputs('Get_items')?['body/value'][0]['Email']. This is the meaning of each part of the expression:
- outputs('Get_items')?['body/value']: Accesses the property "value" inside the "body" of the "Get items" action. In practice, the "value" property stores a list of the records returned by SharePoint. As this is a list of records, its data type is an array.
- [0]: This is a index reference for array elements. As mentioned above, the "value" property is an array, and we can access each of these individual records by passing its index between square brackets. The index counting starts at zero, so when we use "[0]" in a expression, we are accessing the first item of an array (when we use "[1]" we access the 2nd, and so on).
- ['Email']: After the index reference, we can now access any property from the individual list record. In our case, we need to access the email property, so we are passing its name between square brackets and single quotes. From your end, make sure to replace 'Email' for the logical name of your List's email column. Sometimes these columns names are tricky, so let me know if you find any trouble in this step.
I'm adding this expression to a "Compose" action, but you can do it directly into the "Send email"/"Create task":
Step 3 - Update records LastSent date
After assigning the task to the employee, you need to update the list again to ensure that your record won't be the oldest one anymore. You can do it by adding a "Update item" action, and passing the following expression into "id" input:
outputs('Get_items')?['body/value'][0]['ID']. This expression follows the same logic of the Email one, but at this time we are accessing the "id" of the record.
For the other action inputs, you just need to populate your date input (in my case, called "LastSent"), and you will use a utcNow() expression for that:
The utcNow() expression gets the current date/time, so you can ensure that the record will be updated with the exact date of the flow run.
Testing the flow
After running the flow, as expected, our expression returns Frank's email, as he is the employee with the oldest date in "LastSent" column:
And our "Update item" action modifies the SharePoint List, ensuring that Frank's "LastSent" date is set as today:
When I run the flow again, the expression will return Lisa's email, as she now has the oldest "LastSent" date:
And, as expected, her "LastSent" will be updated for today:
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/
https://www.linkedin.com/in/raphael-haus-zaneti/
