Skip to main content
H
harriesrillm0002
February 20, 2023
Question

Not Exist between this date

  • February 20, 2023
  • 5 replies
  • 0 views

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
richardm900458
February 20, 2023

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
stefanhelzle0001
Brainy
February 20, 2023

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
venkatrea696188
March 6, 2023

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
stefanhelzle0001
Brainy
March 6, 2023

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
abhayd3663
February 20, 2023

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

Terms & ConditionsAccessibility statement