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

Question

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

Answer 1

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.