Unanswered

SQL error message when applying Function IsBlank

(0) Share

Report

Posted on by UlricusR

Hi, I have a Dataverse table holding a number of Risks with three lookup columns "Risk-Requirement-Association", "Risk Modeling Activity" and "Risk Testing Activity". I need to check if the Risk has at least one of these (optional) lookups set. For this, I added a column "Is Orphan" with the following function, which is accepted by Power Apps (I can hit the Save button):

And(IsBlank('Risk-Requirement-Association'),IsBlank('Risk Modeling Activity'),IsBlank('Risk Testing Activity'))

After saving and a couple of seconds, this error message appears:

Sql error: Generic SQL error. CRM ErrorCode: -2147204784 Sql ErrorCode: -2146232060 Sql Number: 137

Google didn't really help, it just talked about a missing variable definition.

Any ideas out there? Thanks you!

EDIT 7-Dec-22: This only happens if the column checked by the IsBlank function is a Lookup column.

Categories:

Microsoft Dataverse with Power Apps

Screenshot 2022-1...

I have the same question (0)

All responses (10)

Answers (0)

AhmedSalih

6,678 Moderator on at

Like (0)

Report

Hello, @UlricusR , try IsEmpty instead of IsBlank.

If my reply helped you, please give a 👍 , & if it solved your issue, please 👍 & Accept it as the Solution to help other community members find it more.

I am primarily available on weekdays from 6-10 PM CT and 5-10 PM CT on weekends.

Visit my Blog: www.powerplatformplace.com

Was this reply helpful? Yes No

UlricusR 35 on at

Like (0)

Report

Hi @AhmedSalih , I'd love to, but IsEmpty is unfortunately not available in formula columns, see https://learn.microsoft.com/en-us/power-apps/maker/data-platform/formula-columns#functions.

Was this reply helpful? Yes No
AhmedSalih 6,678 Moderator on at

Like (0)

Report
@UlricusR, If you need to check for at least one of those values are blank, try this:

If(Or(IsBlank('Risk-Requirement-Association'),IsBlank('Risk Modeling Activity'),IsBlank('Risk Testing Activity')),true,false)

Was this reply helpful? Yes No
UlricusR 35 on at

Like (0)

Report
Hi @AhmedSalih , I'd like to take the discussion up again, as the topic still is open. The proposed IsBlank('...') function does not work with lookup fields, i.e., if in
IsBlank('Risk-Requirement-Association')
the Risk-Requirement-Association is a Lookup to another Table (which it is in my case), I get the above mentioned error (Sql error: Generic SQL error. CRM ErrorCode: -2147204784 Sql ErrorCode: -2146232060 Sql Number: 137) when trying to save the field I use the formula in.

I unfortunately have not found any way to check for an empty lookup...

Was this reply helpful? Yes No

AhmedSalih

6,678 Moderator on at

Like (0)

Report

Hello, @UlricusR try:

'Risk-Requirement-Association'= Blank()

I apologize, I thought your Datas ource was Dataverse as delegation differ betweem Data sources.

IsBlank in SQL: is ALL NO 😶

IsBlank No No No No No An expression such as Filter('[dbo].[MyOrders]', !IsBlank(CustomerId)) won't delegate to the server. However, you can use an expression such as Filter('[dbo].[MyOrders]', CustomerId <> Blank()), which does delegate to the server and is sementically close. The difference is that the second expression won't treat the empty string ("") as empty. Although the expressions aren't equivalent, the latter might work for your purposes. You can't use this method for the Guid data type.

https://learn.microsoft.com/en-us/connectors/sql/#power-apps-functions-and-operations-delegable-to-sql-server

If my reply helped you, please give a 👍 , & if it solved your issue, please 👍 & Accept it as the Solution to help other community members find it more.

I am primarily available on weekdays from 6-10 PM CT and 5-10 PM CT on weekends.

Visit my Blog: www.powerplatformplace.com

Was this reply helpful? Yes No

UlricusR 35 on at

Like (0)

Report

This seems to neither work. I get:

Incompatible types for comparison. These types can't be compared: Record, ObjNull.

Was this reply helpful? Yes No
AhmedSalih 6,678 Moderator on at

Like (0)

Report

@UlricusR, if you want, we can have a private Teams meeting to take a closer look into this issue.

Was this reply helpful? Yes No
daniuosagi 55 on at

Like (0)

Report
Hi I don't know if you have an answer yet as it's a year ago, but I encountered the same issue and found the solution by check IsBlank on the GUID column of the lookup record. For example if I'm on Contact and checking for Account lookup field "Company", instead of checking the lookup field is blank or not
- IsBlank(ThisRecord.Company)
we check the GUID column "Account" of that table:
- IsBlank(ThisRecord.Company.Account)

In your scenario it should most likely be, with the assumption of the GUID field of that lookup field table is 'Risk-Requirement-Association':
IsBlank(ThisRecord.'Risk-Requirement-Association'.'Risk-Requirement-Association')

While it might be too late to help the poster, hopefully it helps someone who sees this in the future.

Was this reply helpful? Yes No
UlricusR 35 on at

Like (0)

Report

Hi @daniuosagi, I actually don't know how I solved the issue, but somehow I did. Nevertheless, thanks for providing a potential solution, other people may benefit from it 🙂

Was this reply helpful? Yes No
CU01070752-0 on at

Like (0)

Report

Hi,

Not sure if this helps but i've been losing my mind trying to get around this. I basically wanted to add 2 columns up if a lookup wasn't blank.

I got around this by creating a text field 'lookupText' and creating a business rule to copy the value of a Choice field that drove the logic. You could just create a wildcard value in this text box and run your login against this?

Hope that helped

Was this reply helpful? Yes No