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
PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Top Kudoed Authors
Users online (3,801)