Unable to retrieve data from queryEntity for a date range. The below code is not working and instead throwing an error that "between" operator cannot be used. Please advise how to fix it.
local!chartDatasubset: a!queryEntity( entity: cons!NSARequest_Entity_Cons, query: a!query( aggregation: a!queryAggregation( aggregationColumns: { a!queryAggregationColumn(field: "Status", alias: "Status", isGrouping: true), a!queryAggregationColumn(field: "SubmittedDate", alias: "SubmittedDate", isGrouping: true), a!queryAggregationColumn(field: "Id", aggregationFunction: "COUNT") } ), filter: a!queryFilter( field: "SubmittedDate", operator: "between", value: { if(isnull(ri!StartDate), today(), ri!StartDate), if(isnull(ri!EndDate), today(), ri!EndDate) } ), pagingInfo: local!chartPagingInfo ) ),
Initially had an expression rule below which used to be working in 18.3 version. Not sure if in new 18.4 version it isn't. Please advise.
with( local!chartPagingInfo: a!pagingInfo( startIndex: 1, batchSize: -1, sort: a!sortInfo( field: "SubmittedDate", ascending: true ) ), a!queryEntity_18r3( entity: cons!NSARequest_Entity_Cons, query: a!query( filter: { a!queryFilter( selection: "", field: "SubmittedDate", operator: "between", value: { ri!StartDate, ri!EndDate } ) }, pagingInfo: local!chartPagingInfo ) ) )
Discussion posts and replies are publicly visible
What is the type of SubmittedDate? Have you tried changing today() to now()? It seems like your SubmittedDate when comparing to today() is invalid because they are of differing types. However, if you change today() to now() (probably would want to truncate the now() timestamp to midnight of the current date) it appears to work as expected from my test scenarios.