cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Consolidate data and calculate from SharePoint list and then email it

Hi,

 

This is my 2nd try to post this question since my post/text in that post got lost when I logged in 😠

So this will be a shorter version...

 

The problem/idé

I want to create a email notification that get data from a SharePoint list.

The list contains columns:

Food (text)

Drink (text)

Date (date as yyyy-MM-dd)

Who (a person)

 

I need the email to be able to look something like this:

 

Hi,

 

I would like to order the following item for [future date]:

[count item A] - [item A]

[count item B] - [item B]

[count item C] - [item C]

 

--------------

The result should look something like this:

 

Hi,

 

I would like to order the following item for 2019-12-25:

 

3 - Coffee

2 - Water

5 - Sallads

 

 

 

 

Thank you!

13 REPLIES 13
Super User III
Super User III

@Cizco 

What's the criteria to send the email(s)? Do you need to pack food orders per date, per person, or both?

Let's assume you need to read the whole sharepoint list (SP get items), so if there are N different persons stored in column 'Who', you have to send N emails. Then you need to build an array with all persons (union function can be a good approach). Next iterate over the each person to filter its orders assigned (Filter array can be a good approach) and send the corresponding email

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!



Hi @efialttes,

 

Thank you for your reply!

 


@efialttes wrote:

What's the criteria to send the email(s)? Do you need to pack food orders per date, per person, or both?


The mail need to be sent one day each week if there is any new orders and the mail will be sent to a predefined group or person 🙂

I think I can solve that part with Filer Query in SP: Get Items to only get the items that matches the date:

2019-12-02 10_24_34-Edit your flow _ Power Automate.png

 

Union function

I can use this to get all the items (food and drinks) and only get the item once so I can use it in my email? 

Example: Apple, Apple, Coffee & Salad

Becomes: Apple, Coffee & Salad

(If one or more items have the same name, the last item with that name appears in the result.)

 

 

But how do I count the number of items and then add that table/information to my email?

I've been trying Apply each, Compose, Create HTML table, Select and some others.

 

 

 

 

 

@Cizco 

Please note it is unclear to me how you currently represent in each element (order) in your Sharepoint list the number requested per Food Item (i.e. 3 Apples, 2 Coffees, 7 Salads). I am assume each list element is an order, right?

 

Taking this into account, one possible approach once you have an array with the names of the food items (Apple,Coffee,Salad) is: you can use an Apply to each (use the food items array as input), and inside the loop add a Filter Array (use Get items output as input for this new Filter Array), implementing as condition the presence of current food item. This way each iteration provides you the subset of elements (orders) asking for Apples, Coffe, Salad.

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!



Hi @efialttes,

 

My list looks like this:

2019-12-03 14_03_33-Window.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

So the users can only enter 1 food and/or drink in the Microsoft Form:

2019-12-03 15_08_08-Window.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

If I do:

Apply Each

 Filter Array

I'll get each item once?

 

I'm sorry but for some reason I'm having a hard time connection the dots here 😅 

 

Thank you so much for your help.

@Cizco 

Users can only enter 1 food and/or drink in the Microsoft Form, and each response is represented by a new element in your sharepoint list, right? No way to ask for two yogurths in the same response.

 

AS far as I understand you already built the food dictionary, i.e. an array with all food items represented once. Let's assume you have stored the dictionary in a variable called 'FoodDictionary'

 

Once build, my suggestion is to add an Apply to each action block, Its input would be the variable 'FoodDictionary'.

variables('FoodDictionary')

 

Inside the Apply to each, you place a Filter Array action block. Its input would be the output obtained in Get Items action block

body(Get_items')?['value']

and the condition applying for the Filter Array would be:

equals(item()?['Frukost'],items('Apply_to_each'))

Please note I am assuming Frukost is defined in your Sharepoint List as a string based column. If it is either a Choice column, or a LookUp column, the expression would be slightly different.

 

Now you just need to count the number of elements matching the filter. Let's assume you add a Compose action block with the following expression:

 

concat('Food Item: ', items('Apply_to_each'), '.Nr of units ordered: ',length(body('Filter_array)))

 

You can append the result of each iteration in another array variable, remember this second variable shall also be initialized before the Apply to each. You can use this second array variable as a container of objects like the following:

 

{'Frukost': 'Yogurt', 'NrUnits': 2}

 

Please note I haven't tested myself the expressions yet, hope not to have included any typo

 

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!



Hi @efialttes,

 


@efialttes wrote:

@Cizco 

Users can only enter 1 food and/or drink in the Microsoft Form, and each response is represented by a new element in your sharepoint list, right? No way to ask for two yogurths in the same response.

 


Yes, that is correct.

 

I'm sorry but I'm having a hard time picturing it/seeing it what you are describing to me.

This is what it currently looks like after I cleaned up my test:

2019-12-05 19_50_17-Edit your flow _ Microsoft Power Automate.png

@Cizco 

I think we are very close to detail a solution.

Two more (and hope final) questions:

 

A) Are Food and Drink options always the same shown in the screenshot you shared? Or, do the change frequently?

 

 

FlowFood.png

If always the same, we do not need to create a dynamic dictionary

 

B) Which Column type did you chose when creating Food and Drink columns in your Sharepoint list? Single Line of Text? Choice? other?

 



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!



Hi @efialttes 

 


@efialttes wrote:

A) Are Food and Drink options always the same shown in the screenshot you shared? Or, do the change frequently?

 

 

FlowFood.png


From what I know they will not change but if they change, I'm prepared to make the necessary changes to the flow.

 


@efialttes wrote:

 

B) Which Column type did you chose when creating Food and Drink columns in your Sharepoint list? Single Line of Text? Choice? other?

 


Yes, it's "Single Line of Text" 🙂 

2019-12-09 10_46_43-List Settings.png

@Cizco 

 

I built the following SP list for testing purposes from the example you shared:

Flow_Frukost_SPList.png

You will find the service logic I defined in the following screenshot, both recurrence trigger and 'send email' not shown for simplicity. Also Expressions are copied&pasted as Comments:

Flow_Frukost_Details.png

According to your requirements, I finally opted for a static dictionary instead of a dynamic one for simplicity reasons. I stored it on a variable 'called FoodDrinkDictionary'.

Then I am calling 'Get items'.

Then I perform an 'Apply to each' element from the dictionary.

So what I am doing in each loop iteration is to calculate the number of items from the Sharepoint List whose column 'Food' or 'Drink' is equal to Dictionary element #i, store the Item Name and Nr as an object and append it to the another variable called 'WeeklyOrders'. Please note both variables are of type Array.

 

Outside of the loop, I added a 'Crate HTML Table' to draw a teable with the content of variable 'WeeklyOrders'

Result after flow execution:

Flow_FRukost_Result.png

You can add the output of 'Create HTML table' in the body of your email notification

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!



Hi @efialttes,

 

Thank you! This should work great!

But can you paste the line for "Append to array variable"? Mine doesn't work and I use :

setProperty(setProperty(json('{}'),'ItemName',items('Apply_to_each')),'Nr'.length(body('Filter_array')))

 

@Cizco
I am not with my laptop, but i think the problem should be solved by substituting the dot. Use comma instead

setProperty(setProperty(json('{}'),'ItemName',items('Apply_to_each')),'Nr',length(body('Filter_array')))
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!



Hi @efialttes,

 

It look Ok but my Date filtering doesn't want to work.

 

I've tested both with "Get future time" and my own expression:

2019-12-10 14_29_47-Window.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

My expression is: 

formatDateTime(addDays(utcNow(),2),'yyyy-MM-dd')

And my SP list is:

2019-12-10 14_32_31-Window.png

 

 

 

 

 

 

 

 

And the error message is:

"status": 400,
  "message": "The expression \"('Dag' eq 2019-12-12)\" is not valid.\r\nclientRequestId: 4b35eceb-8cde-4c3c-9f0c-38917d8739be\r\nserviceRequestId: 4b35eceb-8cde-4c3c-9f0c-38917d8739be"


2019-12-10 14_34_51-Window.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Any idé?

@Cizco 

ODATA filters are usually complex.

This post explains how to set up a data range, but not sure if the column type in the example is also Date as yours.

Plase note in ODATA expression from the example, quotes are not delimiting column name but the value to compare with.

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!



Helpful resources

Announcements
Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

New Super Users

Meet the Power Automate Super Users!

Many congratulations to the Season 1 2021 Flownaut Crew!

Microsoft Ignite

Microsoft Power Platform: 2021 Release Wave 1 Plan

Power Platform release plan for the 2021 release wave 1 describes all new features releasing from April through September 2021.

MPA Community Blog

Power Automate Community Blog

Check out the community blog page where you can find valuable learning material from community and product team members!

Users online (47,445)