[Solved] Sorting and filtering GridTemplateColumn by text instead of ID

[wokalio]

I have two EntityDataSources. One for grid and second for column filter:

<asp:EntityDataSource ID="dsTasks" runat="server" ConnectionString="xxx" DefaultContainerName="xxx" EnableFlattening="False" EntitySetName="Tasks" EntityTypeFilter="Task" Include="Type">
</asp:EntityDataSource>
<asp:EntityDataSource ID="dsType" runat="server" ConnectionString="xxx" DefaultContainerName="xxx" EnableFlattening="False" EntitySetName="Types" EntityTypeFilter="Type">

I want to sort and filter grid by type name:

<telerik:GridTemplateColumn DataField="TypeId" HeaderText="Type" SortExpression="Type.Name" UniqueName="TypeId">
        <ItemTemplate>
            <asp:Label ID="lbl" runat="server" Text='<%# Eval("Type.Name") %>'></asp:Label>
        </ItemTemplate>
        <FilterTemplate>
            <telerik:RadComboBox ID="cmb" DataSourceID="dsType" DataTextField="Name"
                DataValueField="Id" Height="200px" SelectedValue='<%# ((GridItem)Container).OwnerTableView.GetColumn("TypeId").CurrentFilterValue %>'
                runat="server" OnClientSelectedIndexChanged="TypeIdIndexChanged">
            </telerik:RadComboBox>
            <telerik:RadScriptBlock ID="rsb" runat="server">
                <script type="text/javascript">
                    function TypeIdIndexChanged(sender, args) {
                        var tableView = $find("<%# ((GridItem)Container).OwnerTableView.ClientID %>");
                        tableView.filter("TypeId", args.get_item().get_value(), "EqualTo");
                    }
                </script>
            </telerik:RadScriptBlock>
        </FilterTemplate>
    </telerik:GridTemplateColumn>

In my code filtering work fine, but I can't sort. I have "Invalid input string format." error.

When I changed "DataField" value to "Type.Name" sorting works fine, but filter always show empty grid.

[Andrey]

Hello,

Yes, you need to set the DataField property to the field you want to filter/sort on. In your case this would be the "Type.Name".

About the problem you ran into, could you verify that the value that is selected from the ComboBox control is also presented in the RadGrid's datasource?

Could you share an excerpt of the Grid and ComboBox data? This will help to identify possible problems.

Regards,
Andrey
Telerik

[wokalio]

dsType data is simple dictionary:
1 - current
2 - planned
3 - done

Grid data is set of Tasks (name, type):
"Prepare documentation", "current";
"Implement financial module", "planned";
"Tests", "planned";
....

When I use this code (previous example with DataField="Type.Name"):

<telerik:GridTemplateColumn DataField="Type.Name" HeaderText="Type" SortExpression="Type.Name" UniqueName="TypeId">
        <ItemTemplate>
            <asp:Label ID="lbl" runat="server" Text='<%# Eval("Type.Name") %>'></asp:Label>
        </ItemTemplate>
        <FilterTemplate>
            <telerik:RadComboBox ID="cmb" DataSourceID="dsType" DataTextField="Name"
                DataValueField="Id" Height="200px" SelectedValue='<%# ((GridItem)Container).OwnerTableView.GetColumn("TypeId").CurrentFilterValue %>'
                runat="server" OnClientSelectedIndexChanged="TypeIdIndexChanged">
            </telerik:RadComboBox>
            <telerik:RadScriptBlock ID="rsb" runat="server">
                <script type="text/javascript">
                    function TypeIdIndexChanged(sender, args) {
                        var tableView = $find("<%# ((GridItem)Container).OwnerTableView.ClientID %>");
                        tableView.filter("TypeId", args.get_item().get_value(), "EqualTo");
                    }
                </script>
            </telerik:RadScriptBlock>
        </FilterTemplate>
    </telerik:GridTemplateColumn>

everything works fine, but my database query looks like:
"select * from Tasks join Type on Type.Id = Tasks.TypeId where Type.Name = 'planned'".

I want to query types by ID not by Name. Query should looks like this:
"select * from Tasks join Type on Type.Id = Tasks.TypeId where Type.Id= 2".
or
"select * from Tasks where TypeId = 2".

[Andrey]

Hi,

As I said in my previous reply the only way to achieve is either to prepare the filter expression manually as described in this help topic. The other way is to set the DataField property to "Type.ID" and thus your filter expression will become as the one you want.

I hope the provided information clears the situation.

Regards,
Andrey
Telerik