Issue updating multi-select ComboBox

[tsparkman]

Hi –

I’m having an issue with a ComboBox in my PowerApp. I’ve read a lot of posts regarding similar issues (and have tried several of the suggestions), but to no avail. I’m not understanding how to resolve this and am hoping someone can help me …any and all help is greatly appreciated!

Background:

I have two SharePoint Lists:

• CSUsers
• CSHourTypes

In my CSUsers list I have a multi-select Lookup Column called HourTypesAllowed. The CSHourTypes list contains two columns (Title and ID).

In my PowerApp User Edit form, I have a ComboBox called cb_HourTypes. The field has the following properties set:

• Items: CSHourTypes
• DefaultSelectedItems: LookUp(CSUsers,ID=varUser.ID,HourTypesAllowed.Value)
• Update: cb_HourTypes.SelectedItems

The Save button for my form has the following in the OnSelect property:

ForAll(
    cb_HourTypes.SelectedItems,
    Collect(
        ColHoursToPatch,
        { '@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
              Id: ThisRecord.ID,
              Value: ThisRecord.Title}
    )
);
Patch( CSUsers,
LookUp( CSUsers, ID = varUser.ID ), { 
    FirstName: DataCardValue129.Text,
    LastName: DataCardValue131.Text,
    EmailAddress: DataCardValue134.Text,
    HourlyRate: Value(DataCardValue78.Text),
    CSRole: DataCardValue135.Selected,
    CSJobTitle: {'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
                        Id: DataCardValue137.Selected.Id,
                        Value:DataCardValue137.Selected.Value
                },
    Manager: {'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
                        Id: DataCardValue138.Selected.Id,
                        Value:DataCardValue138.Selected.Value
                },
    PreferredHourType: {'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
                        Id: DataCardValue119.Selected.Id,
                        Value:DataCardValue119.Selected.Value
                },
    HourTypesAllowed: ColHoursToPatch
    } ); FormMode.New; Refresh(CSUsers); Set(varEdit,false);
    ClearCollect(ColHoursToPatch,
    Blank()
    )

The Issue:

When the form loads, it displays the right information from my SharePoint list. For example, if the record has to items in the HourTypesAllowed field, the form is showing those two items correctly. However:

• If I change another field on the form and do not touch the HourTypesAllowed field, it wipes out the data that was in SharePoint. I think this is because the Update property is set to (cb_HourTypes.SelectedItems) and I didn’t select anything…even though the original SP list values were displayed as selected.
• If I change the HoursTypeAllowed field to add another item, it wipes out what was already there and saves only my new selections.

[RandyHayes · Accepted solution]

@tsparkman 

I'm not sure why you would be using Patch for an EditForm.  So, let's review this from the perspective of how you should be doing this process rather than exclude the one thing that an EditForm brings to the table for you.

Let's start with the DataCard that contains the Combobox that you are referring to.

The Default property of the DataCard should be:

   ThisItem.HourTypesAllowed

The Items property of cb_HourTypes should be:

   CSHourTypes (which you have already)

The DefaultSelectedItems on your cb_HourTypes should be:

   ForAll(ThisItem.HourTypesAllowed, LookUp(CSHourTypes, ID=Id))

The Update property of your Datacard should be:

   ForAll(cb_HourTypes.SelectedItems, {Value: Title, Id:ID})

The EditForm should have an Items property of:

   LookUp(CSUsers, ID=varUser.ID)

The Save button formula should be (and nothing else):

   SubmitForm(yourFormName)

Working from the bottom of the formula up:

You need not clear the ColHoursToPatch as this is no longer even needed.

Not sure what varEdit is for...but if it has some significance, then you should have Set(varEdit, false) in the OnSuccess action of your EditForm.

There is no reason to Refresh CSUsers.  It will have the current information you just submitted already.

You have a FormMode.New stated in your formula - this does nothing.  So, I am not sure your intent there.

Now for the DataCards in your form (top of patch down).

The Update Property on the DataCard for FirstName should be: DataCardValue129.Text

The Update Property on the DataCard for LastName should be: DataCardValue131.Text

The Update Property on the DataCard for EmailAddress should be: DataCardValue134.Text

The Update Property on the DataCard for HourlyRate should be: Value(DataCardValue78.Text)

The Update Property on the DataCard for CSRole should be: DataCardValue135.Selected  (although this seems incorrect but I do not know what kind of Column CSRole is, or what the items property is for the DataCardValue135 control. Assuming a Lookup column)

The Update Property on the DataCard for CSJobTitle should be: DataCardValue137.Selected

The Update Property on the DataCard for Manager should be: DataCardValue137.Selected

The Update Property on the DataCard for PreferredHourType should be: DataCardValue137.Selected

The Update Property on the DataCard for HourTypesAllowed was mentioned above.

I am not sure what type of columns CSRole, CSJobTitle, Manager, and PreferredHourType are, but I assumed, based on your formula, that these are single select Lookup columns in your list.

This is all you need.  Now you will be using the form property and can take advantage of all of the functions of the form (OnSuccess, OnFailure, LastSubmit, etc).

I hope this is helpful for you.

[tsparkman]

Hi @RandyHayes -

Thank you SO much for your response...you are an angel!

I had started with SubmitForm but things weren't working. So I spent about 9 hours yesterday reading threads from the forum and trying EVERYTHING. Clearly I went down a long and winding road to nowhere (LOL).

Your instructions were very clear and perfect. Everything is working as planned.

Thank you...thank you...thank you...from the bottom of my heart!

[RandyHayes]

@tsparkman 

Excellent!!  So glad to help.  I always tell people, if you struggle on something for a minute more than you think it should really take (considering the PowerApps was designed for "easy"), then it's time to post a question on the Forum!

If you want to learn most all there is to learn about EditForms, take a look over my video on the subject. It's a long one, but comprehensive plus some things you probably never thought you could do with an EditForm.  There is a table of contents on the video in the description, so you can hunt around at things you might want to learn more about.

I don't go into the very specifics of what you do for the column types and control types and the exact things you need for all the essential properties of the form (that would have made a 5 hour video), so that is in an upcoming video that I have to complete and publish.

Anyway...glad to help out!

[tsparkman]

Ha! That's exactly what I told myself after you solved my problem. Next time I should just "phone a friend" by posting to the forum, instead of struggling to figure it out myself. And I've said that to myself before, but my stubborn nature got the best of me...again 😁

I will definitely check out your video...and I'll also share it with my team. We're all new to PowerApps and are in full-on learning mode. Thank you for sharing the link!

[VijaySharma]

Hi @RandyHayes 

How would the above issue be resolved, when using cascading dropdowns please?

For example: Dropdown1 = Category (single select) (lookup column - from a separate list)
Dropdown2 = Sub-Category (multi-select) (lookup column from a separate list)

Thanks for your help with this.
Vijay