Filter data between filter of two dates

Hello R5127 
What's your question? Please share some information.

Hello,

The question is how to filter a data, "id" here, between two dates with the code above

Changed a little bit. Can you try out this and check if this is what you are looking for

a!localVariables(
  local!data: a!queryEntity(
    entity:cons!DS_DATA,
    query: a!query(
      logicalExpression: {
        a!queryLogicalExpression(
          operator: "AND",
          filters: {
            a!queryFilter(
              field:"startTime",
              operator: ">=",
              value : todatetime(ri!startDate),
              applyWhen: a!isNotNullOrEmpty(ri!startDate)
            ),
            a!queryFilter(
              field:"endtime",
              operator: "<=",
              value : todatetime(ri!startDate),
              applyWhen: a!isNotNullOrEmpty(ri!startDate)
            ),

          }
        )
      },
      pagingInfo: a!pagingInfo(
        startIndex: 1,
        batchSize: -1
      )
    ),
    fetchTotalCount: true
  ).data,
  index(
    local!data,
    "pId",
    null
  )
)

Changed a little bit. Can you try out this and check if this is what you are looking for

a!localVariables(
  local!data: a!queryEntity(
    entity:cons!DS_DATA,
    query: a!query(
      logicalExpression: {
        a!queryLogicalExpression(
          operator: "AND",
          filters: {
            a!queryFilter(
              field:"startTime",
              operator: ">=",
              value : todatetime(ri!startDate),
              applyWhen: a!isNotNullOrEmpty(ri!startDate)
            ),
            a!queryFilter(
              field:"endtime",
              operator: "<=",
              value : todatetime(ri!startDate),
              applyWhen: a!isNotNullOrEmpty(ri!startDate)
            ),

          }
        )
      },
      pagingInfo: a!pagingInfo(
        startIndex: 1,
        batchSize: -1
      )
    ),
    fetchTotalCount: true
  ).data,
  index(
    local!data,
    "pId",
    null
  )
)

Sorry, the field endTime does not exist in our case. So, I want to query in one field starTime but with 2 variables startDate and endDate with like this :

You can use a single query filter for the field and use the ‘between’ operator and pass your value in an array {ri!startTime, ri!endTime}

a!localVariables(
 local!startDate: todatetime(ri!startTime),
  local!endDate todatetime(ri!endTime),
  local!pId: a!queryEntity(
    entity:cons!DS_DATA,
    query:
    a!query(
      selection:
        a!querySelection(
          columns: {
            a!queryColumn(
              field:"pId",
              alias:"pId"
            )
          }
        ),
      logicalExpression: {
        a!queryLogicalExpression(
          operator: "AND",
          filters: {
            a!queryFilter(
              field:"startTime",
              operator: ">=",
              value : local!startDate
            ),
            a!queryFilter(
              field:"startTime",
              operator: "<=",
              value : local!endDate
            ),
            
          }
        )
      },
      pagingInfo: a!pagingInfo(
        startIndex: 1,
        batchSize: -1
      )
    ),
    fetchTotalCount: true
  ).data,
  local!pIdR:index(
    local!pId,
    "pId",
    {}
  ),

Are you still facing any issues while trying to use this code?

thks, It works with this

Awesome. Make sure that you pass the value for local!startDate and local!endDate otherwise your query will start throwing error. Or else, you use applyWhen within your a!queryFilter to only apply the filter when local!startDate and local!endDate are not null

Great! Please verify the answer which was useful to you, so that the thread will be marked as closed.