cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dyee
Resolver II
Resolver II

Bulk Entry - Patch

Here is the goal.  I have two SQL tables with the same fields.  The goal would be to create a collection of the first table when the app loads.  I did this by collect(SQL_Table,Col_SQL_Table) on the start.  Then I want to transfer the selected items to a temp table.  I do this by creating a gallery w/ a checkbox.  On the checkbox the code is Patch(Col_SQL_Selected,Defaults(Col_SQL_Selected),{thisitem}).  It works fine up until this point.  

 

The issue occurs when I try to patch Col_SQL_Selected into my second SQL table.  I get the error can't find ID the ID #.  This makes sense there is no ID number in this.  However, I was under the impression the Patch(datasource,collection) combo would create new lines.   

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User
Super User

@dyee 

Was going to be my next question...did you have a Required Title column?

Sounds like you discovered that.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

9 REPLIES 9
RandyHayes
Super User
Super User

@dyee 

Patch does not allow what you are doing. 

You need to utilize collect for this:

   Collect(dataSource, collection)

 

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

I might be doing a bad job explaining this.  I know that you can do Patch(datasource,collection) and do a bulk update.  Here are a few examples of this.

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

RandyHayes
Super User
Super User

@dyee 

It is not correct in your scenario.  That will NOT create new records...it will Patch existing records.

 

You can only do that kind of Patch if your collection has an ID column in it to identify the column to be patched.  All of yours are based on Defaults...there will be no identifying in the Patch....and the error you are seeing.

 

If you want to do a bulk update with new rows (as you are), then you need to use Collect(dataSource, yourCollection)

 

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
dyee
Resolver II
Resolver II

The collect works but I'm running into problems getting the collect going.  I've run into two problems:

 

#1 - The SQL table I have has funky headers like Job # and things like that.  I tried to fix the headers but when I try to bring it into a collection it doesn't work. 

#2 - I tried to just bring them in and switch the header name.  I did showcolumn and then rename column to make sure everything matches up with my sharepoint list (I couldn't get it to work w/ SQL).  Unfortunately, now it won't patch saying, "Specified Column Job does not exist, closet column is Job"

Bump.  Anyone have any links/advice on how to do this collect patch?  I've run into so many roadbumps. 

RandyHayes
Super User
Super User

@dyee 

Can you provide more details on your columns between the two sources and also what you current formula looks like that you are having problems with?

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Sure.  Here is what I have in the app right now.  

 

OnStart--> 

Collect(Col_Jobs,'[dbo].[FIN_Freight_JobsSimp]');
Collect(Col_Active,'[dbo].[FIN_Freight_JobsSimp]');
Clear(Col_Active)

 

Logic: I want to make two identical collections.  The second one (Col_Active) should be blank.  

 

I have a gallery with all my Col_Job items.  When someone selects a checkbox it activates this code:

Patch(Col_Active,Defaults(Col_Active),ThisItem)

This will create a new line in the Col_Active gallery.  From here, I created a temporary button that creates the collection Col_Active1.  This is Col_Active but with a showcolumn to shrink it only to 1 column (I will expand this until i have everything then remove it).  

Collect(Col_Active1,ShowColumns(Col_Active,"JOB"))

From here, this matches up with a sharepoint list i created explicitly to match up the columns so it can bulk submit.  As of right now, it only has one column JOB.  

 

Here is the error.  

dyee
Resolver II
Resolver II

Update - I think i figured it out.  It was because I had to do title for the first one.

RandyHayes
Super User
Super User

@dyee 

Was going to be my next question...did you have a Required Title column?

Sounds like you discovered that.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Microsoft Ignite 768x460.png

Find your focus

Explore the latest tools,training sessions,technical expertise, networking and more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (1,655)