Skip to main content
sivanagamalleswarit0003
sivanagamalleswarit0003
April 21, 2022
Question

a!querylogicalExpression

  • April 21, 2022
  • 12 replies
  • 0 views

I have a query entity where i am passing date column AA to fetch some info from table. Now based on Source,i need to use different date columns.
Example: For Source A i have to pass column DateAAAA, for source BB pass DateBBB etc.

I dont find a way to pass the source here. Like if source is A,then do this ... 

a!queryFilter(
field: "dateFilterXXXX",
operator: ">=",
value: gmt(
todatetime(
workday(
local(now(), "America/New_York"),
-10,
ri!holidays
) & " " & totime("16:00 PM")
)
)
),

else....

a!queryFilter(
field: "dateFilterBBBB",
operator: ">=",
value: gmt(
todatetime(
workday(
local(now(), "America/New_York"),
-10,
ri!holidays
) & " " & totime("16:00 PM")
)
)
),

I am not passing parameters from any other place into this rule, so i dont find a way to pass the source. I need to get the source first and then define filters accordingly.

Please suggest on how i can get the source from entity and then pass filters here. I tried to querycolumn for source but unable to notate it or index it anywhere.

 

a!queryEntity(
  entity: cons!TABLE_NAME,
  query: a!query(
    selection: a!querySelection(
      columns: {
        a!queryColumn(field: "id"),
        a!queryColumn(field: "type"),
        a!queryColumn(field: "source").....
      }
    ),
    logicalExpression: a!queryLogicalExpression(
      operator: "AND",
      filters: {
        a!queryFilter(
          field: "fieldAAAA",
          operator: "<>",
          value: "AAAA"
        ),
        a!queryFilter(
          field: "fieldBBB",
          operator: "<>",
          value: "BBBB"
        ),
        
        a!queryFilter(
          field: "dateFilterXXXX",
          operator: ">=",
          value: gmt(
            todatetime(
              workday(
                local(now(), "America/New_York"),
                -10,
                ri!holidays
              ) & " " & totime("16:00 PM")
            )
          )
        )
      },
      logicalExpressions: {
        a!queryLogicalExpression(
          operator: "OR",
          logicalExpressions: {
            a!forEach(
              items: ri!dailyPriorityCriteria,
              expression: {
                a!queryLogicalExpression(
                  operator: "AND",
                  filters: {
                    a!queryFilter(
                      field: "type",
                      operator: if(
                        rule!APN_isEmpty(fv!item.type),
                        "is null",
                        "="
                      ),
                      value: fv!item.type
                    ),
                    a!queryFilter(
                      field: "status",
                      operator: "=",
                      value: fv!item.status
                    ),                    
                    a!queryFilter(
                      field: "priority",
                      operator: "<",
                      value: ""
                    )
                  }
                )/*END AND*/

              }
            )/*END FOR EACH*/

          }
        )/*END OR*/

      },
      ignoreFiltersWithEmptyValues: true
    ),
    pagingInfo: a!pagingInfo(
      startIndex: 1,
      batchSize: cons!FAS_MAX_CASES_TO_INCREASE_PRIORITY
    )
  ),
  fetchTotalCount: false
).data

    12 replies

    N
    nareshs945
    April 22, 2022

    Hi,

    Are you trying to filter or query data based on the selected source. Provide some additional details why you want to filter data based on source?

    harshitb6843
    harshitb6843
    April 22, 2022

    Hi Naresh. Suggest as the answer is a function you use when you think this is the answer to the question asked, 
    In your case, you are just raising another question and marking it as a suggested answer. Please keep the community clean and avoid such practices. 

    stefanhelzle0001
    stefanhelzle0001
    Brainy
    April 22, 2022

    Using a simple applyWhen would allow you to enable certain filters on conditions. My question is: How is this "source" defined and where is the value coming from?

    sivanagamalleswarit0003
    sivanagamalleswarit0003Author
    April 22, 2022

    Hello Stefan,

    Thank you for the response.

    Source is another column in the same Table and coming from table in this case. So i have columns... "Source", "DateFieldAAA", "DateFieldBBB" etc.

    When Source = AAA, query filter on "DateFieldAAA" and when source = BBB, query filter on "DateFieldBBB".

    Even to use applywhen() condition, i have to first get the source.

    i tries something like this, which i am sure would not help me.

    a added a!queryselection(column: "source"), and under filters:

    if(

    a!queryFilter(
    field: "source",
    operator: "=",
    value: "AAA"
    ),

    a!queryFilter(
    field: "DateFieldBBB",
    operator: ">=",
    value: gmt(
    todatetime(
    workday(
    local(now(), "America/New_York"),
    -10,
    ri!holidays
    ) & " " & totime("16:00 PM")
    )
    ),

    a!queryFilter(
    field: "DateFieldAAA",
    operator: ">=",
    value: gmt(
    todatetime(
    workday(
    local(now(), "America/New_York"),
    -10,
    ri!holidays
    ) & " " & totime("16:00 PM")
    )
    ),

    )

    sivanagamalleswarit0003
    sivanagamalleswarit0003Author
    April 22, 2022

    i am adding the actual code i am using for additional info, please check....

    ri!dailyPriorityCriteria would just get the cases based on the filters we pass above..

    IN this code, we only have the filetes for requestReceivedOn date field, now we have new field "requestReceivedfromexternalsource" which is specific to some sources.

    a!queryEntity(
  entity: cons!FAS_DSE_CASE,
  query: a!query(
    selection: a!querySelection(
      columns: {
        a!queryColumn(field: "id"),
        a!queryColumn(field: "clientTypeRefId"),
        a!queryColumn(field: "transactionRefId"),
        a!queryColumn(field: "transactionSubRefId"),
        a!queryColumn(field: "priority"),
        a!queryColumn(field: "requestReceivedOn"),
        a!queryColumn(field: "requestReceivedOn")
      }
    ),
    logicalExpression: a!queryLogicalExpression(
      operator: "AND",
      filters: {
        a!queryFilter(
          field: "caseStatusRefId",
          operator: "<>",
          value: cons!FAS_REF_ID_CASE_STATUS[3]
        ),
        a!queryFilter(
          field: "caseStatusRefId",
          operator: "<>",
          value: cons!FAS_REF_ID_CASE_STATUS[4]
        ),
        /*FROM: created 10 bus days ago at 00:00am*/
        a!queryFilter(
          field: "requestReceivedOn",
          operator: ">=",
          value: gmt(
            todatetime(
              workday(
                local(now(), "America/New_York"),
                cons!FAS_MAX_DAYS_TO_INCREASE_PRIORITY,
                ri!holidays
              ) & " " & totime("16:00 PM")
            )
          )
        ),
        /*UP TO: created 1 bus days ago at 23:59pm*/
        a!queryFilter(
          field: "requestReceivedOn",
          operator: "<",
          value: gmt(
            todatetime(
              workday(
                local(now(), "America/New_York"),
                - 1,
                ri!holidays
              ) & " " & totime("16:00 PM")
            )
          )
        )
      },
      logicalExpressions: {
        a!queryLogicalExpression(
          operator: "OR",
          logicalExpressions: {
            a!forEach(
              items: ri!dailyPriorityCriteria,
              expression: {
                a!queryLogicalExpression(
                  operator: "AND",
                  filters: {
                    a!queryFilter(
                      field: "clientTypeRefId",
                      operator: if(
                        rule!APN_isEmpty(fv!item.clientTypeRefId),
                        "is null",
                        "="
                      ),
                      value: fv!item.clientTypeRefId
                    ),
                    a!queryFilter(
                      field: "transactionRefId",
                      operator: "=",
                      value: fv!item.transactionRefId
                    ),
                    a!queryFilter(
                      field: "transactionSubRefId",
                      operator: "=",
                      value: fv!item.transactionSubRefId
                    ),
                    a!queryFilter(
                      field: "priority",
                      operator: "<",
                      value: tointeger(fv!item.startingPriority) + tointeger(
                        sum(
                          reject(
                            isnull(_),
                            {
                              fv!item.increaseDay1,
                              fv!item.increaseDay2,
                              fv!item.increaseDay3,
                              fv!item.increaseDay4,
                              fv!item.increaseDay5,
                              fv!item.increaseDay6,
                              fv!item.increaseDay7,
                              fv!item.increaseDay8,
                              fv!item.increaseDay9,
                              fv!item.increaseDay10
                            }
                          )
                        )
                      )
                    )
                  }
                )/*END AND*/
                
              }
            )/*END FOR EACH*/
            
          }
        )/*END OR*/
        
      },
      ignoreFiltersWithEmptyValues: true
    ),
    pagingInfo: a!pagingInfo(
      startIndex: 1,
      batchSize: cons!FAS_MAX_CASES_TO_INCREASE_PRIORITY
    )
  ),
  fetchTotalCount: false
).data

    Terms & ConditionsAccessibility statement