cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

Patch SPO list using records in a collection

I have found a few posts similar, but none are an exact match for my issue.  I have a SPO request list, and an accompanying SPO list that lists the items for the request.  What I want to happen, is when a person opens an item in the request list, edits the item (in the customized form), and changes a text field, I want a column in the accompanying item list to relect the change in the request list.  Seems simple enough, but my problem lies in that there may be multiple records in the accompanying item list that point to a single record/item in the request list.  If someone were to change the record/item in the request list, how to I update all the corresponding records in the items list?  The only fields they have in common are the title and ID (the request list record's ID is saved to a 'Request ID' column in the item list).  I have tried the below, but it complains about getting a Table result instead of a Record.

 

ClearCollect(col_AllItems, SortByColumns('Item List', "ID", Ascending))

 

ClearCollect(col_ItemDetailEdit, ShowColumns(Filter(col_AllItems, var_SharepointSelectedID in 'Request ID'), "ItemName", "ItemDescription", "ItemQuantity","ID", "Title"))

 

ForAll(col_ItemDetailEdit, Patch('Item List', Filter('Item List', ID in col_ItemDetailEdit.ID), {Title: Title}))

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Patch SPO list using records in a collection

@lumberjacklurch 

No problem on the help...hang in there, it will all start to "click" eventually, and we'll get this problem solved also.

 

So, let's make some changes...It seems that we're getting some of what we need and the stage is set from the tests.

 

Let's change the Formula in the OnSave to this:

If(SharepointFormMode="CreateForm", 
   SubmitForm(FormNew), 
   SubmitForm(RequestFormEdit)
   );
 
If(SharepointFormMode="EditForm", 
   ForAll(RenameColumns(Filter('Item List', SharePointIntegration.SelectedListItemID in 'Request ID'), "ID", "fID"), 
          Patch('Item List', 
                LookUp('Item List', ID = fID), 
                {Title: RequestFormEdit.LastSubmit.Title
                }
                )
          )
   

The only real difference here is that we are pulling out the extra collection stage and simply running the ForAll against what would have been the same results as the collection.  Not sure if you actually need that collection some place else, but let's ignore that for the moment.

Just so I didn't think I was going crazy...I just tested this out with the same concepts that you are using - and all worked fine.

Let's see what happens for you next.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.

View solution in original post

17 REPLIES 17
Super User
Super User

Re: Patch SPO list using records in a collection

@lumberjacklurch 

 

You're most likely getting that error in your ForAll formula on the Patch function.  In your formula you are providing a table rather than a record (as the error suggests).

Consider changing your formula to the following:

ForAll(col_ItemDetailEdit, Patch('Item List', Lookup('Item List', ID = col_ItemDetailEdit.ID), {Title: Title}))

I hope that is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.

Re: Patch SPO list using records in a collection

Thanks for the reply RandyHayes!

 

I got this to work for my Submit button:

 

ForAll(col_ItemDetailEdit, Patch('Item List', {ID: ID}, {Title: RequestFormEdit.LastSubmit.Title}))

 

It doesn't work in the SharePointIntegration OnSave though.  I know there are limitations on the SharePointIntegration properties.  Perhaps Patch is one of them?

Super User
Super User

Re: Patch SPO list using records in a collection

@lumberjacklurch 

Although that will work, for the most part, I wouldn't recomend that syntax.  The formula I mentioned previously will be accurate all the time as it is getting the record (with the Lookup) that is intended to be Patched.

 

What problem are you running into in the OnSave?  The Patch function will work there as well, but perhaps you are running into some other issue.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.

Re: Patch SPO list using records in a collection

I tried the Lookup code you provided, but I get the red squiggly line under the equals sign, with the error: "Invalid argument type.".  

Re: Patch SPO list using records in a collection

I changed the = to "in", and got rid of the error.  A problem with the Lookp though, Lookup only grabs one record, butg there is potential for more than one record needing processing.  That's why I wasn't using Lookup.  I tried Filter, but it gave me an error saying it was expecting a Record value and I was giving it a Table value.

Super User
Super User

Re: Patch SPO list using records in a collection

@lumberjacklurch 

 

I tried the Lookup code you provided, but I get the red squiggly line under the equals sign, with the error: "Invalid argument type.".

Yes, you actually need to change your formula to this - sorry I was focused on the Lookup part rather than the comparison of ID.

 

ForAll(col_ItemDetailEdit, Patch('Item List', Lookup('Item List', ID = col_ItemDetailEdit[@ID]), {Title: Title}))

 

 

 

I changed the = to "in", and got rid of the error.  A problem with the Lookp though, Lookup only grabs one record, butg there is potential for more than one record needing processing.  That's why I wasn't using Lookup.  I tried Filter, but it gave me an error saying it was expecting a Record value and I was giving it a Table value.

This is why you are using a ForAll - to process all the items in the col_ItemDetailEdit collection.  This will loop through each one and issue the Patch function for each one.

Patch cannot have a table as a source for a record.  You need to supply what record you want to patch.  In this case, with a lookup.  You would not want that part to have multiple records - and, it's quite impossible to have multiple records with the same ID.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.

Re: Patch SPO list using records in a collection

@RandyHayes 

I tried the last iteration, and it behaves the same as the code I mentioned earlier.  It worked for the Submit button, but not the OnSave property.  There must be some sort of restriction for that property.

Super User
Super User

Re: Patch SPO list using records in a collection

@lumberjacklurch 

So, what does your formula look like now in the OnSave action?  

Are you getting any particular error or what is not happening?

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.

Re: Patch SPO list using records in a collection

@RandyHayes 

 

Here's the code in the OnSave property:

 

If(SharepointFormMode="CreateForm", SubmitForm(FormNew), SubmitForm(RequestFormEdit));

 

ClearCollect(col_ItemDetailEdit, ShowColumns(Filter('Item List', SharePointIntegration.SelectedListItemID in 'Request ID'), "ItemName", "ItemDescription", "ItemQuantity","ID", "Title"));

 

If(SharepointFormMode="EditForm", ForAll(col_ItemDetailEdit, Patch('Item List', LookUp('Item List', ID = col_ItemDetailEdit[@ID]), {Title: RequestFormEdit.LastSubmit.Title})))

 

I get no errors, it just doesn't update the 'Item List' records.

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 (4,541)