cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate I
Advocate I

How to loop collection lookup values?

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: AcceptableActive

 

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

1.JPG

But it saves like this in SP:

2.JPG

 

 

 

 

 

 

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Re: How to loop collection lookup values?

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")

3.JPG

 

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

 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
Community Support
Community Support

Re: How to loop collection lookup values?

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")

3.JPG

 

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

 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Resident Rockstar
Resident Rockstar

Re: How to loop collection lookup values?

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

Highlighted
Resident Rockstar
Resident Rockstar

Re: How to loop collection lookup values?

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

Helpful resources

Announcements
secondImage

Demo-Extravaganza 2020

Check out these cool Power Apps & vote on your favorite!

secondImage

Robotic Process Automation

Let's talk about the solution provided by Microsoft for Robotic Process Automation (RPA)

secondImage

Community Highlights

Check out whats happening in Power Apps

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

Top Solution Authors
Top Kudoed Authors
Users online (5,492)