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
Discussion posts and replies are publicly visible
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) ) )
//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]
Or Maybe a Query Rule can do this?
You would have the view in the database and then query that view through a query rule.