Skip to main content
chrisg0006
chrisg0006
May 17, 2024
Solved

queryRecordType with an "or" condition

  • May 17, 2024
  • 19 replies
  • 0 views

I need an Expression Rule that has an or condition for a filed.

I have a list of clergy assignments and I need to pull all current assignments and those that terminated over the last year.  The record type, CPMA Clergy Position has a termination date for the assignment, clergyPositionTermEnd which is a date formatted field.  If the assignment is for a fixed term, then there is a future date in the field.  If the assignment is a permanent assignment, then the field date is null.  

The or function would look like this.

or(
    RecordType!CPMA Clergy Position.field.clergypositiontermend >= today()-365,
    isNullOrEmpty(RecordType!CPMA Clergy Position.field.clergypositiontermend)
    )

However, I do not understand how to use this inside an Expression Rule.  My Expression Rule using the queryRecordType function looks like this.

if(
  a!isNotNullOrEmpty(ri!clergyId),
  a!queryRecordType(
    recordType: 'recordType!CPMA Clergy Position',
    pagingInfo: a!pagingInfo(startIndex: 1, batchSize: 5000),
    filters: {
      a!queryFilter(
        field: 'recordType!CPMA Clergy Position.fields.clergyId',
        operator: "=",
        value: ri!clergyId
      ),
      a!queryFilter(
        field: 'recordType!CPMA Clergy Position.fields.clergypositiontermend',
        operator: ">=",
        value: today()-365
      ),
      a!queryFilter(
        field:'recordType!CPMA Clergy Position.fields.clergypositiontermend',
        operator: "is null"
      )
      
    }
  ),
  null
).data

However, it doesn't work.  No values are returned. I am assuming that Appian treats the listing of queryFilters as an "and" condition.  I have tried using the If function to check to see if the clergyPositionTermEnd field is null, but that only pulls records with a value in the field. 

Thanks for you help!

    Best answer by stefanhelzle0001

    Try this. You did a simple mistake in nesting the correct values into the correct parameters. I can't test it, but comparing it to your code should make it obvious.

    if(
  a!isNotNullOrEmpty(ri!clergyId),
  a!queryRecordType(
    recordType: 'recordType!CPMA Clergy Position',
    pagingInfo: a!pagingInfo(startIndex: 1, batchSize: 5000),
    filters: a!queryLogicalExpression(
      operator: "OR",
      logicalExpressions: {
        a!queryLogicalExpression(
          operator: "AND",
          filters: {
            a!queryFilter(
              field: 'recordType!CPMA Clergy Position.fields.clergyId',
              operator: "=",
              value: ri!clergyId
            ),
            a!queryFilter(
              field: 'recordType!CPMA Clergy Position.relationships.parish.fields.organizationtypeId',
              operator: "=",
              value: cons!CPMA_OrganizationParishTypeId
            ),
            a!queryFilter(
              field: 'recordType!CPMA Clergy Position.fields.clergypositiontermend',
              operator: ">=",
              value: today() - 365
            )
          }
        ),
        a!queryLogicalExpression(
          operator: "AND",
          filters: {
            a!queryFilter(
              field: 'recordType!CPMA Clergy Position.fields.clergyId',
              operator: "=",
              value: ri!clergyId
            ),
            a!queryFilter(
              field: 'recordType!CPMA Clergy Position.relationships.parish.fields.organizationtypeId',
              operator: "=",
              value: cons!CPMA_OrganizationParishTypeId
            ),
            a!queryFilter(
              field: 'recordType!CPMA Clergy Position.fields.clergypositiontermend',
              operator: "is null"
            ),

          }
        )
      }
    ),
  ),
  null
).data

    19 replies

    konduruc733182
    konduruc733182
    May 17, 2024

    Hello [mention:47f656218bd54913aefbdc1e97de975f:e9ed411860ed4f2ba0265705b8793d05] 

    I am not sure if I get your issue, but please get rid of the below queryfilter() or make the operator to "not null", that should return the value if there would be no null data in that field. 

    a!queryFilter(
        field:'recordType!CPMA Clergy Position.fields.clergypositiontermend',
        operator: "is null"
      )


    chrisg0006
    chrisg0006Author
    May 17, 2024

    I'm sorry if I was not clear.  I need to pull records that meet both conditions.

    1. Where the clergypositiontermend date is null
    2. Where the clergypositiontermend  date is >= Today()-365
    konduruc733182
    konduruc733182
    May 17, 2024

    Great in that case, you need to adjust your queryRecordType by adding the Logical expression as Stefan has mentioned with the operator AND

    stefanhelzle0001
    stefanhelzle0001
    May 17, 2024

    I suggest to use a!queryLogicalExpression()

    https://docs.appian.com/suite/help/24.1/fnc_system_a_querylogicalexpression.html

    chrisg0006
    chrisg0006Author
    May 17, 2024

    Thank you to Stefan and Kondru, but I am still struggling here.  I have tried many different ways to nest the a!queryLogicalExpression to no avail.

    The following example does not return any values as it seems it is trying to return records that satisfy both conditions.  Changing the first operator from AND to OR retrieves all the records in the database.

    if(
  a!isNotNullOrEmpty(ri!clergyId),
  a!queryRecordType(
    recordType: 'recordType!CPMA Clergy Position',
    pagingInfo: a!pagingInfo(startIndex: 1, batchSize: 5000),
    filters: a!queryLogicalExpression(
      operator: "AND",
      filters: {
        a!queryFilter(
          field: 'recordType!CPMA Clergy Position.fields.clergyId',
          operator: "=",
          value: ri!clergyId
        ),
        a!queryFilter(
          field: 'recordType!CPMA Clergy Position.relationships.parish.fields.organizationtypeId',
          operator: "=",
          value: cons!CPMA_OrganizationParishTypeId
        ),
        a!queryFilter(
          field: 'recordType!CPMA Clergy Position.fields.clergypositiontermend',
          operator: ">=",
          value: today() - 365
        )
      },
      logicalExpressions: a!queryLogicalExpression(
        filters: {
          a!queryFilter(
            field: 'recordType!CPMA Clergy Position.fields.clergyId',
            operator: "=",
            value: ri!clergyId
          ),
          a!queryFilter(
            field: 'recordType!CPMA Clergy Position.relationships.parish.fields.organizationtypeId',
            operator: "=",
            value: cons!CPMA_OrganizationParishTypeId
          ),
          a!queryFilter(
            field: 'recordType!CPMA Clergy Position.fields.clergypositiontermend',
            operator: "is null"
          ),
          
        }
      )
    ),
    
  ),
  null
).data

    If I try and nest two a!queryLogicalExpressions with an AND condition within another a!queryLogicalExpression with an OR condition, it throws an error, " Expression evaluation error at function a!queryLogicalExpression parameter 2: Invalid dictionary literal, duplicate indices.."  See below.

    if(
  a!isNotNullOrEmpty(ri!clergyId),
  a!queryRecordType(
    recordType: 'recordType!CPMA Clergy Position',
    pagingInfo: a!pagingInfo(startIndex: 1, batchSize: 5000),
    filters: a!queryLogicalExpression(
      operator: "OR",
      filters: {
        logicalExpressions: a!queryLogicalExpression(
          operator: "AND",
          filters: {
            a!queryFilter(
              field: 'recordType!CPMA Clergy Position.fields.clergyId',
              operator: "=",
              value: ri!clergyId
            ),
            a!queryFilter(
              field: 'recordType!CPMA Clergy Position.relationships.parish.fields.organizationtypeId',
              operator: "=",
              value: cons!CPMA_OrganizationParishTypeId
            ),
            a!queryFilter(
              field: 'recordType!CPMA Clergy Position.fields.clergypositiontermend',
              operator: ">=",
              value: today() - 365
            )
          }
        ),
      
      logicalExpressions: a!queryLogicalExpression(
        operator: "AND",
        filters: {
          a!queryFilter(
            field: 'recordType!CPMA Clergy Position.fields.clergyId',
            operator: "=",
            value: ri!clergyId
          ),
          a!queryFilter(
            field: 'recordType!CPMA Clergy Position.relationships.parish.fields.organizationtypeId',
            operator: "=",
            value: cons!CPMA_OrganizationParishTypeId
          ),
          a!queryFilter(
            field: 'recordType!CPMA Clergy Position.fields.clergypositiontermend',
            operator: "is null"
          ),
          
        }
      )
      }
    ),
    
  ),
  null
).data

    stefanhelzle0001
    stefanhelzle0001Answer
    May 17, 2024

    Try this. You did a simple mistake in nesting the correct values into the correct parameters. I can't test it, but comparing it to your code should make it obvious.

    if(
  a!isNotNullOrEmpty(ri!clergyId),
  a!queryRecordType(
    recordType: 'recordType!CPMA Clergy Position',
    pagingInfo: a!pagingInfo(startIndex: 1, batchSize: 5000),
    filters: a!queryLogicalExpression(
      operator: "OR",
      logicalExpressions: {
        a!queryLogicalExpression(
          operator: "AND",
          filters: {
            a!queryFilter(
              field: 'recordType!CPMA Clergy Position.fields.clergyId',
              operator: "=",
              value: ri!clergyId
            ),
            a!queryFilter(
              field: 'recordType!CPMA Clergy Position.relationships.parish.fields.organizationtypeId',
              operator: "=",
              value: cons!CPMA_OrganizationParishTypeId
            ),
            a!queryFilter(
              field: 'recordType!CPMA Clergy Position.fields.clergypositiontermend',
              operator: ">=",
              value: today() - 365
            )
          }
        ),
        a!queryLogicalExpression(
          operator: "AND",
          filters: {
            a!queryFilter(
              field: 'recordType!CPMA Clergy Position.fields.clergyId',
              operator: "=",
              value: ri!clergyId
            ),
            a!queryFilter(
              field: 'recordType!CPMA Clergy Position.relationships.parish.fields.organizationtypeId',
              operator: "=",
              value: cons!CPMA_OrganizationParishTypeId
            ),
            a!queryFilter(
              field: 'recordType!CPMA Clergy Position.fields.clergypositiontermend',
              operator: "is null"
            ),

          }
        )
      }
    ),
  ),
  null
).data

      Terms & ConditionsAccessibility statement