Patch Collection To SharePoint List - Fastest Way?

[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

[WiZey]

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.

[CP153319]

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 

[CP153319]

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

[WiZey · Accepted solution]

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()" :

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

Hope this was helpful to you.

[CP153319]

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. 

[CP153319]

@WiZey - Unfortunately, your code does not work. 

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

The error thrown is:

[WiZey]

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.

[phipps0218]

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

[CP153319]

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