BUG REPORT: Imported Excel Table in Collection - Blank Date Field Can't be Patched

[seraph787]

Unable to patch a date from a datepicker or textbox into a blank date field of a record.

I created a new Excel spreadsheet to test the issue I ran into with my main app and source file just to be sure.  I can't find anyone else to even try and reproduce the issue, but I'm pretty sure it's a bug.

I created the table in Excel with a firstName, lastName, DOB (date field), and randomNumber.  I created 10 test records with all fields filled out.  Then I deleted the DOB for one of those records.

I then created a blank power app, connected to the Excel file through the OneDrive for Business Connectior and added the following OnVisible property:  ClearCollect(testing,Table1)

I created a gallery and set the Items property to: testing

It showed all 10 items.  I added a DatePicker and set its SelectedDate property to: DOB

I added a Button and set its OnSelect property to: Patch(testing,ThisItem,{DOB: DatePicker1.SelectedDate})

To ease the testing process, I created another gallery and set its Items property to: Filter(testing,lastName =Gallery1.Selected.lastName)

I then went in to test it.

I was able to change the DOB for all records that didn't have a blank DOB value.  When I tried to do it with the record where I had left the DOB blank, nothing happened.

In Excel, I verified that the format for all cells in the DOB column, both with a date and blank, were Date* format.

[v-xida-msft · Accepted solution]

Hi @seraph787,

Could you please share more details about your app's configuration?

Thanks for your feedback, I have made a test using the Patch fucntion within my app, and the issue is confirmed on my side.

As an alternative solution, you could also take a try with Update function or UpdateIf function to achieve your needs. I have made a test on my side, please take a try with the following workaround:

Set the OnSelect property of the "Update" button (Button control) to following formula:

Update(
     testing,
     ThisItem,
     {
      lastName:ThisItem.lastName,
      firstName:ThisItem.firstName,
      DOB:DatePicker1.SelectedDate
     }
)

or 

UpdateIf(
     testing,
     lastName=ThisItem.lastName&&firstName=ThisItem.firstName,
     {
      DOB:DatePicker1.SelectedDate
     }
)

The GIF image as below:

Best regards,

Kris

[seraph787]

Thanks for writing back, Kris. 

And thank you for the workaround.  Specifically, the UpdateIf function is the best solution for my purposes due to the high number of fields in each of my records for my actual app/table/collection.  Luckily, I already have a unique record ID field in that one, so I can reference that reliably as the condition parameter.

This is hugely helpful.