Filter nested CDT value from QueryEntity Result

Question

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

startIndex 1 (Number (Integer))

batchSize -1 (Number (Integer))

sort List of SortInfo - 1 item

SortInfo

field "isActive" (Text)

ascending true (Boolean)

totalCount 1 (Number (Integer))

data List of Dictionary - 1 item

Dictionary

equipment List of EMS_MotorEquipment - 2 items

EMS_MotorEquipment

id 189 (Number (Integer))

sapEquipmentNumber "11851552" (Text)

motorId 308 (Number (Integer))

createdBy "user@oilandgas.com" (Text)

createdOn 12/17/2020 12:12 PM CST (Date and Time)

updatedBy "user@oilandgas.com" (Text)

updatedOn 12/17/2020 12:12 PM CST (Date and Time)

isActive true (Boolean)

EMS_MotorEquipment

id 194 (Number (Integer))

sapEquipmentNumber "11851531" (Text)

motorId 308 (Number (Integer))

createdBy "user@oilandgas.com" (Text)

createdOn 12/17/2020 4:30 PM CST (Date and Time)

updatedBy "user@oilandgas.com" (Text)

updatedOn 12/17/2020 4:30 PM CST (Date and Time)

isActive false (Boolean)

isActive true (Boolean)

updatedOn 12/17/2020 4:30 PM CST (Date and Time)

updatedBy "user@oilandgas.com" (Text)

available 2 (Number (Integer))

statusId 296 (Number (Integer))

motorToolServiceId 2052 (Number (Integer))

motorId 308 (Number (Integer))

identifiers List of Number (Integer) - 1 item

308 (Number (Integer))

Hope to hear your thoughts on this one. 
 Much appreciated, JT

Chris · Accepted Answer

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

Mike Schmitt · Answer

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
)

Mike Schmitt · Answer

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.

Chris · Answer

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

Filter nested CDT value from QueryEntity Result

Top Replies