KB Article A1491
Visible to All Users

Is it possible to sort LookUp columns by their displayed values?

Description:
I have a grid that has a column of type LookUpEdit (RepositoryItemLookUpEdit). The LookUp edit contains two fields a CustomerID (Integer), which is the ValueMember, and a CompanyName (the DisplayMember). When I click on the column header to sort the grid by that column, this grid is sorted by CustomerID values. This is obvious because the field bound to the grid column is the CustomerID. However, is it possible to sort by the display field?

Answer:
XtraGrid 3 and higher supports sorting by displayed values. You should set the SortMode property of your LookUp column to DisplayText.
XtraGrid 2 can only sort columns by actual values (lookup codes), because it uses the DataView.Sort property. We will implement our own sorting methods in a future version of the XtraGrid, so sorting by the displayed text will also be possible later on.
However you can try to work around this limitation of the XtraGrid 2. The attached sample project shows how you can emulate sorting by the displayed value of a LookUp column. The idea is to create a hidden column, which will contain the displayed values of a lookup column. It is easy to create such a column and populate it via a SQL SELECT query:

SQL
SELECT o.OrderID, o.CustomerID, o.OrderDate, (SELECT c.CompanyName FROM Customers c WHERE c.CustomerID = o.CustomerID) AS CompanyName FROM Orders o

When the lookup column is clicked, you need to change the SortOrder property of the hidden column. This is accomplished via the GridView.Click event.
It is also necessary to update the hidden column's value when the lookup column is changed. We advise that you handle the GridView.CellValueChanged event for this.
Please check the database connection string (sqlConnection1.ConnectionString) before launching the attached sample project.

Disclaimer: The information provided on DevExpress.com and affiliated web properties (including the DevExpress Support Center) is provided "as is" without warranty of any kind. Developer Express Inc disclaims all warranties, either express or implied, including the warranties of merchantability and fitness for a particular purpose. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.

Confidential Information: Developer Express Inc does not wish to receive, will not act to procure, nor will it solicit, confidential or proprietary materials and information from you through the DevExpress Support Center or its web properties. Any and all materials or information divulged during chats, email communications, online discussions, Support Center tickets, or made available to Developer Express Inc in any manner will be deemed NOT to be confidential by Developer Express Inc. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.