Re: Self Referencing to keep a running balance
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.
