Integrated Application Platform › Forums › General › Cumulative Total
- This topic has 13 replies, 2 voices, and was last updated 9 years, 7 months ago by
ajith.
-
AuthorPosts
-
October 25, 2013 at 12:29 pm #767
ajith
ParticipantHi,
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,
ajithOctober 25, 2013 at 2:34 pm #1029amckinlay
KeymasterIn a report? Or a browse? Or …
October 25, 2013 at 4:17 pm #1030ajith
ParticipantIn a browse
October 25, 2013 at 4:45 pm #1031amckinlay
KeymasterIt 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 #1032ajith
ParticipantHi,
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 #1033amckinlay
KeymasterDoing 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 #1034ajith
ParticipantHi,
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 #1035amckinlay
KeymasterI 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 #1036ajith
ParticipantHi,
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,
ajithOctober 26, 2013 at 6:08 pm #1037amckinlay
KeymasterYou 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 #1038ajith
ParticipantHi,
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,
ajithOctober 28, 2013 at 3:04 pm #1039ajith
ParticipantFrom 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 #1040amckinlay
KeymasterThere 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
October 31, 2013 at 4:19 am #1041ajith
ParticipantHi,
Thanks!
ajith -
AuthorPosts
- You must be logged in to reply to this topic.