Cumulative Total

[ajith]

Hi,
Is it possible to have cumulative totals as a calculated field?
An example would be the a way to implement the last column in the list below as a calculated field.

Item Date Amount Cumulative
A 01-Jan-2013 10 10 —> cumulative equals amount as this is the first record
B 02-Jan-2013 20 30 —> equals yesterdays cumulative and todays amount
C 03-Jan-2013 10 40 —> equals yesterdays cumulative and todays amount
Thanks,
ajith

[amckinlay]

In a report? Or a browse? Or …

[ajith]

In a browse

[amckinlay]

It is difficult to do this with a rule because the value depends on the position in the list.

It is probably easier to do from the controller – looping through the rows, accumulating a total, and setting the value for each row.

If you want this to update automatically you can do it in response to an event from the Browse such as Browse_CellValueChanged(data, column)

[ajith]

Hi,
I am currently defining standard columns and then updating them by calling a function as and when required. There are multiple such columns requiring different accumulations. So, the updation takes some time. It will increase as the number of records increase. If done from within the controller, will it give any speed advantage for the same number of columns and rows?
ajith

[amckinlay]

Doing it in the controller should mean that you can update all the fields “at one time”. This should be faster than if you update them at different times.

Other than that, there is no direct speed advantage to doing it from the controller.

[ajith]

Hi,

If the update function doesn’t run a QueryApply for each column and if in one QueryApply, all the required columns are updated, it should be same as running from inside the controller? In that case, I will stick to the function that I have now.
Thanks for the help,
ajith

[amckinlay]

I was thinking of updating the browse data that is already in memory. That will be quicker than doing queries.

[ajith]

Hi,

Cumulative has to start from the beginning of the records to calculate the value for the last. If there are many records(say around 1000, may be upto 3000), won’t loading all records in the Browse’ memory slow down the processes? Will the Browse unload the records after accessing them?

Can this be made more general without being tied to Browse / Access. I mean, can this calculation be made to happen when a record is inserted or modified by any code. Is Trigger an option?

Thanks,
ajith

[amckinlay]

You could use a trigger but you would need to update the range of records from the one you inserted to the end. If you are usually adding on the end, this is fast.

Browse does load all the records (from the query) into memory.

[ajith]

Hi,

Mostly it is at the end. Though some have to start a few (10,20 based on user selection) records before the current one. May be I can have a Trigger for those columns that adds at the end and keep my function to do the rest.
Thanks,
ajith

[ajith]

From a Trigger, even if it is one record before the current record, I will have to use QueryLast or some other similar function? If it is some few records back, I will have to use QueryApply and cycle from the beginning to reach the record I require, shouldn’t I? Is there some (undocumented) function where we can specify last but one(-1), -5 etc?
ajith

[amckinlay]

There is no way to start some number of records from the end (or beginning).

But I assume you have some field that determines the order of the records. Assuming this field is indexed, you can start at a certain record by doing a query with >= some value and this will be fast since it can use the index.

If you need to select based on other fields as well, then you might need different indexes. For example, if you were doing “group = x and order >= value” then to make this fast you would need an index on group,order

[ajith]

Hi,

Thanks!
ajith

[Author]

Posts