Using values in list from previous (closing balance) entries

Posted by ionacaerex on 07-Jun-2017 12:18

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

All Replies

Posted by Santosh Patel on 08-Jun-2017 12:09

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};
Posted by ionacaerex on 08-Jun-2017 15:46

ok thanks Santosh will try this asap.

This thread is closed