grid ComboBox editor issue

[Peter StoevKeymaster]

Hi David,

The data source does not matter. You can use that functionality with any of the supported data source types.

There are few key parts to know about the sample:

1. The displayed data is coming from 2 different data sources. The Employees source have fields for “First Name” and “Last Name”. We want to have a data field which merge these two data fields and we want that data field to be called “Name”. That’s implemented in the “beforeLoadComplete” callback function of the dataAdapter.

   var employeesSource =
             {
                 datatype: "xml",
                 datafields: [
                     { name: 'FirstName', type: 'string' },
                     { name: 'LastName', type: 'string' }
                 ],
                 root: "Employees",
                 record: "Employee",
                 id: 'EmployeeID',
                 url: "../sampledata/employees.xml",
                 async: false
             };
             var employeesAdapter = new $.jqx.dataAdapter(employeesSource, {
                autoBind: true,
                beforeLoadComplete: function (records) {
                    var data = new Array();
                    // update the loaded records. Dynamically add EmployeeName field. 
                    for (var i = 0; i < records.length; i++) {
                        var employee = records[i];
                        employee.EmployeeName = employee.FirstName + " " + employee.LastName;
                        data.push(employee);
                    }
                    return data;
                }
            });

2. In the Orders data source, we have an EmployeeID data field. We have the same EmployeeID in the Employees data source. We want however to display the Employee’s Name instead of its ID. For doing this, we create a datafield called: EmployeeID in the ordersSource object. We set the field’s displayname for the dataAdapter’s records collection to be “EmployeeName”. This will allow us to get the Employee’s name by using a code like that: adapter.records[0][“EmployeeName”];. The “source” field expects an array of records.

            // prepare the data
            var ordersSource =
            {
                datatype: "xml",
                datafields: [
                    // id - determines the id of a record in the foreign collection(employees.xml) which should match to the record's name in the source collection(orders.xml).
                    // text - determines the display field from the foreign collection.
                    // source - determines the foreign collection.
                    // displayname - the field's name in the adapter's records array.
                    // name - the field's name in the data source.
                    { name: 'EmployeeID', displayname: 'EmployeeName', map: 'm\\:properties>d\\:EmployeeID', text: 'EmployeeName', id: 'EmployeeID', source: employeesAdapter.records },
                    { name: 'EmployeeID', map: 'm\\:properties>d\\:EmployeeID' },
                    { name: 'ShippedDate', map: 'm\\:properties>d\\:ShippedDate', type: 'date' },
                    { name: 'Freight', map: 'm\\:properties>d\\:Freight', type: 'float' },
                    { name: 'ShipName', map: 'm\\:properties>d\\:ShipName' },
                    { name: 'ShipAddress', map: 'm\\:properties>d\\:ShipAddress' },
                    { name: 'ShipCity', map: 'm\\:properties>d\\:ShipCity' },
                    { name: 'ShipCountry', map: 'm\\:properties>d\\:ShipCountry' }
                ],
                root: "entry",
                record: "content",
                id: 'm\\:properties>d\\:OrderID',
                url: "../sampledata/orders.xml",
                pager: function (pagenum, pagesize, oldpagenum) {
                    // callback called when a page or page size is changed.
                }
            };
            var ordersAdapter = new $.jqx.dataAdapter(ordersSource);

3. When we create the Grid we want the first column to have Display and Value fields. In order to achieve that, we set the column’s “datafield” and “displayfield” properties. When the column’s value is changed, the Grid will update the record by using the “datafield” and its associated “displayfield”.

          var ordersAdapter = new $.jqx.dataAdapter(ordersSource);
            $("#jqxgrid").jqxGrid(
            {
                width: 670,
                source: ordersAdapter,
                theme: theme,
                selectionmode: 'singlecell',
                pageable: true,
                autoheight: true,
                editable: true,
                columns: [
                    { text: 'Employee Name', datafield: 'EmployeeID', displayfield: 'EmployeeName', columntype: 'dropdownlist', width: 150 },
                    { text: 'Ship City', datafield: 'ShipCity', width: 150},
                    { text: 'Ship Country', datafield: 'ShipCountry', width: 150 },
                    { text: 'Ship Name', datafield: 'ShipName'}
                ]
            });

Best Regards,
Peter Stoev

jQWidgets Team
http://www.jqwidgets.com

[DavidSimmonsParticipant]

Thanks for your patience, I have this working now… Because I have very large lookups, will this method work with columntype: ‘combobox’?

Here is my test code for create editor and company companyDataSource…

{ text: ‘Company’, datafield: ‘CompanyID’, displayfield: ‘Company’, width: 200, editable: true, filterable: true, filtertype: ‘textbox’, columntype: ‘combobox’,
createeditor: function (row, column, editor) {
editor.jqxComboBox({selectedIndex: 0, source: CompanyDataAdapter, autoComplete: true, autoOpen: false, dropDownHeight: 250, displayMember: “Company”, valueMember: “CompanyID”,
renderer: function (index, label, value) {
var item = CompanyDataAdapter.records[index];
if (item != null) {
var label = item.Company;
return label;
}
return “”;
},
renderSelectedItem: function(index, item) {
var item = CompanyDataAdapter.records[index];
if (item != null) {
var label = item.Company;
return label;
}
return “”;
},
search: function (searchString) {
CompanySearch = searchString;
CompanyDataAdapter.dataBind();
}
});
},
cellvaluechanging: function (row, column, columntype, oldvalue, newvalue) {
if (newvalue == “”) return oldvalue;
}

DataSource:*******
var CompanySearch=”;
var CompanyIndex=0;

var CompanySource = {
datatype: “json”,
datafields: [
{ name: ‘CompanyID’ },
{ name: ‘Company’ }
],
id: ‘CompanyID’,
url: ‘../Company/CompanyJSON.php’,
async: false,
data: {
featureClass: “P”,
style: “full”,
maxRows: 12
}
};

var CompanyDataAdapter = new $.jqx.dataAdapter(CompanySource, {
formatData: function (data) {
data.name_startsWith = CompanySearch;
return data;
}
});

[DavidSimmonsParticipant]

I am confused why there are two data fields with the same name..

{ name: ‘EmployeeID’, displayname: ‘EmployeeName’, map: ‘m\\:properties>d\\:EmployeeID’, text: ‘EmployeeName’, id: ‘EmployeeID’, source: employeesAdapter.records },
{ name: ‘EmployeeID’, map: ‘m\\:properties>d\\:EmployeeID’ },

Do one of these represent the OrdersDataSource and the other EmployeeDataSource?

In my design my columns from the two data sources will not have the same name. That is why I am asking.

[Peter StoevKeymaster]

Hi David,

Please, read the comments below where the meaning of each field is explained:

  datafields: [
                    // id - determines the id of a record in the foreign collection(employees.xml) which should match to the record's name in the source collection(orders.xml).
                    // text - determines the display field from the foreign collection.
                    // source - determines the foreign collection.
                    // displayname - the field's name in the adapter's records array.
                    // name - the field's name in the data source.
                    { name: 'EmployeeID', displayname: 'EmployeeName', map: 'm\\:properties>d\\:EmployeeID', text: 'EmployeeName', id: 'EmployeeID', source: employeesAdapter.records },
                    { name: 'EmployeeID', map: 'm\\:properties>d\\:EmployeeID' },
                    { name: 'ShippedDate', map: 'm\\:properties>d\\:ShippedDate', type: 'date' },
                    { name: 'Freight', map: 'm\\:properties>d\\:Freight', type: 'float' },
                    { name: 'ShipName', map: 'm\\:properties>d\\:ShipName' },
                    { name: 'ShipAddress', map: 'm\\:properties>d\\:ShipAddress' },
                    { name: 'ShipCity', map: 'm\\:properties>d\\:ShipCity' },
                    { name: 'ShipCountry', map: 'm\\:properties>d\\:ShipCountry' }
                ],

Best Regards,
Peter Stoev

jQWidgets Team
http://www.jqwidgets.com

[DavidSimmonsParticipant]

Ok, I think I have everything working. If I am understanding your example there seems to be design issue with the foreign Key lookup…

The problem is if you main datasource does not include every option from the lookup data source the drop down list only contains the items that exist in the main datasource, not the all items from the lookup data source.

What I am trying to do, which is common to most database web applications store the lookupID in the main datasource table and allow the user to select from a complete list of optional choices or that come from a lookupDatasource. This should be a ComboBox that includes values and names.

Proudct1
Proudct2
Proudct3
Proudct4

Because this is like and invoiceApp you are adding LineItems and assigning the Product for each LineItem. The reason I need to use the ComboBox is to use autoComplete. I might have 10000 products.

**********************************************************************
Currently this is my ProductDataSource looks like this

var ProductSearch=”;
var ProductIndex=0;

var ProductSource = {
datatype: “json”,
datafields: [
{ name: ‘ID’ },
{ name: ‘Product’ }
],
id: ‘id’,
url: ‘../Product/ProductJSON.php’,
async: false,
data: {
featureClass: “P”,
style: “full”,
maxRows: 12
}
};

var ProductDataAdapter = new $.jqx.dataAdapter(ProductSource, {
formatData: function (data) {
$(“#message”).html(ProductSearch);
data.name_startsWith = ProductSearch;
return data;
}
});

**********************************************************************
My Column custom editor
{ text: ‘Product’, datafield: ‘Product’, width: 180, editable: true, filterable: true, filtertype: ‘textbox’, columntype: ‘combobox’,
createeditor: function (row, column, editor) {
editor.jqxComboBox({selectedIndex: 0, source: ProductDataAdapter, remoteAutoComplete: true, remoteAutoCompleteDelay: 200, autoOpen: false, dropDownHeight: 200, displayMember: “Product”, valueMember: “ID”,
renderer: function (index, label, value) {
var item = ProductDataAdapter.records[index];
if (item != null) {
var label = item.Product;
return label;
}
return “”;
},
renderSelectedItem: function(index, item) {
var item = ProductDataAdapter.records[index];
if (item != null) {
var label = item.Product;
return label;
}
return “”;
},
search: function (searchString) {
ProductSearch = searchString;
ProductDataAdapter.dataBind();
}
});
editor.bind(‘select’, function (event) {
var item = event.args.item;
if (item) {
var value = item.value;
var label = item.label;
ProductIndex = value;
}
});
},
cellvaluechanging: function (row, column, columntype, oldvalue, newvalue) {
if (newvalue == “”) return oldvalue;
},
},

**********************************************************************
This works perfect the first time and is very fast. My problem is that I don’t think the ProductDataSource resets or preforms a clean lookup the second time. Being that async is false should it perform a lookup each time?

**********************************************************************

[DavidSimmonsParticipant]

When using custom editors is the datasource for the editor ran each time the editor is used, something like an ajax call?

I think this my problem on the datagrid row second edit the datasource is not run because the ComboBox is blank or contains items that exist within the same field within the current data grid.

Is there a way I can send and email to you with files. I could build an example using the northwind database if this would help.

[DavidSimmonsParticipant]

Hi Peter,

I would really appreciate any help on this issue. As you will see below I have found the main trigger for the issue….

I have found where the ComboBox is failing. In my updaterow function shown below I needed to refresh my data grid after an update. This is affecting the ComboBox datasource or the renderer. When I comment out the $(jqxgrid).jqxGrid(‘updatebounddata’); in the update, I was able to update Products for over 50 LineItems using the keyboard navigation with no issues. As soon as I remove the comment on $(jqxgrid).jqxGrid(‘updatebounddata’); I get the same results which are empty ComboBox list or the ComboBox show other Product items in the current data grid. I was able to duplicate this is any page I have.

One key is the ComboBox is using a remoteDataSource …..

I get the same effect when using a button to trigger $(jqxgrid).jqxGrid(‘updatebounddata’); It is absolute and overtime.

Something is wrong with $(jqxgrid).jqxGrid(‘updatebounddata’); or the effect on the ComboBox’s datasource or renderer.

my updaterow function:

$.ajax({
dataType: ‘json’,
url: url,
cache: false,
data: data,
success: function (data, status, xhr) {
$(“#message”).html(“Transaction: ” + xhr.responseText);
commit(true);
$(jqxgrid).jqxGrid(‘updatebounddata’);
},
error: function (jqXHR, textStatus, errorThrown) {
$(“#message”).html(“Transaction: ” + jqXHR.responseText);
commit(false);
$(jqxgrid).jqxGrid(‘updatebounddata’);
}
});

[Peter StoevKeymaster]

Hi David,

I do not see a reason why you call “updatebounddata” within the “updaterow” callback. “updaterow” is a function which is called when a row is updated. Calling commit(true) will confirm the update and will update the UI. Calling commit(false) cancels the changes. It is not supposed to call “updatebounddata” which re-binds and re-renders the Grid within a function which updates the Grid.

Best Regards,
Peter Stoev

jQWidgets Team
http://www.jqwidgets.com

[DavidSimmonsParticipant]

Thanks again Peter for all your help, sorry to be a pain.

When I started my app I did not have to do this. So I started looking into why I needed it and I am finding every ajax update comes back as an error, but the update occurred. Not sure yet but it seems it has to do with the jQuery 8.x release from what I am reading. Something about ajax json returns must now be a json format. I was using just and echo “someMessage” on update and have been for years, BAD ME.

But know I can seem to get a correct JSON return.

$.ajax({
dataType: ‘json’,
url: url,
cache: false,
data: data,
success: function (data, status, xhr) {
$(“#message”).html(“Transaction: ” + xhr.responseText);
commit(true);
},
error: function (jqXHR, textStatus, errorThrown) {
$(“#message”).html(“Transaction: Error ” + errorThrown);
commit(false);
}
});

Is there anything you know of that is required? I commented out all my update results out so there was no results sent back from the server but still get the error.

Have any suggestion?

[Author]

Posts