Known Participant

Solved

How do I add the paymentAmt based on the CaseId

10 replies
23 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

andrewh0007Answer

Inspiring

For a view you could use this.

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

andrewh0007Answer

Inspiring

For a view you could use this.

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

nicholaso0002Author

Known Participant

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

andrewh0007

Inspiring

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)
  )
)

nicholaso0002Author

Known Participant

//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]

nicholaso0002Author

Known Participant

Or Maybe a Query Rule can do this?

stewart.burchell

Employee

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.

nicholaso0002Author

Known Participant

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

Participating Frequently

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]

Sign up

Login with SSO

Login to the community

Login with SSO