Skip to main content
Participating Frequently
Feb 20, 2023 3:46 PM UTC
Question

Not Exist between this date

Hi everyone, Can appian do this query? I would like to query all of the data of the merchant without transaction between a specified date range and show it in the read-only grid, I already have view table  but I can show all of the merchant without transaction between a specified date range.

5 replies

richardm900458
Inspiring
Feb 20, 2023 4:31 PM UTC

can you share you CDT/Record structure(s)?
I think we need to see more input. in general filtering for dates is not an issue.

stefanhelzle0001
Brainy
Feb 20, 2023 6:23 PM UTC

To query record IN a specific range, you do something like "datefield >= START AND datefield <= END". The opposite would be "datefield <= START AND datefield >= END". I think it is not the question whether Appian can do this query ....

venkatrea696188
Inspiring
Mar 6, 2023 9:59 AM UTC

Hey Stefan, I achieved it through following code, Could you please check it & tell me whether it's acceptable or not.(in place of today he can use his dates)

a!queryLogicalExpression(
operator: "AND",
filters: {

a!queryFilter(
field: "HolidayDate",
operator: "not in",
value: a!forEach(items: enumerate(1+tointeger(a!addDateTime(today(),"","",30,"","","","","")-today())),
expression: today()+fv!item)
)
},
ignoreFiltersWithEmptyValues: true
)

stefanhelzle0001
Brainy
Mar 6, 2023 10:10 AM UTC

I think this is not a good idea. You need to compare each item in DB against a list of 31 values! This cries for bad performance.

abhayd3663
Brainy
Feb 20, 2023 7:35 PM UTC

Appian a!queryFilter() supports "BETWEEN" operator however it does not support something like NOT BETWEEN. To achieve the same you can refer to the code below.

a!queryEntity(
  entity: cons!ENT_MERCHANT,
  query: a!query(
    logicalExpression: a!queryLogicalExpression(
      operator: "AND",
      filters: {
        a!queryFilter(
          field: "transaction",
          operator: "is null"
        ),
/* Option-1  To get the data BETWEEN two dates*/
        a!queryFilter(
          field: "transactionDate",
          operator: "between",
          value: {"01/01/2022","12/31/2022"}
        ),
/* Option-2  To get the data NOT BETWEEN two dates*/
        /*a!queryFilter(*/
          /*field: "transactionDate",*/
          /*operator: "<",*/
          /*value: */
        /*),*/
        /*a!queryFilter(*/
          /*field: "transactionDate",*/
          /*operator: ">",*/
          /*value: */
        /*)*/
      },
      ignoreFiltersWithEmptyValues: true
    ),
    pagingInfo: a!pagingInfo(
      startIndex: 1,
      batchSize: -1
    )
  )
)