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
Discussion posts and replies are publicly visible
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.)
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.
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
Hi Peter,
unfortunately the "is null" operator does NOT work for empty string, otherwise I would not post the question here.
Mike mentioned it's a long-known issue, I am hoping you can report this issue to Appian development team. hopefully they can fix it as it's really not a small problem, sometime have to do some trick thing.
Also another thing (mentioned in my post) is that even you explicitly set the value of a field as null in the input data, Appian's "write to data store entity" will still set the field data to empty string, that's also the reason I post this question here as I tried using "is null" operator is query editor, it does NOT return the record with empty string value.
regards,
Lin