cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JC75
Advocate IV
Advocate IV

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
Responsive Resident
Responsive Resident

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
Responsive Resident
Responsive Resident

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

thank you

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Don't miss the call this month on June 16th - 8a PDT

Top Solution Authors
Top Kudoed Authors
Users online (20,501)