Running total in data grid

[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!

[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

[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.

[Fjord]

But how do I do what you said:

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?

[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.