How do I add the paymentAmt based on the CaseId

For a view you could use this.

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

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

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 ajhick is describing in his SQL VIEW example - without ever having to go to the database itself.

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

Thank you ajhick I will try this and let you know.

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

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

I might try this later. Thanks