Get size of view with Expression Rule

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

  Discussion posts and replies are publicly visible

Parents
  • 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:

Reply
  • 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:

Children
  • 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)?

  • 0
    Appian Employee
    in reply to barm

    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