Self Referencing to keep a running balance

[dombrosy30]

I have created a table in dataverse and have a column that is self-referencing (1:N relationship). 

Just wondering how to create a running total in the Account Balance column.

 

Like in Excel, I would like to have each record in Account Balance = Previous balance + Credit - Debit.

I can't seem to find out how to make the Previous Balance work.

Thanks.

[MarioRing]

You must realize that the Dataverse is significantly different from Excel. In Excel, related formula cells automatically update when any of the components of the entire spreadsheet changes. This is not the case in the dataverse, each record is independent of the others.

Note - the exceptions to this rule are the "autocalculated" and "power Fx formula" fields, which change their value when one of the components changes value, but only for the SAME record.

If you want a change in the value of a record to change the value of other records at the same time, you have to write an automation yourself (plugin or Power Automate Flow) that will take care of it.

In your case, you can write a Power Automate Flow that:

• Starts when a new "Transaction" record is created
• Searches the database for the latest transaction (by creation date or serial number)
• Takes "Account Balance" from the previous record, adds "Credit" and subtracts "Debit", and writes the calculated value to the "Account Balance" of the new record.

Note that this is not the safest solution. Operations of this type, for example in banks, must be carefully secured and tested. You have to take into account situations when several transactions are called at the same time or when someone corrects something in one of the historical records, and many others.

It is not recommended to use such mechanisms for important settlements. Try to record transactions and generate balance on demand using reporting tools.

[parvezghumra]

@dombrosy30 As long as your 'Transaction' table has a lookup column to reference the 'previous' Transaction, I think you should be able to achieve what you need, by implementing 'Account Balance' as a calculated column. See here: https://learn.microsoft.com/en-us/power-apps/developer/data-platform/calculated-rollup-attributes

The logic for the Calculated Column would be something like:
IF 'Previous Transaction' is empty, THEN 'Account Balance' = Credit - Debit
ELSE 'Account Balance' = 'Previous Transaction'.'Account Balance' + 'Credit' - 'Debit'