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
Hi anushas0002 couple of quick questions here to understand the root cause behind this error and resolve the same:
Upon knowing these details will help the practitioners to identify and resolve this issue.
looks like the Filter Parameter Between is expecting both inputs are of type Date Time, Please change it to Date Time and then Test It.
{ if(isnull(ri!StartDate), now(), ri!StartDate), if(isnull(ri!EndDate), now(), ri!EndDate) }