Running Balance

[MasterYoda]

I want to display a running balance of inventory transaction for a particular catagory. Summary functions will not allow me to choose related tables so that option does not work. I thought GetNthRecord would do it but maybe I just don't get it quite yet. So here's what I've got.

 

Interface file (This is the abstractionlayer. All keys are set here and I use portals to view all the data.

Inventory (Parent record of an item) Has a category and an ID

Transactions ( In/Out transactions of the inventory quantity ).

 

So lets say in my inventory I have things like, canned goods, fresh produce, meats and frozen goods. When I select canned goods in the interface, in my portal I want to see all canned goods, its first IN transaction and then following all other in and out transaction as I ship these items out and transfer more in. Next to that I want to see the running balance FOR THAT ITEM. So you might see:

 

Selected Canned Goods In Out Balance

 

Campbells Cream of Mushroom soup 20000 20000

Campbells Cream of Mushroom soup -5000 15000

Campbells Cream of Mushroom soup -5000 10000

Campbells Cream of Mushroom soup -5000 5000

Campbells Cream of Mushroom soup 10000 15000

Campbells Chicken Noodle 20000 20000

Campbells Chicken Noodle -5000 15000

 

You get the idea.

 

I tried to use a self relationship on the inventory id and then defined RunningBalance as GetNthRecord ( self relationship::RunningBalance ; -1 ) + transaction qty.

[Ender]

This sounds similar to this recent post:

 

http://filemakertoday.com/filemakertoday.com_non_ssl/com/showthread.php?t=10154

 

In your case, the solution involves filtering the self-join both by Date and by Product. With the relationship showing only the records that are older for the same product, you can use an aggregate sum() to get the previous balance, then add the current quantity.