How to add a column subtotals row to a Datagrid?

Anything beyond the basics in using the LiveCode language. Share your handlers, functions and magic here.

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller

Post Reply
paul@researchware.com
VIP Livecode Opensource Backer
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?

Post by paul@researchware.com » Tue Aug 26, 2014 10:31 pm

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.
Paul Dupuis
Researchware, Inc.

paul@researchware.com
VIP Livecode Opensource Backer
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?

Post by paul@researchware.com » Tue Aug 26, 2014 10:36 pm

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.
Paul Dupuis
Researchware, Inc.

sritcp
Posts: 431
Joined: Tue Jun 05, 2012 5:38 pm

Re: How to add a column subtotals row to a Datagrid?

Post by sritcp » Wed Aug 27, 2014 3:58 pm

paul@researchware.com wrote:.... so that you could change the order to always place a particular index last?
Great idea! Please let us know if it works.

Thanks,
Sri

Martin Koob
VIP Livecode Opensource Backer
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?

Post by Martin Koob » Thu Aug 28, 2014 12:48 pm

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

paul@researchware.com
VIP Livecode Opensource Backer
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?

Post by paul@researchware.com » Thu Aug 28, 2014 1:44 pm

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!
Paul Dupuis
Researchware, Inc.

Martin Koob
VIP Livecode Opensource Backer
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?

Post by Martin Koob » Thu Aug 28, 2014 2:05 pm

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

paul@researchware.com
VIP Livecode Opensource Backer
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?

Post by paul@researchware.com » Thu Aug 28, 2014 8:16 pm

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

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

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.

sritcp
Posts: 431
Joined: Tue Jun 05, 2012 5:38 pm

Re: How to add a column subtotals row to a Datagrid?

Post by sritcp » Fri Aug 29, 2014 6:00 pm

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.

paul@researchware.com
VIP Livecode Opensource Backer
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?

Post by paul@researchware.com » Fri Aug 29, 2014 7:24 pm

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.
Paul Dupuis
Researchware, Inc.

sritcp
Posts: 431
Joined: Tue Jun 05, 2012 5:38 pm

Re: How to add a column subtotals row to a Datagrid?

Post by sritcp » Sun Aug 31, 2014 3:22 am

paul@researchware.com wrote:.... This is for display of data only (the result of an analysis) and so cells ate not editable.
Ah, I get it.
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

paul@researchware.com
VIP Livecode Opensource Backer
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?

Post by paul@researchware.com » Sun Aug 31, 2014 3:48 am

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.
Paul Dupuis
Researchware, Inc.

Martin Koob
VIP Livecode Opensource Backer
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?

Post by Martin Koob » Sun Aug 31, 2014 4:12 pm

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

sritcp
Posts: 431
Joined: Tue Jun 05, 2012 5:38 pm

Re: How to add a column subtotals row to a Datagrid?

Post by sritcp » Sun Aug 31, 2014 5:15 pm

Thanks, Martin, I'll review the lessons you've indicated.

Regards,
Sri

Post Reply