Using values in list from previous (closing balance) entries - Forum - Rollbase - Progress Community

Using values in list from previous (closing balance) entries

 Forum

Using values in list from previous (closing balance) entries

This question has suggested answer(s)

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