Hello,
I have a requirement where i need to query ids with approvedOn date greater than or equal to (today - 5) business days.
What i have in place is working only for equal to but not for greater than.
Any inputs?
index( rule!ACO_getCdt( entity: cons!TEST, type: 'type!{urn:com:appian:types:occrcl}Test?list', fields: {"transactionId"}, filters: { rule!ACO_filterEquals(field:"transactionId", value: 501), rule!ACO_filterEquals(field:"isApproved", value:true), rule!ACO_filterEquals(field:"actionId", value:cons!OCC_RCL_REF_TRANSACTION_ACTION_APPROVE_RECLASS_ID), /*constant has -5 value*/ rule!ACO_filterGreaterThanOrEqualTo(field:"approvedOn", value: gmt(todatetime(workday(rule!ACO_today(), cons!OCC_RCL_AWAITING_APPROVAL_FINAL_NOTIFICATION_REMINDER_DAYS)))), rule!ACO_filterLessThanOrEqualTo(field:"approvedOn", value: gmt(todatetime(workday(rule!ACO_today(), cons!OCC_RCL_AWAITING_APPROVAL_FINAL_NOTIFICATION_REMINDER_DAYS))) + intervalds(23,59,59)) } ), "transactionId", {} ) ================================ rule!ACO_filterGreaterThanOrEqualTo(): rule!ACO_queryFilter( field: ri!field, operator: ">=", value: ri!value )
Discussion posts and replies are publicly visible
These steps might help you pinpoint if there is an issue on the database side or the custom SAIL code side.
Hello Scoot,
thanks for the response.
Even when i used general a!queryfilter function inside a!queryentity,
rule!ACO_queryFilter( field: ri!field, operator: ">=", value: gmt(todatetime(workday(rule!ACO_today(), -5)))),
I am only getting exact today - 5 which is 10/08/2020 records but i also want to query which are in-prior like 10/7/2020,10/06/2020 etc.
Greater than equal operator is not returning the values when applied to field
1. solution use execute stored procedure it will help you.
2. I think the issue you faced is because of datatype of the field approvedOn. I think you have selected it as a string. Please try with datatype date for approvedOn field in this case it must work.
Hope it will help you identify problem
You still need to test this using a straight OOTB queryEntity and queryFilter rule call, because we have no way of knowing, nor any reasonable hope of troubleshooting, the contents of your code constructor functions like rule!ACO_queryFilter.
You also didn't really answer Scott's question, as far as I can tell: is the DB field you're querying on a DATE or DATETIME type? This makes a huge difference when we get into the particulars of querying like this.
If you do want rows that are prior/earlier than the date in the value, then you need your operator to be less than (<=), not greater than (>=).
You are right Scott, i just figured <= alone is enough to do the job.
Thank you so much for the response.
No problem, sometimes it helps a lot to have another pair of eyes with a fresh look at the problem!