Hi everyone,
I'm facing a strange behavior when using a!queryRecordType() to query to related record types.
I'm having 3 record types with relationships as below:
I'm doing the below query to get all the teams that Shaun is a member of (team A and B).
a!localVariables( local!dataset: a!refreshVariable( value: a!queryRecordType( recordType: 'recordType!Team', filters: a!queryLogicalExpression( operator: a!defaultValue(ri!filterOperator,"AND"), logicalExpressions: ri!logicalExpression, filters: { if( a!isNullOrEmpty(ri!id), null, a!queryFilter( field: 'recordType!Team.fields.id', operator: "IN", value: ri!id ), if( a!isNullOrEmpty(ri!name), null, a!queryFilter( field: 'recordType!Team.fields.name', operator: "IN", value: ri!name )), if( a!isNullOrEmpty(ri!isActive), null, a!queryFilter( field: 'recordType!Teams.field.isActive', operator: "=", value: ri!isActive ) ), a!queryFilter( field: 'recordType!Team.relationships.UserTeamRelationship.fields.isActive', operator: "=", value: true ), a!queryFilter( field: 'recordType!Team.relationships.UserTeamRelationship.relationships.User.fields.userName', operator: "=", value: "Shaun" ) } ), fields: {'recordType!Team.relationships.UserTeamRelationship.fields.isActive', 'recordType!Team.relationships.UserTeamRelationship.relationships.User.fields.userName'}, pagingInfo: if( isnull(ri!pagingInfo), a!pagingInfo(startIndex: 1, batchSize: 1000), ri!pagingInfo ), fetchTotalCount: ri!fetchTotalCount, relatedRecordData: {recordType!User, recordType!UserTeamRelationship} ).data, refreshAlways: rule!GBL_replaceNull( nullableValue: ri!refreshAlways, replacementValue: false ), refreshInterval: ri!refreshInterval, refreshAfter: "RECORD_ACTION" ), local!dataSet )
a!localVariables( local!dataset: a!refreshVariable( value: a!queryRecordType( recordType: 'recordType!Team', filters: a!queryLogicalExpression( operator: a!defaultValue(ri!filterOperator,"AND"), logicalExpressions: ri!logicalExpression, filters: { a!queryFilter( field: 'recordType!Team.relationships.UserTeamRelationship.fields.isActive', operator: "=", value: true ), a!queryFilter( field: 'recordType!Team.relationships.UserTeamRelationship.relationships.User.fields.userName', operator: "=", value: "Shaun" ) } ), fields: {'recordType!Team.relationships.UserTeamRelationship.fields.isActive', 'recordType!Team.relationships.UserTeamRelationship.relationships.User.fields.userName'}, pagingInfo: if( isnull(ri!pagingInfo), a!pagingInfo(startIndex: 1, batchSize: 1000), ri!pagingInfo ), fetchTotalCount: ri!fetchTotalCount, relatedRecordData: {recordType!User, recordType!UserTeamRelationship} ).data, refreshAlways: rule!GBL_replaceNull( nullableValue: ri!refreshAlways, replacementValue: false ), refreshInterval: ri!refreshInterval, refreshAfter: "RECORD_ACTION" ), local!dataSet )
My expected result is team A and B, however the result returned were team A, B and E although Shaun is having inactive relationship with team E.
Could anyone help me point out where I did wrong with my query?
Thank you!
Discussion posts and replies are publicly visible
Try the "AND_ALL" filter instead of AND and see if that works.
It worked! Will have a look at the documentation about AND_ALL and AND in querying related records. Thank you!