Skip to main content

Notifications

Power Apps - Building Power Apps
Answered

Patch Collection To SharePoint List - Fastest Way?

(0) ShareShare
ReportReport
Posted on by

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:
  • CP153319 Profile Picture
    CP153319 on at
    Re: Patch Collection To SharePoint List - Fastest Way?

    Amazing work!! Thank you for pointing that out. I'm sure this thread will help many people!!!

  • WiZey Profile Picture
    WiZey 3,023 on at
    Re: Patch Collection To SharePoint List - Fastest Way?

    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. 

  • CP153319 Profile Picture
    CP153319 on at
    Re: Patch Collection To SharePoint List - Fastest Way?

    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

  • MVP-Phipps Profile Picture
    MVP-Phipps 3,447 on at
    Re: Patch Collection To SharePoint List - Fastest Way?

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

  • WiZey Profile Picture
    WiZey 3,023 on at
    Re: Patch Collection To SharePoint List - Fastest Way?

    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:

     

    WiZey_0-1661841794052.png

     

    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.

  • CP153319 Profile Picture
    CP153319 on at
    Re: Patch Collection To SharePoint List - Fastest Way?

    @WiZey - Unfortunately, your code does not work. 

     

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

    CP153319_0-1661793816976.png

    The error thrown is:

    CP153319_2-1661793961463.png

     

     

  • CP153319 Profile Picture
    CP153319 on at
    Re: Patch Collection To SharePoint List - Fastest Way?

    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. 

  • Verified answer
    WiZey Profile Picture
    WiZey 3,023 on at
    Re: Patch Collection To SharePoint List - Fastest Way?

    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 Profile Picture
    CP153319 on at
    Re: Patch Collection To SharePoint List - Fastest Way?

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

  • CP153319 Profile Picture
    CP153319 on at
    Re: Patch Collection To SharePoint List - Fastest Way?

    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 

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,304

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,703

Leaderboard