Patch function duplicates records

(0) Share

Report

Posted on by PowFlow

Hello,

This subject has already been mentioned but I can't find the right answer :

https://powerusers.microsoft.com/t5/Building-Power-Apps/Duplicate-records-adding-from-collection-to-database/m-p/897661#M284903

https://powerusers.microsoft.com/t5/Building-Power-Apps/Bulk-patch-creating-duplicate-records/m-p/771407#M245798

I have an app that's supposed to add/remove text records in an Excel Sheet in OneDrive.

To do that in a batch way, I store my sheet in a collection then Patch that collection to the original data source.

Data source Sheet : "Fériés" with column "Dates"

Collection : "JoursNonTravailles" with column "Dates"

OnStart : ClearCollect(JoursNonTravailles;Fériés.Dates)

Then I do the changes. Then Patch :

Patch(Fériés;Fériés.Dates;JoursNonTravailles.Dates)

It then reuploads the entire collection to the sheet, no matter if the records already exist or not.

I have a feeling I would need to clear the whole sheet before patching my collection into it, but I don't know the right formula to do so.

Thanks a lot in advance for your help,

PowFlow

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (1)

Sort by

CNT 10,921 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@PowFlow I tried the same and it just seems to update rather than reload the whole thing. How is the structure of the Excel file?

Was this reply helpful? Yes No
v-qiaqi@microsoft.com Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @PowFlow,

Based on the issue that you mentioned, do you want to edit records in the Excel table?

Could you please tell me that you wan to duplicate the records or update the records?

The formula you provided will update the Fériés with the duplicated records. If you just want to make the whole collection cover the records in the Excel table, I think you should change your problem-solving ideas.

If you want to update some certain records, you could use the Gallery and the Edit form.

Add a Gallery and an Edit Form

Set the Gallery with your Excel table name

Set the Gallery OnSelect as below:

Navigate(EditForm1)

Set the Edit Form Item as below:

Gallery1.Selected

Add a submit button and set the OnSelect as below:

SubmitForm(EditForm1)

Once you select a record from the Gallery, it will navigate you to the edit form and you could update the date.

Check if it could help you.

Regards,

Qi

Was this reply helpful? Yes No
PowFlow 6 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks for your replies !
Here's the Excel Table : (Dates in text format)

@v-qiaqi-msftthe issue is that I only add / delete records, it's not really an update. I think the only way to do it right is to remove all the records in the table and then Patch the collection in the Excel Sheet.

To simplify, I chose to update directly the data source rather than the collection, it takes a little more time but it is more dynamic. And it works 🙂

Was this reply helpful? Yes No
Verified answer

v-qiaqi@microsoft.com Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @PowFlow,

Have your solved you problem?

I have a test on my side, please take a try as below.

1). Set the OnVisible property of the current screen as below:

ClearCollect(Tem,Table1)

2). Add a Gallery, insert a Text input and X icon, set the Items property of the Gallery as below:

Tem

Set the OnSelect property of the X icon as below:

Remove(Tem,ThisItem) // You could click it to delete a record from the collection

3). Add a + icon outside the Gallery, set the OnSelect property of the Button control as below:

Collect(Tem,CountRows(Tem)) // You could click it to add a record to the collection

4). Add a Button and set the OnSelect property:

RemoveIf(Table1,true) // You could click it to delete all the records from the Excel table

5). Add a Button and set the OnSelect property:

ForAll(Gallery4.AllItems,Patch(Table1,Defaults(Table1),{Code:TextInput3.Text}))

Check the Gif as below:

Regards,

Qi

Was this reply helpful? Yes No
PowFlow 6 on at

Like
a
(0)

Report
Copy link

Link copied!
Thank you for your very detailed answer @v-qiaqi-msft !

It works fine for me ! However, doing it I realized it was taking longer to delete / reupload the whole Excel Table than to upload / remove the records one by one depending on the OnSelect property.

I used that code to do it directly in the Excel Sheet :
If( CountRows( Search("My Excel Table";Text(ThisItem.Value;ShortDate);"Dates"))>0; Remove("My Excel Table";Search("My Excel Table";Text(ThisItem.Value;ShortDate);"Dates")); Collect("My Excel Table";{Dates : Text(ThisItem.Value;ShortDate)}) )

However I'm kind of new to using the power platform, is there a better way to use lists and tables than Excel 365 ? Like Sharepoint Lists for example ? I see them quoted a lot in the topics.

Thanks again for your answer ! I've accepted it as a solution as it works fine for me 🙂

Was this reply helpful? Yes No