How to sort numerically on a string field

[Hector]

I have a RadGridView bound to a table that has numeric values in a string column.  When I sort, it sorts alphabetic rather than numeric.  For example, say the values are 

"1000"

 

 

, "1001", "1002", "159", "17", "13"

it will sort in this order:

1000

1001

1002

13

159

17

I tried doing a custom sort, but I am stuck on how to convert the column to int on the fly.  Any help is appreciated.

Thanks,

[Ludovic Gerbault]

I think you might try to register the autogeneratingcolumn event of the grid, and in the method, something like

private void OnAutoGeneratingColumn(object sender, GridViewAutoGeneratingColumnEventArgs e)
{
      GridViewDataColumn col = e.Column as GridViewDataColumn;
      //Here you can test the content of your column to determine which DataType you want

      col.DataType = typeof(YourType);
}

Hope this helps

[Hector]

Good idea, but it didn't work.  I am not automatically generating the columns, I am creating them programatically and I tried setting the column to dataColumn.DataType = typeof(int); and it will still sort alphabetically.  Thanks for trying.

[Pavel Pavlov]

Hello Hector,

For such scenarios RadGridView exposes the Sorting event.

It allows you to perform a custom sorting logic when the user clicks on the column header.
You may see this event in action in this online example.

This event allows you to 'override' the default sorting logic.So for your scenario you will need to subscribe to the Sorting event and perform  your own sorting within the event handler.

Pavel Pavlov
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.

[Hector]

Thanks Pavel.  I am handling it that way.  I just don't know how to apply the sort to the values in numeric style.
I tried doing

values = values.OrderByDescending(v =>

int.Parse(v.Value));

 

and it wouldn't work.


Here is the code I have now:

private void CustomSortingGrid_Sorting(object sender, GridViewSortingEventArgs e)
        {
            //Gets the value of the ItemsSource property as IEnumerable.
            IEnumerable<ValidValue> values = e.DataControl.ItemsSource as IEnumerable<ValidValue>;
            //Checks if the value of the collection is null.

            if (values == null)
            {
                e.Cancel = true;
                return;
            }
            //If the sorting state is none, sort the items ascending.
            if (e.OldSortingState == SortingState.None)
            {
                e.NewSortingState = SortingState.Ascending;
                values = values.OrderBy(v => v.GetType()
                    .GetProperty(e.SortPropertyName)
                    .GetValue(v, null));

            }
            //If the sorting state is ascending, sort the items descending.
            else if (e.OldSortingState == SortingState.Ascending)
            {
                e.NewSortingState = SortingState.Descending;
                values = values.OrderByDescending(v => v.GetType()
                    .GetProperty(e.SortPropertyName)
                    .GetValue(v, null));

            }
            //If the sorting state is descending, apply default sorting to the items.
            else
            {
                e.NewSortingState = SortingState.None;
                values = values.OrderBy(v => v.ValueID);
            }
            //Set the sorted collection as source of the RadGridView
            e.DataControl.ItemsSource = values.ToList();
            e.Cancel = true;
        }

[Pavel Pavlov]

Hello Hector,

Here is some code I have tested and seems to work ok . ( For simplicity it contains just the sorting logic  to sort ascending once , without cycling the sort states)

public partial class MainPage : UserControl
   {
       public MainPage()
       {
           InitializeComponent();
           List<Item> items = new List<Item>();
           for (int i = 30; i >0; i--)
           {
               items.Add(new Item() { Value = i.ToString() });
           }
           this.RadGridView1.ItemsSource = items;
           this.RadGridView1.Sorting += new EventHandler<Telerik.Windows.Controls.GridViewSortingEventArgs>(RadGridView1_Sorting);
       }
       void RadGridView1_Sorting(object sender, Telerik.Windows.Controls.GridViewSortingEventArgs e)
       {
           e.Cancel = true;//cancel the built in sorting so we can perform our custom logic instead
           this.PerformCustomStringSort();
       }
       private void PerformCustomStringSort()
       {
           var sorted = from i in this.RadGridView1.ItemsSource as List<Item>
                        orderby int.Parse( i.Value) ascending
                        select i;
           this.RadGridView1.ItemsSource = sorted.ToList();
       }
   }
   public class Item
   {
       public string Value { get; set; }
   }

All the best,
Pavel Pavlov
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.

[Hector]

Thanks for your reply, but it did not work.  Here is the error I am getting:
Value cannot be null.
Parameter name: source
I get this error at the linq line

var sorted = from i in this.gridMultiSelect.ItemsSource as List<ValidValue>
                         orderby int.Parse(i.Value) ascending
                         select i;

Any other ideas?

[Pavel Pavlov]

Hi Hector,

I have tested the code and it seems OK . However your exception gives me a hint that you may have null values in your data.

If that is the case here is how you can extend the  linq query to handle and sort null values as well without giving exception.

private void PerformCustomStringSort()
      {
          var sorted = from i in this.RadGridView1.ItemsSource as List<Item>
                       orderby  i.Value==null?0:int.Parse(i.Value) ascending
                       select i;
          this.RadGridView1.ItemsSource = sorted.ToList();
      }

Let me know if you need a working project using this code as a sample.

Sincerely yours,
Pavel Pavlov
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.

[Hector]

Thanks again, Pavel, but I am still getting the error "value cannot be null. parameter name: source"
The error occurs even if I don't do the orderby.

var sorted = from i in this.RadGridView1.ItemsSource as List<Item>

                       select i;

So I think something else in my data is null.

[Pavel Pavlov]

Hi Hector,

Ok lets try dig into the problem . I am sending a project with the code demonstrated in action . To test it - start the application and click on the column header.  Here it seems to work as expected without exceptions. Then please try this with your data ...in case the problem persists I will need to have a look at your data and try debugging it further.

Regards,
Pavel Pavlov
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.

[Ludovic Gerbault]

Quick question on a smiliar matter, is it also possible to filter numerically on a string field ?

(for example, try having the items where the salary is greater than 1500€ when in the source collection, the datamember field has a datatype string)

[Pavel Pavlov]

Hi Subileau Pascal,

I believe it is theoretically  possible with filtering  but it would become rather more complicated than the sorting scenario. It will involve providing custom filtering ui template and other hacks so I would definitely recommend to place numeric data in numeric columns , rather taking the wrong way.

Kind regards,
Pavel Pavlov
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.

[Ludovic Gerbault]

the thing is that I might not have a choice, I'm up for heavy programming either way, and I'm not sure that the hacks you mentionned are any heavier than what I have to do to have a numeric field on my collection

[Neerajan]

Hello,

In my case, I need to sort the strings 

123456789-0
321654789-0
154789632-1
:  :
:  :
874562126-0

These values are in string format but i need to sort them numerically. Any suggestions regarding this?

Regards
Neerajan Lamsal

[Yen-Sheng]

You're probably better off binding your table items to an object with types that implement IComparable. That's what I've done.

[Edward Pauley]

I have a similar kind of problem. My Stored Procedure is returning a string field which contains numeric data. I need the column to be sorted ascending initially or when the grid loads. tried all the above methods but no result.

 

<telerik:RadGridView.SortDescriptors>
                            <telerik:SortDescriptor Member="PolicyInstallmentCode" SortDirection="Ascending"/>
                        </telerik:RadGridView.SortDescriptors>
And it gives error : "The property member was not found in type SortDescriptor" strange
here is my grid :
<telerik:RadGridView Name="policyInstallmentListRadGridView"
                                         AutoGenerateColumns="False"
                                         IsReadOnly="True"
                                         Background="Gainsboro"
                                         ToolTipService.ToolTip="Double click row for navigation."
                                         ShowGroupPanel="False"
                                         ItemsSource="{Binding DataView, ElementName=policyInstallmentListDomainDataSource}"
                                         IsBusy="{Binding IsBusy,ElementName=policyInstallmentListDomainDataSource}"
                                         ShowColumnFooters="True"
                                         LoadingRowDetails="policyInstallmentListRadGridView_LoadingRowDetails"
                                         Loaded="policyInstallmentListRadGridView_Loaded"
                                         Sorting="policyInstallmentListRadGridView_Sorting"
                                         IsSynchronizedWithCurrentItem="false" >
                        <telerik:RadGridView.SortDescriptors>
                            <telerik:SortDescriptor Member="PolicyInstallmentId" SortDirection="Ascending"/>
                        </telerik:RadGridView.SortDescriptors>
                        <telerik:RadGridView.RowDetailsTemplate>
                            <DataTemplate>
                                <telerik:RadTabControl SelectionChanged="RadTabControl_SelectionChanged">
                                    <telerik:RadTabItem Header="Summary">
                                        <telerik:RadGridView AutoGenerateColumns="False" Name="policyInstallmentSummaryRadGridView" IsReadOnly="True" Background="Gainsboro" ShowGroupPanel="False" CanUserSortColumns="False">
                                            <telerik:RadGridView.Columns >


                                                <telerik:GridViewDataColumn  DataMemberBinding="{Binding DetailId, Mode=TwoWay}"
                                                            Header="Detail Id" />
                                                <telerik:GridViewDataColumn  DataMemberBinding="{Binding LineType, Mode=TwoWay}"
                                                            Header="Line Type" />
                                                <telerik:GridViewDataColumn  DataMemberBinding="{Binding Coverage, Mode=TwoWay}"
                                                            Header="Coverage" />
                                                <telerik:GridViewDataColumn  DataMemberBinding="{Binding FeeTax, Mode=TwoWay}"
                                                            Header="Fee/Tax Type" />
                                                <telerik:GridViewDataColumn DataMemberBinding="{Binding TransactionType, Mode=TwoWay}"
                                                            Header="Tran Type" />
                                                <telerik:GridViewDataColumn  DataMemberBinding="{Binding HousePCT, Mode=TwoWay}" DataFormatString="{}{0:0.0000}"
                                                            Header="House %" />
                                                <telerik:GridViewDataColumn  DataMemberBinding="{Binding AgentPCT, Mode=TwoWay}" DataFormatString="{}{0:0.0000}"
                                                            Header="Agent %" />
                                                <telerik:GridViewDataColumn  DataMemberBinding="{Binding TransactionAmount, Mode=TwoWay}" DataFormatString="{}{0:c2}"
                                                            Header="Amount" />
                                            </telerik:RadGridView.Columns>
                                        </telerik:RadGridView>
                                    </telerik:RadTabItem>
                                    <telerik:RadTabItem Header="GL" Name="glTab">


                                        <telerik:RadGridView AutoGenerateColumns="False" Name="policyInstallmentGLRadGridView" IsReadOnly="True" Background="Gainsboro" ShowGroupPanel="False" CanUserSortColumns="False">
                                            <telerik:RadGridView.Columns >


                                                <telerik:GridViewDataColumn  DataMemberBinding="{Binding Account, Mode=TwoWay}"
                                                            Header="Account" />
                                                <telerik:GridViewDataColumn  DataMemberBinding="{Binding Module, Mode=TwoWay}"
                                                            Header="Module" />
                                                <telerik:GridViewDataColumn  DataMemberBinding="{Binding BusinessId, Mode=TwoWay}"
                                                            Header="Business Id" />
                                                <telerik:GridViewDataColumn  DataMemberBinding="{Binding Amount, Mode=TwoWay}" DataFormatString="{}{0:c2}"
                                                            Header="Amount" />
                                                <telerik:GridViewDataColumn DataMemberBinding="{Binding DocumentBalance, Mode=TwoWay}" DataFormatString="{}{0:c2}"
                                                            Header="Doc Balance" />
                                                <telerik:GridViewDataColumn  DataMemberBinding="{Binding Debit, Mode=TwoWay}" DataFormatString="{}{0:c2}"
                                                            Header="Debit" />
                                                <telerik:GridViewDataColumn  DataMemberBinding="{Binding Credot, Mode=TwoWay}" DataFormatString="{}{0:c2}"
                                                            Header="Credit" />


                                            </telerik:RadGridView.Columns>
                                        </telerik:RadGridView>
                                    </telerik:RadTabItem>


                                </telerik:RadTabControl>


                            </DataTemplate>
                        </telerik:RadGridView.RowDetailsTemplate>
                        <telerik:RadGridView.Columns>
                            <telerik:GridViewToggleRowDetailsColumn />


                            <telerik:GridViewDataColumn DataMemberBinding="{Binding PolicyInstallmentCode, Mode=TwoWay}"
                                                        Header="Install Code" 
                                                        UniqueName="Col_policyInstallmentListRadGridView_InstallCode"
                                                        SortMemberPath="PolicyInstallmentId"
                                                        />
                            <telerik:GridViewDataColumn DataMemberBinding="{Binding InstallmentDescription, Mode=TwoWay}"
                                                        Header="Description" />




                            <telerik:GridViewDataColumn DataMemberBinding="{Binding InstallmentTypeCode, Mode=TwoWay}"
                                                        Header="Type Code" />
                            <telerik:GridViewDataColumn Header="Install Date"
                                                        DataMemberBinding="{Binding InstallmentDate,Mode=TwoWay}"
                                                        DataFormatString="{}{0:d}">
                                <telerik:GridViewDataColumn.Footer>


                                    <TextBlock Text="Total:" />




                                </telerik:GridViewDataColumn.Footer>
                            </telerik:GridViewDataColumn>
                            <telerik:GridViewDataColumn Header="Premium Amt"
                                                        DataMemberBinding="{Binding PremiumAmount,Mode=TwoWay}"
                                                        DataFormatString="{}{0:c2}">
                                <telerik:GridViewDataColumn.AggregateFunctions>
                                    <telerik:SumFunction SourceField="PremiumAmount"
                                                         ResultFormatString="{}{0:c2}" />


                                </telerik:GridViewDataColumn.AggregateFunctions>


                                <telerik:GridViewDataColumn.Footer>




                                    <telerik:AggregateResultsList ItemsSource="{Binding}" />




                                </telerik:GridViewDataColumn.Footer>


                            </telerik:GridViewDataColumn>


                            <telerik:GridViewDataColumn Header="Fee Amt"
                                                        DataMemberBinding="{Binding FeeAmount,Mode=TwoWay}"
                                                        DataFormatString="{}{0:c2}">
                                <telerik:GridViewDataColumn.AggregateFunctions>
                                    <telerik:SumFunction SourceField="FeeAmount"
                                                         ResultFormatString="{}{0:c2}" />


                                </telerik:GridViewDataColumn.AggregateFunctions>


                                <telerik:GridViewDataColumn.Footer>




                                    <telerik:AggregateResultsList ItemsSource="{Binding}" />




                                </telerik:GridViewDataColumn.Footer>


                            </telerik:GridViewDataColumn>


                            <telerik:GridViewDataColumn Header="Tax Amt"
                                                        DataMemberBinding="{Binding TaxAmount,Mode=TwoWay}"
                                                        DataFormatString="{}{0:c2}">
                                <telerik:GridViewDataColumn.AggregateFunctions>
                                    <telerik:SumFunction SourceField="TaxAmount"
                                                         ResultFormatString="{}{0:c2}" />


                                </telerik:GridViewDataColumn.AggregateFunctions>


                                <telerik:GridViewDataColumn.Footer>




                                    <telerik:AggregateResultsList ItemsSource="{Binding}" />




                                </telerik:GridViewDataColumn.Footer>


                            </telerik:GridViewDataColumn>


                            <telerik:GridViewDataColumn Header="AR Original"
                                                        DataMemberBinding="{Binding AROriginal,Mode=TwoWay}"
                                                        DataFormatString="{}{0:c2}">
                                <telerik:GridViewDataColumn.AggregateFunctions>
                                    <telerik:SumFunction SourceField="AROriginal"
                                                         ResultFormatString="{}{0:c2}" />


                                </telerik:GridViewDataColumn.AggregateFunctions>


                                <telerik:GridViewDataColumn.Footer>




                                    <telerik:AggregateResultsList ItemsSource="{Binding}" />




                                </telerik:GridViewDataColumn.Footer>


                            </telerik:GridViewDataColumn>


                            <telerik:GridViewDataColumn Header="AR Balance"
                                                        DataMemberBinding="{Binding ARBalance,Mode=TwoWay}"
                                                        DataFormatString="{}{0:c2}">
                                <telerik:GridViewDataColumn.AggregateFunctions>
                                    <telerik:SumFunction SourceField="ARBalance"
                                                         ResultFormatString="{}{0:c2}" />


                                </telerik:GridViewDataColumn.AggregateFunctions>


                                <telerik:GridViewDataColumn.Footer>




                                    <telerik:AggregateResultsList ItemsSource="{Binding}" />




                                </telerik:GridViewDataColumn.Footer>


                            </telerik:GridViewDataColumn>


                            <telerik:GridViewDataColumn Header="AP Original"
                                                        DataMemberBinding="{Binding APOriginal,Mode=TwoWay}"
                                                        DataFormatString="{}{0:c2}">
                                <telerik:GridViewDataColumn.AggregateFunctions>
                                    <telerik:SumFunction SourceField="APOriginal"
                                                         ResultFormatString="{}{0:c2}" />


                                </telerik:GridViewDataColumn.AggregateFunctions>


                                <telerik:GridViewDataColumn.Footer>




                                    <telerik:AggregateResultsList ItemsSource="{Binding}" />




                                </telerik:GridViewDataColumn.Footer>


                            </telerik:GridViewDataColumn>


                            <telerik:GridViewDataColumn Header="AP Balance"
                                                        DataMemberBinding="{Binding APBalance,Mode=TwoWay}"
                                                        DataFormatString="{}{0:c2}">
                                <telerik:GridViewDataColumn.AggregateFunctions>
                                    <telerik:SumFunction SourceField="APBalance"
                                                         ResultFormatString="{}{0:c2}" />


                                </telerik:GridViewDataColumn.AggregateFunctions>


                                <telerik:GridViewDataColumn.Footer>




                                    <telerik:AggregateResultsList ItemsSource="{Binding}" />




                                </telerik:GridViewDataColumn.Footer>


                            </telerik:GridViewDataColumn>
                        </telerik:RadGridView.Columns>
                    </telerik:RadGridView>

[Vladimir Stoyanov]

Hello Edward,

Thank you for the provided xaml.

May I ask you if you are able to successfully build and the run the project on your side? I tested the described behavior, and I was able to observe the error that you are referring to, however the project is building and running correctly. I am attaching the sample project that I used. If you can build and run your project, then you should not worry about this error, since it will not interfere with the application at runtime. My guess is that this is simply an issue with Visual Studio.

If you cannot build or run the application, may I ask you to modify the attached project in order to reproduce this behavior and send it back in a new support ticket? This way I will be able to further investigate.

I am looking forward to your reply.

Regards,
Vladimir Stoyanov
Progress Telerik