queryRecordType with an "or" condition

Question

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!

Stefan Helzle · Accepted Answer

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

Stefan Helzle · Answer

I got the same error when just copying your code from here into the expression editor. I got rid of it after replacing all record references. Did you try this?

Chris.Gillespie · Answer

I copied your code into the expression rule editor and changed every record type and field reference in the code. Maybe I was more careful this time, but it works!!!

queryRecordType with an "or" condition

Top Replies