Having a problem with grouping and sorting

Posted by: hpd71 on 8 September 2017, 1:24 pm EST

  • Posted 8 September 2017, 1:24 pm EST

    <P>Hi,</P>
    <P>I am trying to apply a group to my data and then sort within the group, and this is proving very frustrating as I don't seem to be able to get it to work.</P>
    <P>I have attached an image of the problem which I hope shows the problem.</P>
    <P>The main image shows my data before I apply the grouping. You can see that the data is in order of the subCategory_id.  </P>
    <P>I need to group my data via the category_id, and then keep the sort order on the subCategory_id.</P>
    <P>The inserted window with the black border shows the result after I do the grouping. You can see that whilst the grouping has worked, the order of the subCategory is now broken.</P>
    <P> Below is the code I use to do the grouping</P>
    <P>How can I re-sort, after the grouping and get the rows back in order. </P>
    <P>This is crucial to my application, and I can not release it until I have fixed it.</P>
    <P>Thanks</P>
    <P> </P>
    <P>Code :</P><FONT size=2>
    <P>FpSpread1.ActiveSheet.AllowGroup = </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>True</P></FONT></FONT><FONT size=2>
    <P></FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Dim</FONT></FONT><FONT size=2> gm </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>As</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>New</FONT></FONT><FONT size=2> FarPoint.Win.Spread.Model.GroupDataModel(FpSpread1.Sheets(0).Models.Data)</P>
    <P></FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Dim</FONT></FONT><FONT size=2> si() </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>As</FONT></FONT><FONT size=2> FarPoint.Win.Spread.SortInfo = </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>New</FONT></FONT><FONT size=2> FarPoint.Win.Spread.SortInfo() {</FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>New</FONT></FONT><FONT size=2> FarPoint.Win.Spread.SortInfo(1, </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>True</FONT></FONT><FONT size=2>)}</P>
    <P>gm.Group(si)</P>
    <P>FpSpread1.Sheets(0).Models.Data = gm</P></FONT>

  • Replied 8 September 2017, 1:24 pm EST

    <P>Hello,</P>
    <P>You are trying to sort the ActiveSheet. I mentioned in my post, you need to perform the sort on the GroupDataModel instead.</P><FONT size=2>
    <P></FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Dim</FONT></FONT><FONT size=2> si2() </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>As</FONT></FONT><FONT size=2> FarPoint.Win.Spread.SortInfo = </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>New</FONT></FONT><FONT size=2> FarPoint.Win.Spread.SortInfo() {</FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>New</FONT></FONT><FONT size=2> FarPoint.Win.Spread.SortInfo(3, </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>True</FONT></FONT><FONT size=2>)}</P>
    <P>gm.Sort(si2)</P></FONT>
  • Replied 8 September 2017, 1:24 pm EST

    <P>Hi,</P>
    <P>I have managed to resolve the issue in part, so thank you for your assistance on this.</P>
    <P>The problem now is that I have got the grouping and sorting working, however whenever I enter a value into a cell, the sort order is lost, but the grouping remains.</P>
    <P>I have put together a cut down version of my project so that you can run it and see the issue. Its in the url link attached</P>
    <P>Thanks</P>
  • Replied 8 September 2017, 1:24 pm EST

    <P>Hello,</P>
    <P>You would need to resort the column after changing values in the GroupDataModel.</P>
  • Replied 8 September 2017, 1:24 pm EST

    <P>Hello,</P>
    <P>After grouping, you can use the Sort method on the GroupDataModel to sort the data by another column.</P>
  • Replied 8 September 2017, 1:24 pm EST

    <P>I am able to get sorting working, but only when there is no grouping. </P>
    <P>Once I apply grouping, the sorting is lost completly.</P>
    <P>Without grouping I can see the sorting, including the 'show indicator' in the colum header I want to sort on.</P>
    <P>Again I have attached three images to demo the problem I am having.</P>
    <P>The first, is grouponly.jpg. The code that generates this is :</P><FONT size=2>
    <P></FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2><FONT color=#000000>FpSpread1.ActiveSheet.AllowGroup = </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>True</FONT></FONT> <BR></FONT></FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Dim</FONT></FONT><FONT size=2> gm </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>As</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>New</FONT></FONT><FONT size=2> FarPoint.Win.Spread.Model.GroupDataModel(FpSpread1.Sheets(0).Models.Data)<BR></FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Dim</FONT></FONT><FONT size=2> si() </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>As</FONT></FONT><FONT size=2> FarPoint.Win.Spread.SortInfo = </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>New</FONT></FONT><FONT size=2> FarPoint.Win.Spread.SortInfo() {</FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>New</FONT></FONT><FONT size=2> FarPoint.Win.Spread.SortInfo(1, </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>True</FONT></FONT><FONT size=2>)}<BR>gm.Group(si)<BR>FpSpread1.Sheets(0).Models.Data = gm</FONT></P>
    <P>This grouping is how I want it, as its grouped on the column 'category_name'. However the rows are now not in order, as you can see in the column subcategory_ID </P>
    <P>The second image is the sort only (sortonly.jpg), without any grouping. The code that generates this is just </P><FONT color=#008000 size=2><FONT color=#008000 size=2>
    <P>FpSpread1.ActiveSheet.SortRows(3, True, True)</P>
    <P></FONT></FONT>The sort is correct, but of course there is no grouping.</P>
    <P>Logic would say that I just need to add this sort code after my grouping code, and all should be fine... but for what ever reason that logic doesn't work.</P>
    <P>The thirdimage groupsort.jpg is created from this code </P><FONT size=2>
    <P>FpSpread1.ActiveSheet.AllowGroup = </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>True<BR></FONT></FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Dim</FONT></FONT><FONT size=2> gm </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>As</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>New</FONT></FONT><FONT size=2> FarPoint.Win.Spread.Model.GroupDataModel(FpSpread1.Sheets(0).Models.Data)<BR></FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Dim</FONT></FONT><FONT size=2> si() </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>As</FONT></FONT><FONT size=2> FarPoint.Win.Spread.SortInfo = </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>New</FONT></FONT><FONT size=2> FarPoint.Win.Spread.SortInfo() {</FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>New</FONT></FONT><FONT size=2> FarPoint.Win.Spread.SortInfo(1, </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>True</FONT></FONT><FONT size=2>)}<BR>gm.Group(si)<BR>FpSpread1.Sheets(0).Models.Data = gm<BR>FpSpread1.ActiveSheet.SortRows(3, </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>True</FONT></FONT><FONT size=2>, </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>True</FONT></FONT><FONT size=2>)</FONT></P>
    <P>Now the grouping is messed up, and for some reason it look like its grouping on a different column. and its all messed up.</P>
    <P>What is going on here???</P>
    <P> </P>
    <P>Thanks</P>
    2008/08/grouponly.zip
  • Replied 8 September 2017, 1:24 pm EST

    <P>I have been looking at this for a few days now, and am totally lost. Why would I have to reapply the sort just because the user entered a value into a cell.. that just doesn't make any sense...The sort I have applied should remain unless I remove or change it. The grouping doesn't change or disappear when a value is entered. Is this really by design ?</P>
    <P>So if I have to resort the groupDataModel after everytime the user enters a value into a cell, so be it, but I can't seem to get it to work.</P>
    <P>I apply my grouping in a function. I also apply the groupmodel sort in the same function. If I simply call this function again, it of course applies the grouping yet again. If I create a new function with just the si2/sort command, my sheet data just vanishes and I end up with a sheet with no data.</P>
    <P><FONT size=2>As I said I have been looking at this problem for a few days and I am now probably not seeing a basic answer/solution to this. </FONT></P>
    <P><FONT size=2>I would be grateful if you could show me with a example code, on how to re-apply the sorting everytime the user enters a value.</FONT></P>
    <P><FONT size=2>Thanks</FONT><FONT size=2></P><FONT color=#0000ff size=2><FONT color=#0000ff size=2>
    <P>Private</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Sub</FONT></FONT><FONT size=2> GroupData()</FONT></P>
    <P><FONT size=2>FpSpread1.ActiveSheet.GroupBarVisible = </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>False<BR></FONT></FONT><FONT size=2>FpSpread1.ActiveSheet.AllowGroup = </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>True</FONT></FONT></P><FONT color=#0000ff size=2><FONT color=#0000ff size=2><FONT size=2>
    <P></FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Dim</FONT></FONT><FONT size=2> gm </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>As</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>New</FONT></FONT><FONT size=2> FarPoint.Win.Spread.Model.GroupDataModel(FpSpread1.Sheets(0).Models.Data)</FONT><BR></FONT></FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Dim</FONT></FONT><FONT size=2> si() </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>As</FONT></FONT><FONT size=2> FarPoint.Win.Spread.SortInfo = </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>New</FONT></FONT><FONT size=2> FarPoint.Win.Spread.SortInfo() {</FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>New</FONT></FONT><FONT size=2> FarPoint.Win.Spread.SortInfo(1, </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>True</FONT></FONT><FONT size=2>)}<BR>gm.Group(si)<BR></FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Dim</FONT></FONT><FONT size=2> si2() </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>As</FONT></FONT><FONT size=2> FarPoint.Win.Spread.SortInfo = </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>New</FONT></FONT><FONT size=2> FarPoint.Win.Spread.SortInfo() {</FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>New</FONT></FONT><FONT size=2> FarPoint.Win.Spread.SortInfo(3, </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>True</FONT></FONT><FONT size=2>)}<BR>gm.Sort(si2)<BR>FpSpread1.Sheets(0).Models.Data = gm</P>
    <P>End Function</FONT><FONT color=#008000 size=2><FONT color=#008000 size=2></P></FONT></FONT></FONT>
  • Replied 8 September 2017, 1:24 pm EST

    <P>Hello,</P>
    <P>I am able to reproduce the issue you are seeing. I thought you were changing a value in column 3 and was expecting the data to resort to it;s new location. If you make a change to another cell it is losing it's original sort, which should not happen. This was a reported bug in the Spread that will be fixed in the next maintenance release, which is scheduled for later today.</P>
    <P>I also tested changing a value in column 3 (your sorted column) and it moved that row to it's new sorted location. I would not think that would be the behavior, but it is with the new maintenance release coming soon.</P>
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels