DropDown editors, with large lookup tables, minimized bandwidth: set value in multiple columns off one DDL

[Shea]

I am trying to have a editable grid, minimizing network traffic. The grid is only editied 1/20 page loads.
My data model is like this:
Items: ItemdID, ItemName
ItemSerials: ItemSerialID, ItemSerial, ItemID
Units: UnitID, ItemID, Unit
ItemRental: ItemRentalID, ItemID, ItemSerialID, UnitID, Quantity

There may be multiples serials, and Units for each Item.
The data comes from the backend as a Join: with the fields from all 4 tables.
I would like the grid to Show:
ItemName, SerialNumber, Units, Quantity
But need to save an ItemRental row.

Initially I had the table with an ItemID column, using a template which called a lookup function (getName(itemid)) which displayed the ItemName. In order for this to work, I had to pull the entire Items table into memory, so that the lookup function could use it.

The ItemSerials table is worse, as it has 100,000 serrals, but only a handful are applicable to each item. So in order to show SerialNumber, but have ItemSerialID as my value, I have to pull the whole table into memory too. Yes, for loading the drop-down-list I can use the selected item, and filter the fetched serials based on that, but in order to show SerialNumber, but have ItemSerialID  as my value field using template which alls function like the item name (getSerial(serialid), I need to have all in memory.

I would like to handle this by having all fields as part of my data model, then  only showing four mentioned, and hiding the Id columns. My drop downlist for ItemName woulld be on the item name field would then transport.read in all the items on creation of the first editor (dd list). The DDL would read in the whole Items table, be the editor for the ItemName column, but I would also need it to set the value in the ItemID column:  
I can get the itemID in my 'change' function for the DDL,  like this: e.sender.options.dataSource[e.sender.selectedIndex].ItemID, but then what do I do with it? How do I set the value of the hidden ItemID column?

Then another tricky part is when the Item is selected, I then need to cascade the DDL for the SerialNumber column based on ItemID, not on ItemName....

Is there a better way to solve this problem, and avoid pulling unecessary data into memory? I need to show employer bandwidth test in order to buy.

Thanks,
~S

[Shea]

Sovled a couple problems:

Turns out in your template you can call other fields. So in the template for my ItemID column I simply have: '#= ItemName #'.  My dropdown editor changes the ItemID, so on the change function, I have manually set ItemName, so display template works.

columns: [

    { field: "ItemID", title: "Equipment", width: 260, editor: itemDropDownEditor, template: '#= ItemName #' },

    { field: "ItemSerialID", title: "Unit #", width: 200, editor: serialDropDownEditor, template: '#= SerialNumber #' },

    { field: "Quantity", title: "QTY", width: 60 }

],

function itemDropDownEditor(container, options) {

    $('<input id="ItemDDL" required data-text-field="ItemName" data-value-field="ItemID" data-bind="value:' + options.field + '"/>')

        .appendTo(container)

        .kendoDropDownList({

            autoBind: false,

            dataSource: items_src,

            change: itemChanged

        });

}

function itemChanged(e) {

    var item_id = e.sender.dataItem().ItemID;

    var item_name = e.sender.dataItem().ItemName;

 

    var data = $('#equipment-data').data().kendoGrid.dataSource.data();

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

        if (data[i].ItemID == item_id ) {

            data[i].set('ItemName', item_name);

            data[i].set('SerialNumber', '--select--');

            break;

        }

    }

}

My problem is now getting the list of serials based on the ItemID. if serials is a kendo datasource, then at the time that transport.read is called, I need to pass an itemID as a parameter, so the server, can only return relevant serials. The serials then must know the row that it is being used for...

I have to think that this is a fairly common problem?

[Petur Subev]

Hello Shea,

Basically to send additional parameters to the server you should use the data function of the dataSource.read cofngiration. Check the following example from the documentation:

transport: {
    read: {
        data: function() {
            return {
                id: 42,
                name: "John Doe"
            };
        }
    }
}

In your case the dataSource should be the items_src variable.

I hope this helps.

Kind regards,
Petur Subev
the Telerik team

[Shea]

Petur,

My problem is that in that the transport.read.data function will be called when the editor for the SerialNumber column is created, and in that sample funciton I need to be able to get the value of the ItemID colomn.

+--------+--------------+--
| ItemID | SerialNumber |
+--------+--------------+--
|  123   | UDF-342345   |
+--------+--------------+--
|* 199   |  editor   V  |    <-------- This row!
+--------+--------------+--
| 234    |  X-02345     |
+--------+--------------+--
| 757    |  4353453456  |
+--------+--------------+--

In the above table, when I change ItemID to 199, I now need to only show serials that are valid for an ItemID of type 199 in the editor in the SerialNumber column. So I need: 

transport: {
    read: {
        data: function() {
            return {
                item_id: getValueFromItemIdColumnInMyRow();
            };
        }
    }
}

So the missing piece of the puzze is the function getValueFromItemIdColumnInMyRow:

function getValueFromItemIdColumnInMyRow() {
    //I am not sure how to write this function....
    //I know how to read data from the grid,
    //but how do I know which row to read?
}

Can you tell me how to know which row?

[Petur Subev]

Hello Shea,

You have the whole model available inside of the editor function.

So you can send it to the server through the data function.

consider the following code from the demo:

function categoryDropDownEditor(container, options) {
    $('<input required data-text-field="CategoryName" data-value-field="CategoryID" data-bind="value:' + options.field + '"/>')
        .appendTo(container)
        .kendoDropDownList({
            autoBind: false,
            dataSource: {
                type: "odata",
                transport: {
                    read: {
                        url: "http://demos.kendoui.com/service/Northwind.svc/Categories",
                        data: function () {
                            return { value: options.model.ProductID }
                        }
                    }
                }

            }
        });
}

I hope you got the idea.

Kind Regards,
Petur Subev
the Telerik team

[Shea]

Petur,

Thanks that works. The problem I was having, was that I was trying to define my DataSource outside of the editor.

I'll call this case closed. thanks.
~S