Not able to filter basis on year into timestamp

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 .

  Discussion posts and replies are publicly visible

Parents
  • 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
    )
    )
    )
    )
    )}

    )

  • 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.

Reply Children
No Data