Save grid state (column visibility, filtering, sorting) in database

[Hatem Muhsen]

I'd like to allow the user to save the state of the grid (after they have sorted, filtered, etc) so when the next time they visit the app the grid is saved in the same state as they left it. I understand there is the option for local save but I want the user to have the ability to save their own view permanently as well as a "reset" button to reset the view back to default.

I've seen an example you guys did but it was for the EJ model. I'm using EJS. Here's my code:

View

<div class="control-section">

        @(Html.EJS().Grid<ProjectMvcModel>("Grid")

                .DataSource(DataManager => { DataManager.Url("/Proj/UrlDataSource").CrudUrl("/Proj/CrudUpdate").Adaptor("UrlAdaptor"); })

                //.DataSource(DataManager => { DataManager.Url("/PMRS/UrlDataSource").InsertUrl("/PMRS/Insert").Adaptor("UrlAdaptor"); })

                .Width("100%")

                .AllowResizing(true)

                .AllowFiltering()

                .FilterSettings(filter => filter.Type(Syncfusion.EJ2.Grids.FilterType.Excel))

                .AllowSelection()

                .AllowGrouping()

                //.AllowMultiSorting()

                //.AllowTextWrap()

                .AllowSorting(true)

                .AllowReordering()

                .ShowColumnChooser(true)

                .ShowColumnMenu(true)

                .AllowExcelExport(true)

                .EnablePersistence(true)

                

                .ToolbarClick("toolbarClick")

                .GroupSettings(group => { group.ShowDropArea(false).ShowUngroupButton(true); })

                    //.ActionBegin("onActionBegin")

                    .Columns(col =>

                    {

                        col.Field(p => p.Id).Width("60").IsPrimaryKey(true).IsIdentity(true).Type("number").Width("120").Add();

                        col.HeaderText("Project Details").Visible(true).Template(" <a rel='nofollow' href='ProjectDetailsMaster?id=${Id} '>View</a> ").Width("140").AllowEditing(false).Add();

                        //col.Field(p => p.Id).Width("75").IsPrimaryKey(true).Type("number").Add();

                        col.Field(p => p.ProjName).AllowSorting(true).Width("175").Visible(false).Add();

                        col.Field(p => p.GroupName).EditType("dropdownedit").Width("175").ValidationRules(new { required = "true" }).Add();

                        col.Field(p => p.ProjDesc).ValidationRules(new { required = "true" }).Width("325").Add(); //allow editing(true)

                        col.Field(p => p.PortLead).ValidationRules(new { required = "true" })/*.EditType("dropdownedit")*/.Width("180").Add();

                        col.Field(p => p.ProjLead).ValidationRules(new { required = "true" })/*.EditType("dropdownedit")*/.Width("180").Add();

                        col.Field(p => p.ProgramName).ValidationRules(new { required = "true" }).EditType("dropdownedit").Width("180").Add();

                        col.Field(p => p.AssignedDate).ValidationRules(new { required = "true" }).Format("yMd").EditType("datepickeredit").Width("175").Add();  //Date

                        col.Field(p => p.StatusName)/*.Template("#statusTemplate")*/.EditType("dropdownedit").Width("175").Template("#statusTemplate").ValidationRules(new { required = "true" }).Add();

                        

                        col.Field(p => p.DecisionTypeName).EditType("dropdownedit").Width("180").Add();

                        col.Field(p => p.JRCDate).Format("yMd").EditType("datepickeredit").Width("175").Add();  //Date

                        col.Field(p => p.PriorityName).EditType("dropdownedit").Width("175").ValidationRules(new { required = "true" }).Add();

                        col.Field(p => p.CompletionDate).Format("yMd").EditType("datepickeredit").Width("200").Visible(false).Add();

                        col.Field(p => p.CreatedOn).Format("yMd").Width("200").Visible(false).Add();

                        col.Field(p => p.CreatedBy).Width("200").Visible(false).Add();

                        col.Field(p => p.ModifiedOn).Format("yMd").Width("200").Visible(false).Add();

                        col.Field(p => p.ModifiedBy).Width("180").Visible(false).Add();

                        col.Field(p => p.KSNLink).Width("180").Visible(false).Add();

                        //col.Field(p => p.ProjSched).Width("180").Visible(false).Add();

                        //col.Field(p => p.Budget).Width("100").Add();

                    })

                    //.Load("load")

                    //.ActionBegin("actionBegin")

                    //.BatchAdd("insert")

                    //.CellSave("insert")

                    

                    .ActionComplete("actionComplete")

                    .AllowPaging()

                    .QueryCellInfo("querycellinfo")

                    //.TextWrapSettings(text => { text.WrapMode(Syncfusion.EJ2.Grids.WrapMode.Header); })

                    .PageSettings(page => page.PageCount(5))

                    .EditSettings(edit => { edit.AllowAdding(true).AllowEditing(true).AllowDeleting(true).ShowDeleteConfirmDialog(true).Mode(Syncfusion.EJ2.Grids.EditMode.Normal); })

                    .Toolbar(new List<string>() { "Add", "Edit", "Delete", "Update", "Cancel", "ColumnChooser", "ExcelExport", "Search" })

                    .PageSettings(page =>

                    {

                        //page.PageSizes(true);

                        page.PageSize(25);

                        //page.PageSizes(new List<int>() { 10, 25, 50, 100 });

                        page.PageSizes(new List<string>() { "10", "25", "50", "100", "All" });

                        //page.PageSizes(new List<string>() { "All" });

                    })

                    //.Created("onGridCreate")

                    //.RowSelected("rowSelected")

                    //.Load("load")

                    //.ActionComplete("complete")

                    //.ActionBegin("Begin")

                    //.Height("600")

                    //.EnableVirtualization(true)

                    //.SearchSettings()

                    .Render()

        )

    </div>

</div>

Also, do I need a separate table in my db?

Any help would be appreciated. Thank you. 

[Thavasianand Sankaranarayanan]

Hi Hatem, 

Thanks for contacting Syncfusion support. 

From your query we found that you want to store and retrieve the Grid state in DB. To store the Grid state, we suggest you to use the ajax to send the Grid state to server and store it in your DB. You can use Grid getPersistData method to collect the current persist data in Grid.  

For the retrieve action you need to collect the stored Grid state from DB and provide it to Grid through the setProperties method. Please refer the below code snippet, 

Store button click: 

 

<script type="text/javascript"> 

    function store(e) {  // button click 

        var grid = document.getElementById("Grid").ej2_instances[0]; 

        var persistData = JSON.stringify({ persistData: grid.getPersistData() }); // Grid persistData 

        var ajax = new ej.base.Ajax({ // used our ajax to send the stored persistData to server 

            url: "/Home/StorePersistData", 

            type: "POST", 

            contentType: "application/json; charset=utf-8", 

            datatype: "json", 

            data: persistData  

        }); 

        ajax.send(); 

    } 

 

    function reset(e) { // button click 

        var ajax = new ej.base.Ajax({ // used our ajax to send the retrive the persistData from server 

            url: "/Home/restore", 

            type: "POST", 

            contentType: "application/json; charset=utf-8" 

        }); 

        ajax.send(); 

        ajax.onSuccess = function (result) { 

            var grid = document.getElementById("Grid").ej2_instances[0]; 

            var state = JSON.parse(result); 

            grid.setProperties({ // provide the retrived state to Grid through the setProperties method 

                filterSettings: state.filterSettings, 

                sortSettings: { columns: state.sortSettings.columns } // provide the retrieved values to GRid 

            }, false); 

           grid.setProperties({ 

                columns: state.columns 

            }) 

        } 

    } 

</script> 

Controller: 

 

public void StorePersistData(string persistData) 

        { 

            gridState = persistData; 

 

            // please do the DB save action here 

 

        } 

        public string restore() 

        { 

           // please do the retrieve action here 

 

            return gridState; 

        } 

Note: In this code we have used local variable to save and retrieve the state in server for the sample purpose. Please modified this codes with your requirement. 

We have prepared the sample with this requirement and you can find that sample from the below link, 

Sample: https://www.syncfusion.com/downloads/support/directtrac/general/ze/EJ2Grid-214726025.zip 

Regards, 

Thavasianand S. 

[Hatem Muhsen]

Thank you for getting back to me. This solution worked but with a few minor bugs. I have a Template column with "View" links that take users to the record's detail page. The "View" link disappears when the user clicks the Apply State button (in green). I have attached two pictures, one with the original view and the other view when the user reverts back to the saved state. The other issue is that the column names return as their database names, not the display names I want them to be (refer to both screen shots).

Here is my razor code:

 @(Html.EJS().Grid<ProjectMvcModel>("Grid")

                .DataSource(DataManager => { DataManager.Url("/Proj/UrlDataSource").CrudUrl("/Proj/CrudUpdate").Adaptor("UrlAdaptor"); })

                //.DataSource(DataManager => { DataManager.Url("/PMRS/UrlDataSource").InsertUrl("/PMRS/Insert").Adaptor("UrlAdaptor"); })

                .Width("100%")

                .AllowResizing(true)

                .AllowFiltering()

                .FilterSettings(filter => filter.Type(Syncfusion.EJ2.Grids.FilterType.Excel))

                .AllowSelection()

                .AllowGrouping()

                //.AllowMultiSorting()

                //.AllowTextWrap()

                .AllowSorting(true)

                .AllowReordering()

                .ShowColumnChooser(true)

                .ShowColumnMenu(true)

                .AllowExcelExport(true)

                .EnablePersistence(true)

                .ToolbarClick("toolbarClick")

                .GroupSettings(group => { group.ShowDropArea(false).ShowUngroupButton(true).ShowGroupedColumn(true).ShowToggleButton(true); })

                    //.ActionBegin("onActionBegin")

                    .Columns(col =>

                    {

                        col.Field(p => p.Id).Width("60").IsPrimaryKey(true).IsIdentity(true).Type("number").Width("120").Add();

                        col.HeaderText("Project Details").Visible(true).Template(" <a rel='nofollow' href='ProjectDetailsMaster?id=${Id} '>View</a> ").Width("140").AllowEditing(false).Add();

                        //col.Field(p => p.Id).Width("75").IsPrimaryKey(true).Type("number").Add();

                        col.Field(p => p.ProjName).AllowSorting(true).Width("175").Visible(false).Add();

                        col.Field(p => p.GroupName).EditType("dropdownedit").Width("175").ValidationRules(new { required = "true" }).Add();

                        col.Field(p => p.ProjDesc).ValidationRules(new { required = "true" }).Width("325").Add(); //allow editing(true)

                        col.Field(p => p.PortLead).ValidationRules(new { required = "true" })/*.EditType("dropdownedit")*/.Width("180").Add();

                        col.Field(p => p.ProjLead).ValidationRules(new { required = "true" })/*.EditType("dropdownedit")*/.Width("180").Add();

                        col.Field(p => p.ProgramName).ValidationRules(new { required = "true" }).EditType("dropdownedit").Width("180").Add();

                        col.Field(p => p.AssignedDate).ValidationRules(new { required = "true" }).Format("yMd").EditType("datepickeredit").Width("175").Add();  //Date

                        col.Field(p => p.StatusName)/*.Template("#statusTemplate")*/.EditType("dropdownedit").Width("175")/*.Template("#statusTemplate")*/.ValidationRules(new { required = "true" }).Add();

                        col.Field(p => p.DecisionTypeName).EditType("dropdownedit").Width("180").Add();

                        col.Field(p => p.JRCDate).Format("yMd").EditType("datepickeredit").Width("175").Add();  //Date

                        col.Field(p => p.PriorityName).EditType("dropdownedit").Width("175").ValidationRules(new { required = "true" }).Add();

                        col.Field(p => p.CompletionDate).Format("yMd").EditType("datepickeredit").Width("200").Visible(false).Add();

                        col.Field(p => p.CreatedOn).Format("yMd").Width("200").Visible(false).Add();

                        col.Field(p => p.CreatedBy).Width("200").Visible(false).Add();

                        col.Field(p => p.ModifiedOn).Format("yMd").Width("200").Visible(false).Add();

                        col.Field(p => p.ModifiedBy).Width("180").Visible(false).Add();

                        col.Field(p => p.KSNLink).Width("180").Visible(false).Add();

                        //col.Field(p => p.ProjSched).Width("180").Visible(false).Add();

                        //col.Field(p => p.Budget).Width("100").Add();

                    })

                    //.Load("load")

                    //.ActionBegin("actionBegin")

                    //.BatchAdd("insert")

                    //.CellSave("insert")

                    //.RowSelecting("rowSelecting")

                    .ActionComplete("actionComplete")

                    .AllowPaging()

                    //.QueryCellInfo("querycellinfo")

                    //.TextWrapSettings(text => { text.WrapMode(Syncfusion.EJ2.Grids.WrapMode.Header); })

                    .PageSettings(page => page.PageCount(5))

                    .EditSettings(edit => { edit.AllowAdding(true).AllowEditing(true).AllowDeleting(true).ShowDeleteConfirmDialog(true).Mode(Syncfusion.EJ2.Grids.EditMode.Normal); })

                    .Toolbar(new List<string>() { "Add", "Edit", "Delete", "Update", "Cancel", "ColumnChooser", "ExcelExport", "Search" })

                    .PageSettings(page =>

                    {

                        //page.PageSizes(true);

                        page.PageSize(25);

                        //page.PageSizes(new List<int>() { 10, 25, 50, 100 });

                        page.PageSizes(new List<string>() { "10", "25", "50", "100", "All" });

                        //page.PageSizes(new List<string>() { "All" });

                    })

                    //.Created("onGridCreate")

                    //.RowSelected("rowSelected")

                    //.Load("load")

                    //.ActionComplete("complete")

                    //.ActionBegin("Begin")

                    //.Height("600")

                    //.EnableVirtualization(true)

                    //.SearchSettings()

                    .Render()

        )

Attachment: Screenshots_38449f7.zip

[Thavasianand Sankaranarayanan]

Hi Hatem, 

Sorry for the inconvenience caused. 

To resolve this issue, we suggest you to call the Grid refreshHeader method instead of changing the columns through the setProperties. Please remove below yellow highlighted code from your project and call the refreshHeader method, 

ajax.onSuccess = function (result) { 

            var grid = document.getElementById("Grid").ej2_instances[0]; 

            var state = JSON.parse(result); 

            grid.setProperties({ // provide the retrived state to Grid through the setProperties method 

                filterSettings: state.filterSettings, 

                sortSettings: { columns: state.sortSettings.columns } 

            }, false); 

            grid.refreshHeader();// please add this code 

 

            grid.setProperties({// please remove this codes 

                columns: state.columns 

            })  

        } 

Regards, 

Thavasianand S. 

[Hatem Muhsen]

This worked perfectly, thank you. Anyway I can also save/retrieve the user's page count settings, column visibility settings, and column order? In other words, exactly the way they have saved the view. 

Best Regards,

[Thavasianand Sankaranarayanan]

Hi Hatem, 

 

Thanks for your update. 

 

We are happy that the problem has been resolved at your end. 

 

Regards, 

Thavasianand S.  

[Hatem Muhsen]

Hi,

How do I also save and reset the column positioning? So if a user moves columns around, they can also save that into the grid state.

Thanks you,

[Jacques Okes]

Hi there,

I am also trying to store the grid layout based on the code samples you laid out here.

I can only store the grid ordering, if I add a filter, or a grouping and trying to reload to stored string, there are a multitude a javascript errors saying the type cannot be found.

I am storing the exact string into the database based on the ordering, grouping and filtering, but it wont load the string without errors. 

I added a "Stored string" for the purposes of this example as follows:

 public string customString()

        {

            string customFiltering = @"{""pageSettings"":{""currentPage"":1,""pageCount"":8,""pageSize"":12,""totalRecordsCount"":45},""sortSettings"":{""columns"":[{""field"":""EmployeeID"",""direction"":""Ascending""}]},""filterSettings"":{""immediateModeDelay"":1500,""type"":""Menu"",""columns"":[]},""groupSettings"":{""columns"":[""EmployeeID""],""showToggleButton"":false,""disablePageWiseAggregates"":false,""showDropArea"":true,""showGroupedColumn"":false,""showUngroupButton"":true},""columns"":[{""allowSorting"":true,""allowResizing"":true,""allowFiltering"":true,""allowGrouping"":true,""allowReordering"":true,""showColumnMenu"":true,""enableGroupByFormat"":false,""allowEditing"":true,""filter"":{},""showInColumnChooser"":true,""allowSearching"":true,""sortDirection"":""Descending"",""field"":""OrderID"",""isPrimaryKey"":true,""width"":""100"",""uid"":""grid-column0"",""foreignKeyField"":""OrderID"",""visible"":true,""index"":0,""type"":""number"",""backupHeader"":""Order ID""},{""allowSorting"":true,""allowResizing"":true,""allowFiltering"":true,""allowGrouping"":true,""allowReordering"":true,""showColumnMenu"":true,""enableGroupByFormat"":false,""allowEditing"":true,""filter"":{},""showInColumnChooser"":true,""allowSearching"":true,""sortDirection"":""Ascending"",""field"":""EmployeeID"",""width"":""120"",""uid"":""grid-column1"",""foreignKeyField"":""EmployeeID"",""visible"":false,""index"":1,""type"":""number"",""backupHeader"":""Employee ID""},{""allowSorting"":true,""allowResizing"":true,""allowFiltering"":true,""allowGrouping"":true,""allowReordering"":true,""showColumnMenu"":true,""enableGroupByFormat"":false,""allowEditing"":true,""filter"":{},""showInColumnChooser"":true,""allowSearching"":true,""sortDirection"":""Descending"",""field"":""OrderDate"",""format"":""yMd"",""width"":""120"",""uid"":""grid-column2"",""foreignKeyField"":""OrderDate"",""visible"":true,""index"":2,""type"":""date"",""backupHeader"":""Order Date""}],""searchSettings"":{""key"":"""",""fields"":[]},""selectedRowIndex"":-1}";

            return customFiltering;

        }

Then I added a new button for custom layout read and the button calls this event:

function loadState() {

        var ajax = new ej.base.Ajax({ // used our ajax to send the retrive the persistData from server

            url: "/Home/customString",

            type: "POST",

            contentType: "application/json; charset=utf-8"

        });

        ajax.send();

        ajax.onSuccess = function (result) {

            var grid = document.getElementById("Grid").ej2_instances[0];

            var state = JSON.parse(result);

            for (var i = 0; i < state.columns.length; i++) {

                state.columns[i].headerText = state.columns[i].backupHeader;

            }

            grid.setProperties({ // provide the retrived state to Grid through the setProperties method

                filterSettings: state.filterSettings,

                sortSettings: { columns: state.sortSettings.columns }

            }, false);

            grid.setProperties({

                columns: state.columns

            })

        }

    }

What needs to be added/edited to be able to correctly save the grid layout 100% - each user needs to save their layout and reload it on databound as there are quite a few columns and each user needs to see it seperately in their own ways.

[Rajapandi Ravi]

Hi Jacques, 

Thanks for the update 

Query#: I can only store the grid ordering, if I add a filter, or a grouping and trying to reload to stored string, there are a multitude a javascript errors saying the type cannot be found. 

We have analyzed your query and based on your requirement we have prepared a sample and tried to reproduce the issue. But it works fine from our end. Please refer the below sample and video demo for more information. 

Sample: https://www.syncfusion.com/downloads/support/forum/145592/ze/145592791922005.zip 

Video demo: https://www.syncfusion.com/downloads/support/forum/145592/ze/145592-1531796365.zip 

If you still face the issue, please share the below details that will be helpful for us to provide better solution. 

1)     If you still face any issue, Please share any issue reproducible sample or try to reproduce the issue with our above attached sample. 

2)     If possible please share the issue scenario in video demonstration.  

Regards, 

Rajapandi R 

[Emiliano]

This sample have a problem.

After i press the 'Store' button and 'Reset' button one time each one and in this order, anything i do to the Grid, filter, order, group or even press the Reset button again, the grid hangs.

One thing i noticed, it that the header text, changes when i press the Reset button. How to save the Header text to persist data?

[Rajapandi Ravi]

Hi Hatem, 

Based on your reported scenario we have checked our provided sample from our end and tried to reproduce the issue. But your reported problem was not occurs from our end. For your reference we have attached a video demo. Please refer the below video demo for more information.  

Video demo: https://www.syncfusion.com/downloads/support/forum/145592/ze/145592demo1643497768.zip 

Query#: One thing i noticed, it that the header text, changes when i press the Reset button. 

By default in EJ2 Grid the persist data does not contain/store the properties like, column template, column formatter, header text since these values can be mapped as id or method that is defined in the application level or even modified and hence cannot be maintained. 

Regards,
Rajapandi R

[Emiliano]

I've got to say that your support it's very good. You try to understand the user problems and even send videos!

I like that! thanks for your support.

The problem is still there. I dont know what happened before, but i think that when the persistence data breaks, the only way to recover the grid, is to change the grid name.

Because the Reset button it's not a reset button, but a restore button which makes an ajax request to get the data from server.

In the video i try to do a lot of random things to break the grid, and then finally it stops working. The only way to recover the grid, it's changing the name. that's why the grid in the video, have the id "Grid5" name. The "previous numbers names" was other attempts to break the grid.

Thanks.

Attachment: grid_error_d13ce28d.zip

[Rajapandi Ravi]

Hi Emiliano, 

Thanks for the update 

Based on your reported scenario we have checked our provided sample from our end and tried to reproduce the issue. But your reported problem was not occurs from our end.  

In this below sample we have backup the headerText when you store the Grid state. And then set the same headerText when you reset the state. Please refer the below code example, sample and video demo for more information. 

[index.cshtml]  

 

<button id="store" class="e-btn e-warning" onclick="store()">Store</button> 

<button id="reset" class="e-btn e-success" onclick="reset()">Reset</button> 

<button id="hide" class="e-btn e-success" onclick="hide()">Hide Column</button> 

 

@Html.EJS().Grid("Grid").DataSource((IEnumerable<object>)ViewBag.datasource).AllowReordering(true).AllowSorting(true).AllowFiltering(true).AllowPaging(true).Columns(col => 

{ 

    col.Field("OrderID").HeaderText("Order ID").IsPrimaryKey(true).Width("100").Add(); 

    col.Field("EmployeeID").HeaderText("Employee ID").Width("120").Add(); 

    col.Field("CustomerID").HeaderText("Customer Name").Width("150").Add(); 

    col.Field("OrderDate").HeaderText("Order Date").Width("120").Format("yMd").Add(); 

    col.Field("ShipCity").HeaderText("Ship City").EditType("dropdownedit").Width("150").Add(); 

 

}).FilterSettings(filter => { filter.Type(Syncfusion.EJ2.Grids.FilterType.Menu); }).Render() 

 

<script type="text/javascript"> 

    function store(e) {  // button click 

        var grid = document.getElementById("Grid").ej2_instances[0]; 

        for (var i = 0; i < grid.columns.length; i++) { 

            grid.columns[i].backupHeader = grid.columns[i].headerText;    //take headerText duplicate to store in another property  

        } 

       var persistData = JSON.stringify({ persistData: grid.getPersistData() }); // Grid persistData 

        var ajax = new ej.base.Ajax({ // used our ajax to send the stored persistData to server 

            url: "/Home/StorePersistData", 

            type: "POST", 

            contentType: "application/json; charset=utf-8", 

            datatype: "json", 

            data: persistData  

        }); 

        ajax.send(); 

    } 

    function reset(e) { // button click 

        var ajax = new ej.base.Ajax({ // used our ajax to send the retrive the persistData from server 

            url: "/Home/restore", 

            type: "POST", 

            contentType: "application/json; charset=utf-8" 

        }); 

        ajax.send(); 

        ajax.onSuccess = function (result) { 

            var grid = document.getElementById("Grid").ej2_instances[0]; 

            var state = JSON.parse(result);  

           for (var i = 0; i < state.columns.length; i++) { 

                state.columns[i].headerText = state.columns[i].backupHeader; //restore headerText 

            } 

           grid.setProperties({ // provide the retrived state to Grid through the setProperties method 

                filterSettings: state.filterSettings, 

                sortSettings: { columns: state.sortSettings.columns } 

            }, false); 

            grid.setProperties({ 

                columns: state.columns 

            })  

        } 

    }</script> 

Sample: https://www.syncfusion.com/downloads/support/forum/145592/ze/145592-1346906362.zip 

Video Demo: https://www.syncfusion.com/downloads/support/forum/145592/ze/145592demo-173916920.zip 

Regards, 

Rajapandi R