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
Super User
Super User

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
Super User
Super User

Hi @nickstef 

 

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

cwebb365
Super User
Super User

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
Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on August 17, 2022 at 8am PDT.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Users online (1,956)