We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. Image for the cookie policy date
close icon

Search Issue with foreign key column

Hello,
We are evaluating your grid. It is important for us being able to accomplish effective searches on huge datasets easily. Your Essential Grid is an excellent product. It is really fast!
But I think there  is a problem for us..

Please look at the sample provided by you at the link below:

There are two foreign key columns in the grid. I  changed the grid a little bit for making searches like below:


@(Html.EJ().Grid<object>("Grid")
.AllowSearching()
    .Datasource(((IEnumerable<object>)ViewBag.datasource1))
                .EditSettings(edit => { edit.AllowAdding().AllowDeleting().AllowEditing(); })
                .AllowPaging()
                    .ToolbarSettings(toolbar =>
            {
                toolbar.ShowToolbar().ToolbarItems(items =>
                {
                    items.AddTool(ToolBarItems.Add);
                    items.AddTool(ToolBarItems.Edit);
                    items.AddTool(ToolBarItems.Delete);
                    items.AddTool(ToolBarItems.Update);
                    items.AddTool(ToolBarItems.Cancel);
                    items.AddTool(ToolBarItems.Search);
                });
            })
        .Columns(col =>
                {
                    col.Field("OrderID").HeaderText("Order ID").IsPrimaryKey(true).TextAlign(TextAlign.Right).Add();                 
                    col.Field("EmployeeID").HeaderText("Employee ID").Add();
                    col.Field("Freight").Format("{0:c}").Add();
                    col.Field("CustomerID").HeaderText("Designation").ForeignKeyField("CustomerID").ForeignKeyValue("ContactTitle").DataSource(((IEnumerable<object>)ViewBag.name)).Add();//Dropdownlist based on which the Country column dropdown is refreshed
                    col.Field("ShipCity").HeaderText("Country").ForeignKeyField("ShipCity").ForeignKeyValue("Country").DataSource(((IEnumerable<object>)ViewBag.country)).Add();//Refreshed upon change in Designation Column
        })
        .ClientSideEvents(eve=>eve.ActionComplete("Complete"))
)

@(Html.EJ().ScriptManager())

but I cant search in the foreign fields with names 'Designation' and 'Country'. I could not figure out how to do that. Can you please help me a little bit? Thank you very much..

3 Replies

VA Venkatesh Ayothi Raman Syncfusion Team November 16, 2016 04:35 PM UTC

Hi Inanc, 
Thanks for contacting Syncfusion support. 

We have achieved your requirement using ActionBegin event in Grid. In this event, we can check the requestType as searching and input value is foreignkey column value. If given value is foreignkey column field then we can change the search settings. Please refer to the code example, Sample and Help document, 
Code example
@(Html.EJ().Grid<OrdersView>("ForeignKey") 
        .Datasource((IEnumerable<object>)ViewBag.dataSource1) 
        .AllowPaging() 
               
        .ToolbarSettings(toolbar => 
        { 
            toolbar.ShowToolbar().ToolbarItems(items => 
            { 
                 
                items.AddTool(ToolBarItems.Search); 
            }); 
        }) 
        .Columns(col => 
        { 
             .  . .  
            col.Field("EmployeeID").HeaderText("Employee Name").ForeignKeyField("EmployeeID") 
               .ForeignKeyValue("FirstName").DataSource((IEnumerable<object>)ViewBag.dataSource2) 
               .TextAlign(TextAlign.Left).Width(90).Add(); 
            . . . 
        }).ClientSideEvents(eve => 
        { 
            eve.ActionBegin("actionbegin"); 
        }) 
) 
</div> 
<script> 
    function actionbegin(args) { 
 
        if (args.requestType == "searching") { 
 
            if (ej.DataManager(this.model.dataSource).executeLocal(new ej.Query().search(args.keyValue, ["OrderID", "EmployeeID", "Freight", "ShipName", "ShipCountry"], true)).length == 0) { 
                var length = this.model.columns.length; 
                for (var i = 0; i < length; i++) { 
                    if (this.model.columns[i]["foreignKeyValue"] == "FirstName") { 
                        var data = ej.DataManager(this.model.columns[i]["dataSource"]).executeLocal(new ej.Query().search(args.keyValue, [this.model.columns[i]["foreignKeyValue"]], true)), foreignkeyFieldValue = (data[0].EmployeeID).toString(); 
                        this.model.searchSettings.fields = [this.model.columns[i]["foreignKeyField"]]; //Search the data in foreignkey field 
                        this.model.searchSettings.operator = "equal"; // set equal operator for get the equal values from foreignkey data source 
                        this.model.searchSettings.key = foreignkeyFieldValue; // set the foreignkey field 
 
                    } 
                } 
            } 
            else { 
                this.model.searchSettings.fields = ["OrderID", "EmployeeID", "Freight", "ShipName", "ShipCountry"]; //Search the data source with selected fields 
                this.model.searchSettings.operator = "contains"; // Set the operator to contains  
                this.model.searchSettings.key = args.keyValue; // Set the foreign key value 
            } 
 
        } 
    } 
</script> 
 



Regards, 
Venkatesh Ayothiraman.  



IN inanc November 21, 2016 11:41 AM UTC

Thank you very much. This solved my problem. Kind regards.


VA Venkatesh Ayothi Raman Syncfusion Team November 22, 2016 08:43 AM UTC

Hi Inanc, 

Thanks for the update. 

We are happy to hear that your requirement is achieved. 

Regards, 
Venkatesh Ayothiraman. 


Loader.
Live Chat Icon For mobile
Up arrow icon