I am trying to build a Flow that will be triggered when an employee's total travel in a SharePoint Online list meets/exceeds a specific threshold. The only way I have found to calculate an employee's total travel when the employee has more than 1 list item is to use the GroupBy feature in the list view. I would like a total (aggregated or running) of all travel days for each employee, then trigger a Flow when a threshold is met, i.e. 25 total travel days reported. See example below.
In this example, the Flow would not be triggered for Sam. The Flow would trigger for John once his May 15th trip is reported since his Total Travel Days exceeds 25.
Employee Arrival Date Departure Date Travel Days Aggregated Total
Sam 01/01/2018 01/05/2018 5 16
Sam 02/01/2018 02/10/2018 10 16
Sam 04/10/2018 04/10/2018 1 16
John 01/01/2018 01/20/2018 20 30
John 05/15/2018 05/24/2018 10 30
Solved! Go to Solution.
Hi @RPowers,
Please make sure the Travel Days and the Aggregated Total are number type column, and you could refer to screenshot below to create the flow:
The expression in the Value column of "Increment variable" as below:
int(items('Apply_to_each')['Travel_x0020_Days'])
When John's May 15th trip is reported, the flow would run successfully as below:
Best regards,
Alice
Hi @RPowers,
Please make sure the Travel Days and the Aggregated Total are number type column, and you could refer to screenshot below to create the flow:
The expression in the Value column of "Increment variable" as below:
int(items('Apply_to_each')['Travel_x0020_Days'])
When John's May 15th trip is reported, the flow would run successfully as below:
Best regards,
Alice
@v-yuazh-msft - Thank you for the feedback and sample FLOW. I have built the FLOW was outlined but it fails at the "Get Items" action each time I conduct test runs. I have tested a few different Filter Queries (i.e. Employee Name, Employee Number, and Email Address - all of which are columns in my referenced SharePoint list) but each of them have failed.
Error: Action 'Get Items' failed
Error Details: TThe expression "Email Address eq 'renee.powers@kbslp.com'" is not valid.
clientRequestId: 3a2f4e84-f74c-4738-bdad-4edfa4a7c4ee
serviceRequestId: d07bb99e-706e-8000-17e4-683bb36895e8
User | Count |
---|---|
38 | |
35 | |
15 | |
13 | |
12 |
User | Count |
---|---|
23 | |
20 | |
18 | |
13 | |
13 |