cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

Sum values in a Sharepoint list column between two dates and send an email

Hi,

 

I have a stock out list to monitor the use of cleaning chemicals:

 

stockoutlist.png

 

What I want to do is send an email at the end of the month which sums the quantity issued of each item for the last month and puts it into an html table with Title in one column and TOTALQtyIssued in the second with the table covering one calendar month.

 

So far I have used Get items with a filter query to get the data for the last month:

 

stockoutlist2.png

 

But, I have no idea about the next step. How do I get Flow to sum the values I need?

 

Any help would be much appreciated, thanks.

13 REPLIES 13
Highlighted
Dual Super User III
Dual Super User III

Re: Sum values in a Sharepoint list column between two dates and send an email

Hi!

"What I want to do is send an email at the end of the month which sums the quantity issued of each item"

I guess each item is represented by a unique 'ChemID', right? Is this SP column of type number? And also the nr of items is high, right?

 

If so, my suggestion is to add a 'Select action' block, assign as its input 'Get items' output. Now flip to input text mode (click on the icon on the right), select from Dynamic content menu 'ChemID' and assign it as its value. I've just uploaded a video to see this step more clearly:

https://youtu.be/mju7v631cgY

 

Next, add an 'Initialize variable' action block, let's call it Total, type integer, value 0.

Next, add an 'Initialize variable' action block, let's call it myTotalsArray, type Array, value empty.

Next, add an 'Apply to each', assign as its input 'Select' output. Inside the apply to each:

-add a 'Filter array' action block, assign as its input 'Get items' output, define its condition rule to verify if

 

items('Apply_to_each')

equals

item()?['ChemID']

 

-Still inside the 'Apply to each', add a second 'Apply to each', assign as its input 'Filter array's output. Inside this nested 'Apply to each' :

-Add an 'increment variable' action block, name 'Total', assign as its value the following WDL expression:

 

item()?['QtyIssued']

 

-Now inside first 'Apply to each' but out of the second 'Apply to each' add an 'Append to array variable', name, myTotalsArray, assign as its value the following:

 

{
"Title": "@{item()?['Title']}",
"QtyIssued": "@{variables('Total')"}
}

 

-Still inside first 'Apply to each' add a 'Set variable', name Total, value 0.

-Finally, out of the 'Apply to each', add a 'create HTML table' assign as its input variable 'myTotalsArray; then add a'Send an Email', assign inside its body 'Create HTML table' outputs

 

I am afraid a nested 'Apply to each' with variables will perform really really slow, but I cannot think on a better approach

Hope this helps



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted
Helper III
Helper III

Re: Sum values in a Sharepoint list column between two dates and send an email

Hi @efialttes ,

Thank you. I will work through your answer.

Izzy.

Highlighted
Helper III
Helper III

Re: Sum values in a Sharepoint list column between two dates and send an email

Hi @efialttes ,

I am getting the following error on the 'increment variable' action?

 

stockoutlist3.png

Highlighted
Dual Super User III
Dual Super User III

Re: Sum values in a Sharepoint list column between two dates and send an email

Hi!

Please follow this steps to add your WDL expression

https://youtu.be/LPZ-WZoWwp4

 

Thanx!



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted
Dual Super User III
Dual Super User III

Re: Sum values in a Sharepoint list column between two dates and send an email

Hi again!

Please also note the 'Append to array' value shall be appended as regular text

Flow_ObjectInsideAppend2Array.png

Hope this helps



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted
Helper III
Helper III

Re: Sum values in a Sharepoint list column between two dates and send an email

Hi @efialttes ,

OK, but where do I put the Compose action box?

Sorry, this is all new to me and I'm not beyond the very basics.

Thanks.

Highlighted
Helper III
Helper III

Re: Sum values in a Sharepoint list column between two dates and send an email

Hi @efialttes ,

I am still confused as to how the Compose action helps me with the Increment Variable action that is asking me for a valid integer?

Thanks.

Highlighted
Dual Super User III
Dual Super User III

Re: Sum values in a Sharepoint list column between two dates and send an email

@IzzyWizz 

The video is just an example, they way to add a WDL expression t oan action block is the same no matter you deal with a 'Compose' action block, an 'Apply to each'...

No need for a 'Compose' action block i the steps I suggested

Hope this makes sense



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted
Helper III
Helper III

Re: Sum values in a Sharepoint list column between two dates and send an email

Hi @efialttes,

Yes, I see what you mean now, thank you.

I have completed writing the flow but it comes up with the following error:

stockoutlist4.png

The part of the flow here is:

stockoutlist4.png

Thanks.

Izzy.

 

Highlighted
Helper III
Helper III

Re: Sum values in a Sharepoint list column between two dates and send an email

Hi,

Is anyone able to help me with the following error please? I have followed the instructions above. To summarise, I have a stock out list to monitor the use of cleaning chemicals:

 

stockoutlist.png

What I want to do is send an email at the end of the month which sums the quantity issued of each item for the last month and puts it into an html table with Title in one column and TOTALQtyIssued in the second with the table covering one calendar month.

I have made the following flow following the instructions above:

stockoutlist2.png

 

But I am getting this error on the increment variable action:

 

stockoutlist3.png

 

Can anyone help please? Where am I going wrong?

Thanks,

Izzy.

Highlighted
Dual Super User III
Dual Super User III

Re: Sum values in a Sharepoint list column between two dates and send an email

Hi!

On your 'Initialize variable' action block, name Total, change the type from Integer to Float, and try again

Hope this helps



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted
Helper III
Helper III

Re: Sum values in a Sharepoint list column between two dates and send an email

Hi @efialttes ,

No, it doesn't like that either:

 

stockoutlist3.png

 

Thanks.

Highlighted
Helper III
Helper III

Re: Sum values in a Sharepoint list column between two dates and send an email

Hi,

Anyone able to help with this? Is there a different method I could try?

Thanks,

Izzy.

Helpful resources

Announcements
secondImage

August 2020 CYST Winners!

Check out the winners of the recent 'Can You Solve These?' community challenge!

thirdImage

Experience what's new for Power Automate

Join us for an in-depth look at the new Power Automate features and capabilities at the free Microsoft Business Applications Launch Event.

firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

Top Solution Authors
Top Kudoed Authors
Users online (4,209)