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!
Discussion posts and replies are publicly visible
I suggest to use a!queryLogicalExpression()
https://docs.appian.com/suite/help/24.1/fnc_system_a_querylogicalexpression.html
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
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
Another try was to create an A!queryLogicalExpressions with an AND condition for two a!queryFilters and one a!queryLogicalExpression with an OR condition. This throws an error, :"Expression evaluation error at function a!queryRecordType [line 3]: The field [] does not exist."
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 ), logicalExpressions: a!queryLogicalExpression( operator: "OR", filters: { 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
I copied your code exactly, however it throws an error, "Syntax error. Details: Expression evaluation error: Keyword and non-keyword arguments must not be mixed. Used in: queryrecordtype_v1.." I removed the IF statement as I was originally getting the error message there. There must be something out of place. I checked all the commas, the parenthesis and braces, but they all look good to me.
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" ) } ) } ) ).data
I do see where I made the mistake. Thank you for pointing it out. However, now getting a different error message.
I copied just the AND queryLogicalExpressions into a separate Expression Rule and they worked fine independently of one another. It seems the issue is in combining them under a single list of logicalExpressions..
What is this "different error message"?
The error message, "Syntax error. Details: Expression evaluation error: Keyword and non-keyword arguments must not be mixed. Used in: queryrecordtype_v1.."
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?
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!!!
Thanks!!!!!