Sorting when using GridDropDownColumn

[Derek]

We've used some GridDropDownColumns in RadGrids, but I'm a little confused about sorting. If we sort the column that uses the dropdown, it sorts by the value, and not the text, which is confusing to the user and not the desired result. For example, let's say I have a GridDropDownColumn for Widget, with the following values

ID  Name
-------------
2   Alpha
3   Bravo  
1   Charlie

If I sort by that column, i'd like to see it as Alpha, Bravor, Charlie, however what happens is that it sorts on the value, so it ends up being Charlie, Alpha, Bravo.

Is there a way to have the column sort by the displayed text (the ListTextField) rather than the value field (the ListValueField, my foreign key column).

[Shinu]

Hello,

You need to set SortExpression value as "Name" for that GridDropDownColumn.

-Shinu.

[Derek]

I think you misunderstood the question, or I didn't state it clearly enough. We have a griddropdowncolumn. When we sort that column, the default behaviour is to sort by the id value (in this case CompanyID), not the displayed value. The displayed value, CompanyName, isn't part of the radgrid datasource, it's retrieved via the listtextfield on the dropdown. Here's the definition for that column.

<telerik:GridDropDownColumn DataField="CompanyID" DataType="System.Int32" HeaderText="Company"
                    SortExpression="CompanyID" UniqueName="CompanyID" DataSourceID="dsCompany" ListTextField="CompanyName"
                    ListValueField="CompanyID" ColumnEditorID="customDropdown">
                </telerik:GridDropDownColumn>

Is it possible to sort by the displayed value, rather than the internal FK value?

[Veli]

Hi Derek,

No, the GridDropDownColumn, like any other column in RadGrid, can sort only by fields present in RadGrid's own data source. You cannot sort by fields in the lookup data source of the column or any other data that is not part of RadGrid's data source.

Veli
the Telerik team

[Derek]

Thanks Veli; as I thought.

I did find a workaround that allows me to sort by lookup columns, although it introduces a little bit of database overhead.

In the underlying datasource, we created an associated subquery, which preserves the insert/edit/delete behaviour. Then we used both the lookup (FK) column and the results of the subquery in the grid. The lookup column is set as not visible, so it only appears when editing. The subquery column is sortable in the grid, but automatically disappears when editing/inserting. From the user perspective, it's transparent, even though there are two different columns appearing as a single column.

So here's the query (simplified for the example)

select
   ValueID,
   CompanyID,
   (select CompanyName from Company
   where Company.CompanyID = MyValues.CompanyID) as CompanyName
from MyValues

Then we use a griddropdown for CompanyID, set as not visible, and a gridboundcolumn for CompanyName. This gives us the expected behaviour when sorting.

[Cori]

Hello Derek,

If you're including the CompanyName in your query, you just set it as the SortExpression in the GridDropDownColumn. I've down this in my own applications. Is this not the case for you?

[Derek]

Hi Cori;

We generally use datasets. If you create a dataset that uses a single table, the dataset will automatically generate methods for SELECT, UPDATE, INSERT and DELETE. If you create a dataset on a query, or on multiple tables using joins, the resulting dataset only has the SELECT method generated. Using a correlated subquery allows you to retrieve a lookup value, but the dataset will still contain all 4 methods.

Let me give an example. If I create a dataset and use the following query;

select

   MyValues.ValueID,

   MyValues.CompanyID,

   Company.CompanyName
from
    MyValues, Company
where Company.CompanyID = MyValues.CompanyID

Then the dataset will only contain a single method for SELECT. Any updating, deleting or inserting will need to be coded manually. However, iIf I create the dataset using a correlated subquery, such as the following;

select
   ValueID,
   CompanyID,
   (select CompanyName from Company
   where Company.CompanyID = MyValues.CompanyID) as CompanyName
from MyValues

The dataset will contain the same resulting values, but will still include all methods. It's an anomaly of VS datasets, but well documented.