Skip to main content
L
linp0001
September 15, 2022
Question

How to set the filter for a field (varchar, yes NULL ) with empty value in Query Editor

  • September 15, 2022
  • 4 replies
  • 1 view

Hi there,


I have database table, one field "matnr" configured as varchar, can be NULL, but when setting it to null or NULL in the process model, then writing to database (pic 1), the field value is not NULL, while kind like empty (pic 2) .

I am trying to create a expression rule searching the information with this field "matnr" without value, set "matnr is null" is not working. what's the way to do this? why Appian condition "is null" in Query editor does not work for empty field as other languages?

regards,
Lin

4 replies

mikes0011
mikes0011
Brainy
September 15, 2022

This is a long-known "bug" of sorts with the way a!queryEntity works (unfortunately); but there's a relatively easy workaround once you know it - a!queryFilter(field: "matnr", operator: "=", value: " ")  (that's an empty space). So you'd make a queryLogicalExpression() entry with the "OR" operator doing this *and* the "is null" operator check.

(I have no idea what you'd do, however, if you wanted to find entries where the value of the field = "one empty space", but whatever.)

The Expression Guru
L
linp0001Author
September 16, 2022

I see, I was set it to value: "", no space character, it did not work. I just tried you way: value: " " (with a space character), seems it working, though feel it's kind weird.

Thank you very much for the information.

peter.lewis
peter.lewis
Employee
September 16, 2022

Interesting I'd never done this before - instead I've always used the "in" operator, which allows you to provide an empty string. That way, you'd still be able to distinguish between a true empty string and a space.

Here's an example:

filter: a!queryFilter(
  field: "text",
  operator: "in",
  value: {""}
)

For what it's worth, if you use synced records you don't need to do this - using the "is null" operator will return both explicit NULL values and empty string values [emoticon:c4563cd7d5574777a71c318021cbbcc8]

Terms & ConditionsAccessibility statement