Search Issue with foreign key column

[inanc]

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:

http://www.syncfusion.com/downloads/support/directtrac/118216/12.4_Sample813240959.zip

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

[Venkatesh Ayothi Raman]

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> 

 

Sample:http://www.syncfusion.com/downloads/support/directtrac/general/7z/ForeignkeySearching1982272559 

Help document: https://help.syncfusion.com/api/js/ejgrid#events:actionbegin 

Regards, 

Venkatesh Ayothiraman.  

[inanc]

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

[Venkatesh Ayothi Raman]

Hi Inanc, 

Thanks for the update. 

We are happy to hear that your requirement is achieved. 

Regards, 

Venkatesh Ayothiraman.