Answered

Patch Collection To SharePoint List - Fastest Way?

(0) Share

Report

Posted on by CP153319

Here is my scenario:

I am creating an inspection app which has 100 inspection questions in a SharePoint list. This same 100 question inspection will happen over and over again so I can't save inspection results back to the original list of questions.
When the app opens I use the "OnStart" property to grab all 100 inspection questions into a collection
At some point in the process, all 100 questions are patched from the collection into another, separate SharePoint list using a "ForAll" formula (something like "ForAll(collectionInspection, Patch(sharePointDatasource, Defaults(sharePointDatasource), {columns: data etc. etc.})

I find that patching the 100 questions from the collection to the SharePoint list takes a LONG TIME. Is there a faster way to do this?

Thank you

Categories:

Building Power Apps

I have the same question (0)

All responses (11)

Answers (1)

Sort by

WiZey 3,023 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
Hello @CP153319 ,

Try swapping the "Patch()" and "ForAll()".

Patch( source, ForAll() )

Patching a table takes way less time than patching one element at a time.

Hope this was helpful.

Was this reply helpful? Yes No
CP153319 on at

Like
a
(0)

Report
Copy link

Link copied!
Would you mind replying back with how you would write the formula if:
The collection is called "collectionInspectionQuestions" and it has columns named "Title" and "Question"
The SharePoint list being patched is called "Completed Inspections" and has columns named "Title" and "Questions"
In the mean time, I'll mess around with your suggestion... but it isn't clear right now how that would work.

@WiZey

Was this reply helpful? Yes No
CP153319 on at

Like
a
(0)

Report
Copy link

Link copied!

@WiZey - This board is asking me if your solution is the acceptable solution but I'm waiting for you to give an example of how the code should look. Can you let me know if you don't know the answer just so I can close this thread or leave it open?

Was this reply helpful? Yes No
Verified answer

WiZey 3,023 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!
Hello @CP153319 , sorry for the delay.

This blogpost gives a good example how to proceed:

https://www.matthewdevaney.com/patch-multiple-records-in-power-apps-10x-faster/

Basically, calling "Patch()" once works way faster than calling "Patch()" inside a "ForAll()", so you want to try and gives the "Patch()" the whole table of records you want to modify.

Assuming your collection doesn't have the same structure as your sharepoint datasource, and you want to create a hundred or so new records, the code below would be the closest to your solution :

Patch( SharePointDataSource, ForAll(collectionInspection, Defaults(SharePointDataSource) ), ShowColumns(collectionInspection, "Column1", "Column2", ...) )

Here the "Patch()" :
Edit the SharePointDataSource
"ForAll()" return a table equivalent to collectionInspection in size, full of "Defaults()" so "Patch()" understand it must create new records
"ShowColumns()" allows you to extract the columns from your collection which correspond to your SharePointDataSource

Hope this was helpful to you.

Was this reply helpful? Yes No
CP153319 on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you for the reply - I will test this today and mark your response as the solution if it works, which I'm sure it will.

Was this reply helpful? Yes No
CP153319 on at

Like
a
(0)

Report
Copy link

Link copied!

@WiZey - Unfortunately, your code does not work.

Here is the code (blue boxes to hide specific names):
The error thrown is:

Was this reply helpful? Yes No
WiZey 3,023 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

Are you perhaps missing a closing bracket to close the "Patch()"?

If not, could you give more context about your issue. Where did you put the code? In which control/property? How do you call it?

Can you verify if "ForAll()" and "ShowColumns()" work individually? They should both return a table.

To test on my side, I put a similar code in the "OnSelect" of a button. Here is how it looks:

This did duplicate my SharePoint list and re-create all existing records while only taking the "Title" column, so I think we're only missing a little detail somewhere in your code.

Was this reply helpful? Yes No
MVP-Phipps 3,521 Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!

You are missing a closing bracket. Add a bracket and this will work!

Was this reply helpful? Yes No
CP153319 on at

Like
a
(0)

Report
Copy link

Link copied!

Just curious, how does the "showcolumns" work when the SharePoint list being patched has column names that do not match the names of the column in the collection? For example, if I have a column named "Sports" in the collection, it will patch the "Sports" column in the SharePoint list. What if the "Sports" column in SharePoint is not named "Sports" but something else like "Football"?

I guess what I'm saying is how does the patch statement know which SharePoint column to patch from the collection if the collection column names don't match the SharePoint column names?

This is the most confused I've been in a LONG TIME. Feels good... LOL

Was this reply helpful? Yes No
WiZey 3,023 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
After some test on my side, it seems the "Collect()" itself will detect if the column exist or not in the list, and throw an error if it does not. This could need some more testing to see how far it can go.

By the way, it's absolutely possible to rename a column with "RenameColumns()". You can then mix up the two functions like this:

https://docs.microsoft.com/en-us/power-platform/power-fx/reference/function-table-shaping

RenameColumns( ShowColumns( Collection, "Sport" ), "Sport", "Football" )

First, "ShowColumns()" will retrieve the "Sport" column. Then "RenameColumns()" will rename "Sport" as "Football". Simple as that.

It's also possible to rename multiple columns at once.

P.S.: After one quick test, I'm proud to announce that renaming the column to match the SharePoint list does work.

Was this reply helpful? Yes No