Skip to main content
nicholaso0002
nicholaso0002
February 28, 2022
Solved

How do I add the paymentAmt based on the CaseId

  • February 28, 2022
  • 10 replies
  • 0 views

Hello Team,

I am trying to create a new view with expression rule or sql script that will include the sum(PaymentAmt) for cases where directDepositTF is 1. For example in the attached image, caseId 138465, I would like a view that sums the amount in paymentAmt column into a new view. Thanks

    Best answer by andrewh0007

    For a view you could use this.

    SELECT
    `caseId`,
    SUM(`paymentAmt`)
FROM `TABLE_NAME`
WHERE
	`directDepositTF` = 1
GROUP BY `caseId`;

    10 replies

    andrewh0007
    andrewh0007Answer
    March 1, 2022

    For a view you could use this.

    SELECT
    `caseId`,
    SUM(`paymentAmt`)
FROM `TABLE_NAME`
WHERE
	`directDepositTF` = 1
GROUP BY `caseId`;

      nicholaso0002
      nicholaso0002Author
      March 1, 2022

      Thank you [mention:e70b14ab0a7346dab1a7041b57706860:e9ed411860ed4f2ba0265705b8793d05] I will try this and let you know.

      andrewh0007
      andrewh0007
      March 1, 2022

      And for an expression rule you could use this.

      a!queryEntity(
  entity: cons!DATA_STORE_ENTITY,
  query: a!query(
    aggregation: a!queryAggregation(
      aggregationcolumns: {
        a!queryAggregationColumn(
          field: "caseId",
          isgrouping: true
        ),
        a!queryAggregationColumn(
          field: "paymentAmt",
          isgrouping: false,
          aggregationfunction: "SUM"
        )
      }
    ),
    filter: a!queryFilter(
      field: "directDepositTF",
      operator: "=",
      value: true /*Or "1"??*/
    ),
    pagingInfo: a!pagingInfo(1, - 1)
  )
)

        nicholaso0002
        nicholaso0002Author
        March 2, 2022

        //How do I put this in Appian:

SELECT [caseId]
,[directDepositTF]
,[deptCaseTypeId]
      ,[caseTypeCode]
      ,[paymentFormTypeId]
,SUM([paymentAmt]) AS aggPaymentAmt
FROM [Appian_DB].[dbo].[CSO_PAYMENT_FILE_OUTPUT_VIEW]
WHERE directDepositTF = '1' AND paymentFormTypeId = '1' and recptDateTS >CONVERT(VARCHAR(10),GETDATE(),110) and recptDateTS < CONVERT(VARCHAR(10),GETDATE()+1,110)
GROUP BY [caseId],[directDepositTF],[deptCaseTypeId],[caseTypeCode],[paymentFormTypeId]

        nicholaso0002
        nicholaso0002Author
        March 2, 2022

        Or Maybe a Query Rule can do this?

        stewart.burchell
        stewart.burchell
        March 1, 2022

        In the recent versions of Appian that have Modern Records you can create relationships between the different Record Types and then include Custom Record Fields - i.e. values that are derived from attributes on a Record Type. You can include functions like 'sum', so this would be another way of achieving your requirement. In short this allows you to construct the kinds of functionality that [mention:e70b14ab0a7346dab1a7041b57706860:e9ed411860ed4f2ba0265705b8793d05] is describing in his SQL VIEW example - without ever having to go to the database itself.

        nicholaso0002
        nicholaso0002Author
        March 1, 2022

        Can you share more light on this with examples based on the question. Also, a link on how to implement it will be great. Thanks

        gopalk2865
        gopalk2865
        Participating Frequently
        March 3, 2022

        Hi, you can go through this link for more information about relationships in record.

        [View:https://docs.appian.com/suite/help/21.4/record-type-relationships.html]

        Terms & ConditionsAccessibility statement