a!querylogicalExpression

Question

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

Stefan Helzle · Answer

No. In SQL code you can do a subselect and fetching data and use it in a condition. This is not possible in Appian. So putting a queryFilter as a condition into an if statement will not work.

Mike Schmitt · Answer

I don't understand why you can't just use QueryLogicalExpression nesting to accomplish this. Your example code posted above already contains an example of nested QLEs so you already understand how it should be structured. In pseudocode all you'd need to add for the source/date combinations is something like this: 
 logicalExpressions: {
 a!queryLogicalExpression(
 operator: "OR",
 logicalExpressions: {
 
 /* first source */
 a!queryLogicalExpression(
 operator: "AND",
 filters: {
 /* Query Filter for Source = AAA */
 (...)
 /* Query Filter on Date Field AAA */
 (...)
 }
 ),
 
 /* second source */
 a!queryLogicalExpression(
 operator: "AND",
 filters: {
 /* Query Filter for Source = BBB */
 (...)
 /* Query Filter on Date Field BBB */
 (...)
 }
 )
 }
 )
},

Mike Schmitt · Answer

As you read through the rows of returned data (including the unexpected extras), what rows are being returned that don't match the desired parameters? In what way do those rows fail to meet your expectations? This is important to know to determine what the issue might be. If you're able to try again, could you post the full code of that version of the query?

a!querylogicalExpression

Top Replies