CODEWITHSUNDEEP
javascriptgridviewkendo-uiforeign-keys
user2012783
user2012783

Reputation: 193

kendoui: How to display foreign key from remote datasource in grid

i have a kendoui grid which list claims. one of the columns is lenders which is a foreign key reference to the lenders table. what i want is to be able to display the lender name in the grid instead of its id reference.

ive setup the lenders datasource as follows

var dsLenders = new kendo.data.DataSource({
    transport: {
        read: {
          url: "../data/lenders/",
          dataType: "jsonp"
      },
      parameterMap: function(options, operation) {
          if (operation === "read") {
              return options;
          }
      }
    }
});

and the grid looks like this

 $("#gridClaims").kendoGrid({
      dataSource: claimData,
      autoSync:true,
      batch: true,
      pageable: {
          refresh: true,
          pageSizes: true
      },
      filterable: true,
      sortable: true,
      selectable: "true",
      editable: {
          mode: "popup",
          confirmation: "Are you sure you want to delete this record?",
          template: $("#claimFormPopup").html()
      },
      navigable: true,  // enables keyboard navigation in the grid
      toolbar: ["create"],  // adds insert buttons
      columns: [
          { field:"id_clm", title:"Ref", width: "80px;" },
          { field:"status_clm", title:"Status", width: "80px;" },
          { field:"idldr_clm", title:"Lender", values: dsLenders },
          { field:"type_clm", title:"Claim Type"},
          { field:"value_clm", title:"Value", width: "80px;", format:"{0:c2}", attributes:{style:"text-align:right;"}},
          { field:"created", title:"Created", width: "80px;", format: "{0:dd/MM/yyyy}"},
          { field:"updated", title:"Updated", width: "80px;", format: "{0:dd/MM/yyyy}"},
          { field:"user", title:"User" , width: "100px;"},
          { command: [
              {text: "Details", className: "claim-details"},
              "destroy"
            ],
            title: " ",
            width: "160px"
          }
      ]
  });

however its still displaying the id in the lenders column. Ive tried creating a local datasource and that works fine so i now is something to do with me using a remote datasource.

any help would be great

thanks

Upvotes: 4

Views: 7311

Answers (3)

lee-m
lee-m

Reputation: 2267

For those stumbling across this now, this functionality is supported:

https://demos.telerik.com/aspnet-mvc/grid/foreignkeycolumnbinding

Upvotes: 0

Siri How
Siri How

Reputation: 143

Although this post past 2 years, I still share my solution

1) Assume the api url (http://localhost/api/term) will return:

{
    "odata.metadata":"http://localhost/api/$metadata#term","value":[
        {
            "value":2,"text":"2016-2020"
        },{
            "value":1,"text":"2012-2016"
        }
    ]
}

please note that the attribute name must be "text" and "value"

2) show term name (text) from the foreign table instead of term_id (value). See the grid column "term_id", the dropdownlist will be created if added "values: data_term"

<script>
    $.when($.getJSON("http://localhost/api/term")).then(function () {
        bind_grid(arguments[0].value);
    });

    function bind_grid(data_term) {
        $("#grid").kendoGrid({
            dataSource: ds_proposer,
            filterable: true,
            sortable: true,
            pageable: true,
            selectable: "row",
            columns: [
                { field: "user_type", title: "User type" },
                { field: "user_name", title: "User name" },
                { field: "term_id", title: "Term", values: data_term }
            ],
            editable: {
                mode: "popup",
            }
        });
    }
</script>

Upvotes: 0

Aleks
Aleks

Reputation: 1689

Short answer is that you can't. Not directly anyway. See here and here.

You can (as the response in the above linked post mentions) pre-load the data into a var, which can then be used as data for the column definition.

I use something like this:-

function getLookupData(type, callback) {
    return $.ajax({
        dataType: 'json',
        url: '/lookup/' + type,
        success: function (data) {
            callback(data);
        }
    });
}

Which I then use like this:-

var countryLookupData;
getLookupData('country', function (data) { countryLookupData = data; });

I use it in a JQuery deferred to ensure that all my lookups are loaded before I bind to the grid:-

$.when(
    getLookupData('country', function (data) { countryLookupData = data; }),
    getLookupData('state', function (data) { stateLookupData = data; }),
    getLookupData('company', function (data) { companyLookupData = data; })
)
.then(function () {
    bindGrid();
}).fail(function () {
    alert('Error loading lookup data');
});

You can then use countryLookupData for your values.

You could also use a custom grid editor, however you'll probably find that you still need to load the data into a var (as opposed to using a datasource with a DropDownList) and ensure that the data is loaded before the grid, because you'll most likely need to have a lookup for a column template so that you're newly selected value is displayed in the grid.

I couldn't quite get ForeignKey working in any useful way, so I ended up using custom editors as you have much more control over them.

One more gotcha: make sure you have loaded your lookup data BEFORE you define the column. I was using a column array that was defined in a variable I was then attaching to the grid definition... even if the lookup data is loaded before you use the grid, if it's defined after the column definition it will not work.

Upvotes: 8

Related Questions