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
Hello Chris.Gillespie
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" )
I suggest to use a!queryLogicalExpression()
https://docs.appian.com/suite/help/24.1/fnc_system_a_querylogicalexpression.html
I'm sorry if I was not clear. I need to pull records that meet both conditions.
Great in that case, you need to adjust your queryRecordType by adding the Logical expression as Stefan has mentioned with the operator AND
... I think you will not be able to find any record that meets both conditions ...
Stefan, you are correct. A single record will not meet both conditions. I need records that meet one or the other condition as well as other conditions.
OK. Then a!queryLogicalExpression() is your friend. It allows you to query by almost any logic you want. The documentation has plenty examples on how to use it.
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