Not able to filter basis on year into timestamp

Question

Hi , 
 
 I have list of year which i want to pass in dataset to filter out date between those years. But query is not returning anything. 
 
 I am new to this concept so please guide me on that. 
 
 with(
 local!years: {2015,2016,2017},
 a!forEach(
 items: local!years, 
 expression: a!queryEntity(
 entity: cons!YEAR_DETAILS,
 query: a!query(
 filter: {a!queryFilter(
 field: "created",
 operator: "between",
 value: {datetime(fv!item,month(1),day(1),hour(1),minute(1),second(1)),datetime(fv!item,month(12),day(31),hour(12),minute(1),second(1))}
 )},
 pagingInfo: a!pagingInfo(
 startIndex: 1,
 batchSize: - 1,
 sort: a!sortInfo(
 field: "created",
 ascending: true
 )
 )
 )
 )
 )
 
) 
 
 Kindly consider YEAR_DETAILS table .

Harshit Bumb (Appyzie) · Accepted Answer

You can achieve this by using the "logical expressions" inside a!query(). It helps in applying two or more filters at once. For your convenience, I am adding the updated code snippet below. This might help you.

with( local!years: {2015,2016,2017}, {a!forEach( items: local!years, expression: a!queryEntity( entity: cons!YEAR_DETAILS, query: a!query( logicalExpression: a!queryLogicalExpression( operator: "AND", filters: { a!queryFilter( field: "created", operator: ">=", value: datetime(fv!item,month(1),day(1),hour(1),minute(1),second(1)) ), a!queryFilter( field: "created", operator: "<=", value: datetime(fv!item,month(12),day(31),hour(12),minute(1),second(1)) ) } ), pagingInfo: a!pagingInfo( startIndex: 1, batchSize: - 1, sort: a!sortInfo( field: "created", ascending: true ) ) ) ) )} )

Mike Schmitt · Answer

For what it's worth, instead of running this query in a loop, I believe the filter values for all 3 years could be added inside a nested LogicalExpression, where the top-level logical expression uses the "OR" operator, and then the inner one uses code similar to what you have here. If querying among a large list of years this could be dramatically less hurtful to system performance.

Not able to filter basis on year into timestamp

Top Replies