Grid search toolbaritem and foreignkeyvalue column

[Johann Permanne]

Hello,

I added a search toolbar item on a grid and when I try to search a value, it works for all columns except the dropdown one. It only works on ForeignKeyField, not on ForeignKeyValue (Family and sub-family columns in my given example).

Here's a sample from my code :

@(Html.EJ().Grid("Grid_Item")
.Datasource(ds => ds.Json((IEnumerable
)ViewBag.datasourceItem).UpdateURL("../Parameters/itemUpdate").InsertURL("../Parameters/itemInsert").RemoveURL("../Parameters/itemRemove").Adaptor(AdaptorType.RemoteSaveAdaptor))
.AllowSorting()
.AllowFiltering()
.AllowSearching()
.FilterSettings(filter => { filter.FilterType(FilterType.Excel); })
.AllowPaging()
.PageSettings(p => { p.PageSize(14); })
.IsResponsive()
.EnableHeaderHover()
.EnableAltRow()
.EditSettings(edit => { edit.AllowAdding().AllowDeleting().AllowEditing().EditMode(EditMode.Normal).ShowDeleteConfirmDialog().AllowEditOnDblClick(false); })
.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("PK_ID").HeaderText("PK_ID").IsPrimaryKey(true).Visible(false).TextAlign(TextAlign.Center).Width(100).Add();
col.Field("FK_ID_Family").HeaderText(Resources.Parameters.ItemView_Family_HeaderText).ForeignKeyField("PK_ID").ForeignKeyValue("Wording").DataSource((IEnumerable)ViewBag.dataSourceFamily).TextAlign(TextAlign.Center).Width(100).Add();
col.Field("FK_ID_Sub_Family").HeaderText(Resources.Parameters.ItemView_SubFamily_HeaderText).ValidationRules(v => v.AddRule("required", true)).ForeignKeyField("PK_ID").ForeignKeyValue("Wording").DataSource((IEnumerable)ViewBag.dataSourceSubFamily).TextAlign(TextAlign.Center).Width(100).Add();
col.Field("Wording").HeaderText(Resources.Parameters.ItemView_Wording_HeaderText).Width(100).ValidationRules(v => v.AddRule("required", true)).TextAlign(TextAlign.Left).Add();
col.Field("Price").HeaderText(Resources.Parameters.ItemView_Price_HeaderText).TextAlign(TextAlign.Right).Format("{0:N}").EditType(EditingType.NumericEdit).Format("{0:N}").NumericEditOptions(new Syncfusion.JavaScript.Models.EditorProperties() { DecimalPlaces = 2 }).Width(50).Add();
})

Thanks for your help ! :)

[Farveen Sulthana Thameeztheen Basha]

Hi Johann,

 

Thanks for contacting Syncfusion Support.

 

We have checked your query and while using Foreign key Data, foreign-key-field has some limitations such as sort/group/ Search operations on column will happen based on “field” property instead of foreign-key-field”. When you search by using Employee ID, it will filter corresponding records related to the id not using value which was the behavior when we bind foreign key column. To overcome this, we suggested you to used Foreign Key Adaptor on Load event of the Grid. In the code example, you have used remoteSaveAdaptor to bind the Data’s grid and on the Load event of the Grid we have changed the adaptor type to ForeignkeyAdaptor.  

 

Please refer to the documentation and Online Demo Link:-

https://mvc.syncfusion.com/demos/web/grid/foreignkey

https://help.syncfusion.com/aspnetmvc/grid/data-adaptors?cs-save-lang=1&cs-lang=js#foreign-key-adaptor

 

Please get back to us if you need any further assistance.

 

Regards,

Farveen sulthana T

[Johann Permanne]

Hello and thank you for your answer !

Following your first link, it's working fine with just one "foreignkey" column. But in my example, I've got two "foreignkey" columns. So on my "onLoad" javascript function, how can I affect the model datasource adaptor to use both "familyObj" and "subFamilyObj" ?

@(Html.EJ().Grid<object>("Grid_Item")
    .Datasource(ds => ds.Json((IEnumerable<object>)ViewBag.datasourceItem).UpdateURL("../Parameters/itemUpdate").InsertURL("../Parameters/itemInsert").RemoveURL("../Parameters/itemRemove").Adaptor(AdaptorType.RemoteSaveAdaptor))
    .AllowSorting()
    .AllowFiltering()
    .AllowSearching()
    .FilterSettings(filter => { filter.FilterType(FilterType.Excel); })
    .AllowPaging()
    .PageSettings(p => { p.PageSize(14); })
    .IsResponsive()
    .EnableHeaderHover()
    .EnableAltRow()
    .EditSettings(edit => { edit.AllowAdding().AllowDeleting().AllowEditing().EditMode(EditMode.Normal).ShowDeleteConfirmDialog().AllowEditOnDblClick(false); })
    .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("PK_ID").HeaderText("PK_ID").IsPrimaryKey(true).Visible(false).TextAlign(TextAlign.Center).Width(100).Add();
        col.Field("FK_ID_Family_Wording").HeaderText(Resources.Parameters.ItemView_Family_HeaderText).TextAlign(TextAlign.Center).Width(100).Add();
        col.Field("FK_ID_Sub_Family_Wording").HeaderText(Resources.Parameters.ItemView_SubFamily_HeaderText).ValidationRules(v => v.AddRule("required", true)).TextAlign(TextAlign.Center).Width(100).Add();
        col.Field("Wording").HeaderText(Resources.Parameters.ItemView_Wording_HeaderText).Width(100).ValidationRules(v => v.AddRule("required", true)).TextAlign(TextAlign.Left).Add();
        col.Field("Price").HeaderText(Resources.Parameters.ItemView_Price_HeaderText).TextAlign(TextAlign.Right).Format("{0:N}").EditType(EditingType.NumericEdit).Format("{0:N}").NumericEditOptions(new Syncfusion.JavaScript.Models.EditorProperties() { DecimalPlaces = 2 }).Width(50).Add();
    })
    .ClientSideEvents(eve =>
    {
        eve.Load("onLoad");
    }))

@Html.EJ().ScriptManager()

<object><object>

[Johann Permanne]

This is the javascript part :

<script type="text/javascript">
    var familyData = @Html.Raw(Json.Encode(ViewBag.dataSourceFamily));
    var subFamilyData = @Html.Raw(Json.Encode(ViewBag.dataSourceSubFamily));

    var familyObj = [
        {
            dataSource: familyData,
            foreignKeyField: "PK_ID",
            field: "FK_ID_Family",
            foreignKeyValue: "Wording"
        }
    ];

    var subFamilyObj = [
        {
            dataSource: subFamilyData,
            foreignKeyField: "PK_ID",
            field: "FK_ID_Sub_Family",
            foreignKeyValue: "Wording"
        }
    ];

    function onLoad(args) {
        this.model.dataSource.adaptor = new ej.ForeignKeyAdaptor(familyObj, "remoteSaveAdaptor"); //?????
        this.model.dataSource.adaptor = new ej.ForeignKeyAdaptor(subFamilyObj, "remoteSaveAdaptor"); //?????
    }

</script>

[Farveen Sulthana Thameeztheen Basha]

Hi Johann, 

We have checked your query and if you are using two Foreign Key columns, you can add the object into the array and you can use into the Load event of the Grid. 

Please refer to the code example:- 

<script type="text/javascript"> 

   var familyObj = [ 

        { 

            dataSource: familyData, 

            foreignKeyField: "PK_ID", 

            field: "FK_ID_Family", 

            foreignKeyValue: "Wording" 

        }, 

        { 

            dataSource: subFamilyData, 

            foreignKeyField: "PK_ID", 

            field: "FK_ID_Sub_Family", 

            foreignKeyValue: "Wording" 

        } 

    ]; 

 

    function onLoad(args){ 

        this.model.dataSource.adaptor = new ej.ForeignKeyAdaptor(familyObj, "remoteSaveAdaptor"); 

    } 

</script> 

Please get back to us if you need any further assistance. 

Regards, 

Farveen sulthana T 

[Johann Permanne]

Thanks a lot for your answer. It was so simple...

Now I have a last question. In my previous example, searching is working very well ! But........

familyData contains 3 objects :

- PK_ID "1" / Wording "Family1"

- PK_ID "2" / Wording "Family2"

- PK_ID "3" / Wording "Family3"

Grid datasource contains two rows :

- Item 1 (linked to Family 1)

- Item 2 (linked to Family 2).

When I try to add another item, only Family1 and Family2 are available in my dropdownlist...... I don't understand why..?

[Farveen Sulthana Thameeztheen Basha]

Hi Johann,

 

We can reproduce your reported problem at our end. To overcome this problem we suggested you to define the DataSource for the ForeignKey column which should be in the form of text and pair value. Otherwise you can define it on the DropDownEditOptions  of the Grid.

 

Please refer to the code example:-

 

@(Html.EJ().Grid<object>("FlatGrid")

       .Datasource(ds =>{

            ds.Json((IEnumerable<object>)ViewBag.datasource)

            .UpdateURL("/Grid/ForeignKeyUpdate")

            .InsertURL("/Grid/ForeignKeyInsert")

            .RemoveURL("/Grid/ForeignKeyDelete")

            .Adaptor(AdaptorType.RemoteSaveAdaptor);

        })

       

        .AllowPaging()

        .AllowFiltering()

        .AllowSorting()

        .Columns(col =>

        {

            col.Field("OrderID").HeaderText("Order ID").IsPrimaryKey(true).TextAlign(TextAlign.Right).Add();

             col.Field("EmployeeID_FirstName").HeaderText("First Name")

                .DataSource((IEnumerable<object>)ViewBag.datasource).Add();

                .  .  .   .

           

        })

    )

 

 

Please get back to us if you need any further assistance.

 

Regards,

 

Farveen sulthana T

[Johann Permanne]

Hello,

Sorry, this is not "fully" fonctionnal.

You can find, as linked file, a full example of my code. It contains code from controller and view.

If I run the code as in the file, grid show is OK, search is OK, but adding a new record doesn't show full list.

If I add .DataSource((IEnumerable<object>)ViewBag.dataSourceERP) on FK_ID_ERP_Wording column, grid show is OK, search is OK, but adding a new record shows an empty list.

If I add .DataSource((IEnumerable<object>)ViewBag.dataSourceERPSelect) on FK_ID_ERP_Wording column, grid show is OK, search is OK, adding a new record shows a full list, but added record only shows pk_id, not wording of FK_ID_ERP_Wording.

I'm really sorry and I hope you'll be able to help me.

Regards

Attachment: fullExample_c9adc7d2.zip

[Farveen Sulthana Thameeztheen Basha]

Hi Johann, 

We can reproduce your reported problem at our end. By checking your code example while defining the column dataSource you need to set the DropdownList data in the form of text and value pair. 

Please refer to the code example:- 

@(Html.EJ().Grid<object>("FlatGrid") 

       .Datasource(ds =>{ 

            ds.Json((IEnumerable<object>)ViewBag.datasource) 

            .UpdateURL("/Grid/ForeignKeyUpdate") 

            .InsertURL("/Grid/ForeignKeyInsert") 

            .RemoveURL("/Grid/ForeignKeyDelete") 

            .Adaptor(AdaptorType.RemoteSaveAdaptor); 

        }) 

         .  .    

        .Columns(col => 

        { 

             

            col.Field("EmployeeID_FirstName").HeaderText("First Name").EditType(EditingType.Dropdown).DataSource((IEnumerable<object>)ViewBag.datasource2).DropDownEditOptions(new DropDownListProperties() { DropDownListFields = { Text = "FirstName", Value = "EmployeeID" } }) 

               .Width(90) 

               .Add(); 

             

        }) 

                .ClientSideEvents(eve => 

                { 

                    eve.Load("onLoad"); 

                }) 

    ) 

 

<script type="text/javascript"> 

    var foreignData = @Html.Raw(Json.Encode(ViewBag.foreignCol)); 

 

    var foreignObj = [ 

        { 

            dataSource: foreignData, 

            foreignKeyField: "EmployeeID", //Property in the Grid's main dataSource 

            field: "EmployeeID", //Property in foreignkey dataSource 

            foreignKeyValue: "FirstName" //Property in foreignkey dataSource 

        } 

    ]; 

 

 

    function onLoad(args){ 

        this.model.dataSource.adaptor = new ej.ForeignKeyAdaptor(foreignObj, "remoteSaveAdaptor"); 

    } 

</script> 

Server side:- 

 

       public ActionResult GridFeatures() 

         { 

             BindDataSource(); 

             var datasource = order.ToList(); 

             ViewBag.datasource = datasource; 

             var DataSource1 = new NorthwindDataContext().EmployeeViews.ToList(); 

             ViewBag.datasource2 = DataSource1; 

 

             ViewBag.foreignCol = new NorthwindDataContext().EmployeeViews.ToList(); 

             return View(); 

        } 

 

Refer to the sample Link:- 

http://www.syncfusion.com/downloads/support/forum/137699/ze/SAMPLE~3-1608178737.zip 

Please get back to us if you need any further assistance. 

Regards, 

Farveen sulthana T 

[Farveen Sulthana Thameeztheen Basha]

Hi Johann, 

We can reproduce your reported problem at our end. By checking your code example while defining the column dataSource you need to set the DropdownList data in the form of text and value pair. 

Please refer to the code example:- 

@(Html.EJ().Grid<object>("FlatGrid") 

       .Datasource(ds =>{ 

            ds.Json((IEnumerable<object>)ViewBag.datasource) 

            .UpdateURL("/Grid/ForeignKeyUpdate") 

            .InsertURL("/Grid/ForeignKeyInsert") 

            .RemoveURL("/Grid/ForeignKeyDelete") 

            .Adaptor(AdaptorType.RemoteSaveAdaptor); 

        }) 

         .  .    

        .Columns(col => 

        { 

             

            col.Field("EmployeeID_FirstName").HeaderText("First Name").EditType(EditingType.Dropdown).DataSource((IEnumerable<object>)ViewBag.datasource2).DropDownEditOptions(new DropDownListProperties() { DropDownListFields = { Text = "FirstName", Value = "EmployeeID" } }) 

               .Width(90) 

               .Add(); 

             

        }) 

                .ClientSideEvents(eve => 

                { 

                    eve.Load("onLoad"); 

                }) 

    ) 

 

<script type="text/javascript"> 

    var foreignData = @Html.Raw(Json.Encode(ViewBag.foreignCol)); 

 

    var foreignObj = [ 

        { 

            dataSource: foreignData, 

            foreignKeyField: "EmployeeID", //Property in the Grid's main dataSource 

            field: "EmployeeID", //Property in foreignkey dataSource 

            foreignKeyValue: "FirstName" //Property in foreignkey dataSource 

        } 

    ]; 

 

 

    function onLoad(args){ 

        this.model.dataSource.adaptor = new ej.ForeignKeyAdaptor(foreignObj, "remoteSaveAdaptor"); 

    } 

</script> 

Server side:- 

 

       public ActionResult GridFeatures() 

         { 

             BindDataSource(); 

             var datasource = order.ToList(); 

             ViewBag.datasource = datasource; 

             var DataSource1 = new NorthwindDataContext().EmployeeViews.ToList(); 

             ViewBag.datasource2 = DataSource1; 

 

             ViewBag.foreignCol = new NorthwindDataContext().EmployeeViews.ToList(); 

             return View(); 

        } 

 

Refer to the sample Link:- 

http://www.syncfusion.com/downloads/support/forum/137699/ze/SAMPLE~3-1608178737.zip 

Please get back to us if you need any further assistance. 

Regards, 

Farveen sulthana T 

[Johann Permanne]

Hi.

Sorry for my late answer, I've been struggling one week long with my code...

I still have the same problem : everything is fine until I had a new record, when added I only see the id (not the wording) on the grid. I'm gonna give up this feature, I think.

[Sathyanarayanamoorthy Eswararao]

Hi Johann, 

Query:  when added I only see the id (not the wording) on the grid. 

 

We suspect that the mentioned issue occurs if the values for  Text and Value fields for the Foreign Key column are not given properly. To show the Wording in the newly added record the Wording must be given as Text Field and Id must be given in Value  field. 

Please refer the below code example. 

 

@(Html.EJ().Grid<object>("FlatGrid")  

       .Datasource(ds =>{  

            ds.Json((IEnumerable<object>)ViewBag.datasource)  

            .UpdateURL("/Grid/ForeignKeyUpdate")  

            .InsertURL("/Grid/ForeignKeyInsert")  

            .RemoveURL("/Grid/ForeignKeyDelete")  

            .Adaptor(AdaptorType.RemoteSaveAdaptor);  

        })  

         .  .     

        .Columns(col =>  

        {  

              

            col.Field("EmployeeID_FirstName").HeaderText("First Name").EditType(EditingType.Dropdown).DataSource((IEnumerable<object>)ViewBag.datasource2) 

.DropDownEditOptions(new DropDownListProperties() { DropDownListFields = { Text = "FirstName", Value = "EmployeeID" } })  

               .Width(90)  

               .Add();  

              

        })  

                .ClientSideEvents(eve =>  

                {  

                    eve.Load("onLoad");  

                })  

    )  

  

Server side:-  

  

       public ActionResult GridFeatures()  

         {  

             BindDataSource();  

             var datasource = order.ToList();  

             ViewBag.datasource = datasource;  

             var DataSource1 = new NorthwindDataContext().EmployeeViews.ToList();  

             ViewBag.datasource2 = DataSource1;  

  

             ViewBag.foreignCol = new NorthwindDataContext().EmployeeViews.ToList();  

             return View();  

        }  

  

In the above code example the EmployeeID is given as Value Field and Firstname is given as Text field hence if a new record is added then FirstName will be shown in grid. 

Refer to the sample Link:-  

http://www.syncfusion.com/downloads/support/forum/137699/ze/SAMPLE~3-1608178737.zip  

If you need any further assistance please get back to us. 

Regards,
Sathyanarayanamoorthy 

[Johann Permanne]

Sorry, I paid attention to my code and DropdownListFields were good, but still the same issue.

I tried to open your sample project but I got a CS0121 error :
The call is ambiguous between the following methods or properties: 'Syncfusion.MVC.EJ.JavaScriptExtension.EJ<dynamic>(System.Web.Mvc.HtmlHelper<dynamic>)'and  'Syncfusion.MVC.EJ.JavaScriptExtension.EJ<dynamic>(System.Web.Mvc.HtmlHelper<dynamic>)'


[Farveen Sulthana Thameeztheen Basha]

Hi Johann, 

  

A support incident has been created under your account to track the status of this requirement. Please log on to our support website to check for further updates.    

  

https://www.syncfusion.com/account/login?ReturnUrl=/support/directtrac/incidents   

  

  

Regards, 

Farveen sulthana T 

[Johann Permanne]

Hello,

I'm really sorry to open this thread again, but I've got a new issue.

I added an "Excel Export" ToolBarItem and I encounter an System Null Exception on exporting. I found an explanation on your site : https://www.syncfusion.com/kb/3018/null-exception-while-exporting

It says : "The dataSource passed to the exporting method is compared with the Grid dataSource. Thus, when a column is not defined in the Grid dataSource, it is obtained as null value and hence throws a null exception."

Is there a way to have an Excel Export working in this thread conditions ?

[Seeni Sakthi Kumar Seeni Raj]

Hi Johann,  

 

We could see you would like to render column which is not in the Grid DataSource. But this case is not valid, and we cannot collect the column value for a cell which is not in the datasource. So, we suggest to give the columns which is present in the Grid DataSource.  

 

If you would like to populate the Grid columns with unbounded values, you can use the template column. Refer to the following Help Documents and Showcase demo,  

 

Document: https://help.syncfusion.com/aspnetmvc/grid/exporting#columntemplate-exporting 

Demo: https://mvc.syncfusion.com/demos/web/grid/columntemplateexporting 

 

Note: In this feature also, we cannot mention a column which is not in the Grid. But we can populate a column and modify the cell values.  

 

Regards,  

Seeni Sakthi Kumar S. 

 

[Johann Permanne]

Okay, I changed everything from the beginning. Now, everything is working fine. Thanks.

[Prasanna Kumar Viswanathan]

Hi Johann, 

We are happy to hear that your issue has been resolved. 

Please get back to us if you need any further assistance. 

Regards, 

Prasanna Kumar N.S.V