Sharepoint patch invalid argument (table)

[JPdarling9]

I am really hitting my head against the wall about this. Recently we moved our app's datasource from an excel file to a series of Sharepoint lists to improve access and performance amongst other reasons. In doing so, however, one of my screens for "approving" content for use is no longer working. Here's what I had before when this was based on Excel:

Patch(translationContent,reviewItemSelection,ForAll(reviewItemSelection,{status: "approved", updatedBy: First(userPrefID).username, updatedDate: Now(), approvedBy: First(userPrefID).username}));Clear(reviewItemSelection)

Basically what I do is "select" items from a gallery and add them to a collection (via a Checkbox in the Card and the Collect()/Remove() commands). The second step is to click a button which runs the PATCH command above to update the Status, ApprovedBy and Approved Date fields. Final step is to clear out the Collection which in turn clears all checkboxes.

What I need help with is making this process work in Sharepoint. As a simplified PATCH command, I'm trying just the following:

Patch(translated_database,reviewSelection,ForAll({Title:"",approvedBy:"testing"}))

The DS and Collection both changed names, but the process is the same. What I'm getting as my error though is: Invalid argument type (table). Expected a Record value instead.

Once I am able to get this working, I will ultiamtely want the Patch command to look something like this: 

Patch(translated_database,reviewSelection,ForAll({Title:"", Approver.email: User().email, Approved: Now(), status.ID: 1}))

The SP Columns are as follows: Approver (Person), Approved(Date/Time), Status(Lookup to another list;ID 1 = "approved").

I really have no idea what I need to do here to make this work, so any thoughts would be much appreciated. And just to clarify, the Title field is not  required on any of my lists.

[hpkeong]

Hi @JPdarling9

Patch(Student, StudentList, ForAll(AttendanceGallery.AllItems, {StuAtt: Toggle2.Value}))

Above sample codes are something I have written for something else.

After comparing with yours, I suspect that your

- reviewitemselection may need further checking

If it comes from a Gallery (I suppose yes0, then, write as

ForAll(Gallery.AllItems,....)

Your Original Text

Patch(translationContent,reviewItemSelection,ForAll(reviewItemSelection,{status: "approved", updatedBy: First(userPrefID).username, updatedDate: Now(), approvedBy: First(userPrefID).username}));Clear(reviewItemSelection)

Please try and I hope your wall will not collapse if this helps.

[JPdarling9]

Hi, I tried your suggestion and wrote as this: Patch(translated_database,reviewSelection,ForAll(Gallery2.AllItems,{Title:"translated_database", approvedBy:"Joe"})) In this I am populating Gallery2 with the contents of the reviewSelection collection. I am then able to process the Gallery2.AllItems as you said. I still am getting the same error though. I believe the issue is that it doesn't like "reviewSelection" (collection / table of records) as the Record input. It's just really annoying though that it worked perfectly with Excel. What else could it be?

[hpkeong]

HI @JPdarling9

Patch(translated_database,reviewSelection,ForAll(Gallery2.AllItems,{Title:"translated_database", approvedBy:"Joe"}))...

please change to:

Patch(translated_database, ForAll(Gallery2.AllItems,{Title:"translated_database", approvedBy:"Joe"}))

- that is, take away the reviewSelection.

Patch(DS, ForAll(Gallery.AllItems, {...........................all the fields......}))

Please try

[JPdarling9]

Still the same error: Invalid argument type (Table). Expecting a record value instead. Is there something I need to do to force passing Records rather than a table?

[hpkeong]

Hi

Maybe something went wrong with database. I can't see the DS unless with screenshot and sample DS.

Mayyo refer to my previous post on:

Useful features of PowerApps (15)：

https://powerusers.microsoft.com/t5/PowerApps-Forum/Useful-features-of-powerapps-15-Multiple-Batch-R...

Maybe you can get some idea out of my sharing.

I really wish to help but can;t figure out.

Have a nice day.

[JPdarling9]

Hi, I have used the following with great success trying to Patch to other datasources just as a test: --- Patch(translatedStatus,Filter(translatedStatus,ID=1),ForAll(translatedStatus,{statusCN:"你好"})) --- Patch(translatedStatus,Filter(translatedStatus,ID=ddUpdateStatus.Selected.ID),ForAll(translatedStatus,{statusCN:"你好"})) --- Patch(translatedCategory,Filter(translatedCategory,ID=1),ForAll(translatedCategory,{categoryCN:"你好"})) I then tried a variation of this, in the correct context with my database List and it works: Patch(translated_database,reviewSelection,ForAll(reviewSelection,{cn:"你好"})) So it seems that the error is being thrown because the particular columns I'm trying to patch are Lookups. The error does not occur and works as it did in excel with all columns that aren't lookups. So the question is how do I properly pass the lookup value for the following columns: --- status(ID=1,status=approved) --- Approver(person) Here's my first pass: Patch(translated_database,reviewSelection,ForAll(reviewSelection,{Approved:Now(),Approver.email:User().email,status.TEXT:"approved"}))

[JPdarling9 · Accepted solution]

I cracked it. I ultimately had to do some lookups and write a ClearCollect statement to grab an existing record that's been approved already to grab the "approved" record (oDataType,ID,status). Here's the final command: Patch(translated_database,reviewSelection,ForAll(reviewSelection,{Approved:Now(),Approver:LookUp(collectedUsers,user.Email=User().Email).user,status:First(statusApproved)}));Clear(reviewSelection) ClearCollect for approved status, runs at Page onVisible: ClearCollect(statusApproved,First(Filter(translated_database,status.Id=1)).status) thanks @hpkeong for your help and hope this can help some others.

[hpkeong]

Hi @JPdarling9

I can now see what is your error.

Approver:LookUp(collectedUsers,user.Email=User().Email).

You are looking for a SINGEL VALUE for Approver, Not a Table, where it confuses the Patch function within {....}.

Glad to have you finally get it done, though I can;t really help much.

Please do check for my coming blog on Multi-Patch and Single Record Update (use UpdateIf), if not delay, in Tuesday morning, at PowerApps Community Blog, where I will break into few parts to explain for to use

- Patch

- ForAll

- UpdateIf, etc.

  to accomplish something like you are doing, regardless where the DS from.

Have a nice day.

[somebodycool]

hello im  wondering if i could use the referenced formula some of you provided as the following?

Patch('Kristina''s list',ThisItem.'Project Lead', ForAll(ThisItem.'Project Lead',{status:"approved", approvedBy:First(userPreflD).username}))

that is my actual formula i placed in powerapps inside the Update property for my Project Lead_DataCard1 see screen shot for reference. I have a power automate flow that has an approval that grabs the user name for who approves the flow, thinking that is where the status: approved comes in?? 'Project Lead' is my list column name that generates the user name using my sharepoint datasource 

I need something that will work to that isn't generating a table formula as im getting an expected record error

I am a newb with power apps still, havent learned all the different functions, properties etc. and how to use them all.