Skip to main content
steffena0001
Known Participant
Feb 4, 2022 9:22 AM UTC
Question

Filter a value and null values

Code snippet below shows a filter for field test whose value is reviewing.

a!queryFilter(
field: recordType! a.fields.test
operator: "=",
value: "Reviewing"
)

How can I filter field test whose values are "reviewing" or null?  I should be able to get rows 1 and 2

test
1 reviewing
2
3 resolved

9 replies

gayathris111098
Inspiring
Feb 4, 2022 10:41 AM UTC

a!queryEntity(
  entity: cons!recordType,
  query: a!query(
    selection: a!querySelection(
      columns: {
       
      }
    ),
    logicalExpression: a!queryLogicalExpression(
      operator: "OR",
      filters: {
        a!queryFilter(
field: recordType! a.fields.test
operator: "=",
value: "Reviewing"
),
        a!queryFilter(
field: recordType! a.fields.test
 operator: "is null"
)
      },
      ignoreFiltersWithEmptyValues: true
    ),
    pagingInfo: a!pagingInfo(
      startIndex: 1,
      batchSize: -1
    )
  )
)	

abhayd3663
Brainy
Feb 12, 2023 1:41 AM UTC

Just FYI, a!queryEntity() does not work for recordType. Need to use a!queryRecordType().

stefanhelzle0001
Brainy
Feb 4, 2022 11:33 AM UTC

a!queryFilter(
  field: recordType!a.fields.test
  operator: "in",
  value: {"Reviewing", null}
)

mikes0011
Brainy
Feb 4, 2022 3:34 PM UTC

For the record - this method will not return results where the field is "DB Null" - but it does return entries where the DB row contains a blank value (extra frustrating since the DB treats these differently but Appian gives us no direct way to control it).

The Expression Guru
steffena0001
Known Participant
Feb 7, 2022 2:00 AM UTC

This was actually my problem when I posted this question. so I was thinking if there's a method to get that as well

New Participant
Feb 4, 2022 6:48 PM UTC

Incase, if you want empty value along with null or any other text from backend, use the following code,

a!queryLogicalExpression(
 operator: "OR",
 filters: {
  a!queryFilter(
   field: "col2",
   operator: "in",
   value: {"Reviewing",""}
  ),
  a!queryFilter(
   field: "col2",
   operator: "is null"
  )
 }
)

Known Participant
Jan 20, 2023 9:01 AM UTC

I have the same issue but not working given answers... any other suggestion ......

New Participant
Feb 3, 2023 9:14 AM UTC

Could you treat empty values as null? Because if yes, then you could use a custom field and add a default value of "" (empty string) to it

a!customFieldDefaultValue(
  value: 'recordType.stringFieldWithNulls',
  default: ""
)

Name it something like fieldName + "NullToEmpty", and then filter on that new field.

abhayd3663
Brainy
Feb 12, 2023 1:37 AM UTC

Few points to note. 

- Must use a!queryRecordType(). Cannot use a!queryEntity() with recordType constant as the entity.

- The batch size when querying record data must be between 0 and 5,000.

a!queryRecordType(
  recordType: cons!XXX_RECORD_EMP,
  filters: a!queryLogicalExpression(
    operator: "OR",
    filters: {
      a!queryFilter(
        field: 'recordType!{xx-xx-xx-xx-xx}Employee.fields.{xx-xx}department',
        operator: "=",
        value: "IT"
      ),
      a!queryFilter(
        field: 'recordType!{xx-xx-xx-xx-xx}Employee.fields.{xx-xx}department',
        operator: "is null"
      )
    },
    ignoreFiltersWithEmptyValues: true
  ),
    pagingInfo: a!pagingInfo(
      startIndex: 1,
      batchSize: 50
    )
)