Consolidate data and calculate from SharePoint list and then email it

(0) Share

Report

Posted on by Cizco

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!

Categories:

Building flows

I have the same question (0)

All responses (13)

Answers (0)

efialttes 14,756 on at

Like (1)

Report

@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

Was this reply helpful? Yes No
Cizco 17 on at

Like (0)

Report

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:

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.

Was this reply helpful? Yes No
efialttes 14,756 on at

Like (1)

Report

@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

Was this reply helpful? Yes No
Cizco 17 on at

Like (0)

Report

Hi @efialttes,

My list looks like this:

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

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.

Was this reply helpful? Yes No
efialttes 14,756 on at

Like (1)

Report
@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

Was this reply helpful? Yes No
Cizco 17 on at

Like (0)

Report

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:

Was this reply helpful? Yes No
efialttes 14,756 on at

Like (1)

Report

@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?

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?

Was this reply helpful? Yes No
Cizco 17 on at

Like (0)

Report

Hi @efialttes

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

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" 🙂

Was this reply helpful? Yes No
efialttes 14,756 on at

Like (1)

Report

@Cizco

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

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:

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:

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

Hope this helps

Was this reply helpful? Yes No
Cizco 17 on at

Like (0)

Report
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')))

Was this reply helpful? Yes No