Send email with filtered data ForAll employees

[Unknown_]

Hi,

I have created an app connected to a table with 4 columns (Employee, ProductType, Price, Sector), like represented in the print bellow.

When submiting the form, I want to send an email to myself with the information of the sales of each employee (example in the right side of the print bellow):

- I want to filter my table Sales based on the Sector I choose in my ComboBoxSector and for each employee I want to calculate their sales of the product X and their sales of all the other products (ProductType<>X)

I have tried to use ForAll function but I can't figure out how to make it work for calculating sums of both ProductType=X and ProductType<>X in the same Loop.
Please note that I have many employees and don't want to specify a condition for each one of them individually.

Can you help me find a solution?
Thank you

[v-qiaqi-msft · Accepted solution]

Hi @Unknown_,

Do you want to send email with a html table based on Combo Box selected sector?

You should firstly re-shape the table using a Gallery as below.

1). Set the Gallery Items property as below:

Filter(TableEMployee,Sector=ComboBox5.Selected.Sector,ProductType="X")

2). Set the Label Text property of the Employee, Others and ProductX as below:

ThisItem.Employee

ThisItem.Price

If(
    Sum(
        ForAll(
            Filter(
                TableEMployee,
                Sector = ComboBox5.Selected.Sector,
                !(ProductType = "X"),
                Employee = ThisItem.Employee
            ).Price,
            Value(Price)
        ),
        Value
    ) > 0,
    Sum(
        ForAll(
            Filter(
                TableEMployee,
                Sector = ComboBox5.Selected.Sector,
                !(ProductType = "X"),
                Employee = ThisItem.Employee
            ).Price,
            Value(Price)
        ),
        Value
    ),
    "0"
)

3). Then you should set the send email button as below to send a html table:

Office365Outlook.SendEmailV2(
    User().Email,
    "EmployeeData",
    "<h3>Employee Data Summary</h3>" & "<table width='100%' border='1' cellpadding='5' style='border:1px solid black; border-collapse:collapse'>" & "<tr style='background-color:#efefef'>
<th>Employee</th><th>ProductX</th><th>Others</th>
</tr>
<tr>" & Concat(
        Gallery9.AllItems,
        "<td>" & Body9.Text & " </td>
<td>" & Title14.Text & " </td>
<td>" & Subtitle12.Text & " </td>

",
        "
</tr><tr>"
    ) & "</table>"
)

Note that the Body9, Title14 and Subtitle12 is the Label where I displat Employee , ProductX and Others