cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
JC75
Level: Powered On

Problem Patching SP Lookup column from a collection

I have a SP List(METCAP) - which amongst other values contains a Lookup column, whose data is in a seperate list.

 

I have a collection in powerapps which is  created from the Lookup source list. 

 

I have a gallery in Powerapps populated with items from that collection - the gallery also has checkboxes.

 

I have a submit button - which when selected, creates a second collection that is populated from items which were checked, the collection(ColCheckedWx) has ID and TITLE as columns.

 

The submit then is supposed to patch the main SP list(which contains the lookup column and other columns) My patch is fine for all the other columns but I am trying to patch the lookup column(WXObs) with the values from the checkbox selected collection.

 

This is a cut down version of my patch.

 

ClearCollect(ColCheckedWX, ForAll(Filter(GalWXList.AllItems,Checkbox2.Value = true), {ID:ID, Title:Title}));

 

Patch(MetCap,Defaults(MetCap),{Title: Label18.Text}, {WXObs:{'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference", ID:ColCheckedWX.ID ,Value:ColCheckedWX.Title}})

 

It wont work and errors saying The type of this argument "WXObs" does not match the expected type "Table". Found type "Record"

 

Any ideas/suggestions?

1 ACCEPTED SOLUTION

Accepted Solutions
tommyly
Level 8

Re: Problem Patching SP Lookup column from a collection

Based on my understanding, you have a gallery (GalWXList) that has a checkbox (Checkbox2) within its datacards. You want to take the selected datacard records and patch those records as lookup values within the WXObs (Multiple-value lookup) field.

First, let's consider your collection:

01: ClearCollect(ColCheckedWX,
02:   ForAll(
03:     Filter(GalWXList.AllItems,
04:       Checkbox2.Value = true
05: ),
06: {ID:ID, Title:Title}
07: )
08: )

Optionally, you could avoid having to use a collection at all. This collection yields the selected records from GalWXList, but you could simply refer to these results as (lines 3, 4, and 5):

Filter(GalWXList.AllItems, Checkbox2.Value = true)

 

Next, your patch syntax:

01: Patch(MetCap, Defaults(MetCap),
02: {
03: Title: Label18.Text
04: },
05: {
06: WXObs:
07: {
08: '@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
09: ID:ColCheckedWX.ID,
10: Value:ColCheckedWX.Title
11: }
12: }
13: )

Lines 4 and 5 seem suspect. It almost looks like it's writing record 1 and then immediately writing record 2 in the same Patch command. I've never tried it like that--if it works for you, then you obviously don't need to change a thing.

Your curly braces in lines 7 and 11 seems to be setup to receive a record (one row). In lines 9 and 10, your reference to ID and Value (which are number and text, respectively) are assigned to ColCheckedWX.ID and ColCheckedWX.Title, respectively. The problem here is that ColCheckedWX is a collection of (possibly) multiple records (aka Table). So you would get an error when trying to assign a table to a number data-type or a text data-type.

 

My suggestion is something like this:

01: Patch(MetCap, Defaults(MetCap),
02: {
03: Title: Label18.Text
04: WXObs:
05: ForAll(AddColumns(Filter(GalWXList.AllItems, CheckBox2.Value = true), "ID2", ID),
06: {
07: '@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
08: ID:ID2
09: Value:Title
10: }
11: )
12: }
13: )

So you have to add a column "ID2" (or the like) becaeuse the Lookup uses a column "ID" (line 8), and PowerApps gets confused (e.g. for that reason, if line 8 read "ID:ID", that would be ambiguous to PowerApps). I've had trouble before using AddColumns in a ForAll statement; if PowerApps gripes at you, then you may have to use a collection after all... or you could set GalWXList.Item to:

AddColumns(yourGalleryDataSource,
  "ID2", ID
)

...and delete the AddColumns in the Patch command.

If this doesn't solve your issue, then hopefully it will lead you in the right direction.

View solution in original post

2 REPLIES 2
tommyly
Level 8

Re: Problem Patching SP Lookup column from a collection

Based on my understanding, you have a gallery (GalWXList) that has a checkbox (Checkbox2) within its datacards. You want to take the selected datacard records and patch those records as lookup values within the WXObs (Multiple-value lookup) field.

First, let's consider your collection:

01: ClearCollect(ColCheckedWX,
02:   ForAll(
03:     Filter(GalWXList.AllItems,
04:       Checkbox2.Value = true
05: ),
06: {ID:ID, Title:Title}
07: )
08: )

Optionally, you could avoid having to use a collection at all. This collection yields the selected records from GalWXList, but you could simply refer to these results as (lines 3, 4, and 5):

Filter(GalWXList.AllItems, Checkbox2.Value = true)

 

Next, your patch syntax:

01: Patch(MetCap, Defaults(MetCap),
02: {
03: Title: Label18.Text
04: },
05: {
06: WXObs:
07: {
08: '@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
09: ID:ColCheckedWX.ID,
10: Value:ColCheckedWX.Title
11: }
12: }
13: )

Lines 4 and 5 seem suspect. It almost looks like it's writing record 1 and then immediately writing record 2 in the same Patch command. I've never tried it like that--if it works for you, then you obviously don't need to change a thing.

Your curly braces in lines 7 and 11 seems to be setup to receive a record (one row). In lines 9 and 10, your reference to ID and Value (which are number and text, respectively) are assigned to ColCheckedWX.ID and ColCheckedWX.Title, respectively. The problem here is that ColCheckedWX is a collection of (possibly) multiple records (aka Table). So you would get an error when trying to assign a table to a number data-type or a text data-type.

 

My suggestion is something like this:

01: Patch(MetCap, Defaults(MetCap),
02: {
03: Title: Label18.Text
04: WXObs:
05: ForAll(AddColumns(Filter(GalWXList.AllItems, CheckBox2.Value = true), "ID2", ID),
06: {
07: '@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
08: ID:ID2
09: Value:Title
10: }
11: )
12: }
13: )

So you have to add a column "ID2" (or the like) becaeuse the Lookup uses a column "ID" (line 8), and PowerApps gets confused (e.g. for that reason, if line 8 read "ID:ID", that would be ambiguous to PowerApps). I've had trouble before using AddColumns in a ForAll statement; if PowerApps gripes at you, then you may have to use a collection after all... or you could set GalWXList.Item to:

AddColumns(yourGalleryDataSource,
  "ID2", ID
)

...and delete the AddColumns in the Patch command.

If this doesn't solve your issue, then hopefully it will lead you in the right direction.

View solution in original post

JC75
Level: Powered On

Re: Problem Patching SP Lookup column from a collection

thank you

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (3,954)