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:
SQLSELECT
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.