Filter nested CDT value from QueryEntity Result

I don't have any nested CDTs sitting around to conveniently try this out first, but off the top of my head, what happens if you try something like:

a!queryFilter(
  field: "equipmentList.isActive",
  operator: "<>",
  value: false
)

Apologies for the late response, Mike. (Holidays)

Looks like Appian responds with a test output error: Expression evaluation error at function a!queryEntity_18r3: Cannot filter by field [equipment.isActive] because it is a complex, multiple, or child of a multiple data type.

Any other options to try, Mike? Thanks again.

Sorry - I'm not sure if there's any other workaround in the case where the nested properties of a CDT are lists.  You could always consider doing an overly-inclusive query and then use in-process logic to loop over the results and exclude the ones you don't want returned, i suppose.

Np. Thanks Mike.

Sorry I couldn't help.  My experience with nested CDTs and especially querying on them is pretty limited (partly due to avoiding them thanks to such issues as seen here), but I'm still hoping someone else here might be able to chip in with some additional insight.

If I recall correctly, back when Query Rules came out we ran into a similar situation and were not able to resolve directly in the filtering - sub CDT filtering was not supported, believe that still applies to queryEntity.

How are you using this data? Are you always expecting one item in the DataSubset with one to many equipment, or multiple parents with multiple equipment children?

You may have to do a little manipulation on the result from queryEntity before it is returned to the interface.  In this example, one parent has multiple sub/child CDTs and we filter out any of those children who's "data" field is "b", by casting back to the data types with only what we want to return.

Food for thought:

a!localVariables(
  local!cdt: 'type!{urn:com:gdit:types}chris_test_cdt'(
    id: 1,
    sub_cdt: {
      'type!{urn:com:appian:types}chris_test_cdt_sub'(data: "a"),
      'type!{urn:com:appian:types}chris_test_cdt_sub'(data: "b")
    }
  ),
  
  'type!{urn:com:gdit:types}chris_test_cdt'(
    id: local!cdt.id,
    sub_cdt: a!flatten(
      fn!reject(
        fn!isnull,
        a!forEach(
          items: local!cdt.sub_cdt,
          expression: {
            if(
              fv!item.data="b",
              null,
              'type!{urn:com:appian:types}chris_test_cdt_sub'(
                data: fv!item.data
              )
            )
          }
        )
      )
    )
  )
)

Yes, Chris. Result would return multiple tool service items along with multiple equipment associated (could be 0 or more equipment). 

I will certainly look into your provided thoughts. Much appreciated on the explanation and clear code walk through.

JT

No problem!  

This is an updated example for multiple service items:

a!localVariables(
  local!cdt: {
    'type!{urn:com:gdit:types}chris_test_cdt'(
      id: 1,
      sub_cdt: {
        'type!{urn:com:appian:types}chris_test_cdt_sub'(pId: 1, id: 1, data: "a"),
        'type!{urn:com:appian:types}chris_test_cdt_sub'(pId: 1, id: 2, data: "b"),
        'type!{urn:com:appian:types}chris_test_cdt_sub'(pId: 1, id: 3, data: "b"),
        'type!{urn:com:appian:types}chris_test_cdt_sub'(pId: 1, id: 4, data: "a")
      }
    ),
    'type!{urn:com:gdit:types}chris_test_cdt'(
      id: 2,
      sub_cdt: {
        'type!{urn:com:appian:types}chris_test_cdt_sub'(pId: 2, id: 5, data: "b"),
        'type!{urn:com:appian:types}chris_test_cdt_sub'(pId: 2, id: 6, data: "b"),
        'type!{urn:com:appian:types}chris_test_cdt_sub'(pId: 2, id: 7, data: "a"),
        'type!{urn:com:appian:types}chris_test_cdt_sub'(pId: 2, id: 8, data: "b")
      }
    ),
    'type!{urn:com:gdit:types}chris_test_cdt'(
      id: 3,
      sub_cdt: {
        'type!{urn:com:appian:types}chris_test_cdt_sub'(pId: 3, id: 9, data: "a"),
        'type!{urn:com:appian:types}chris_test_cdt_sub'(pId: 3, id: 10, data: "b"),
      }
    )
  },

  a!flatten(
    a!forEach(
      items: local!cdt,
      expression: {
        a!localVariables(
          local!cdt_active: fv!item,
          'type!{urn:com:gdit:types}chris_test_cdt'(
            id: local!cdt_active.id,
            sub_cdt: a!flatten(
              fn!reject(
                fn!isnull,
                a!forEach(
                  items: local!cdt_active.sub_cdt,
                  expression: {
                    if(
                      fv!item.data="b",
                      null,
                      'type!{urn:com:appian:types}chris_test_cdt_sub'(
                        id: fv!item.id,
                        pId: fv!item.pId,
                        data: fv!item.data
                      )
                    )
                  }
                )
              )
            )
          )
        )
      }
    )
  )
)

a!localVariables(
  local!motorNestedEquipment: a!queryEntity(
    entity: cons!EMS_DB_RM_UPDATE_MOTOR,
    query: a!query(
      pagingInfo: ri!pagingInfo,
      logicalExpression: a!queryLogicalExpression(
        operator: "AND",
        filters: {
          if(
            rule!HAL_checkNullOrEmpty(ri!toolServiceId),
            null,
            a!queryFilter(
              field: "motorToolServiceId",
              operator: "=",
              value: ri!toolServiceId
            )
          ),
          a!queryFilter(
            field: "isActive",
            operator: "<>",
            value: false
          )
        }
      )
    )
  ),
  a!flatten(
    a!forEach(
      items: local!motorNestedEquipment,
      expression: {
        a!localVariables(
          local!cdt_active: fv!item,
          'type!{urn:com:appian:types:EMS}EMS_RmUpdateMotor'(
            motorId: local!cdt_active.motorId,
            motorToolServiceId: local!cdt_active.motorToolServiceId,
            statusId: local!cdt_active.statusId,
            available: local!cdt_active.available,
            updatedBy: local!cdt_active.updatedBy,
            updatedOn: local!cdt_active.updatedOn,
            isActive: local!cdt_active.isActive,
            equipment: a!flatten(
              fn!reject(
                fn!isnull,
                a!forEach(
                  items: local!cdt_active.equipment,
                  expression: {
                    if(
                      fv!item.isActive = false(),
                      null,
                      'type!{urn:com:appian:types}EMS_MotorEquipment'(
                        id: fv!item.id,
                        motorId: fv!item.motorId,
                        sapEquipmentNumber: fv!item.sapEquipmentNumber,
                        createdBy: fv!item.createdBy,
                        createdOn: fv!item.createdOn,
                        updatedBy: fv!item.updatedBy,
                        updatedOn: fv!item.updatedOn,
                        isActive: fv!item.isActive
                      )
                    )
                  }
                )
              )
            )
          )
        )
      }
    )
  )
)