cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dyee
Helper I
Helper I

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 III
Super User III

@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. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

View solution in original post

9 REPLIES 9
RandyHayes
Super User III
Super User III

@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. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

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 III
Super User III

@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. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!
dyee
Helper I
Helper I

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 III
Super User III

@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. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

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
Helper I
Helper I

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

RandyHayes
Super User III
Super User III

@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. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

View solution in original post

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Top Kudoed Authors
Users online (45,496)