cancel
Showing results for
Did you mean:
New Member

## Count of occurrence and sum in a sharepoint list

Hi all,

Very rookie question here as I'm not even sure it's possible to build this flow.

I have a sharepoint list where the inputs are the leaves of the employees.

The sherepoint list is fed through power app.

Once a new item is created or modified, i want two operations to happen on this item:

- Count the number of time one person have appeared in the list for the last 365 days from the date of return (Occurrences last 12 months)

- Sums up the durations fot the last 12 months from the date of return (Sum durations last 12 months)

Here is a table representing the datas, last two colomns are the one i try to calculate through a power automate workflow:

 ID Name Date return Duration Occurrences last 12 months Sum durations last 12 months 1 James 12/12/2022 4 1 4 2 Michael 01/01/2023 5 1 5 3 James 09/01/2023 3 2 7 4 James 04/02/2023 5 3 12 5 James 03/02/2024 3 2 8 6 James 09/01/2025 2 2 5

Not sure if helps, but here are the Excel formulas corresponding to the operation:

-Occurrences last 12 months = =COUNTIFS([Name];[@Name];[Date return];">="&([@[Date return]]-365);[Date return];"<="&[@[Date return]])

-Sum durations last 12 months =SUMIFS([Duration];[Name];[@Name];[Date return];">="&([@[Date return]]-365);[Date return];"<="&[@[Date return]])

Thanks a lot for any feedback 🙂

Thomas.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

That was fun 🙂. Hopefully this is what you're looking for.

For this example, I have the following list.

Below is the full flow. I'll go into each of the actions.

When an item is created or modified will is fairly straightforward.

Because our trigger fires when an item is modified, and we are modifying our items within the flow, we would end up with an infinite loop (our flow would continue to run over and over and over again). To avoid this, we can check the previous version of the item that was modified and check if the Date Return, or Duration has changed, and only continue if one of them have. And if it was a new item just created, it wouldn't have a previous version, so we need to cater for that too.

Get changes for an item or a file (properties only) will retrieve the previous version of the item and provide us with dynamic properties to check if fields have changed.

Condition checks to see if it's a new item that was created (Version Number is 1.0), or if any of the fields (Date Return or Duration) have changed. This will ensure the flow doesn't get into an infinite loop. It uses the following expression to get the version number.

``float(triggerOutputs()?['body/{VersionNumber}'])``

If any of the conditions return true, we go into the Yes branch and continue on with our flow.

Get items Current and Future will return all items where the Title is equal to the Title of the item we created/modified and has an ID greater than or equal to the ID of the item we created/modified. This will cater for changes made to previous entries where we need to recalculate all entries after that one. We also sort by ID, so we update the earliest items first since the later items use the data from the previous items in their calculations.

Apply to each iterates over each of the items from Get items Current and Future.

For each of the items, we retrieve the data 12 months from the Date Return up until the Date Return. The expression used is:

``Title eq '@{triggerOutputs()?['body/Title']}' and DateReturn ge '@{addToTime(item()?['DateReturn'], -12, 'Month')}' and DateReturn le '@{items('Apply_to_each')?['DateReturn']}'``

Update item uses the ID and Title from Get items Current and Future and uses the following expressions to calculate the Occurrences and Sum. Note that Sum is using XPath so we can sum across the rows easily.

``````//Occurrences
length(outputs('Get_items_Current_and_Past')?['body/value'])

//Sum
xpath(xml(json(concat('{"root": { value:', outputs('Get_items_Current_and_Past')?['body/value'], '}}'))), 'sum(//root/value/Duration/text())')``````

This should cater for new items and any items that have the Date Return or Duration updated anywhere in the list.

----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.

----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.
7 REPLIES 7
Super User

Sounds challenging, but definitely doable. I'll see what I can come up with 🙂

A few questions.

1. Is your Name column data type a Single line of text or Person?
2. Approx. how many items are in your list - and how many would there be within the next 5 years?
3. What happens if you modify an earlier item for a person? Would you want to recalculate all occurrences of that person from that point forward since the later calculations may now be incorrect? Or would you only ever modify the latest item for a person?
• Example - I add some leave and it performs the calculations. Then I edit a previous entry for that person - I'd need to recalculate for the item I just modified, plus the latest item since that is calculated on previous items.

----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.
Super User

That was fun 🙂. Hopefully this is what you're looking for.

For this example, I have the following list.

Below is the full flow. I'll go into each of the actions.

When an item is created or modified will is fairly straightforward.

Because our trigger fires when an item is modified, and we are modifying our items within the flow, we would end up with an infinite loop (our flow would continue to run over and over and over again). To avoid this, we can check the previous version of the item that was modified and check if the Date Return, or Duration has changed, and only continue if one of them have. And if it was a new item just created, it wouldn't have a previous version, so we need to cater for that too.

Get changes for an item or a file (properties only) will retrieve the previous version of the item and provide us with dynamic properties to check if fields have changed.

Condition checks to see if it's a new item that was created (Version Number is 1.0), or if any of the fields (Date Return or Duration) have changed. This will ensure the flow doesn't get into an infinite loop. It uses the following expression to get the version number.

``float(triggerOutputs()?['body/{VersionNumber}'])``

If any of the conditions return true, we go into the Yes branch and continue on with our flow.

Get items Current and Future will return all items where the Title is equal to the Title of the item we created/modified and has an ID greater than or equal to the ID of the item we created/modified. This will cater for changes made to previous entries where we need to recalculate all entries after that one. We also sort by ID, so we update the earliest items first since the later items use the data from the previous items in their calculations.

Apply to each iterates over each of the items from Get items Current and Future.

For each of the items, we retrieve the data 12 months from the Date Return up until the Date Return. The expression used is:

``Title eq '@{triggerOutputs()?['body/Title']}' and DateReturn ge '@{addToTime(item()?['DateReturn'], -12, 'Month')}' and DateReturn le '@{items('Apply_to_each')?['DateReturn']}'``

Update item uses the ID and Title from Get items Current and Future and uses the following expressions to calculate the Occurrences and Sum. Note that Sum is using XPath so we can sum across the rows easily.

``````//Occurrences
length(outputs('Get_items_Current_and_Past')?['body/value'])

//Sum
xpath(xml(json(concat('{"root": { value:', outputs('Get_items_Current_and_Past')?['body/value'], '}}'))), 'sum(//root/value/Duration/text())')``````

This should cater for new items and any items that have the Date Return or Duration updated anywhere in the list.

----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.

----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.
New Member

What a legend, never thought i would get such a comprehensive and detailed answer !

I will give it a try as soon as possible and will let you know 🙂

1. Column "Name" is a person column but i figured out earlier how to transform it in text.
2. There will be around 750 items per year, so around 4000 items in 5 years. I will probably create an auto-archive every year to keep the list light
3. In a perfect world, the other lines would update when one line is modified. I see you have integrated this option in your flow, if it doesn't make the flow too heavy that's an incredible feature, else I can maybe try to allow only the modification of the lastest dates of the list (not sure if this option exists in power app 😁).

Not sure how to thank you but this is an incredible help !

Have a good weekend,

Thomas.

Super User

I'll try it with a Person column instead of a Single line of text column and see how it goes - I'm fairly sure it won't affect the flow as it is. If you wanted to use a Person column, what would you put in the Title field since it's a required field?

Given the number of items in the list, I'd also suggest modifying the Get items Current and Future and Get items Current and Past so they're using Pagination and have a Threshold set which will allow for more items to be returned. Threshold would be set to a number greater than the number of items you expect your list to have.

And setting the Top Count to 5000 will return your items in batches of 5000 at a time.

You would do this for both of the Get items actions.

----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.
New Member

Thanks again for all your details.

I did apply everything above and the occurrence is working just fine.

However the sum formula always return 0.

I'm not sure if it comes from that but whenever I add the update item action and choose ID from "Get items current and past", it automatically creates an "apply to each" action:

I tried many different configurations (like using the datas from "get items current and future") but the issue is still there.

When i do a test run, the previsous steps seem to retrieve the rigth data, is it possible that it comes from the formula ?

And to anwser your question about the title field, I'm not really sure how to efficently use it so i just increment it for every new line created in the sharepoint list.

Thanks again for everything,

Thomas.

Super User

@ThomasdotM Sorry, I made a mistake when describing what you use for the ID and Title in Update item. The ID and Title should be coming from Get items Current and Future which wouldn't add another loop. I've updated my original post to reflect the changes.

Let me know if this change resolves the issue.

----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.
New Member

This definitely fixed the additionnal loop 🙂

However I'm still unable to calculate the sum of durations.

It's very mysterious for me because the step "get item current and past" do recover the good shortilisted items, including "Dur_x00e9_eabsence":5.0 (and the followng ones) which is my field name equivalent to duration in your case

The sum of duration formula then becomes

``xpath(xml(json(concat('{"root": { value:', outputs('Get_items_Current_and_Past')?['body/value'], '}}'))), 'sum(//root/value/Dur_x00e9_eabsence/text())')``

But it always return 0 in the sum of duration field.

I'm sure I'm doing something wrong somewhere but I tried so many alternatives and setting I'm a bit lost on that.

And there is also another "side effect", whenever an early item is modified and trigger the flow, it will erases the results of the occurrences of the later lines.

So to be honest it's a bit out of my range and I will probably remove these calculations so the table stays simple and future maintenance of the system can be performed.

There is still the possibility to extract the datas to an excel spreadsheet and do a simple caculation.

Anyway, thanks a lot for your time, learnt a lot about the different features and the limits of the sharepoint lists through the differen tests.

Will probably get back to it in the future when I have time to play with the solution 🙂

Announcements