Page 1 of 1
Running total in data grid
Posted: Tue Sep 13, 2011 3:57 pm
by Fjord
Hi,
I have a data grid that shows a bank account. Each record has a field for the date, a field for money input or output and a field for the balance. The grid is sorted by date with a call to
Code: Select all
dispatch "SortDataByKey" to group "myList" with "MEDateFld" , "dateTime" , "ascending"
The user can modify the MEDateFld, and then the records of the dgData must be sorted again.
My problem is, I want to compute the balance, ie a running total of all inputs/outputs of the records of previous dates. How can I do that, considering that the order of records is important? I can't acces the records of the dgData in order...
Thanks for your help!
Re: Running total in data grid
Posted: Tue Sep 13, 2011 4:05 pm
by dunbarx
I am not understanding. You can sort your data at any time by any sortkey. You can extract, act on and restore the data to its original sorted order. Can you tell me in a different way what the issue is?
Or you can wait for someone smarter to just answer...
Craig Newman
Re: Running total in data grid
Posted: Tue Sep 13, 2011 9:17 pm
by Fjord
Well, I can sort the records of the datagrid all right.
But how can I loop through the data
in the order the records are displayed in the datagrid in order to compute a running total? The balance of record 1 (first record displayed, the record with oldest date) must be equal to the first money input/output amount, then the balance of each subsequent record must be equal to the input/ouput of that record plus the balance of previous record. Something like:
1/01/2011 +33 =33
2/02/2011 +10 = 43
3/03/2011 - 4 =39
etc.
To make sure if I can process the datagrid records in date order, I tried the following which does not work:
Code: Select all
on sortGrid
dispatch "SortDataByKey" to group "laliste" with "MEDateFld" , "dateTime" , "ascending"
put the dgData of group "laliste" into myData
put the keys of myData into kList -- kList is not sorted
sort lines of kList -- now kList is sorted
answer kList -- nothing fancy: 1, 2, 3 etc.
repeat for each line k in kList
put k && myData[k]["MEDateFld"] & return after s
end repeat
answer s -- the dates are not sorted here; same if I remove the line "sort lines of kList"
end sortGrid
I need to loop through the data in date order.
Re: Running total in data grid
Posted: Sat Sep 17, 2011 7:16 pm
by Fjord
But how do I do what you said:
dunbarx wrote:You can extract, act on and restore the data to its original sorted order
I can sort the grid OK, put the dgData into a container OK, but how can I act on it
in its sorted order? That's what I tried to do in my previous post. Could anyone show where I did wrong, with a few lines of code?
Re: Running total in data grid
Posted: Sat Sep 17, 2011 10:34 pm
by Fjord
I finally found a solution which I post here for whoever may need it in the future:
Code: Select all
dispatch "SortDataByKey" to group "myList" with "MDateFld" , "system dateTime" , "ascending"
put the dgNumberOfLines of group "myList" into nl
put 0 into q
repeat with i = 1 to nl -- loop through each line of dataGrid
put the dgDataOfLine[i] of group "myList" into theData -- extract each record in turn
add theData["MQteFld"] to q -- add each amount to running total
put q into theData["SQteFld"] -- field for running total
set the dgDataOfLine[i] of group "myList" to theData -- put record back into the dGrid
end repeat
Rather obvious. I was on the wrong track trying to either work in the dgData or extract the whole array.