cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Amik
Advocate II
Advocate II

Bulk patch creating duplicate records

Hi everyone,

 
 

I am using a method detailed by @mdevaney  to bulk update records using the PATCH function without using ForAll. My requirement does not need to use a traditional checkbox/toggle to identify which records should be updated in bulk. The PowerApp should take the field values from each gallery item and PATCH to their respective IDs in the data source.

 

My data source is PowerBI Integration Data and my update source is a SharePoint list. It is noted in the guidance referenced below that it is important to ensure that the column names from my updates collection matches the column names in my data source. As a result I have ensured my PowerBI fields match up the SharePoint fields.

 

Thus far this is working exceptionally quicker than a ForAll function - from 20 minutes for 40 rows to seconds for 300+ rows. However, I cannot determine why the code is creating new/duplicate items rather than updating existing items.

 

I have included an ID in my updates collection to ensure new rows are not added. I have also ensured the ID column name is the same in both the data source and the update source.

 

I feel that the mistake I am making is obvious. Any ideas?

 

Submit Button On Select:

 

Patch(DB_MarketFeedback, ShowColumns(colPowerBI, "MDStatus", "Title", "Comment"));

 

("MDStatus" is a text field and it is the ID which connects the data source to the updates collection)

 

App On Start

 

ClearCollect(colPowerBI, PowerBIIntegration.Data)

 


References:

https://powerusers.microsoft.com/t5/Building-Power-Apps/Poor-performance-when-patch-whole-collection...

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

5 REPLIES 5

@Amik 

 

From https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-patch

 

PatchDataSourceBaseRecordsTableChangeRecordTable1 )

 

In your example you give this:

 

@Amik wrote:
Patch(DB_MarketFeedback, ShowColumns(colPowerBI, "MDStatus", "Title", "Comment"));

If there are only two arguments you are calling version of Patch which creates a brand new record on the data source DB_MarketFeedback. You must give three arguments. The second argument must be the record you are trying to change, and 3rd argument is what you want to change it to.

 

In that docs link above, it is described somewhat in theoretical way but we noticed. that maybe it has no specific example of the syntax to create. In other words, nowhere does it say in the above docs link under Syntax, that you can do this:

PatchDataSource, NewRecordTable)

 

However the above variant does exist in Power Apps and you might be using it without realizing it. Here is one specific example we can give you that Patch does have the three argument version for editing records, and it does also have the two argument version for creating new records. You can see example of this in our recently tested response and accepted solution here

 

specifically:

 


@poweractivate  wrote:

Patch(TitleColumnPersonList
   ,
   {Title: _BaseRecord.Title, Column1: _BaseRecord.Column1, Person: TextInput1.Text}
)

in that example, notice we call Patch with just two arguments. a SharePoint List Data Source, and a record.

Notice when we use two arguments in that tested solution, it does actually create a BRAND NEW record in the data source. 

 

So your solution is to make sure to use three arguments in Patch. First argument should be the target data source to change the record in. The second argument of Patch should be the actual record you want to change from the data source. If it has to be looked up directly, use Lookup to find it - we happen to have an example of this kind of Lookup in actually that very same link above by the way. Finally, third argument should be a record of what are the columns and new values for those columns. 

 

So now, please check if above helps @Amik 

v-bofeng-msft
Community Support
Community Support

Hi @Amik :

Firstly,let me explain why you encountered this prbolem.

The point is Patch(DataSouce,ChangeRecords) is not a standard syntax.There is no introduction to it in the related documentation of Patch.

Secondly,If you want to update some records in batches with Patch, you should tell it where the table you need to modify is.

I assume I want to update the first and second records in the data source.The formula should be:

Patch(
 DB_MarketFeedback,
 FirstN(DB_MarketFeedback,2),
 ShowColumns(colPowerBI, "MDStatus", "Title", "Comment")
)

Best Regards,

Bof

 

Thanks @v-bofeng-msft  & @poweractivate  for your quick responses.

 

I need to update all records visible in the Gallery . I have taken your example and instead of running on the first or second records, I have revised it to run on all Gallery items.

 

Patch(
 DB_MarketFeedback,
 FirstN(DB_MarketFeedback,CountRows(Gallery1.AllItems)),
 ShowColumns(colPowerBI, "MDStatus", "Title", "Comment")
)

 

 

This seems to work but I am not sure if there is a better way instead of using FirstN? I also need to figure out how to insert new records if they do not exist, either via a separate button or within the same execution (without using ForAll).

@imranamikhan 

Is this

DB_MarketFeedback

 

the data source for the Gallery too?

 

Is that same SharePoint List also the one used in the Power BI report?

 

If so, we are not sure how there can be items in the Gallery that do not exist in DB_MarketFeedback - and same can be said about the Read Only Collection too if they match. 

 

Please indicate about what specific aspect may warrant the need of the batch inserting of new records specifically, or more detail about what specifically may not exist in  

DB_MarketFeedback

 that exists "somewhere else" in the App or the Power BI Report

Hi @poweractivate,

 

DB_MarketFeedback is a SharePoint list and it is not the data source for the Gallery.

 

imranamikhan_0-1608042479550.png


The data source for the Gallery is PowerBI Integration Data. I want to take data from PowerBI Integration Data and either insert records or update records to a SharePoint list (DB_MarketFeedback).

 

I am currently able to do this for individual records using:

 

 

If(CountRows(Filter(DB_MarketFeedback, MDStatus = ThisItem.MDStatus)) > 0,
Patch(
    DB_MarketFeedback,
    LookUp(DB_MarketFeedback,MDStatus=ThisItem.MDStatus),
    {
        Title: Dropdown_Status.SelectedText.Value,
        Comment: TextInput_Comment.Text
        }),
Patch(
    DB_MarketFeedback,
    Defaults(DB_MarketFeedback),
    {
        Title: Dropdown_Status.SelectedText.Value,
        Comment: TextInput_Comment.Text,
        MDStatus: ThisItem.MDStatus
        }))

 

 

I am also able to do this in Bulk using ForAll. However, the performance is too slow so I am trying to apply the method from @mdevaney

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Power Apps Ideas

Changes to Ideas Coming

We are excited to announce a new way to share your ideas for Power Apps!

Top Solution Authors
Top Kudoed Authors
Users online (1,559)