Bulk patch creating duplicate records

[Amik]

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/

[poweractivate]

@Amik 

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

Patch( DataSource, BaseRecordsTable, ChangeRecordTable1 )

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:

Patch( DataSource, 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]

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

[imranamikhan]

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

[poweractivate]

@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

[imranamikhan]

Hi @poweractivate,

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

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.