Bulk Entry - Patch

[dyee]

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.   

[RandyHayes]

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

[dyee]

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]

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

[dyee]

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"

[dyee]

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

[RandyHayes]

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

[dyee]

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]

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

[RandyHayes · Accepted solution]

@dyee 

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

Sounds like you discovered that.