Ideas for creating a running balance column with collections

Hello,

Has anyone done an accounting-style listbox using Form and an entity selection or collection? What I’m trying to decide on is the easiest way to handle a running balance column.

I’ve come up with a couple of ways to do it but none of them employ the ORDA magic of working without having to loop through the collection. And this is 17.3 so I can’t add a formula to the displayed object.

This is the first time I’ve needed to revisit a form that does this sort of thing with ORDA. Accounting is always less intuitive but this seems harder than it should be.

Thanks

Is entitySelection.sum() not a good candidate?
https://doc.4d.com/4Dv18/4D/18/entitySelectionsum.305-4505913.en.html
https://doc.4d.com/4Dv18/4D/18/collectionsum.305-4505854.en.html

Hi Keith,
Sum is great for getting the sum of the entire column. But what I am looking for is creating a running balance showing the accumulated sum for each item.

It’s like a check register: you have the Open Balance at the top and for each check or deposit the balance column shows the balance after each transaction.

Using arrays it’s something like:
<code 4D>
$bal_array{$thisLine}:=$bal_array{$thisLine-1}+$trxn_amount{$thisLine}
</code 4D>

So there are two issues:

  1. if I’m using an entity selection how do I add the column for the balance column?
  • a listbox column and formula?
  • add a property to each record?
  • something else?
  1. how to deal with the getting the value from the prior element?

The best approach I’ve come up with is to loop the entity selection and create a display collection where each element of the collection is an object containing the record reference and a balance property:

<code 4D>
$display_col:=New collection
$sum:=0 // or the opening balance
For each($entity;$entitySelection)
$sum:=$sum+$entity.amount
$display_col.push(New object(“record”;$entity;“balance”;$sum))
End for each

</code 4D>

This works OK. I can still reference the entity directly and I’ve got a purely memory based property, “balance” that I can adjust any way I want.

It just seems a bit clunky and I feel like I’m locked into thinking about it from my old perspective.

Thoughts?

I have used an extra field (attribute) to a similar end. It has always worked, but it is only valid in the one specific context.

In a checkbook example, one idea to avoid constantly recalculating is to think of the selection as always being the entire list of records, and the running balance being the balance by date.
Once this running balance is calculated it becomes a static part of the record. It and all following entries only change if a previous entry is modified. This way you can show all records for a month, and the first entry has a running balance that already accounts for the previous months. The records must always be displayed in a specific order for this attribute to look right.

It’s an odd field in that its value is dependent on other entities, and its position within them.

: Kirk BROOKS

The best approach I’ve come up with is to loop the entity selection
and create a display collection where each element of the collection
is an object containing the record reference and a balance property:

Code 4D : Voir en français View in simple mode for copy :

1 $display_col:=New collection
2 $sum:=0//or the opening balance
3 For each($entity;$entitySelection)
4 $sum:=$sum+$entity.amount
5 $display_col.push(New object(“record”;$entity;“balance”;$sum))
6 End for each
7

Kirk, I have the same need for an account book and will use the same method to solve it.
If I need a dynamic recalculation, I will rebuild the entitySelection or collection.
A Keith pointed out, display a running balance has a meaning only if the list is sorted on date. In other sort orders, I will hide it.

Hi Kirk,
I understand you want something like this:
value…cumulate
2…2
4…6
3…9
7…16
5…21
If so, I’d try using a formula, but I’m a bit newbie with it and don’t see the trick:

  • I can read a current collection item with ‘This’, but how to get ‘This-1’?
  • a sum in the current item with a filter meaning “all items from start to me” (coll.slice?)

Perhaps using something like:

<code 4D>
$c:=New collection(2;4;3;7;5)
balance:=0
$c2:=$c.map(“getBalance”)

</code 4D>

method getBalance:

<code 4D>
C_OBJECT($1)
balance:=balance+$1.value
$1.result:=New object(
“value”;$1.value;
“balance”;balance)

</code 4D>

$c2:

[{value:2,balance:2},{value:4,balance:6},{value:3,balance:9},{value:7,balance:16},{value:5,balance:21}]

:muscle::clap:

Nice Vincent.

I had a feeling there was something I wasn’t thinking of. I haven’t really used .map() and this is a clear use case.

Thanks!