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

Send email with filtered data ForAll employees

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)

Unknown__0-1670241268373.png

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

1 ACCEPTED SOLUTION

Accepted Solutions
v-qiaqi-msft
Community Support
Community Support

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

vqiaqimsft_0-1670295789537.png

 

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

vqiaqimsft_1-1670296391285.png

 

Best Regards,
Qi

View solution in original post

1 REPLY 1
v-qiaqi-msft
Community Support
Community Support

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

vqiaqimsft_0-1670295789537.png

 

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

vqiaqimsft_1-1670296391285.png

 

Best Regards,
Qi

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (3,407)