Filter nested CDT value from QueryEntity Result

Howdy,

1st time post.

I'm facing a particular issue with my Query Entity results. Essentially, we need to filter out (not include) inactive equipment in a nested CDT structure. The QueryEntity rule queries against an parent order and has equipment (nested CDT) numbers associated. The results return all equipment records regardless if the equipment is active or inactive. Would there be a way to effectively retrieve the order form only with active equipment?

Query Entity Expression:

a!queryEntity_18r3(
  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
        )
      }
    )
  )
)

Results:

DataSubset

    • startIndex1(Number (Integer))
      • batchSize-1(Number (Integer))
        • sortList of SortInfo - 1 item
            • SortInfo
                • field"isActive"(Text)
                  • ascendingtrue(Boolean)
                • totalCount1(Number (Integer))
                  • dataList of Dictionary - 1 item
                      • Dictionary
                          • equipmentList of EMS_MotorEquipment - 2 items
                              • EMS_MotorEquipment
                                  • id189(Number (Integer))
                                    • sapEquipmentNumber"11851552"(Text)
                                      • motorId308(Number (Integer))
                                        • createdBy"user@oilandgas.com"(Text)
                                          • createdOn12/17/2020 12:12 PM CST(Date and Time)
                                            • updatedBy"user@oilandgas.com"(Text)
                                              • updatedOn12/17/2020 12:12 PM CST(Date and Time)
                                                • isActive true(Boolean)
                                                • EMS_MotorEquipment
                                                    • id194(Number (Integer))
                                                      • sapEquipmentNumber"11851531"(Text)
                                                        • motorId308(Number (Integer))
                                                          • createdBy"user@oilandgas.com"(Text)
                                                            • createdOn12/17/2020 4:30 PM CST(Date and Time)
                                                              • updatedBy"user@oilandgas.com"(Text)
                                                                • updatedOn12/17/2020 4:30 PM CST(Date and Time)
                                                                  • isActive false(Boolean)
                                                                • isActivetrue(Boolean)
                                                                  • updatedOn12/17/2020 4:30 PM CST(Date and Time)
                                                                    • updatedBy"user@oilandgas.com"(Text)
                                                                      • available2(Number (Integer))
                                                                        • statusId296(Number (Integer))
                                                                          • motorToolServiceId2052(Number (Integer))
                                                                            • motorId308(Number (Integer))
                                                                          • identifiersList of Number (Integer) - 1 item
                                                                              • 308(Number (Integer))

                                                                            Hope to hear your thoughts on this one.

                                                                            Much appreciated,
                                                                            JT

                                                                              Discussion posts and replies are publicly visible

                                                                            Parents
                                                                            • 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

                                                                            Reply Children
                                                                            • 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
                                                                                                    )
                                                                                                  )
                                                                                                }
                                                                                              )
                                                                                            )
                                                                                          )
                                                                                        )
                                                                                      )
                                                                                    }
                                                                                  )
                                                                                )
                                                                              )



                                                                              Works like a charm! Thanks Chris!!