How to add a column subtotals row to a Datagrid?
Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller
-
- VIP Livecode Opensource Backer
- Posts: 153
- Joined: Wed Aug 26, 2009 7:42 pm
- Contact:
How to add a column subtotals row to a Datagrid?
Datagrids are great for displaying numerical data. Column resizing and sorting is all done for you by the Datagrid. Adding a set of ROW subotals (i.e. a column on the right that contains subtotals for each row) is easy. You can show or hide the column as you like.
Adding a set of COLUMN subotals (i.e. a row at the bottom with subtotals for each column) is trickier. I could add a field outside the data grid and below it and populate it separate field. This has the big disadvantage that now any user column resizing in the Datagrid must also be caught and replicated in the separate field.
Calculate a "last row" (highest numerical index of the dgData array + 1) with the subtotals for each column is also easy and now the resizing of columns is part of the datagrid object.
But the problem is always having that row appear last regardless of what column sorts the user selected. Anyone know of a trick to always make a particular index of the dgData array appear as the last line in the datagrid regardless of what column is sorted on?
I searched these forums. ~ 3000 post on datagrids of which I seemed to manage to filter to a few hundred that MIGHT have covered this problem, but none appeared to. Seems like something someone would have already tried to do.
Adding a set of COLUMN subotals (i.e. a row at the bottom with subtotals for each column) is trickier. I could add a field outside the data grid and below it and populate it separate field. This has the big disadvantage that now any user column resizing in the Datagrid must also be caught and replicated in the separate field.
Calculate a "last row" (highest numerical index of the dgData array + 1) with the subtotals for each column is also easy and now the resizing of columns is part of the datagrid object.
But the problem is always having that row appear last regardless of what column sorts the user selected. Anyone know of a trick to always make a particular index of the dgData array appear as the last line in the datagrid regardless of what column is sorted on?
I searched these forums. ~ 3000 post on datagrids of which I seemed to manage to filter to a few hundred that MIGHT have covered this problem, but none appeared to. Seems like something someone would have already tried to do.
Paul Dupuis
Researchware, Inc.
Researchware, Inc.
-
- VIP Livecode Opensource Backer
- Posts: 153
- Joined: Wed Aug 26, 2009 7:42 pm
- Contact:
Re: How to add a column subtotals row to a Datagrid?
The Datagrid documentation references:
dgIndexes
- get the dgIndexes
- Returns the internal list of indexes in the order in which they appear in the data grid. Indexes are the
numeric indices used when setting the dgData property
It doesn't say whether this is settable so that you could change the order to always place a particular index last? Anyone know? I could build a test stack to try it, but figured I would ask first.
dgIndexes
- get the dgIndexes
- Returns the internal list of indexes in the order in which they appear in the data grid. Indexes are the
numeric indices used when setting the dgData property
It doesn't say whether this is settable so that you could change the order to always place a particular index last? Anyone know? I could build a test stack to try it, but figured I would ask first.
Paul Dupuis
Researchware, Inc.
Researchware, Inc.
Re: How to add a column subtotals row to a Datagrid?
Great idea! Please let us know if it works.paul@researchware.com wrote:.... so that you could change the order to always place a particular index last?
Thanks,
Sri
-
- VIP Livecode Opensource Backer
- Posts: 256
- Joined: Sun May 27, 2007 8:19 pm
Re: How to add a column subtotals row to a Datagrid?
I haven't tried this but it seems you can set the order of individual items in the index.
http://goulding.ws/2010/10/01/livecode- ... eordering/
Interested to know if this works.
Martin Koob
http://goulding.ws/2010/10/01/livecode- ... eordering/
Interested to know if this works.
Martin Koob
-
- VIP Livecode Opensource Backer
- Posts: 153
- Joined: Wed Aug 26, 2009 7:42 pm
- Contact:
Re: How to add a column subtotals row to a Datagrid?
Martin,
Thanks! Now that I know that dgIndexes is a settable property and can control the order, I just need to figure out how to detect when a column is sorted to readjust the indexes. It looks like I can use a mouseUp handler that checks "If the dgHeader of the target is not empty then ..." to determine that the header was clicked in.
It would be nice if the DataGrid provided a few more messages that could be easily responded to for actions like: column sorting, column resizing in addition to the solitary "selectionChanged" message. I still wish RunRev would add the datagrid API to the dictionary!
Thanks! Now that I know that dgIndexes is a settable property and can control the order, I just need to figure out how to detect when a column is sorted to readjust the indexes. It looks like I can use a mouseUp handler that checks "If the dgHeader of the target is not empty then ..." to determine that the header was clicked in.
It would be nice if the DataGrid provided a few more messages that could be easily responded to for actions like: column sorting, column resizing in addition to the solitary "selectionChanged" message. I still wish RunRev would add the datagrid API to the dictionary!
Paul Dupuis
Researchware, Inc.
Researchware, Inc.
-
- VIP Livecode Opensource Backer
- Posts: 256
- Joined: Sun May 27, 2007 8:19 pm
Re: How to add a column subtotals row to a Datagrid?
Hi Paul
Again haven't tried this but here is a lesson on customized sorting. The Data Grid info should be in the dictionary but there are lessons for using it.
http://lessons.runrev.com/m/datagrid/l/ ... mn-sorting
Martin Koob
Again haven't tried this but here is a lesson on customized sorting. The Data Grid info should be in the dictionary but there are lessons for using it.
http://lessons.runrev.com/m/datagrid/l/ ... mn-sorting
Martin Koob
-
- VIP Livecode Opensource Backer
- Posts: 153
- Joined: Wed Aug 26, 2009 7:42 pm
- Contact:
Re: How to add a column subtotals row to a Datagrid?
Martin,
Thank you! The following small variation on the custom sorting lesson script ensures that the last Index of the DataGrid is always at the bottom regardless of sort. Specifically the code snip
which moves the last index to the end after a sort based upon a property of the owning object (group) of the DataGrid which determines whether there are subtotals present or not. Full script below. Works like a charm.
Thank you! The following small variation on the custom sorting lesson script ensures that the last Index of the DataGrid is always at the bottom regardless of sort. Specifically the code snip
Code: Select all
if (the showColSubtotals of the owner of me) then
put max(theIndexSequencing) into tLastIndex -- subtotals row to always sort to bottom
put itemOffset(comma&tLastIndex&comma,comma&theIndexSequencing&comma) into tOS
delete item tOS of theIndexSequencing
put comma & tLastIndex after theIndexSequencing
end if
Code: Select all
on SortDataGridColumn pColumn
if the dgIndexes of me = empty then exit SortDataGridColumn
-- get index and data to sort on
put the dgData of me into theDataA
repeat for each key theIndex in theDataA
put theIndex & tab & theDataA[theIndex][pColumn] & cr after theData
end repeat
delete the last char of theData
set the itemdelimiter to tab
-- perform sort. use the current sort direction for this column
put the dgColumnSortDirection[pColumn] of me into theSortDirection
if theSortDirection is "ascending" then
sort lines of theData ascending by item 2 to -1 of each
else
sort lines of theData descending by item 2 to -1 of each
end if
-- rebuild the order of indexes in the data grid
put empty into theIndexSequencing
repeat for each line theLine in theData
put item 1 of theLine & comma after theIndexSequencing
end repeat
delete the last char of theIndexSequencing
set the itemdelimiter to comma
-- set the dgIndexes property to new order
if (the showColSubtotals of the owner of me) then
put max(theIndexSequencing) into tLastIndex -- subtotals row to always sort to bottom
put itemOffset(comma&tLastIndex&comma,comma&theIndexSequencing&comma) into tOS
delete item tOS of theIndexSequencing
put comma & tLastIndex after theIndexSequencing
end if
set the dgIndexes of me to theIndexSequencing
-- tell data grid to hilite column
HiliteAndStoreSortByColumn pColumn
end SortDataGridColumn
Paul Dupuis
Researchware, Inc.
Researchware, Inc.
Re: How to add a column subtotals row to a Datagrid?
Hi Paul:
1. How do you ensure this when operations other than sort (e.g., AddData) are carried out? In other words, is there a more general way to keep the totals row at the bottom?
2. Do you recompute the column totals each time the data grid is edited (e.g., a field is edited, or a row is deleted)? Is there a general way to implement this?
Regards,
Sri.
1. How do you ensure this when operations other than sort (e.g., AddData) are carried out? In other words, is there a more general way to keep the totals row at the bottom?
2. Do you recompute the column totals each time the data grid is edited (e.g., a field is edited, or a row is deleted)? Is there a general way to implement this?
Regards,
Sri.
-
- VIP Livecode Opensource Backer
- Posts: 153
- Joined: Wed Aug 26, 2009 7:42 pm
- Contact:
Re: How to add a column subtotals row to a Datagrid?
Sri,
1) In my case, I have enclosed the DataGrid and a couple of check boxes (below the DataGrid to show or hide the subotals) in a parent group. The parent group serves as my "matrix" control with a set of custom properties, one of which is the "dataArray" of the control. I use a setProp handler for the dataArray property in the "matrix" group that in turn sets the dgData of the enclosed DataGrid, so I am not adding or removing data via AddData or DeleteIndex. To update my object's display, you update the dataArray property of the "matrix" control.
2) Yes. the setProp handler for the dataArray property of the "matrix" control recomputes the row and column subtotals when it populates the dgData property of the Datagrid from the array passed to it. This is for display of data only (the result of an analysis) and so cells ate not editable.
I choose to use a DataGrid rather than a standard field set up as a table because the DataGrid handles things like column resizing and column sorting easily for me. However, in some ways, it is overkill for what I an using it for which is just the display of a table of numbers. I often wish LiveCode had a built in object that was more spreadsheet like than a regular field is capable of and yet not as complex as a DataGrid when I don't need custom controls in columns or stuff like that.
1) In my case, I have enclosed the DataGrid and a couple of check boxes (below the DataGrid to show or hide the subotals) in a parent group. The parent group serves as my "matrix" control with a set of custom properties, one of which is the "dataArray" of the control. I use a setProp handler for the dataArray property in the "matrix" group that in turn sets the dgData of the enclosed DataGrid, so I am not adding or removing data via AddData or DeleteIndex. To update my object's display, you update the dataArray property of the "matrix" control.
2) Yes. the setProp handler for the dataArray property of the "matrix" control recomputes the row and column subtotals when it populates the dgData property of the Datagrid from the array passed to it. This is for display of data only (the result of an analysis) and so cells ate not editable.
I choose to use a DataGrid rather than a standard field set up as a table because the DataGrid handles things like column resizing and column sorting easily for me. However, in some ways, it is overkill for what I an using it for which is just the display of a table of numbers. I often wish LiveCode had a built in object that was more spreadsheet like than a regular field is capable of and yet not as complex as a DataGrid when I don't need custom controls in columns or stuff like that.
Paul Dupuis
Researchware, Inc.
Researchware, Inc.
Re: How to add a column subtotals row to a Datagrid?
Ah, I get it.paul@researchware.com wrote:.... This is for display of data only (the result of an analysis) and so cells ate not editable.
I also assume that your table is small enough that no scrolling is needed.
For a more general need, where the column totals need to be always visible at the bottom (even when the data grid is too big to fit), I think a good alternative would be to attach (i.e., place) another data grid with with no headers and just 1 row showing. Its location (and col widths) could be tied to the visible rect of the main data grid, so it always stays stuck to the bottom of the main data grid. This also gives us the flexibility to add more rows to the second data grid, to display mean, variance, or some other properties.
The only question is how to know if the main data grid has been changed, either by the user or through script action. We need to know this to update the second data grid.
This goes back to my earlier question in this forum "How to know when an object has been edited or modified?"
http://forums.livecode.com/viewtopic.php?f=7&t=21172
I never managed to get a satisfactory answer.
Regards,
Sri
-
- VIP Livecode Opensource Backer
- Posts: 153
- Joined: Wed Aug 26, 2009 7:42 pm
- Contact:
Re: How to add a column subtotals row to a Datagrid?
Actually in my application there can be a fair amount of rows and columns. In nearly all cases a person would need to scroll to the subtotals.
And, yes, I could have placed the totals in separate fields to the right and bottom of the DataGrid to always have the totals visible. I choose not to for ease of implementation. By having the subtotals in the DataGrid the synchronization/detecting of when the DataGrid is scrolled to then adjust the scroll of companion fields/Datagrids is unnecessary.
However, look at the Command in the Datagrid API (starting on page 223 or so). SortByColumn is what Martin (previous posts) suggested I trap to implement my own sort routine to always place a subtotal row last. Commands to a DataGrid can be intercepted, some action takes and then passed on to the actual Datagrid handlers.
I am guessing you could similarly trap ScrollIndexIntoView or ScrollLineIntoView to add code to get the scroll of the Datagrid and synch a companion field. Your handler for the message would need to be in the script of the Datagrid object.
on ScrollIndexIntoView pIndex
set the vScroll of fld "SomeOtherField" to the dgVScroll of me
pass ScrollIndexIntoView
end ScrollIndexIntoView
I have not tried this approach. Another alternative may be to try a setProp handler to catch the dgVScroll or dgHScroll
setProp dgVScroll pNewValue
set the vScroll of fld "SomeOtherField" to pNewValue
pass dgVScroll
end dgVScroll
I also don't know if this would work either. Just suggestions to try.
And, yes, I could have placed the totals in separate fields to the right and bottom of the DataGrid to always have the totals visible. I choose not to for ease of implementation. By having the subtotals in the DataGrid the synchronization/detecting of when the DataGrid is scrolled to then adjust the scroll of companion fields/Datagrids is unnecessary.
However, look at the Command in the Datagrid API (starting on page 223 or so). SortByColumn is what Martin (previous posts) suggested I trap to implement my own sort routine to always place a subtotal row last. Commands to a DataGrid can be intercepted, some action takes and then passed on to the actual Datagrid handlers.
I am guessing you could similarly trap ScrollIndexIntoView or ScrollLineIntoView to add code to get the scroll of the Datagrid and synch a companion field. Your handler for the message would need to be in the script of the Datagrid object.
on ScrollIndexIntoView pIndex
set the vScroll of fld "SomeOtherField" to the dgVScroll of me
pass ScrollIndexIntoView
end ScrollIndexIntoView
I have not tried this approach. Another alternative may be to try a setProp handler to catch the dgVScroll or dgHScroll
setProp dgVScroll pNewValue
set the vScroll of fld "SomeOtherField" to pNewValue
pass dgVScroll
end dgVScroll
I also don't know if this would work either. Just suggestions to try.
Paul Dupuis
Researchware, Inc.
Researchware, Inc.
-
- VIP Livecode Opensource Backer
- Posts: 256
- Joined: Sun May 27, 2007 8:19 pm
Re: How to add a column subtotals row to a Datagrid?
Hi Sri
Can't you use the message 'CloseFieldEditor' in your DataGrid to determine if there was a change in any of the cells?
In the CloseFieldEditor you could set a property to indicate there was a change and then check that property. That would cover the user making changes to the dataGrid itself.
There are a few tutorials that use this message, for forms and tables. They relate to how to save data when there is a change but I think you could use the principles here for your purpose.
http://lessons.runrev.com/m/datagrid/l/ ... or-editing
http://lessons.runrev.com/m/datagrid/l/ ... -grid-form
http://runrev.screenstepslive.com/s/352 ... ata-source
As far and tracking changes to the dataGrid by script you would have have a line in the scripts that change the dataGrid that would set the property showing that it had changed.
Martin
Can't you use the message 'CloseFieldEditor' in your DataGrid to determine if there was a change in any of the cells?
In the CloseFieldEditor you could set a property to indicate there was a change and then check that property. That would cover the user making changes to the dataGrid itself.
There are a few tutorials that use this message, for forms and tables. They relate to how to save data when there is a change but I think you could use the principles here for your purpose.
http://lessons.runrev.com/m/datagrid/l/ ... or-editing
http://lessons.runrev.com/m/datagrid/l/ ... -grid-form
http://runrev.screenstepslive.com/s/352 ... ata-source
As far and tracking changes to the dataGrid by script you would have have a line in the scripts that change the dataGrid that would set the property showing that it had changed.
Martin
Re: How to add a column subtotals row to a Datagrid?
Thanks, Martin, I'll review the lessons you've indicated.
Regards,
Sri
Regards,
Sri