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

    )

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

    )

Children