Hello all,
I would like to use queryEntity to add a filter which uses LISTAGG function in the database view.
I'm trying to add DECISION filter something like below:
select * from DB_VIEW where RECEIVED_DATE > to_date('6/1/2021', 'mm/dd/yyyy') and (DECISION like ('%Disapproved%') or DECISION like ('%Approved%'));
Now the issue is, we do not have 'like' value for 'operator' in a!queryFilter. How can I add 'like' operator to get the filtered result on a view in the Appian Report?
Discussion posts and replies are publicly visible
The operator "includes" will do this for you - I guess you can think of it adding the % wildcards at the start and end of the value you provide.
"includes" is throwing the following error:
An error occurred while executing a save: Expression evaluation error in rule 'test_getReportview' at function a!queryEntity_18r3: Cannot apply operator [INCLUDES] to field [decision_text] when comparing to value [TypedValue[it=103,v={Approved}]].
"in" is returning the values for filter "Disapproved" when we actually filter for "Approved" values, as Disapproved term has approved in it.
The error is because you're trying to use the "includes" operator (which can only work on a single text value) on a value that is an array! The "in" operator checks the values in the database to those in the lost of values you're providing and returns those records that match that criteria. Those are very different operators. If your objective is to implement the equivalent of the following:
then you're going to have a problem because the value "Disapproved" also contains the string "approved". If you know that the former always starts with the string "Dis" you can use the "starts with" operator (the equivalent of adding the %wildcard AFTER the value you provide e.g. "Dis%", and that the latter always starts with "App" then, similarly you can also use the "starts with" operator.
I feel, however, that you have a design issue in that such status values should be immutable i.e. they should always be fixed values, in which case you can use the "=" operator and pass fixed values to the filter(s).