HTML table

[Pedrito]

Hi,

I am having issues to produce an HTML table to include in a daily email. I have a draft of the flow but taking in account I am having some issues I will tell you what I receive and what I want to get.

I receive daily a table similar to this one, with the updated daily sales (just included 2 days):

Date	Store	City	Product	Delivery quantity
26/12/2021	A	AAAAA	X	1
26/12/2021	B	AAAAA	Y	4
26/12/2021	C	BBBBB	Y	5
26/12/2021	D	CCCCC	Y	10
26/12/2021	E	CCCCC	X	4
26/12/2021	F	CCCCC	X	10
26/12/2021	G	DDDDD	Y	2
26/12/2021	H	EEEEE	Y	13
27/12/2021	A	AAAAA	X	1
27/12/2021	A	AAAAA	Y	4
27/12/2021	C	BBBBB	Y	5
27/12/2021	D	CCCCC	X	10
27/12/2021	E	CCCCC	Y	4
27/12/2021	E	CCCCC	X	10
27/12/2021	G	DDDDD	Y	2
27/12/2021	H	EEEEE	Y	13

At the end of each day I want to send an email with an HTML table similar to this one below (assuming today is 21/12/27):

Store	City	Product X deliver quantity	Product Y deliver quantity
A	AAAAA	1	4
C	BBBBB		5
D	CCCCC	10
E	CCCCC	10	4
G	DDDDD		2
H	EEEEE		13

I know how to trigger and how to embed an HMTL table into an email, but I am struggling to have the information set up like this.

Some help really appreciated.

Regards

[fchopo]

Hi @Pedrito 

Please, have a look at this excellent blog post by @Expiscornovus on how to do a group by (in your case, by store) and get the sum of values: How to get a Group By Subtotal – Expiscornovus

Hope it helps!

Ferran

[v-bofeng-msft · Accepted solution]

Hi @Pedrito :

I've made a test for your reference:

The Result:

Some Expression for your reference:

@and(
 and(
  equals(item()['City'], items('Apply_to_each')?['City']),
  equals(item()['Store'], items('Apply_to_each')?['Store'])
 ),
 equals(item()['Product'], 'X')
)

If(
 equals(length(body('Filter_array_2')),0),
 null,
 body('Filter_array_2')[0]['Delivery quantity']
)

Best Regards,

Bof

[Pedrito]

Thank you both @fchopo and @v-bofeng-msft , great help.

@v-bofeng-msft regarding you flow, I tried to adapt it to mine (you find it below) and I am struggling with 2 topics:

- I am not being able to filter properly inside Apply to each. Until then I am able to filter but in both filters inside apply to each I don't get any output. I don't know if it's a issue with union formula or filters (or if it's where I have my data source) but seems is not working. The formulas I am using are:

Union: union(body('Select'),body('Select'))

Filter array 2 (adapted to my table and product names): @and(and(equals(item()['City'],items('Apply_to_each_4')?['City']),equals(item()['Name'], items('Apply_to_each_4')?['Name'])),equals(item()['Description'],'Product 1 ESP'))

Filter array 3 (adapted to my table and product names): @and(and(equals(item()['City'], items('Apply_to_each_4')?['City']),equals(item()['Name'], items('Apply_to_each_4')?['Name'])),equals(item()['Description'],'Product 2 ESP'))

- I get the error below we I try to create HTML table (with no filtered information at apply to each).

Do you think you can tell me what is wrong?

Regards

[v-bofeng-msft]

Hi @Pedrito :

Please check the 'Filter array 2' and make sure it's output is not null.

Best Regards,

Bof

[Pedrito]

The otput is not null and his correct, filtered bu the todays date. Any other option? Regards

[v-bofeng-msft]

Hi @Pedrito :

Have you checked whether the return value of ‘union(body('Select'),body('Select'))’ is empty?

Where is the 'body' form?'Filter array' or 'Filter array 2'?

What are ''Product 1 ESP' and 'Product 2 ESP'?This did not appear in the demo you gave me. Because your actual situation is different from the demo you gave, can you go to run history to check whether the conditions you set are appropriate?

Best Regards,

Bof

[Pedrito]

Hi,

- The return value of ‘union(body('Select'),body('Select')) is OK, the issue is not here

- 'body' form is from Filter array 2

- The product 1 ESP and 2 ESP are the same as X and Y, just to avoid send you the real names

- Until Filter array 7 and 8 everything is correct, and the output of this is empty, so the issue has to come from this steps. Do you see any error in the formulas? (Name instead of store).Could be the "?" ?

@And(and(equals(item()['City'], items('Apply_to_each_4')?['City']),equals(item()['Name'], items('Apply_to_each_4')?['Name'])),equals(item()['Description'],'Product 1 ESP'))

@And(and(equals(item()['City'], items('Apply_to_each_4')?['City']),equals(item()['Name'], items('Apply_to_each_4')?['Name'])),equals(item()['Description'],'Product 2 ESP')

Thank you

[v-bofeng-msft]

Hi @Pedrito :

City and Name do not actually exist in your data source, please use the correct name to call the field

Best Regards,

Bof

[Pedrito]

Great, thank you very much, I kept in the mapping Name and City (instead Hospital and Ciudad) and it worked, now all apply to each 4 is working.

But keep me having the error creating html table: The property 'columns' must be specified unless the 'from' property value is an array of objects.

Any suggestion?

[Pedrito]

Hi,

Finally I realized I was doing a "stupid" mistake on append to array variable step, using () instead of {}. With that fixed the flow is working perfectly as @v-bofeng-msft suggested. Thank you a lot for the support.

[Pedrito]

Hi, sorry to reopen this discussion, but I am struggling with one question:

- Sometime in this sales table I have 2 lines from the same store and city with sales for the same product, something like this below. Actually with this flow the final table just includes the sales quantity of the first line, in this case 4 units for the product X. How can I have the sum of the both lines, in this case 8 units for te product X in this store?

I think is something in this expression, bur I don't know how to express the add in case the number is not 0.

If(
equals(length(body('Filter_array_2')),0),
null,
body('Filter_array_2')[0]['Delivery quantity']
)

27/12/2021	A	AAAAA	X	4
27/12/2021	A	AAAAA	X	4

Thank you, much appreciated.