Running total / get value from previous record

[rafig]

Hi,
I am trying to generate in a related table view a list of stock movements for an item.
I had been using static values and a global stock move method that would pick up the previous closing stock, set the new movement opening stock to that and then set the closing stock to that value + / - whatever the stock move was (a delivery [+] or usage [-]), but the problem is that not everything happens in the correct chronological order, so sometimes deliveries can't be entered until weeks later (when everything is confirmed about it) and so that means things don't 'flow' correctly with opening and closing values.

I tried doing this in the tables calculations

Code: Select all

function c_qty_closing ()
{
   return c_qty_opening + qty_added + qty_adjusted + stock_discrepency - qty_allocated - qty_subtracted;
}

function c_qty_opening ()
{
   var i = currentcontroller.getSelectedIndex() ; // pick up which line in the related set we are?
   if (i == 1)
   {
      return paper_stock_movement_by_paper_printer_to_paper_stock.opening_stock ; // picks up parent's opening stock value
   }
   else
   {
      return foundset.getRecord(i-1).c_qty_closing; // get the previous records closing value
   }
}


but it didn't work (I have vague memories of calcs not being able to access some of these things...)

I hate to say it, but FileMaker has the ability to get a value from the previous record and do running totals, which I need to be able to do now... (this system replaced a FM system and I thought I'd managed to beat it, but now need those capabilities...)

The only other possible solution might be to put all the related stock movements into an array and then loop thru that calculating it all and then displaying that array in the form tab, instead of a table view (how??)

Thanks

Rafi

[jcompagner]

don't use things like currentcontroller in a calculation.
That is a really really bad thing to do.
What happens for example if that calc is calculated from a tabpanel in a mainpanel X and later on it is calculated in a tabpanel in mainpanel Y?

Also we really don't track these things so a calc is not recalculated on the fly, because we have no idea that something did change..

What you can try is use a self reference relation.. (that is a relation where you have in source and destination the same and no matching columns defined, so no relation items)
problem that i can't also guarantee that we will changes through that relation and recalculate if the previous record is not the same anymore (so if the foundset is really changed by a find)

[rafig]

don't use things like currentcontroller in a calculation.
That is a really really bad thing to do.
What happens for example if that calc is calculated from a tabpanel in a mainpanel X and later on it is calculated in a tabpanel in mainpanel Y?

Also we really don't track these things so a calc is not recalculated on the fly, because we have no idea that something did change..

Ok

What you can try is use a self reference relation.. (that is a relation where you have in source and destination the same and no matching columns defined, so no relation items)
problem that i can't also guarantee that we will changes through that relation and recalculate if the previous record is not the same anymore (so if the foundset is really changed by a find)

Will give that a try. Never made a self-join before with nothing in the columns...
Thanks

Rafi

[david]

There are a number of ways of accomplishing running totals -- the first of which was how it was done in Filemaker before they had the getFromPreviousRecord (or whatever it is called) function.

1- Start with an incrementing column that represents the record order. Create a calculation that is one less than the incrementer column. Then create a relationship from the calc to the incrementer. Through this relation you see the previous record.

2- Start with a date column that represents record order. Create a self relation from this column to this column with the match being "greater than". Order the relation by this column. This relation in theory returns all the records less than the current one but behaviorally, if you reference a related record value directly in a calc -- it will pick up the value of the first record match in the relation. Because of the sort order in the relation, you are then always picking up the next greatest date value less than the current record's date value.

3- Create a non-stored calc and set it to "// return 1". This gives you an "empty" calc. For all the events that show and change the running total, run a method that fills the empty calc column with the correct values.

4- Create a merge table based on a cross join or some other SQL trick (check out Postgres' windowing functions) and base your form on the merge table.

5- Base your form on a dataset returned via a stored procedure. Stored procedure does all the heavy lifting for you. Return the stored procedure every time you need to update the view.

#3 is quite easy to do and all of your business logic is in a method (easy to find, modify and debug) and is very fast (everything happens at the client). #1 and #2 I would shy away from but I do have a bias against using calculations for this level of complexity (slow, lots of moving parts). #4 works great if other systems are involved. #5 may already be done for you if you are hooking into a financial system.

Each technique needs to be "managed" to some degree. #1 you need to make sure the ordering column doesn't get out of sync or skip a number. So when you delete and add records you have to manage this value. #5 requires you to handle all CRUD operations back the database. #3 requires you to make sure you are updating the totals at every event. Etc.

[rafig]

Wow!
Thanks David!

A lot to think about and try

I will have a play next time I am at client...

Johan, can I put in a feature request for these two, access to previous record and running totals?

Thanks

Rafi

[sbutler]

Maybe try a rowbg color trick/hack? Don't try this at home

- Create a non-stored calc and set it to "// return 1", lets say its called fsCountStore
- Use this code in the rowbgcolor calc

Code: Select all

//use a JS global
//create if not yet defined
try{
  if(!fsCount)
    fsCount = []
}
catch(e){
  fsCount = []
}

//set the value in the JS global to hold the info
var _rowindex = arguments[0];
fsCount[_rowindex] = columnNameToSum

var fsCountSum = 0
for(var i=0; i<_rowindex; i++){
  fsCountSum += fsCount[i]
}
//store it in the non-stored calc
fsCountStore = fsCountSum

//return color
return "#FFFFFF"


Something like that would probably require some more work, but off the top of my head, something like that may work, I'm just not sure if the rowBGColor event will fire in ascending order, and often enough to make it count properly. Its also pretty inefficient since it does a loop, so on a large set would be slow. plust the rowbg event fires WAY too much

[jcompagner]

Johan, can I put in a feature request for these two, access to previous record and running totals?

add a feature request in our support system..

i am currently thinking a bit how we could do it. Because it is kind of a special aggregate..
The problem with getPreviousRecord() is, so a calc like this:

function runningtotal()
{
return getPreviousRecord().runningtotal + mynumber;
}

that this will result in a stack overflow when you just scroll to the latest record if you had about 1000 in them...
Because you get 1000 nested calls..

So the best thing is if it is managed from the outside like #3 from davids list.

an idea for that is this:

make that self relation (so orders_to_orders without relation items)

make 2 calcs, 1 like david describes (where the running total comes in). and another that is your real total that you also show on the screen..

In that real total calc, you walk over the self join, setting the running total in all the records and returning the total value..

[rafig]

Thanks Scott & Johan.
I will try a few things on Thursday when I am at client & put in a feature request when I get a chance.

Rafi

[jasantana]

4- Create a merge table based on a cross join or some other SQL trick (check out Postgres' windowing functions) and base your form on the merge table.

Could you tell us some more about how to create the table and base the form on that table?

Let´s say that this is the SQL to generate the windowing function:
SELECT orderitems_id, sum(quantity) OVER (ORDER BY irderitems_id) FROM order_items

How do I base a form on that SQL?

Thanks.

[ROCLASI]

Hi Juan,

You can create a new form using the solutionModel and base that on a dataset like so.

Code: Select all

var yourDataSource = yourDataSet.createDataSource(formname, [columntypes]) // see JSDataSet object

Then you use this datasource object to create a new form and place your fields on it.

Hope this helps.