Sorting multiple columns

Posted by: lg_sony_27 on 8 September 2017, 1:22 pm EST

  • Posted 8 September 2017, 1:22 pm EST

    Hi! How do i sort multiple columns? for example Columns A,B,C,D has the following LastName, FirstName MiddleName, Gender. I would like to separate the ladies from gentlemen but the lastname is arranged alphabetically ascending and the gender - descending.


     Btw, the gender column is a hidden column.


     

  • Replied 8 September 2017, 1:22 pm EST

    Hi! 


    I want to do this programmatically. I accessed the whole table and display it in spread with some columns hidded. Is there a way to sort column A (LastName as secondary key, sort Ascending) and column D (Gender as primary key, sort Descending).  I dont like the user to click the header to sort the column, I want it sorted automatically when i display the records.


    Any help please?

  • Replied 8 September 2017, 1:22 pm EST

    Hello,


    You can use the SortRows method to implement this.


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


    FpSpread1.Sheets(0).SortRows(0, FpSpread1.Sheets(0).RowCount, si)

  • Replied 8 September 2017, 1:22 pm EST

    Hello scotts,


    Actually, when I did a searched on sorting I've seen your post and I've tried it and it works but...something is not right with my output. I have a list of names, and I am sorting records alphabetically using Gender column as my primary key in Descending order and Lastname column as my secondary key in Ascending order. I am confused why is it that there are name which are not sorted right. I want all the Male first followed by all Female but there are some Male students still displayed after Female.


    I have 700 plus columns and i put these in datatable. I'll just state here the first few columns (Fields in my table) in order.


    Column A -->Ukey


    Column B -->IDNo


    Column C -->LastName


    Column D -->FirstName


    Column E -->MiddleName


    Column F-->Gender


    A,B and F are hidden columns in the spread. In my understanding (correct me if I'm wrong) your code sorts the spread using Column A and Column B as basis?


    here is the part of the code i have right now


    Private Sub btnDisplay_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDisplay.Click


    SuspendLayout()


    cnn.ConnectionString = "Data Source=.\sqlexpress;Initial Catalog=NDDU-IBED;Integrated Security=True"


    If cnn.State = ConnectionState.Closed Then cnn.Open()


    cmdGrade = cnn.CreateCommand


    cmdGrade.CommandText = "SELECT DISTINCT * FROM tblEnrol WHERE SectionName = '" & cboSection.Text & "' AND SubjectName = '" & cboSubjectName.Text & "' AND YearLevel = '" & cboYearLevel.Text & "'"


    daGrade.MissingSchemaAction = MissingSchemaAction.AddWithKey


    dsGrade.Clear()


    dsSection.Clear()


    dsSubject.Clear()


    daGrade.SelectCommand = cmdGrade


    daGrade.Fill(dsGrade, "tblEnrol")


    cnn.Close()


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


    ''HideSheets()


    'bind FpSpread2 to datasource


    '1st Grading


    FpSpread2.Sheets(0).DataSource = dsGrade


    FpSpread2.Sheets(0).DataMember = "tblEnrol"


    'Create CellType and specify number decimal for 1st grading sheet


    With FpSpread2.Sheets(0)


    .DataAutoCellTypes = False


    Dim num As New FarPoint.Win.Spread.CellType.NumberCellType


    num.DecimalPlaces = 0


    .Columns(12, 81).CellType = num 'column 12 to 81


    .Columns(82).CellType = num


    .Columns(86).CellType = num


    .Columns(90).CellType = num


    .Columns(94).CellType = num


    .Columns(98).CellType = num


    .Columns(102).CellType = num


    .Columns(106).CellType = num


    .Columns(84, 85).CellType = num


    .Columns(88, 89).CellType = num


    .Columns(92, 93).CellType = num


    .Columns(96, 97).CellType = num


    .Columns(100, 101).CellType = num


    .Columns(104, 105).CellType = num


    .Columns(109).CellType = num


    'sort multi-column


    .SortRows(0, FpSpread2.Sheets(0).RowCount, si)


    End With


    FormatSpread()


       :


       :


    End Sub


    Thanks for your reply.

  • Replied 8 September 2017, 1:22 pm EST

    Hello,


    The columns are 0 based, so if you want to sort column A and then B, then the indexes in the sortinfo array you created should be 0 and 1. However, from your schematic, it looks like the columns you want to sort by are 4 and 2. Also, column 4, you said you wanted to be descending, so you need to pass false into the first SortInfo object in the aray.


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

  • Replied 8 September 2017, 1:22 pm EST

    Thanks! this works fine.
Need extra support?

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

Learn More

Forum Channels