cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ChrisHiggins
New Member

Patch a Collection to SQL

 

Hi,

 

I am trying to push a collection to a database in SQL. In the past I have done this via a SubmitForm but I would like the user to be able to create a list of items for review before moving all to the collection. So a form doesn't work. I plan on holding the items in a collection, and then when the user wants to save them, it will send the whole list to the database. I can't get my head around whether Patch would work for me! Below are a sample list and the table I am trying to patch to.  2018-10-25 09-39-57-DHRApp2 - Saved (Unpublished) - PowerApps.jpg2018-10-25 09_44_45-MCNSQL01_OPSDATASERVER.DHR - dbo.DHRTrackerDetails - Microsoft SQL Server Manage.png

 

 Any Help would be great

EDIT: I should also add I want to add as new entries. I do not want to overwrite anything.

EDIT2: Ignore me, I didn't realise Collect works here... 

 

Thanks

3 REPLIES 3
mr-dang
Community Champion
Community Champion

Hi @ChrisHiggins,

I just answered a very similar question to yours:

https://powerusers.microsoft.com/t5/General-Discussion/Method-for-collect-all-objects-in-array/m-p/1...

 

Big idea: make sure the columns in the collection and datasource match exactly, then you can use Collect(datasource,tempcollection) to write the whole thing back.

Microsoft Employee
@8bitclassroom
v-sheyu-msft
Community Support
Community Support

hi @ChrisHiggins,

Do you want to submit a Collection to your SQL table?

 

I made a test.

1.Create a collection.

2.PNG

2.Create a table in sql

4.PNG

3.Add a button in powerapps.

3.PNG

 

Set the  Onselect property of the button to:

ForAll(mycollection,Patch('[dbo].[Table_1]',Defaults('[dbo].[Table_1]'),{DHRTrackerDetailsID: DHRTrackerDetailsID,
NCID: NCID,RejectedDoc: RejectedDoc,ReasonForReject:  ReasonForReject,RejectComment: RejectComment}))

Note: Please make sure you have defined a Primary Key within your SQL table.

 

Regards,

Eason

Community Support Team _ Eason YU
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

19 May 2020, works great!!!! Thanks!!!!

Helpful resources

Announcements
October Events

Mark Your Calendars

So many events that are happening this month - don't miss out!

Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Users online (3,819)