How to loop collection lookup values?

[Kevin_Maxwell]

I have a collection called "Beneficiary_Collection" and a sharePoint list called "Beneficiary". In SP list I have a Lookup column called "Conditions" which it allows Multiple Values to be saved (Allow multiple values is enabled). With the following code, I'm trying to save multiple values to SP but it only saves the first value (Acceptable).

Collection has the following values: Acceptable, Active

This is the way that it should be saved in SP:

But it saves like this in SP:

ForAll(Beneficiary_Collection,
Patch(Beneficiary,LookUp(Beneficiary, Beneficiary_ID = "50114"),
{
    Conditions: Table(
    {
           '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
           Id: LookUp(Conditions_Lookup, English_Def in Conditions.English_Def, ID),
           Value: LookUp(Conditions_Lookup, English_Def in Conditions.English_Def, English_Def)
           
    })
}))

I don't know how to Loop all values in the Collection Lookup column (Conditions) as it only selects the First value!

Any help would be greatly appreciated. Thanks

[v-xida-msft · Accepted solution]

Hi @Kevin_Maxwell,

Could you please share a bit more about the "Beneficiary_Collection" collection and the Conditions_Lookup data source within your formula?

I think the issue is related to the LookUp fucntion within the formula that you provided. The LookUp function could only find the first record in a table that satisfies a formula.

More details about the LookUp function in PowerApps, please check the following formula:

LookUp function

I found that the user @KroonOfficeSol has made a response within the thread you posted before, I agree with his thought almost. You could consider take a try to add new collection (Collection1) with three parameters to update the LookUp type column. 

I assume that you created a Beneficiary_Collection collection using following formula:

ClearCollect(Beneficiary_Collection,"Acceptable","Active")

Then create a new collection (called Collection1) within your app using following fomrula:

ForAll(
Beneficiary_Collection,
Collect(Collection1,
{
'@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
Id:LookUp(Conditions_Lookup, English_Def in Conditions.English_Def, ID),
Value:LookUp(Conditions_Lookup, English_Def in Conditions.English_Def, English_Def)
}
))

Modify your Patch function as below:

Patch(
Beneficiary,
LookUp(Beneficiary, Beneficiary_ID = "50114"),
{
    Conditions: Collection1
}
)

Best regards,

Kris

[KroonOfficeSol]

He Kevin_Maxwell,

Is it Sharepoint online you're using? When so you can just do:

Patch(Beneficiary,LookUp(Beneficiary, Beneficiary_ID = "50114"),
    {Conditions:YourComboBox.SelectedItems.Value}
)

It as simple as that. Just did a small test and this works at my side.

You could try this also when you have a onpremise Sharepoint, maybe it works.

btw 1 Probably the ComboBox gives back a collection/table when you use the SelectedItems property (where the selected property gives back a string)

btw 2 I think that maybe MS updated the connector on SharePoint so all the extra '@odata.type' stuff is not needed anymore.

Greatings Paul

[KroonOfficeSol]

Oke, first was for a choice field with one value. For the lookup then first add a formula to the combobox to store the selected items to a collection. Do this on the onchange property like:

ClearCollect(YourCollection, YourCombobox.SelectedItems)

Then Patch this collection to your condition:

Patch(Beneficiary,LookUp(Beneficiary, Beneficiary_ID = "50114"),
    {Conditions:YourCollection}
)

As I mentioned earlier the Conditions want a table as input. A collection is a table so if the collection is like structure the same as the expected lookup field it will patch it with no problem. 

And again by looping through your combobox rows, you will just overwrite the values. Getting always just one selected item in the lookup. So that why you should patch a table in one statement.

If you run into errors then probably the table structure in the collection doesn't match the structure wanted by the lookup. In this case you can try to change the collection behind the combobox || specifiy the columns on the clearcollect() in the onselect property off the combobox, so it matches the requirements off the lookup table.

Worked add my side, so trilled to hear if it will work at your side.

Paul