cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nickstef
New Member

Patch function patches on wrong duplicate record in an Excel file in OneDrive

Hello everyone,

 

I start building an app for a company.

This app uses a gallery (named Gallery1), which is connected with a specific Excel file in OneDrive as a data source.

Excel file is named Inventory_2021. The first column of Inventory_2021 is named ITEM.

The user can filter Gallery1 (rows of Inventory_2021) through a text input, using a value of the ITEM column.

Everything is working fine, except of when there are duplicates on ITEM column.

 

When I save a record with the same ITEM value, patch function patches only on the first record that finds on the ITEM column with the same ITEM value. It is impossible to patch the second or third record with the same ITEM value. Patch keeps on patching only the first record, even if I use multiple filters to identify the exact record with the values of the other columns. I use a button for saving on a desired existing record in Inventory_2021.

 

My variables are all text type. I don't think the problem is the type of data.

If I use Form instead of a Gallery, the same thing happens.

 

I used also SubmitForm and UpdateIf but they did the same.

I used also lookup function and filter function to identify the proper record, but they didn't work.

I also checked ThisItem and Gallery1.Selected properties, but they seem fine.

 

If the value of ITEM column is unique, patch is properly working.

 

I am looking forward for your replies,

Thank you very much

 

 

Inventory_2021 (Excel file):

 

Inventory 2021 (Excel file).PNG

 

Search Gallery1:

 

Search Gallery1.PNG

 

 

Save Button:

 

Save Button.PNG

 

 

Alternate Code for Search Gallery1:

 

Alternate Code for Search Gallery1.PNG

 

4 REPLIES 4
zmansuri
Super User
Super User

It doesn't matter how you put your search code.

On your patch code

Patch(ListName,Record To Update,{Update})

 

This bold part is important. Usually there is a unique id here. If you can number your excel data with unique id it will work. If not it will always patch the first matching record it finds

StalinPonnusamy
Community Champion
Community Champion

Hi @nickstef 

The patch command needs to be updated to get the exact record otherwise it creates new one

 

Patch(SourceName, If(IsEmpty(Lookup(SP List)), Defaults(SPList),Lookup(SP List)),
{
column: data
}

)

 

StalinPonnusamy
Community Champion
Community Champion

Hi @nickstef 

 

One more thing, Not sure why Patch and Lookup is different Source

cwebb365
Power Participant
Power Participant

I'd just use a ForAll(datasource,ITEM = Item_1.Text,Patch(your patch logic)) That should go through all 3 and patch them.  You can use ThisRecord.Item in the patch for the match..... being excel thou not sure how it would treat it, might still update the same one but worth a shot. Other than that you may need to get some form of Unique identifier. When using excel there might be a rowID or something you can reference in the forall to use for the record match instead as the unique value of records so you can update each one. 

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

secondImage

Power Apps Community Call

Please join us on Wednesday, October 20th, at 8a PDT. Come and learn from our amazing speakers!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

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