Add items selected from combo-box (lookup) to collection, send email to certain user(s) based on selection

[jharville1]

I have a combo-box called 'reviewComboBox' (that contains items pulled from a Lookup column) inside my application. Some of the items are: January, February, March, April, May ... and so on.

My combo-box allows for multiple selections. I would like for: when the user selects multiple items and submits the form once they have made all their changes (ex: selecting what items they'd like, and de-selecting what they would not like if they change their mind), the items selected in that combo-box will be added together to a collection in the form of a table. Here's a visual of the collection I'd like if the 3 items: 'January' , 'February' , and 'March' are selected in the combo-box -->

I have tried the following code (as well as ClearCollect(colReviewItems, yourComboBox.SelectedItems) ) in both the "OnSelect" and "OnChange" properties (in the 'Advanced Properties') section of my combo-box, but nothing is collected at all... Does anybody see any issue with my code?

-------------------------------------

I have been told from other users on this forum that I wouldn't need to create a collection, as I can rather simply reference yourComboBox.SelectedItems in any place of my app. However, the reason why I am trying to collect the items from the combo-box is because I'm building an 'Approvals' application that creates an approval for each of the items selected in the combo-box. The collection will count each item stored in it and ultimately uses a patch statement to create the approvals. I already have the 'Approvals' app built so that it creates one approval for the entire form, but need to tweak it to create an approval for each item selected.

The application: consists of one form with an input text field where the user inserts their name, and a combo-box section. I want each item selection within the combo-box to route the form to a specific person, since the look-up that my combo-box is using is pulling from a separate list ('review materials') that has 2 columns. One for the item (ex: January), and the second column for an email address. So in one row, there is a corresponding Item and Email Address.

ex: January emails (routes) the form to 123@gmail.com for approval of that 1 item (January), while February emails the form to xyz@gmail.com for approval of that 1 item (February). If both January and February are selected, both of the email recipients stated will be sent an email for approval of those 2 items (January and February).

My goal is to add those items selected to the collection, write code to have a count of how many items are in that collection, and then create sub-approvals for each item with a patch. But as of now, I just need help adding those selected combo-box items to a collection, where I can later count how many are in the collection and send out emails based on what was selected.

[RandyHayes]

@jharville1 

So, it is true that your SelectedItems property is already a table/collection.  Creating another one from it is a bit redundant.  Also, consider that a collection is a bit of excessive overhead for what you are trying as, if you truly wanted to "snapshot" the selected items, you could simply store it in a variable - 

ex.  Set(myItemsSelected: yourCombobox.SelectedItems)

If your items property of the Combobox already includes the full record from your Review Materials list, then you have pretty much everything you need.

Now, I am not sure exactly what you mean by "routing the form".  Does this mean that you have a PowerAutomate flow that is going to take over and do some routing, or that you want then for your App to send out emails?

If you are going with the "snapshot" approach, then consider the following:

1) Prior to your SubmitForm, put in a Set(myItemsSelected: yourCombobox.SelectedItems) (obviously replacing the name of the combobox with the proper name of your control).

2) In the OnSuccess action of your Form, conduct the following:

   ForAll(myItemsSelected,

      Pacth(aList, aRecord, {someValues})
   )

Of course the specifics of #2 above are going to be based on my question of what you mean by "routing the form".

I hope this is helpful for you.

[jharville1]

@RandyHayes Thank you for the response Randy. When I mentioned "routing" the form, I meant emailing the users specified in the same row (in the SharePoint List) as the item name. Ex: the selection of 'January' emails 123@gmail.com that they have a form that needs to be approved. I was thinking of using the Office365Outlook.SendEmail() function to send the email to the corresponding email address in the same row, but still need to plan how I'd set up the code so that it would only send to those emails specified.

[RandyHayes]

@jharville1 

You had mentioned Patching something before, so I am not sure where that fits in now.

But, if you are sending then from your app, again follow as I outlined before and then in your OnSuccess action, use the following:

ForAll(myItemsSelected,

    Office365Outlook.SendEmailV2(
        'Routed To',
        subject,
        body
     )
)

The above is based on the assumption that your original Combobox has an Items property of 'review materials'.

In this case, the 'Routed To' information will all be in the myItemsSelected variable that you are setting in the formula before you do a SubmitForm.

[jharville1]

@RandyHayesI really do appreciate your help and the response. But for the above code, how would I be able to differentiate who I send the email to based on what item is selected, instead of sending all selected items to the same group of people?

ex: January emails (routes) the form to 123@gmail.com for approval of that 1 item (January), while February emails the form to xyz@gmail.com for approval of that 1 item (February). If both January and February are selected, both of the email recipients stated will be sent an email for approval of those 2 items (January and February).

[RandyHayes · Accepted solution]

@jharville1 

So the logic it there...  It all starts from your Combobox.  Since you have the Items property set to 'review materials', the selected items will have the full record of the item from the list.

So, if January and February are selected, then you will have a variable called mySelectedItems (using the Formula I provided before), and that variable will have the following structure:

[
   {Title: "January",  'Routed To':"123@gmail.com"},
   {Title: "February", 'Routed To':"xyz@gmail.com"}
]

Now, in your OnSuccess, you would have the formula below and it will execute the SendEmailV2 on all of the records above:

ForAll(myItemsSelected,

    Office365Outlook.SendEmailV2(
        'Routed To',
        subject,
        body
     )
)

Now, the above will send an individual email to 123@gmail.com and xyz@gmail.com.  You can craft your subject/body as needed:

If you want to sent 1 email to both selected with information about both selected, then you can do the following:

Office365Outlook.SendEmailV2(
    Concat(myItemsSelected, 'Routed To' & ";"),
    "Approvals",
    "Approvals are needed for " & Concat(myItemsSelected, Title & ", ")
)

This would send 1 email to 123@gmail.com; xyz@gmail.com (notice they are semicolon separated) and it will have a body that shows the two selected months (from Title) in it.

Am I close to what you are looking for?

[jharville1]

@RandyHayesThis is exactly what I'm looking for and what I was having troubles with. Thank you so much, I really do appreciate your time and help!

[RandyHayes]

@jharville1 

Glad that hit the spot!!

[jharville1]

@RandyHayes 

I'm sorry but I have one last question on something I failed to realize. I actually checked the 'Items' property of my combo-box, and it isn't 'review materials' ... it is Choices([@'test list'].reviewItem). reviewItem is the name of the column in my main list, which is called test list. This was the lookup I mentioned earlier. I have a list called 'review materials' that holds all of my items (selections) that I want in my combo-box , but the combo-box references a lookup column in my main list ('test list').

Would I be able to implement the same code you mentioned if this is the case to send the email to specific users based on the full record of the item from the original list? I'm not sure if I will be able to do so since there is only one lookup column in my main list ('test list'), rather than having the full record of the item from the original list ('review materials').

Thank you so much, I really do apologize for not catching this minor detail earlier.

[RandyHayes]

@jharville1 

That should be fine.  Just change the Combobox Items property to 'review materials'

This is correct as typically (for lookups) you would reference the actual source of the lookup and not the choices.  This will give you the full records.

So, set the Items to 'review materials' and your value to display as Title.