Different query results for related data in a!queryRecordTYpe and a!query LogicalExpression

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:

  1. You can't construct a!queryFilters or a!queryLogicalExpressions with related data,
  2. You have to construct your queries differently when using related data.
  3. A fault somewhere in my logic that used the related data.
  4. A bug in the system

  Discussion posts and replies are publicly visible

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

    1. I could not get the "two deep" relationship to work.  I.e. Record Type.firstrelationship.secondrelationship.fields.field.  I tried several different relationship definitions and could not get it to work.  I settled on using just the one relationship and filtering by the Position Id.  I will have to create a constant for this as the value may change as I move from Dev-Test-Prod.
    2. The query now pull every Pastor in the database and only the Assignment Records that meet the criteria.  See the picture below.