Patch SPO list using records in a collection

[lumberjacklurch]

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

[RandyHayes]

@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.

[lumberjacklurch]

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?

[RandyHayes]

@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.

[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.".  

[lumberjacklurch]

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.

[RandyHayes]

@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.

[lumberjacklurch]

@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.

[RandyHayes]

@lumberjacklurch 

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

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

[lumberjacklurch]

@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.