I ran into this problem when creating an expression rule for pulling the current pastor for a given parish Id (organization Id). The Id is passed in a Rule Input as parishId. I have three tables that are needed for this query, all are related. Clergy Member, Clergy Assignment, and the reference table for the Position name. I need to pull the assignments that are current, i.e. where the Position Term Date is greater than today or null (for permanent assignments). This is a very similar problem to the one I posted here: queryRecordType with an "or" condition
Now that a!queryLogicalExpression is my new best friend (a big thank you to Stefan Helzle for the intro) is used a very similar logic. Since I wanted the results to be a Clergy Member record type, I used that as the record in the a!queryRecord function.
a!queryRecordType( recordType: 'recordType!PMSO Clergy Member', fields: { 'recordType!PMSO Clergy Member.fields.clergyId', 'recordType!PMSO Clergy Member.fields.clergyfullname', }, pagingInfo: a!pagingInfo(startIndex: 1, batchSize: 1000), filters: a!queryLogicalExpression( operator: "OR", logicalExpressions: { a!queryLogicalExpression( operator: "AND", filters: { a!queryFilter( field: 'recordType!PMSO Clergy Member.relationships.clergyAssignment.fields.clergyorganizationId', operator: "=", value: ri!parishId ), a!queryFilter( field: 'recordType!PMSO Clergy Member.relationships.clergyAssignment.relationships.refPosition.fields.position', operator: "=", value: "Pastor" ), a!queryFilter( field: 'recordType!PMSO Clergy Member.relationships.clergyAssignment.fields.clergypositiontermend', operator: "is null" ) } ), a!queryLogicalExpression( operator: "AND", filters: { a!queryFilter( field: 'recordType!PMSO Clergy Member.relationships.clergyAssignment.fields.clergyorganizationId', operator: "=", value: ri!parishId ), a!queryFilter( field: 'recordType!PMSO Clergy Member.relationships.clergyAssignment.relationships.refPosition.fields.position', operator: "=", value: "Pastor" ), a!queryFilter( field: 'recordType!PMSO Clergy Member.relationships.clergyAssignment.fields.clergypositiontermend', operator: ">=", value: today() ) } ) } ), ).data,
For the most part this logic worked except if the Pastor also happened to be a Pastor at another church in the past. Since we rotate pastors every 6 years in the dioceses, we have quite a few pastors that were pastors at other churches. When this condition occurred, the query would pull EVERY RECORD for that pastor no matter what parish it was in or what position they served. It seemed to ignore the filters for position and Parish ID. It would do this if the Clergy Member was a pastor in the past for the Parish ID that is being queried.
I created another expression rule, this time using the Clergy Position table as the query record. Other than the fact that I am not so reliant on related data, the logic is exactly the same. The output is different, but the correct records are pulled.
a!queryRecordType( recordType: 'recordType!PMSO Clergy Assignment', pagingInfo: a!pagingInfo(startIndex: 1, batchSize: 1000), fields: { 'recordType!PMSO Clergy Assignment.fields.clergyId', 'recordType!PMSO Clergy Assignment.relationships.clergyMember.fields.clergyfullname' }, filters: a!queryLogicalExpression( operator: "OR", logicalExpressions: { a!queryLogicalExpression( operator: "AND", filters: { a!queryFilter( field: 'recordType!PMSO Clergy Assignment.fields.clergyorganizationId', operator: "=", value: ri!parishId ), a!queryFilter( field: 'recordType!PMSO Clergy Assignment.relationships.refPosition.fields.position', operator: "=", value: "Pastor" ), a!queryFilter( field: 'recordType!PMSO Clergy Assignment.fields.clergypositiontermend', operator: "is null" ) } ), a!queryLogicalExpression( operator: "AND", filters: { a!queryFilter( field: 'recordType!PMSO Clergy Assignment.fields.clergyorganizationId', operator: "=", value: ri!parishId ), a!queryFilter( field: 'recordType!PMSO Clergy Assignment.relationships.refPosition.fields.position', operator: "=", value: "Pastor" ), a!queryFilter( field: 'recordType!PMSO Clergy Assignment.fields.clergypositiontermend', operator: ">=", value: today() ) } ) } ), ).data,
So is this:
Discussion posts and replies are publicly visible
Maybe this example helps: https://docs.appian.com/suite/help/24.1/fnc_system_relatedrecorddata.html#only-return-the-latest-customer-support-case
Thanks again Stefan for you answer. I changed up my original expression rule to the following. I had to put in some extra related fields to troubleshoot.
a!queryRecordType( recordType: 'recordType!PMSO Clergy Member', fields: { 'recordType!PMSO Clergy Member.fields.clergyId', 'recordType!PMSO Clergy Member.fields.clergyfullname', 'recordType!PMSO Clergy Member.relationships.clergyAssignment.fields.clergypositionId', 'recordType!PMSO Clergy Member.relationships.clergyAssignment.fields.clergyorganizationId', 'recordType!PMSO Clergy Member.relationships.clergyAssignment.fields.positionId', 'recordType!PMSO Clergy Member.relationships.clergyAssignment.fields.clergypositiontermend' }, pagingInfo: a!pagingInfo(startIndex: 1, batchSize: 1000), relatedRecordData: { a!relatedRecordData( relationship: { 'recordType!PMSO Clergy Member.relationships.clergyAssignment' }, filters: a!queryLogicalExpression( operator: "OR", logicalExpressions: { a!queryLogicalExpression( operator: "AND", filters: { a!queryFilter( field: 'recordType!}PMSO Clergy Assignment.fields.clergyorganizationId', operator: "=", value: ri!parishId ), a!queryFilter( field: 'recordType!PMSO Clergy Assignment.fields.positionId', operator: "=", value: 25 ), a!queryFilter( field: 'recordType!PMSO Clergy Assignment.fields.clergypositiontermend', operator: "is null" ) } ), a!queryLogicalExpression( operator: "AND", filters: { a!queryFilter( field: 'recordType!PMSO Clergy Assignment.fields.}clergyorganizationId', operator: "=", value: ri!parishId ), a!queryFilter( field: 'recordType!PMSO Clergy Assignment.fields.positionId', operator: "=", value: 25 ), a!queryFilter( field: 'recordType!PMSO Clergy Assignment.fields.clergypositiontermend', operator: ">=", value: today() ) } ) } ), ) } ).data,
I now have two issues.