cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
stapes
Impactful Individual
Impactful Individual

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

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?

1 ACCEPTED SOLUTION

Accepted Solutions
v-xida-msft
Community Support
Community Support

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,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
v-xida-msft
Community Support
Community Support

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,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
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.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Users online (2,547)