Filter function to return multiple approver's information

[jessi82315]

Hello all!

I am still new to PowerApps.

Currently I'm really building and learning and feel like I may have missed something so I'm hoping to get some help here.

I am building a approval app where the approval will be based on 2 conditions:

1. Department

2. $ amount

The Approvers table looks like below:

I was able to use lookup to find the approvers with no problem.

LookUp(table1, 'Department' = Res_Dept.Selected.Value && 'Approval Limit'> Value(Total_Cost.Text)).'Approvers Email'

Res_Dept is a combo box

Total_Cost is a regular input box

My problem is that some departments have "more than 1" approvers. 

I have the result stored in the "Default" section of the Data Card.

With Lookup this will work, but with filter since the result will be a table so it won't work...

I tried to add several box there and each one of them can store the first, second, third...etc. approves but it comes with a lot of problem.

(1. the delegation problem. my data is in Share point list 

2. I will need to create more column to store them all?

3. how to send email to all of them...)

Then I thought about "Collection". Maybe I could collect the table result from the filter function and use For All to send email?

But I haven't done enough research to learn about collection...(I'm reading/watching material now...)

Any ideas will be greatly appreciated!!

Thanks!!!!

[gabibalaban]

Hi @jessi82315 ,

1. For delegation warning - the problem is the usage of function Value inside the formula (where value is not delegable to sharepoint).

LookUp(table1, 'Department' = Res_Dept.Selected.Value && 'Approval Limit'> Value(Total_Cost.Text)).'Approvers Email'

So set an context variable in OnChange property of the Total_Cost control like this:

UpdateContext({myTotalCostAsNumber:Value(Total_Cost.Text)})

and use this variable inside your formula:

LookUp(table1, 'Department' = Res_Dept.Selected.Value && 'Approval Limit'> myTotalCostAsNumber).'Approvers Email'

2. No. You can use the concat() function to transform the answer in a string, something like:

Concat(

     LookUp(table1, 'Department' = Res_Dept.Selected.Value && 'Approval Limit'> Value(Total_Cost.Text)

     ).'Approvers Email'&";") (where ";" is the separator).

3. It enough to use the formula above in a Office365Outlook.SendEmailV2() function. To make it work you need a connection to Office365Outlook.

Here you can find more details about Office365Outlook connector.SendEmailV2() function

https://docs.microsoft.com/en-us/connectors/office365/#send-an-email-(v2)  

Hope it helps !

[v-xiaochen-msft]

Hi @jessi82315 ,

Could you tell me:

1\ What’s the data type of 'Approvers Email' column? Is it a ‘Single line of text’ column?

2\ If there are 5 people in a department, do you have 5 records in that department?

3\ Do you want to send emails in batches by clicking a button control?

4\ Is there a department with more than 2000 people?

Due to lack of information, I made some assumptions.

I assume:

1\ 'Approvers Email' is a ‘Single line of text’ column.

2\ If there are N people in a department, there are corresponding N records, and each record records the email address of each person.

3\ You want to send emails in batches by clicking a button control.

4\ The number of people in each department does not exceed 2000.

If my assumptions are correct, first of all, let me answer a few of your questions

1\ If your data volume is not particularly large ( people number of a department < 2000), you don’t need to worry about delegation.

2\ If you have "more than 1" approvers, you should use filter() function instead of lookup() function.

3\ Your idea is right, you should use the forall() function to send emails in a loop.

Since I don’t know what control you use, I used a gallery control and a button control to make a simple example.

1\ This is my list ‘LIST63’. ‘Department’ and ‘Approvers Email’ are ‘Single line of text’ column.  ‘Approval Limit’ is a number column.

2\ Add a combo box control ‘Res_Dept’ and set its Items property to:

Distinct(LIST63,Department).Result

3\ Add a textinput control ‘Total_Cost’

4\ Add a gallery control ‘Gallery2’ and set its Items property to (You could replace it with dropdown control or combo box control):

Filter(LIST63, Department=Res_Dept.Selected.Result && 'Approval Limit'> Value(Total_Cost.Text)).'Approvers Email'

5\ Add a Office365Outlook connection.

6\ Add a button control and set its onselect property to:

ForAll(Gallery2.AllItems.'Approvers Email',Office365Outlook.SendEmailV2('Approvers Email',"Test","Test1"))

7\ The result is as follows:

Best Regards,

Wearsky

If my post helps, then please consider Accept it as the solution to help others. Thanks.

[jessi82315]

Hi @gabibalaban 

Thanks so much for your reply!!

The delegation problem went away because I figured I need to return more than 1 record but I did tried your no.2 answer which works pretty good. Thanks!!!

I'm trying to use ForAll to send email to that concat string and am confusing what to fill in for source parameter also for the "To" in email function.

I fill in the datacard where I stored the concat string but it doesn't seem to work? am I missing something?

ForAll(Approver_Email.Text,Office365Outlook.SendEmailV2(Approver_Email.Text,"sample","SampleTest1"))

Also, I haven't gotten there yet, but just want to check with you when we're talking about this....

If I want to update this "approver email" field in the form with the first responder's email, will adding a "Set()" function to set the text property of "approver email" to current login user on the "approve" button the first responder is going to click work?

thanks

Jessie

[jessi82315]

Hi @v-xiaochen-msft  Wearsky!

Thanks for your reply!

Your assumption is basically correct except for no. 3.

I'd like to send email when the form is submitted successfully.

I guess in this case I can put your no. 6 formula in "OnSuccess" field for the Form right?

Also Thanks so much for clarifying and answering my questions!! It really helps me as I'm learning PowerApps!! Also for the very detailed step-by-step answer I really appreciate it!

But besides gallery, if I want to send the email based on the concat string, is that possible?

Because this approver email is a column in my SharePoint list, I would like to collect the "first approver's email"(out of so many other approvers) and store it in this field. (maybe something like when they click on approve, update this field with current login user's email)

Thanks!

Jessie

[jessi82315 · Accepted solution]

@gabibalaban  and @v-xiaochen-msft 

never mind.....I just figured I don't need to use ForAll to send email if I already have a concat string....haha!!!😂