Unable to remove a row from SQL

(0) Share

Report

Posted on by WillPage

2,307 Super User 2025 Season 2

I'm trying to use the following formula to remove a row from SQL:

Remove(
 '[dbo].[TempPriceBook]',
 LookUp(
 '[dbo].[TempPriceBook]',
 'Supplier Code' = 2684 && ManufactureMarking = "#00/1" && UploadedAsInt = 202003270149
 )
)

The table [dbo].[TempPriceBook] has a primary key that comprises those three columns; [Supplier Code], ManufactureMarking and UploadedAsInt.

In reality the data being used for the LookUp is dynamic based on a selected item in a gallery, but for the purpose of testing I hard-coded some values into the formula that I verify are good (it's the first item in the table).

When I run the formula it completes successfully, in as much as there's no run time error returned by the connector, but the record remains in the table: The delete doesn't actually happen.

I'm pretty sure my formula is OK, because removing rows using Remove() is something I've done hundreds of times in formulas, but I'd happily be corrected on it. More the question, is what's wrong with my SQL database, or at least how can I troubleshoot without any runtime error being returned?

Whether or not this is significant, if I isolate the Lookup into the Text property of a label, the LookUp works fine:

But in the OnSelect of a button, we didn't find any data. Same formula!

I have also tried First(Filter()) instead of LookUp() with the same result.

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (0)

WarrenBelz 153,026 Most Valuable Professional on at

Like (0)

Report
Hi @WillPage ,
You might try
RemoveIf( '[dbo].[TempPriceBook]', 'Supplier Code' = 2684 && ManufactureMarking = "#00/1" && UploadedAsInt = 202003270149 )

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Was this reply helpful? Yes No
v-xida-msft on at

Like (0)

Report
Hi @WillPage ,

Do you mean that the record in your SQL Table could not be removed when you execute the Remove formula?

Based on the formula that you provided, I could not find any syntax error with it. Please also consider take a try with the RemoveIf function to achieve your needs:

RemoveIf( '[dbo].[TempPriceBook]', 'Supplier Code' = 2684 && ManufactureMarking = "#00/1" && UploadedAsInt = 202003270149 )

or

RemoveIf( '[dbo].[TempPriceBook]', PrimaryKeyColumn = LookUp( '[dbo].[TempPriceBook]', 'Supplier Code' = 2684 && ManufactureMarking = "#00/1" && UploadedAsInt = 202003270149 ).PrimaryKeyColumn )

Note: The PrimaryKeyColumn represents the Primary Key you defined in your SQL Table.

In addition, you could also consider take a try with the "Monitor (experimental)" tool in your canvas app. You could open it in another tab, then execute your Remove or RemoveIf function within your canvas app again, then you could track the execution processing for the Remove formula:

Please check the following blog for more details:

https://powerapps.microsoft.com/en-us/blog/introducing-monitor-to-debug-apps-and-improve-performance/

Best regards,

Was this reply helpful? Yes No
WillPage 2,307 Super User 2025 Season 2 on at

Like (0)

Report

I stopped work on this issue because it was frustrating me and came back to it later. After closing the app and reopening it again in the studio the Remove() function is working fine. I'll put that down to a bug. Wasted a lot of time on that one - sometimes it's the basics you've got to check first!

Was this reply helpful? Yes No