Write multi-select combo box to SharePoint List column with data type choice (multiselect)

[finguin]

Hello, I've already done a whole lot of digging through various ressource but can't find a solution to my problem. 

Let's say I've got two columns in a SharePoint List:
1) Column A, data type: text

2) Column B, data type: choice, multiselect, three choices

I also have a Power Apps app, with two field:

1) Field A, text input

2) Field B, combo box, multiselect, three choices

The select values for field B are provided by a collection called "ColB" which is based on a separate SP-List. This is due to the app being multilingual which is not possible with basic SP-List choice columns. 

Now, is it possible to not only write the values from the combo box to column B but also update the record (column A and column B)? 

So far I've tried this:

ForAll(DataCardValue20.SelectedItems, Collect(ColB, {Id:ThisRecord.ID, Value: ThisRecord.field_3}));


Patch (DataSource, LookUp(DataSource, ID="111111"),
{
Column A: DataCardValue1_1.Text,
Column B: ColB
}
);

ClearCollect(ColB, Blank());

Unfortunately, this worked only partially. No issues with column A but column B seems to be a bit stubborn. Running the patch does indeed write the selected values field B into column B but only until I reopen the record to update Field B. If I click on field B it at first looks like that values are selected but then they are not:

If I now submit the form again, column B shows no values because after reopening the record no values are actually selected anymore. I guess it is probably due to column B being a choice column and the app is trying to store values from a collection into this column which is kind of a mismatch. 

Any input is highly appreciated! 

Cheers

Finn

[v-mengmli-msft]

Hi @finguin ，

Are the two multi-select columns have different choices in your two SharePoint lists? If they are not same there will be a problem such as blank, which means the addition failed.

Perhaps could you tell me what is inside of the field_3 in your list and whether the Colum B is a LookUp cloumn?

If the column you want to update is Choice type, you can try this:

ForAll(DataCardValue20.SelectedItems, Collect(ColB,Value));
Patch (DataSource, LookUp(DataSource, ID="111111"),
{
Column A: DataCardValue1_1.Text,
Column B: ColB
}
);
ClearCollect(ColB, Blank());

If this is not what you expected, you can provide me with more details about your App if it is convenient for you and it will be better if there are some pictures attached to describe.

Best regards,

Rimmon Li

[finguin]

@v-mengmli-msft 

Hi Rimmon Li, thanks a lot for the input. Let me go into a bit more detail without, hopefully, making a mess. 

Regarding "Column B" -
this column actually looks like this, it contains two values (both in German) and is no LookUp:

Regarding field_3 -
the corresponding SP-List looks like this (I've filtered it down for simplicity):

Field_3 is actually column "Local". The SP-List "ESG_Choices_Multi_lang" serves as a base for the combo boxes:

Regarding the collection colChoices - 

the code for the collection looks as follows:

The collections is filtered by the variable "varCurrentLanguage" wich can be changed by the user on the next screen from DE to EN and vice versa. 

 
The multilang requirement is actually the root of all evil in this case. The requirement came long after the initial single language setup, thats why we have this choice solumn in the SP-List, and is now causing a massive headache 😄

Through the gallery view the user has the option to edit records and that is where the current issue appears/happens:

Regarding your suggestion - 
I tried what you suggested but the result is the same as before:

If I reopen the record the values are indeed shown but as soon as I submit again the values vanish because "empty" / no selection is communicated to the SP-List. As you can see in the screenshot the dropdown values are not highlighted as they would be if they were "really" selected. I can also select them both once again to then have "selected" four values...

Hope this helps!

Cheers

Finn

[Talha_Dar]

Hi @finguin,

Based on my understanding, when you create a new record in 'Column B,' it is generated without any issues. However, when attempting to edit it, the data from 'Column B' doesn't appear in 'Field B.' Additionally, if you save it again without selecting an option, 'Column B' becomes empty. Have I grasped the situation correctly?

[finguin]

Hi!
Basically yes.

It is generated without issues but if I open the record in the app to edit a field, for example the field shown in the following screenshot, the previously made selection gets erased / set to empty:

It still shows the previous selection (kind of) but after re-submitting the selection is gone. I think by looking at the scrrenshot it becomes apparent that the combo box indeed displays the values from the SP-List but that at the same time the dropdown values ARE NOT selected anymore. Otherwise they would be highlightend as selected...

It is like that the selection does not really stick to the SP-List column ...

[Talha_Dar]

Hi @finguin,

The try adding the following formula in default property of your combo-box

Coalesce(
    Parent.Default,
    LookUp(listName, ID = ThisItem.ID).comboboxColumnName
)

If you have a unique identification column in your SharePoint list then you can try using that instead of "ID".

If you found my answer helpful, please consider giving it a thumbs-up or a like. Your feedback is greatly appreciated!

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[finguin]

Thanks for the swift reply! 

Unfortunately I get this here:

listName in your example refers to the SP-List with results or the SP-List from which the dropdown values are taken? ESG_Data is the SP-List with the result/data sets. "NegativeFactorsEmission" the column in which the selection should be shown. 

[Talha_Dar]

Can you please try adding first only.

Parent.Default

and see if it shows some error. if it does please attach the error it is showing. then add the following and see for same.

LookUp(listName, ID = ThisItem.ID).comboboxColumnName

[finguin]

Error Parent.Default

Error full formula:

Formula:

Coalesce(
    Parent.Default,
    LookUp(ESG_Data, ID=ThisItem.ID).NegativeFactorsEmission
);

[Talha_Dar]

Try adding this only.

LookUp(ESG_Data, ID=ThisItem.ID).NegativeFactorsEmission