Skip to main content
steffena0001
steffena0001
February 4, 2022
Question

Filter a value and null values

  • February 4, 2022
  • 9 replies
  • 0 views

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
gayathris111098
February 4, 2022

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
    abhayd3663
    February 12, 2023

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

    stefanhelzle0001
    stefanhelzle0001
    Brainy
    February 4, 2022

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

      mikes0011
      mikes0011
      Brainy
      February 4, 2022

      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
      steffena0001Author
      February 7, 2022

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

      A
      akhils
      February 4, 2022

      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"
        )
       }
      )

        K
        kavitar298810
        January 20, 2023

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

        M
        michals0005
        February 3, 2023

        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
          abhayd3663
          February 12, 2023

          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
    )
)

          Terms & ConditionsAccessibility statement