Skip to main content
B
barm
December 4, 2022
Question

Get size of view with Expression Rule

  • December 4, 2022
  • 6 replies
  • 1 view

Hi,

Is there any way I can get the size (number of rows) of a view by running a code in Expression Rule?

In addition,

Is there any way I can write SQL queries directly in Expression Rule?

Thanks in advance

6 replies

stefanhelzle0001
stefanhelzle0001
Brainy
December 4, 2022

https://docs.appian.com/suite/help/22.4/fnc_system_a_queryentity.html#using-the-fetchtotalcount-parameter

We cannot write SQL code in an expression rule. What is your use case? Maybe there are alternative ways.

    B
    barmAuthor
    December 6, 2022

    Thank you very much.

    I would like to "run" the following query:

    SELECT COUNT(*) FROM <table_name>
    WHERE <table_name>.<column_name> >= CAST(CURRENT_TIMESTAMP() AS DATE) - INTERVAL 1 DAY

    According to your suggestion, I use a!queryEntity() to count the number of rows.

    However, how can I add the condition (where)?

      mikes0011
      mikes0011
      Brainy
      December 6, 2022
      However, how can I add the condition (where)?

      Of course.  a!queryEntity() accepts a broad variety of filtering parameters, via a!queryFilter() and a!queryLogicalExpression.  You'd just add an a!queryFilter like, a!queryFilter(field: "myTimestampField", operator: ">=", value: today() - 1), or similar.

      The Expression Guru
      stewart.burchell
      stewart.burchell
      December 5, 2022

      Hey - you can think of Appian queries - a!queryEntity() or a!queryRecord() - as being the low-code equivalents of SQL queries. You can, for example, run an a!queryEntity() thaty returns no rows but does return the totalCount based upon your query. Here's a simple example that doesn't apply any filters but does return the totla number of rows for the target entity:

      a!queryEntity(
  entity: cons!GIA_DSE_AUDIT,
  query: a!query(
    pagingInfo: a!pagingInfo(
      startIndex: 1,
      batchSize: 0
    )
  ),
  fetchTotalCount: true
)

      and the result looks like this:

        B
        barmAuthor
        December 6, 2022

        Thank you very much.

        I would like to "run" the following query:

        SELECT COUNT(*) FROM <table_name>
        WHERE <table_name>.<column_name> >= CAST(CURRENT_TIMESTAMP() AS DATE) - INTERVAL 1 DAY

        According to your suggestion, I use 

        a!queryEntity(
        entity: cons!CM_VW_ECHO_CM_CASE,
        query: a!query(
        pagingInfo: a!pagingInfo(
        startIndex: 1,
        batchSize: 0
        )
        ),
        fetchTotalCount: true
        ).totalCount

         to count the number of rows.

        However, how can I add the condition (where)?

        stewart.burchell
        stewart.burchell
        December 6, 2022

        In a Query Entity (as per your example) you will use the 'filter' attribute of a Query Entity to apply the required condition. An individual filter is made up of 3 attributes:

        • filed - this maps to the column you're interested in filtering by
        • operator - what logical operator you want to apply - in your case it'll be "="
        • value - the value you want to apply the operator to - in your case it'll be be something like fn!todate(fn!now() - 1

        Note that if your existing table/view has a column of type datetime my example won't work as Appian cannot compare a Date to a Datetime. I think you'd need to create an additional column in your View (I'm guessing it's a view given its prefix in your example!) that is already CAST to Date, and then my example should work

        Terms & ConditionsAccessibility statement