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

    Hi,


    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.


    I have attached an image of the problem which I hope shows the problem.


    The main image shows my data before I apply the grouping. You can see that the data is in order of the subCategory_id.  


    I need to group my data via the category_id, and then keep the sort order on the subCategory_id.


    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.


     Below is the code I use to do the grouping


    How can I re-sort, after the grouping and get the rows back in order.


    This is crucial to my application, and I can not release it until I have fixed it.


    Thanks


     


    Code :


    FpSpread1.ActiveSheet.AllowGroup = True


    Dim gm As New FarPoint.Win.Spread.Model.GroupDataModel(FpSpread1.Sheets(0).Models.Data)


    Dim si() As FarPoint.Win.Spread.SortInfo = New FarPoint.Win.Spread.SortInfo() {New FarPoint.Win.Spread.SortInfo(1, True)}


    gm.Group(si)


    FpSpread1.Sheets(0).Models.Data = gm



  • Replied 8 September 2017, 1:24 pm EST

    Hello,


    You are trying to sort the ActiveSheet. I mentioned in my post, you need to perform the sort on the GroupDataModel instead.


    Dim si2() As FarPoint.Win.Spread.SortInfo = New FarPoint.Win.Spread.SortInfo() {New FarPoint.Win.Spread.SortInfo(3, True)}


    gm.Sort(si2)

  • Replied 8 September 2017, 1:24 pm EST

    Hi,


    I have managed to resolve the issue in part, so thank you for your assistance on this.


    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.


    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


    Thanks

  • Replied 8 September 2017, 1:24 pm EST

    Hello,


    You would need to resort the column after changing values in the GroupDataModel.

  • Replied 8 September 2017, 1:24 pm EST

    Hello,


    After grouping, you can use the Sort method on the GroupDataModel to sort the data by another column.

  • Replied 8 September 2017, 1:24 pm EST

    I am able to get sorting working, but only when there is no grouping. 


    Once I apply grouping, the sorting is lost completly.


    Without grouping I can see the sorting, including the 'show indicator' in the colum header I want to sort on.


    Again I have attached three images to demo the problem I am having.


    The first, is grouponly.jpg. The code that generates this is :


    FpSpread1.ActiveSheet.AllowGroup = True 
    Dim gm As New FarPoint.Win.Spread.Model.GroupDataModel(FpSpread1.Sheets(0).Models.Data)
    Dim si() As FarPoint.Win.Spread.SortInfo = New FarPoint.Win.Spread.SortInfo() {New FarPoint.Win.Spread.SortInfo(1, True)}
    gm.Group(si)
    FpSpread1.Sheets(0).Models.Data = gm


    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 


    The second image is the sort only (sortonly.jpg), without any grouping. The code that generates this is just


    FpSpread1.ActiveSheet.SortRows(3, True, True)


    The sort is correct, but of course there is no grouping.


    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.


    The thirdimage groupsort.jpg is created from this code


    FpSpread1.ActiveSheet.AllowGroup = True
    Dim gm As New FarPoint.Win.Spread.Model.GroupDataModel(FpSpread1.Sheets(0).Models.Data)
    Dim si() As FarPoint.Win.Spread.SortInfo = New FarPoint.Win.Spread.SortInfo() {New FarPoint.Win.Spread.SortInfo(1, True)}
    gm.Group(si)
    FpSpread1.Sheets(0).Models.Data = gm
    FpSpread1.ActiveSheet.SortRows(3,
    True, True)


    Now the grouping is messed up, and for some reason it look like its grouping on a different column. and its all messed up.


    What is going on here???


     


    Thanks


    2008/08/grouponly.zip
  • Replied 8 September 2017, 1:24 pm EST

    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 ?


    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.


    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.


    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.


    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.


    Thanks


    Private Sub GroupData()


    FpSpread1.ActiveSheet.GroupBarVisible = False
    FpSpread1.ActiveSheet.AllowGroup = True


    Dim gm As New FarPoint.Win.Spread.Model.GroupDataModel(FpSpread1.Sheets(0).Models.Data)
    Dim si() As FarPoint.Win.Spread.SortInfo = New FarPoint.Win.Spread.SortInfo() {New FarPoint.Win.Spread.SortInfo(1, True)}
    gm.Group(si)
    Dim si2() As FarPoint.Win.Spread.SortInfo = New FarPoint.Win.Spread.SortInfo() {New FarPoint.Win.Spread.SortInfo(3, True)}
    gm.Sort(si2)
    FpSpread1.Sheets(0).Models.Data = gm


    End Function

  • Replied 8 September 2017, 1:24 pm EST

    Hello,


    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.


    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.

Need extra support?

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

Learn More

Forum Channels