Hi,
Would appreciate some help and design advice. I have a list of transactions (either debits or credits), which need to influence the opening and closing balance position which I would like to compute and display on each entry (See attachment below) This is a list view currently.
How can I get the opening balance of each list item to be set as the closing balance of the previous list item and for this logic to carry on within the sequence?
I am non-technical so any simple step by step advice would be much appreciated.the closing balance of the previous list item to be set as the
Thanks
Explanation:
Ordering by createdAt (descending) and limiting max rows for the query to 1 ensures that we receive the latest entry in the ledger. The where clause 'id!={!id}' ensures that we do not take into account the the ledger entry being created (note that we are using after create timing which means the record is already available)
Steps to follow on the ledger object:
Note: This approach does not let you mass update and make existing record entries in order.
Create an 'Update field value' trigger, say "Update Opening Balance"
timing: after create
field to change: opening balance
Formula code (change integration names for fields accordingly):
var result = rbv_api.selectQuery2("SELECT Closing_Balance FROM Ledger where id!={!id} order by createdAt desc", 0, 1);
var prevClosingBalance = 0;
if (result.length > 0) {
prevClosingBalance = result[0][0];
}
if (prevClosingBalance === undefined || prevClosingBalance === null) {
return 0;
} else {
return prevClosingBalance;
}
Create an 'Update field value' trigger "Update Closing Balance"
timing: after create
field to change: closing balance
Formula code:
return {!Opening_Balance#value} + {!Credit#value} - {!Debit#value};ok thanks Santosh will try this asap.