I am having a column named due On in the record with data type as date and time,In db the data type for this field is timestamp.I have to add a filter ,having two options one Before or at 7:59 PM Today and After 7:59 PM Today and display this filter and read only grid in a interfaceThis is the filter code I am writing currently,but it is not working correctly kindly help me regarding thisa!recordFilterList( name: "SLA Due Date", options: { a!recordFilterListOption( id: "before", name: "Before or at 7:59 PM Today", filter: a!queryFilter( field:'recordType!{ee5aa46d-0228-430f-a794-1134a11fbcc0}CMGT_Task.fields.{56f4ac00-4b4b-4c1b-b841-1938bd23ac80}dueOn', operator: "<=", value: rule!NWS_SlaCutOffTime(), ) ), a!recordFilterListOption( id: "after", name: "After 7:59 PM Today", filter: a!queryFilter( field:'recordType!{ee5aa46d-0228-430f-a794-1134a11fbcc0}CMGT_Task.fields.{56f4ac00-4b4b-4c1b-b841-1938bd23ac80}dueOn', operator: ">", value:rule!NWS_SlaCutOffTime(), ) ) }, allowMultipleSelections: false)NWS_SlaCutOffTime(),having code below
datetime( year(today()), month(today()), day(today()), 19,59,00 ),
Discussion posts and replies are publicly visible
I am assuming Timestamp precision mismatch - database records include seconds/milliseconds but your cutoff time is exactly 7:59:00, causing records like 7:59:30 PM to incorrectly fall into "After 7:59 PM". Use 8:00 PM as your cutoff instead of 7:59 PM, and use < and >= operators instead of <= and >. Update your NWS_SlaCutOffTime() rule:
/* Use 8:00 PM for cleaner boundary logic */ datetime( year(today()), month(today()), day(today()), 20, 0, 0 )
a!recordFilterList( name: "SLA Due Date", options: { a!recordFilterListOption( id: "before", name: "Before or at 7:59 PM Today", filter: a!queryFilter( field: 'recordType!{ee5aa46d-0228-430f-a794-1134a11fbcc0}CMGT_Task.fields.{56f4ac00-4b4b-4c1b-b841-1938bd23ac80}dueOn', operator: "<", value: rule!NWS_SlaCutOffTime() ) ), a!recordFilterListOption( id: "after", name: "After 7:59 PM Today", filter: a!queryFilter( field: 'recordType!{ee5aa46d-0228-430f-a794-1134a11fbcc0}CMGT_Task.fields.{56f4ac00-4b4b-4c1b-b841-1938bd23ac80}dueOn', operator: ">=", value: rule!NWS_SlaCutOffTime() ) ) }, allowMultipleSelections: false )
I also agree with this, but as OP did not really specify what they meant by "not working", it's hard to know whether this is even vaguely the direction of their issue. But this is good general guidance for implementing timestamp-based filters.
True, without seeing the actual behavior it's hard to pinpoint the exact issue. The timestamp precision and timezone considerations are the most common culprits for date/time filter problems, so starting with these solutions usually resolves most cases.