October 25, 2013 at 12:29 pm #767
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
ajithOctober 25, 2013 at 2:34 pm #1029
In a report? Or a browse? Or …October 25, 2013 at 4:17 pm #1030
In a browseOctober 25, 2013 at 4:45 pm #1031
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)October 25, 2013 at 5:00 pm #1032
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?
ajithOctober 25, 2013 at 5:34 pm #1033
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.October 25, 2013 at 6:26 pm #1034
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,
ajithOctober 26, 2013 at 1:54 pm #1035
I was thinking of updating the browse data that is already in memory. That will be quicker than doing queries.October 26, 2013 at 2:57 pm #1036
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?
ajithOctober 26, 2013 at 6:08 pm #1037
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.October 27, 2013 at 11:53 am #1038
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.
ajithOctober 28, 2013 at 3:04 pm #1039
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?
ajithOctober 30, 2013 at 2:30 am #1040
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,orderOctober 31, 2013 at 4:19 am #1041
- You must be logged in to reply to this topic.