Filter Text

[Philip Senechal]

I have an interesting scenario here that I could use some assistance with.

I have a RadGrid that is bound to the values from one database. One of the columns is an Employee ID. I actually display the Employee Name in the column, but I have to do a lookup to a completely different database to get the Employee Name. This works fine...I just do it in the ItemDataBound event using the Employee ID as DataKeyValue.

My problem comes when I turn this column into a filterable column. The dropdown used for filtering brings back the Employee ID and Employee Name from the outside database and the filtering process works fine. What happens after the column is filtered though is that the filter dropdown displays the Employee ID instead of the Employee Name. This is because the RadComboBox has its Text property set to <%# (Container as GridItem).OwnerTableView.GetColumn("ReqEmpID").CurrentFilterValue %> which of course is the Employee ID that I just filtered on.

What I need to do is after the grid is filtered, I need the dropdown to display the filter text, not the value. Or I need a way to put the Employee Name in that text property after the grid is filtered.

Can you maybe point me in the right direction on how to do that? Thanks!

[Mira]

Hello Philip,

I recommend that you set the SelectedValue of the RadComboBox to

<%# (Container as GridItem).OwnerTableView.GetColumn("ReqEmpID").CurrentFilterValue %>

Please give this suggestion a try and let me know how it goes.

Kind regards,
Mira
the Telerik team

[Philip Senechal]

Hi Mira,

Thanks for the suggestion. This would normally work on the pre-populated drop-down filter controls, however, the one I'm working with is a load-on-demand drop down, so those values aren't pre-populated in the drop-down.

Perhaps going along those lines, is there a way I could add that particular selected value/text as a drop-down item when the filter is applied? Perhaps somewhere in the JavaScript that does the filtering? Or is there an event somewhere in the filtering timeline that I can use?

Much appreciated for the assistance.

[Philip Senechal]

Thought I would share some code with you if it helps at all to see what I'm doing.

Here is the .aspx page

<telerik:GridTemplateColumn HeaderText="Requester" DataField="ReqEmpID" UniqueName="ReqEmpID">
    <HeaderStyle Wrap="false" />
    <ItemStyle Wrap="false" />
    <ItemTemplate>
        <asp:Label ID="lbl_ReqEmpNm" Text="" runat="server" /><br />
        <asp:Label ID="lbl_DeptNm" Text="" runat="server" />
    </ItemTemplate>
    <FilterTemplate>
        <telerik:RadComboBox ID="dd_requester_filter" Skin="Office2007" Width="120px" Font-Size="11px"
            Filter="Contains" AppendDataBoundItems="true" ShowMoreResultsBox="true" EnableLoadOnDemand="true"
            EnableVirtualScrolling="true" Text='<%# (Container as GridItem).OwnerTableView.GetColumn("ReqEmpID").CurrentFilterValue %>'
            OnItemsRequested="dd_requester_filter_ItemsRequested" OnClientSelectedIndexChanged="RequesterIndexChanged"
            runat="server">
            <Items>
                <telerik:RadComboBoxItem Text="All" />
            </Items>
        </telerik:RadComboBox>
        <telerik:RadScriptBlock ID="RadScriptBlock2" runat="server">

            <script type="text/javascript">
                function RequesterIndexChanged(sender, args) {
                    var tableView=$find("<%# (Container as GridItem).OwnerTableView.ClientID %>");
                    tableView.filter("ReqEmpID", args.get_item().get_value(), "EqualTo");
                }
            </script>

        </telerik:RadScriptBlock>
    </FilterTemplate>
</telerik:GridTemplateColumn>

here is the code that loads the grid...

protected void RadGrid1_NeedDataSource(object source, GridNeedDataSourceEventArgs e)
{
    if (!e.IsFromDetailTable)
    {
        RadGrid1.DataSource = DataAccess.GetRequests(queueFilter, typeFilter);
    }
}

which calls this...

public static IQueryable GetRequests(string queue, string type)
{
    Requests.RequestsDataContext db = new Requests.RequestsDataContext();
    var query = from requests in db.tRequests
                orderby requests.DueDt, requests.ReqID
                select new { requests.ReqID, requests.tRequestType.ReqNm, requests.ReqDt, requests.ReqEmpID, requests.ReqTtl, requests.DueDt, requests.AnlysEmpID, requests.tStatus.StatNm, requests.tPriority.PrtyNm, Age = (requests.ReqTyp == 10 ? (requests.ReqStat == 4 || requests.ReqStat == 5 ? SqlMethods.DateDiffDay(requests.ReqDt, requests.CmplDt) - ((SqlMethods.DateDiffDay(requests.ReqDt, requests.CmplDt) / 7) * 2) - (requests.ReqDt.Value.DayOfWeek.ToString() == "Friday" && SqlMethods.DateDiffDay(requests.ReqDt, requests.CmplDt) % 7 >= 3 ? 2 : (requests.ReqDt.Value.DayOfWeek.ToString()) == "Thursday" && SqlMethods.DateDiffDay(requests.ReqDt, requests.CmplDt) % 7 >= 4 ? 2 : (requests.ReqDt.Value.DayOfWeek.ToString()) == "Wednesday" && SqlMethods.DateDiffDay(requests.ReqDt, requests.CmplDt) % 7 >= 5 ? 2 : (requests.ReqDt.Value.DayOfWeek.ToString()) == "Tuesday" && SqlMethods.DateDiffDay(requests.ReqDt, requests.CmplDt) % 7 >= 6 ? 2 : 0) : SqlMethods.DateDiffDay(requests.ReqDt, DateTime.Now) - ((SqlMethods.DateDiffDay(requests.ReqDt, DateTime.Now) / 7) * 2) - ((requests.ReqDt.Value.DayOfWeek.ToString()) == "Friday" && SqlMethods.DateDiffDay(requests.ReqDt, DateTime.Now) % 7 >= 3 ? 2 : (requests.ReqDt.Value.DayOfWeek.ToString()) == "Thursday" && SqlMethods.DateDiffDay(requests.ReqDt, DateTime.Now) % 7 >= 4 ? 2 : (requests.ReqDt.Value.DayOfWeek.ToString()) == "Wednesday" && SqlMethods.DateDiffDay(requests.ReqDt, DateTime.Now) % 7 >= 5 ? 2 : (requests.ReqDt.Value.DayOfWeek.ToString()) == "Tuesday" && SqlMethods.DateDiffDay(requests.ReqDt, DateTime.Now) % 7 >= 6 ? 2 : 0)) : ((requests.ReqStat == 4 || requests.ReqStat == 5) && requests.AssnDt == null ? SqlMethods.DateDiffDay(requests.ReqDt, requests.CmplDt) : (requests.ReqStat == 4 || requests.ReqStat == 5) && requests.AssnDt != null ? SqlMethods.DateDiffDay(requests.AssnDt, requests.CmplDt) : requests.ReqStat != 4 && requests.ReqStat != 5 && requests.AssnDt == null ? SqlMethods.DateDiffDay(requests.ReqDt, DateTime.Now) : SqlMethods.DateDiffDay(requests.AssnDt, DateTime.Now))), Tasks = requests.tTasks.Count(), MinTaskDueDate = (from tasks in requests.tTasks where tasks.TaskStat != 4 && tasks.TaskStat != 5 select tasks.TaskDueDt).Min() };

    return query;
}

here is the code for the load-on-demand drop-down...

protected void dd_requester_filter_ItemsRequested(object source, RadComboBoxItemsRequestedEventArgs e)
{
    RadComboBox combo = (RadComboBox)source;
    string requesterCheck = e.Text;

    DataTable table = DataAccess.GetEmployeesFilter(requesterCheck);

    int itemsPerRequest = 10;
    int itemOffset = e.NumberOfItems;
    int endOffset = Math.Min(itemOffset + itemsPerRequest, table.Rows.Count);
    e.EndOfItems = endOffset == table.Rows.Count;

    for (int i = itemOffset; i < endOffset; i++)
    {
        combo.Items.Add(new RadComboBoxItem(table.Rows[i]["EmpNm"].ToString(), table.Rows[i]["EmpID"].ToString()));
    }

    e.Message = Functions.dd_StatusMessage(endOffset, table.Rows.Count);
}

which calls this...

public static DataTable GetEmployeesFilter(string EmpNm)
{
    DataTable table = SqlGetDataTable("SELECT EmpID, EmpFNm + ' ' + EmpLNm AS EmpNm FROM tEmployee WHERE (EmpFNm + ' ' + EmpLNm LIKE '%" + EmpNm + "%') ORDER BY EmpFNm + ' ' + EmpLNm", "dbVSPNet");
    return table;
}

public static DataTable SqlGetDataTable(string query, string source)
{
    string connstr = ConfigurationManager.ConnectionStrings[source].ToString();
    SqlConnection dbconn = new SqlConnection(connstr);
    SqlDataAdapter dbadapter = new SqlDataAdapter(query, dbconn);
    DataTable table = new DataTable();
    try
    {
        dbadapter.Fill(table);
        dbadapter = null;
    }
    finally
    {
        dbconn.Close();
    }

    return table;
}

[Mira]

Hello Philip,

Please try using the following declaration of the combo and let me know whether it helps:

<telerik:RadComboBox ID="dd_requester_filter" Skin="Office2007" Width="120px" Font-Size="11px"
    Filter="Contains" AppendDataBoundItems="true" ShowMoreResultsBox="true" EnableLoadOnDemand="true"
    EnableVirtualScrolling="true" SelectedValue='<%# (Container as GridItem).OwnerTableView.GetColumn("ReqEmpID").CurrentFilterValue %>'
    OnItemsRequested="dd_requester_filter_ItemsRequested" OnClientSelectedIndexChanged="RequesterIndexChanged"
    DataTextField="EmpNm" DataValueField="EmpID" runat="server">
    <Items>
        <telerik:RadComboBoxItem Text="All" />
    </Items>
</telerik:RadComboBox>

All the best,
Mira
the Telerik team

[Philip Senechal]

Hi Mira,

As expected, that solution didn't work since there aren't any values bound to this filter dropdown when the page refreshes. I'm going to try adding some logic to the ItemDataBound event that attempts to read the currentfiltervalue, then does a lookup in that separate database to get the name, then assigns that to the text property of the filter dropdown. I'll let you know if it works. Thanks.

[Philip Senechal]

Hi Mira,

I believe I have resolved the issue with the following...

protected void RadGrid1_ItemDataBound(object source, GridItemEventArgs e)
{
    if (e.Item is GridFilteringItem)
    {
        GridFilteringItem filterItem = (GridFilteringItem)e.Item;
        string ReqEmpID = e.Item.OwnerTableView.GetColumn("ReqEmpID").CurrentFilterValue;
        RadComboBox combo = (RadComboBox)filterItem.FindControl("dd_requester_filter");
        if (ReqEmpID == "")
        {
            combo.Text = "All";
        }
        else
        {
            combo.Text = DataAccess.EmployeeNameLookup(ReqEmpID).ToString();
        }
    }
}

The EmployeeNameLookup function simply takes an ID and returns the name.

Thanks for the assistance!