Grid with mix of static and dynamic columns -- need sorting and filtering

[Kevin Finke]

I have a Grid with a mix of static and dynamic columns.  The static columns have a lot going on -- the user can choose groups of columns to display.  There's formatting, commands, etc. I retrieve the main list of models from the database based on some filter criteria from the user.  Based on the user selection for displayed columns, I augment the data returned with additional data and fill out more data on the models. 

However, the tricky part is that users can customize additional fields they want to see in this table.  These fields are dynamic.  Our application is for building forms, so they define their own fields.  Let's say a customer defines 200 fields they want to have on their form.  Of those 200 fields, they wish to display 4 of them in the grid I'm struggling with.  These fields are stored in a table "flat",so like:

CustomerID       FieldID    FieldName

1                        1              "age"

The answers to these are stored like:

FieldID      UserID     Value:

1                1              25

 

I retrieve the data for these dynamic fields with a combination of Linq and SQL.  I pivot the data, so get:

 

UserID        1              3                4         6

1                25          "foo"            "bar"      3/4/2010

 

Currently, I'm taking these results and I'm adding it to a Dictionary<> per Model.  Then I dynamically add them to my Kendo MVC grid:

             
              foreach (var key in Model.AdminColumns.Keys)
              {
                  c.Template(@<text>@item. </text>).ClientTemplate(String.Format("#=CustomFields['{0}']#", key.ToUpper())).HeaderTemplate(Model.AdminColumns[key]);
                  }
              })

 

I can display the data just fine.  However, I really need to add sorting and filtering to these columns.  I understand that's not supported the way I'm doing it.  The problem is, I can't seem to find a solution that will give me what I need.  I can't change the nature of the data obviously.  But, I'm willing to take a new approach.  However, I keep running into road blocks.  I tried using the dynamic keyword and building a model, but the grid helper didn't like that.  I was thinking of going down the path of returning a json model with the dynamic fields merged in as first class members of each dataitem, but then i'm not sure how to define the grid.

 

Do you have any suggestions?  My complete grid code is shown below.  

 

@(Html.Kendo().Grid(Model.Donors).Name("DonorGrid")
      .DataSource(ds =>
          ds.Ajax()
              .ServerOperation(false)
              .Model(m => m.Id(x => x.DonorId))
              .Read(r => r
                .Action("GetDonors", "Donor")
                .Type(HttpVerbs.Post)
                .Data("getAdditionalData"))
      .PageSize(Model.RowsPerPage)

      )
      //.ToolBar(t => t.Excel())
      .Excel(e => e.FileName("DonorsExport.xlsx"))
      .Columns(c =>
      {
      c.Group(g => g.Title(" ").CenterGroupHeader()
          .Columns(cm =>
          {
                  //cm.Select().Width(30);
                  cm.Bound(m => m.DonorId).ClientTemplate("<a href=\"/App/Donor.aspx/EditDonor?donorId=#=DonorId# \">Edit</a>").HeaderTemplate("Edit").Width(75).Filterable(false).Sortable(false).Locked(true).CenterCell();
              cm.Bound(m => m.AdminThumbnail).ClientTemplate("<img src=\"#=AdminThumbnail#\" width=50px;").HeaderTemplate("").Visible(Model.Columns.Thumbnail).Width(75).Filterable(false).Sortable(false).Locked(true).CenterCell();
              cm.Bound(m => m.Email).Visible(Model.Columns.Email);
              cm.Bound(m => m.Unsubscribed).ClientTemplate("#if(Unsubscribed){# <span class='red glyphicon glyphicon-flag'></span> #}#").Visible(Model.Columns.Email).Width(80).CenterCell();

          }));
      c.Group(g => g.Title("Current Status").CenterGroupHeader()
          .Columns(cs =>
          {
              cs.Bound(m => m.CompositeStatusText).HeaderTemplate("Status").Visible(Model.Columns.Status);
          }));
      c.Group(g => g.Title("Participation").CenterGroupHeader()
          .Columns(cg =>
          {
              cg.Bound(m => m.ParticipationStatusText).HeaderTemplate("Participation").Visible(Model.Columns.Participation);
              cg.Bound(m => m.ParticipationStatusDateTimeText).HeaderTemplate("Date").Format("{0:M/d/yy}").Visible(Model.Columns.Participation);
              cg.Bound(x => x.LastLoginDays).HeaderTemplate("Last Login Date").Visible(Model.Columns.Participation);

          }));
      c.Bound(m => m.SharedStatus).HeaderTemplate("Shared").Visible(Model.Columns.Shared);
      c.Group(g => g.Title("App Dates").CenterGroupHeader()
          .Columns(ca =>
          {
              ca.Bound(m => m.PreScreenDate).HeaderTemplate("Pre-screen").Format("{0:M/d/yy}").Visible(Model.Columns.AppDates);
              ca.Bound(m => m.OverallAppText).HeaderTemplate("Overall App").Format("{0:M/d/yy}").Visible(Model.Columns.AppDates);
              ca.Bound(m => m.PercentComplete).HeaderTemplate("% Complete").Visible(Model.Columns.AppDates).Format("{0:P}");
              ca.Bound(m => m.AppUpdateDays).HeaderTemplate("App Update").Visible(Model.Columns.AppDates);

          }));
      c.Group(g => g.Title("Eligibility").CenterGroupHeader()
          .Columns(ce =>
          {
              ce.Bound(m => m.EligibilityPrescreen).ClientTemplate(
                  "<div>#= EligibilityPrescreen#<br/>" +
                  "#=Eligibility.ScreeningEligible#, #=Eligibility.ScreeningQuestionable#, #=Eligibility.ScreeningIneligible#" +
                  "</div>"
                  ).HeaderTemplate("Pre-screen").Visible(Model.Columns.Eligibility);
              ce.Bound(m => m.EligibilityOverall).ClientTemplate(
                  "<div>#= EligibilityOverall#<br/>" +
                  "#=Eligibility.OverallEligible#, #=Eligibility.OverallQuestionable#, #=Eligibility.OverallIneligible#" +
                  "</div>"
                  ).HeaderTemplate("Overall").Visible(Model.Columns.Eligibility);

          }));
      c.Group(g => g.Title("Frozen").CenterGroupHeader()
          .Columns(cf =>
          {
              cf.Bound(m => m.FrozenEnabled).ClientTemplate(
                  "<table><thead><td>A</td><td>S</td><td>I</td><td>N</td></thead><tr>" +
                  "<td>#=FooCount#</td>" +
                  "<td>#=SelectedCount#</td>" +
                  "<td>#=NotSelectedCount#</td>" +
                  "<td>#=NetworkCount#</td>" +
                  "</tr></table>"
                  ).HeaderTemplate("Frozen Enabled").Visible(Model.Columns.FrozenEnabled);
          }));


      c.Bound(m => m.AlternateId).HeaderTemplate("Reference #");
      foreach (var key in Model.AdminColumns.Keys)
      {
          c.Template(@<text>@item. </text>).ClientTemplate(String.Format("#=CustomFields['{0}']#", key.ToUpper())).HeaderTemplate(Model.AdminColumns[key]);
          }
      })

      .Filterable()
      .Pageable(p => p.AlwaysVisible(true).PageSizes(new[] { 25, 50, 100, 500, 1000 }))
      .Sortable()
      .Selectable(s => s.Mode(GridSelectionMode.Multiple).Type(GridSelectionType.Row).Enabled(true))
      .Resizable(r => r.Columns(true))
      .NoRecords("No donors were found matching this criteria."));

[Konstantin Dikov]

Hello Kevin,

For the scenario that you have I could suggest one of the following two options:

• https://www.telerik.com/blogs/dynamic-data-in-the-kendo-ui-grid 
• https://docs.telerik.com/aspnet-mvc/helpers/grid/how-to/Binding/grid-bind-to-datatable

The first approach initialized Kendo UI Grid on client-side, which gives more flexibility when it comes to dynamic data. Note that the exact structure of the Grid is retrieved with AJAX request.

The second solution is to bind the Grid to DataTable, which resolves the main problem with the dynamic data, which is the field names and their type. With a DataTable you can create the columns dynamically with specific names and data types and use that DataTable for configuring the Grid.

Hope this helps.


Regards,
Konstantin Dikov
Progress Telerik