SUM Arrays In Microsoft Flow

Подписаться 65 тыс.

Просмотров 16 тыс.

50% 1

Learn how to sum up the values in an array in Microsoft Flow.
In this video we look at how to get values from a SharePoint list and sum the values in one of the columns to get a total.
Watch the video about creating a dynamic invoice PDF with Microsoft Flow: • Create a Dynamic Invoi... 👇
Check out my full courses and ebooks here
👉 www.howtoexcel...
DOWNLOAD the example workbook here
📖 www.howtoexcel...
SUBSCRIBE & get my 3 FREE eBooks
📧 www.howtoexcel...
CONNECT with me on social
Facebook: / howtoexcelblog
Twitter: / howtoexcelblog
LinkedIn: / john-macdougall
Thanks for all your support!

Опубликовано:

15 сен 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 42

@higiniofuentes2551 Год назад

Thank you for this very useful video!

@luda_c 4 года назад

Your video tutorials are excellent. Thank you very much for your time and willingness to share what you know. Much appreciated.

@alejandraserrano7194 2 года назад

You Rock! Thanks for sharing

@LeUliUli 4 года назад

Good one. Thanks for sharing John!

@HowToExcelBlog 4 года назад

You're welcome!

@borisyanovsky3345 5 лет назад

Nice tutorial, thank you.

@HowToExcelBlog 5 лет назад

Thanks Boris!

@Kailadan 4 года назад

Thank you very much for this tutorial - I was getting so frustrated! I have a form that enters a number of people attending a program. I wanted to send emails (confirmation, waitlist and an email to myself when it was getting full) based on the total number of attendees. This worked beautifully once I added "int" (in front of items) to the add(items...) step since Microsoft Forms enters number results as text even when you require a number input. So my Set Variable step for the TempTotal looked like: add(int(items('Apply_to_each')['Number of people attending (please include all adults and children)_x003a_']),variables('Total')). Thank you again!

@HowToExcelBlog 4 года назад

Yes, I've definitely had that experience where something that looks like a number was actually text 😂

@YourOlya 2 года назад

Can you help me? I do all that you say in the video, but my filter outputs only the first item, but I have 10 items with the same title.

@dustinbates 2 года назад

Really helpful! One of the only solutions that seemed to work well for my project at work. Any thoughts on how to prevent an infinite trigger loop but still have this work to keep the total correct when items are modified or even deleted? I'm trying to get it to calculate various project totals depending on the requirements and need it to be automated so that the total project value is correct. Right now, I have it set up with a trigger for new SharePoint list items being added and it works great, but if I delete a record from the project or modify the estimated cost, I need it to recalculate. Any ideas would be appreciated!

@amitvinchurkar6677 3 года назад

How can i get the sum of all the invoice numbers ?

@surrealismo4937 4 года назад

Hi, thanks for the info. It could be possible just to get the total of a column? By the sharepoint setting its possible to enable, but i need to get the total in a variable thanks

@HowToExcelBlog 4 года назад

Not that I know. I haven't explored the SharePoint API yet, maybe it's possible with that and the SharePoint http connector.

@pawankhatri4 3 года назад

Instead of providing an input, Is it possible to use an automatic trigger for a similar situation where the total needs to be updated on a different SharePoint list. Lets say, we edit any of line item amount and that triggers the flow to run for all rows of that list with same Invoice No. and updates the invoice total in a different SharePoint list.

@HowToExcelBlog 3 года назад

Yes, you can trigger a flow when an item is updated in a SharePoint list.

@MrRumJumbie 3 года назад

Awesome video, I've been trying to figure this out for the last couple of days. Is there a way to do this and have the flow output a list of all the invoices and their totals?

@martinhagara7176 4 года назад

hi, please help: Set variable "TempTotal" => Expression: add(items('Apply_to_each')['Amount'],variables('Total')) => reporting an error: The template validation failed: 'The repetition action(s) 'Apply_to_each' referenced by 'inputs' in action 'Nastaviť_premennú' are not defined in the template.'. Where am I making a mistake, can you help me? I will be happy and yes. Thank you.

@HowToExcelBlog 4 года назад

Did you rename the apply to each action? If so, you also need to change it in the expression.

@TheLastrider91 4 года назад

Can you please help with condition incase I want to send sum of amount filtered by month.

@johnoj9459 4 года назад

Thanks - points me in right direction . I'm Trying to output table of all invoice totals but getting stuck adding in an array instead of single invoice number. seems like I need an array within an array? Any help please

@HowToExcelBlog 4 года назад

Where is the data? Maybe you can do the calcs in the source? It will be difficult in Flow.

@johnoj9459 4 года назад

@@HowToExcelBlog Hi, it’s in sharepoint at the moment - same as your example. In the future it will be sql azure so yes at that point I think it would be more robust as a view. I have seen a post where a new summarised SP list is created through flow - I think I will see what I can do with that . cheers

@johnoj9459 4 года назад

in case anyone requires this, the link is powerusers.microsoft.com/t5/General-Power-Automate/Sum-a-column-from-one-sharepoint-list-with-a-condition-to/td-p/122078 I just then fed the list back into the flow to create an HTML table and emailed out

@brendanc3705 4 года назад

Thanks for the video - this is almost what I am after in achieving a solution. If I could replicate this, but have the flow automatically pickup new entries and create a running total in another SharePoint list, that would be perfect. Any ideas on how to approach this? Similar to your data, I have BatchCodes instead of Invoice numbers, and I want flow to calculate the total stock with the same batchcode and populate the result in a separate list. Then if I was to add more stock in the first list against an existing BatchCode, the flow will then recalculate the total and modify this in the second list.....

@HowToExcelBlog 4 года назад

I'd ask is it necessary to get a running total back into SharePoint? Maybe pulling data from the list into Excel via power query and creating a running total in your query would be a sufficient solution?

@troyjenhorse 4 года назад

Great walkthrough...only strange thing is that I am getting an email for each line item in the list as it is totaling. The final is correct, but I would prefer not to get all of them. Any ideas on how to correct?

@troyjenhorse 4 года назад

Figured out my own issue as soon as I posted...I had the email step in the Apply to Each step, when I needed to add a new step for the email.

@HowToExcelBlog 4 года назад

Yes, that would be why 👍

@robertu.2357 4 года назад

Great tutorial but I'm having trouble making this work when the value in the column I am trying to subtotal has a null value. I'm actually using a document library but I think the principle applies.... using "Get Files (properties only)" results in the property being skipped in the array that is output if it has a null value. So then the property is not available to be manipulated in the Set Variable action. -okay so I figured out I can use a Conditional Control to skip loops where the property I want is "null". -I could probably also have set the default value for the column to 0 so there would never be a null value.

@kotofej1973 3 года назад

Is possibe to use with string? InvalidTemplate. Unable to process template language expressions in action 'Nastaviť_premennú' inputs at line '1' and column '14206': 'The template language function 'add' expects its first parameter to be an integer or a decimal number. The provided value is of type 'String'. Please see aka.ms/logicexpressions#add for usage details.'.

@HowToExcelBlog 3 года назад

Not sure what you're trying to do, but maybe use concat function to join strings.

@kotofej1973 3 года назад

@@HowToExcelBlog BadRequest. The variable 'TempTotal' of type 'Float' cannot be initialized or updated with value of type 'String'. The variable 'TempTotal' only supports values of types 'Float, Integer'. I try to make a sum from date diference. How parameter has your row "ammount"? How parameter has row amount in the list? My row is calculated. When i change formula add(int(items('Apply_to_each')['Diference_date_in_hour_with_two_decimal'],variables('Total'))) i get InvalidTemplate. Unable to process template language expressions in action 'Set_variable' inputs at line '1' and column '14115': 'The template language function 'int' expects one parameter: the value that is to be converted to an integer. The function was invoked with '2' parameters. Please see aka.ms/logicexpressions#int for usage details.'.

@higiniofuentes2551 Год назад

How to install Microsoft Flow?

@JonDoesFlow 5 лет назад

Nice, but where is the the 0 in the decimal point section. i.e. 5639.10 not 5639.1

@HowToExcelBlog 5 лет назад

Unfortunately, formatting numbers isn't possible at the moment with Flow 😒

@JonDoesFlow 5 лет назад

How To Excel I have been able to do this with an expression.

@HowToExcelBlog 5 лет назад

@@JonDoesFlow try getting a thousand comma separator in there now 😂

@HowToExcelBlog 5 лет назад

What expression did you use to get two decimal places?

@JonDoesFlow 5 лет назад

How To Excel {concat('£',first(split(string(variables('varResult5')),'.')),'.',if(less(mod(float(first(split(string(mul(variables('varResult5'), 100)),'.'))),100),1),'00',if(less(mod(float(first(split(string(mul(variables('varResult5'), 100)),'.'))),100),10),concat('0',mod(float(first(split(string(mul(variables('varResult5'), 100)),'.'))),100)),mod(float(first(split(string(mul(variables('varResult5'), 100)),'.'))),100))))}