Power Platform Community Forum Thread Details

Hi gang, hope everyone is well.

We are using a canvas app in conjunction with an Azure SQL server and have noticed some intermittent failures when patching changes back into a database row

The rough architecture is that we have a gallery that is populated with data from a filtered SQL data source added to the app.

We have a button that initially allows us to add rows to this real time and this seems to work without issue. This simply uses the below type of approach but this is obviously redacted.

Patch( SQLDataSource, Defaults(SQLDataSource), {ID: GUID() )

This enters a new row into the DB with a specific GUID as "ID" (to allow us to reference it later, below) and every other data column in that new row will be the SQL defaults. The gallery subsequently refreshes to show this new row.

The issue we are having is when we make edits to this now displayed row in our gallery.

Below is the code that we are using to achieve this, again redacted. This is triggered from an "OnSelect" action from a button in each row.

If(
IsError(
Patch(
  SQLDataSource,
  LookUp(SQLDataSource,ID=ThisItem.ID),
  {
DateFrom: datepickerFromDate.SelectedDate, DateTo: datepickerFromDate.SelectedDate
  }
  )

),

Notify("Failure"),NotificationType.Error);,

Notify("Success",NotificationType.Success);

I had hoped that using the "IsError" condition along with the patch command, that the query would wait for a response and display "success" or "failure" accordingly but it isn't working that way.

It always displays success yet sometimes, this patch isn't working at all and I don't understand why.

I guess it could be a performance thing but if I could even find a better way to work out if the patch succeeded that would be a start. Any thoughts?

Also, is there a better way for me to update these rows? I had thought patch was a nice and clean way to achieve this but these missed inserts with no record that they have failed is causing me big issues

I know I could create a flow with a sql action to insert/update and call that flow from the app etc but that seems like a huge amount of calls to achieve one simple thing.

Am I missing something?

Appreciate any guidance

Categories:

Building Power Apps

Hi,

After you insert the new row, are you doing a Refresh(MyDataSource), if not please do, this way the patch, which will still be looking at that Source, actually sees the row. Depending on refresh times (if you aren't doing it), its possibly it just doesn't see it. So essentially your ThisItem.ID may be blank, which btw might cause it to patch the 1st row in the list versus the correct one.

The Patch itself will considered working if it sends it and no errors happens on the back end. But if for some reason it doesn't see the row, it will succeed.

So please add the Refresh, if you don't have it.

You can also run Monitor (if this issue happened frequently enough) to capture why, and also, you could put in a debug (hidden) label, that you assign ThisItem.ID to make sure there is a value and what value it thinks it is.

The only other thing I can think of is somehow it doesnt think a row is Selected and therefor fails.

Now, how can you tell if it worked?

You can change your Patch to

either option

Set(A, Patch())

ClearCollect(MyPatchResult, Patch())

Then you can check the values (ID etc of the variable A) or check the collection by looking at First(MyPatchResult).ID etc

You can check these to validate it actually updated something.

But I am hoping when you add the refresh it works.