Lookup command is failing to find the record but we are not notified?

[stapes]

I am trying to perform some updates from the OnChange Action of a  particular field:

    Set(Updated, false);
    If(!IsBlank(txtActualQtyDelivered.Text),
    Patch(
        '[dbo].[TBL_JS_GOODS_IN_CONTROLSHEET]',
        LookUp('[dbo].[TBL_JS_GOODS_IN_CONTROLSHEET]',ID = SelectedItem.ID),
        {
        ActualQtyDelivered:Value(txtActualQtyDelivered.Text),
        UpdatedBy:User().FullName,
        UpdatedOn: Now()
        });
        UpdatesToControlSheet.Run();
        Notify("Updated");
        Set(Updated, true);
    )

The update is not happening in the database, so I have come to debug.

When I re-open the screen to edit, it has underlined the formula here, and when I hover over, the tooltip tells me:

     Server Response: Resource not found.

There are a number of issues here:

1) Can I trap for this somehow - so I can do something else here?

2) The record exists? Why would it not find it?

3) It is displaying the notification 'Updated' even though it hasn't. Why is it dropping through here when it has failed?

[v-xida-msft · Accepted solution]

Hi @stapes ,

Could you please share a bit more about the error message within your formula?

Do you mean that the LookUp formula could not find the specific record in your SQL Table?

Is the ID column a Primary Key column in your '[dbo].[TBL_JS_GOODS_IN_CONTROLSHEET]' table?

Please check if the result the SelectedItem.ID formula returned is a proper ID value rather than Blank value. On your side, please consider modify your formula as below:

Set(Updated, false);
If(
   !IsBlank(txtActualQtyDelivered.Text),
   If(
      IsBlank(LookUp('[dbo].[TBL_JS_GOODS_IN_CONTROLSHEET]',ID = SelectedItem.ID)),
      Notify("The record you want to update is not existed in your SQL Table, please check if you have provided proper ID vlaue to retrieve", NotificationType.Warning), // If the record you want to retrieve is not existed, it would show an warning message
      Patch(
           '[dbo].[TBL_JS_GOODS_IN_CONTROLSHEET]',
            LookUp('[dbo].[TBL_JS_GOODS_IN_CONTROLSHEET]',ID = SelectedItem.ID),
            {
              ActualQtyDelivered: Value(txtActualQtyDelivered.Text),
              UpdatedBy: User().FullName,
              UpdatedOn: Now()
            }
      );UpdatesToControlSheet.Run();Notify("Updated");Set(Updated, true)
    )
 )

In addition, you could also consider modify your formula as below:

Set(Updated, false);
If(
   !IsBlank(txtActualQtyDelivered.Text),
   If(
      IsBlank(LookUp('[dbo].[TBL_JS_GOODS_IN_CONTROLSHEET]',ID = SelectedItem.ID)),
      Notify("The record you want to update is not existed in your SQL Table, please check if you have provided proper ID vlaue to retrieve", NotificationType.Warning), // If the record you want to retrieve is not existed, it would show an warning message
      UpdateIf(
               '[dbo].[TBL_JS_GOODS_IN_CONTROLSHEET]',
               ID = SelectedItem.ID),
               {
                ActualQtyDelivered: Value(txtActualQtyDelivered.Text),
                UpdatedBy: User().FullName,
                UpdatedOn: Now()
               }
      );UpdatesToControlSheet.Run();Notify("Updated");Set(Updated, true)
    )
 )

Note: Please make sure you have defined a proper Primary Key within your '[dbo].[TBL_JS_GOODS_IN_CONTROLSHEET]' table, if not, the update operation would not work in your SQL Table.

More details about known limits with SQL Server connector, please check the following article:

https://docs.microsoft.com/en-us/connectors/sql/#known-issues-and-limitations

If the issue still exists, please consider re-create a new connection to your SQL Table from your app, then try above formula again, check if the issue is solved.

Best regards,