Drop down list on Editor popup using JSON object array and binding to the underlying DataTable

kahlil312

Hi

I had a specific question on whether it is possible for the editor plugin and the Datatables source to 'Share' a data source

Scenario-
There is a datatable with a number of text and dropdown fields,all of which need to be edited, validated, and submitted by the user.

Requirements -
a) Would like to use Editor plugin to achieve this functionality.
b) All DT contents need to be validated before postback.
c) Drop-downs should default to currently selected values from database
d) On the editor popup/interface, need to link contents of drop down to another field e.g. when selecting product name from drop down for e.g., a read-only text box should be populated with the product code

Approach so far -
DT has been implemented with JSON object source array from a MVC view model.
Drop downs and display of selected value working fine - (Data source is the 'manufdata' JSON array shown below)
[code]
{"mData": "ProductDDList","aTargets": [3]},"mRender": function ( data, type, full ) {
var returnValue="";
var listItems= "";
for (var i = 0; i < data.length; i++){
if(data[i].ProductId==full.SelectedProductId){
listItems+= "" + data[i].ProductName + "";
}else{
listItems+= "" + data[i].ProductName + "";
}
}
return returnValue.concat(listItems,"");
}
},
[/code]
As per my reading of the documentation and several posts on this site, it seems in order to implement dropdowns on the editor popup (from the database) we have to fetch the contents using a Ajax call and then bind to the Editor Field using "ipOpts" and the DT field using "mdata".
(is this correct?)
I am using the DT documentation from http://editor.datatables.net/release/DataTables/extras/Editor/examples/fieldTypes.html
and this post http://datatables.net/forums/discussion/10742/datatables-editor-populate-foreign-key-of-table-using-a-drop-down-of-values./p1 as the starting point for my work.

So...

Sample JSON object data returned via Ajax call is:
[code]
var manufdata = [
{
"SurveyDataRecordId": "604",
"ProductVolume": "150.0000",
"ProductCode": "ABC.PQR.FRC.004",
"ProductDDList": [
{
"ProductId": "122",
"ProductName": "Corn Chips"
},
{
"ProductId": "446",
"ProductName": "Chilli Chips"
}
],
"SelectedProductId": "446"
}]
[/code]

Array Logic to parse and populate an array of Product items from above is:

[code]
var productListItems = [];

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

allan

Hi kG,

> As per my reading of the documentation and several posts on this site, it seems in order to implement dropdowns on the editor popup (from the database) we have to fetch the contents using a Ajax call and then bind to the Editor Field using "ipOpts" and the DT field using "data".

You don't have to do it that way, but you do need to tell Editor what the options for the list are, and hooking into the Ajax used by DataTables seems like a is the typical way doing this.

> a) DT Select item does not bind with the error above

Currently you've got:

> "mData": "ProductList"

However, in the data source you show above, there is no `ProductList` property in the data source object which is why you are getting the error you are.

I think we might want to step back just a little bit here. What is the full data structure that you are returning to the client-side in the JSON call and how is DataTables configured to show the data? If you aren't able to give me a link (please feel free to PM it if you don't want to make it public), it would be very helpful if you could run the table over the DataTables debugger so I can see how it is configured.

The reason I'm wondering is that I'm not quite sure why there is a `ProductDDList` property in the data source object, as well as a selected option. Is the `ProductDDList` different for each row?

Regards,
Allan

kahlil312

Full data structure being sent is as per the JSON array object in the original post i.e.
[code]
var manufdata = [
{
"SurveyDataRecordId": "604",
"ProductVolume": "150.0000",
"ProductCode": "ABC.PQR.FRC.004",
"ProductDDList": [
{
"ProductId": "122",
"ProductName": "Corn Chips"
},
{
"ProductId": "446",
"ProductName": "Chilli Chips"
}
],
"SelectedProductId": "446"
}]
[/code]

[quote]allan said: However, in the data source you show above, there is no ProductList property in the data source object which is why you are getting the error you are.[/quote]

agree - see update to the post.

[quote]allan said: What is the full data structure that you are returning to the client-side in the JSON call and how is DataTables configured to show the data[/quote]

Exactly as per the set up in http://live.datatables.net/unator/15/ are you able to get to this?
The only difference is that in my web site I am getting the JSON from an AJAX call so have an sAjaxsource attribute. TBH i haven;t even tried this on the MVC app yet, just want to get it working on an html page first.

As per :
[code]
$('#dTManufactSurveyData').dataTable({
"fnDrawCallback": function () {
$(".ddProductList").searchable({
maxListSize: 20,
exactMatch: false,
latency: 200
});
},
"sDom": "Tfrtip",
bProcessing: true,
sAjaxSource: '@Url.Action("getSurveyDataViewModel", "Manufacturer", new { viewManufId = @Model.ManufacturerId })',
"bPaginate": true,
"bFilter": false,
"bAutoWidth": false,
"sScrollY": "300px",

"aoColumnDefs": [
{ "mData": "SurveyDataRecordId", "sName": "SurveyDataRecordId", "aTargets": [5] },
{ "mData": "ProductVolume", "sName": "ProductVolume", "aTargets": [3] },
{ "mData": "ProductCode", "sName": "ProductCode", "aTargets": [2] },
{ "mData": "ProductDDList", "aTargets": [1], "mRender": function (data, type, full) {
var returnValue = "";
var listProductItems = "";
for (var i = 0; i < data.length; i++) {
if (data[i].ProductId == full.SelectedProductId) {
listProductItems += "" + data[i].ProductName + "";
} else {
listProductItems += "" + data[i].ProductName + "";
}
}
return returnValue.concat(listProductItems, "");
}...
[/code]

I have copied the JSON directly off Fiddler as rendered by my back end MVC controller.
As explained in a previous post http://datatables.net/forums/discussion/15917/how-to-create-a-drop-down-list-from-a-json-object-array-item-updated#Item_2
this is working perfectly when binding to a Datatable. Just trying to figure the easiest way to add Editor to the mix to add CRUD functionality.

[quote]allan said: The reason I'm wondering is that I'm not quite sure why there is a ProductDDList property in the data source object, as well as a selected option[/quote]

not sure what you refer to here, can you clarify?

[quote]allan said: it would be very helpful if you could run the table over the DataTables debugger so I can see how it is configured.[/quote]

am trying to, but it keeps running (at least 10-15 mins) and I get no output...it says "creating debug information.." and no further response...

Update = debugger just worked - code is ibeqeb

allan

Thanks for the extra information.

What I'm wondering is, is ProductDDList going to be the same for every entry? If so, why not just pass it back once, rather than in every object?

For example:

[code]
manufdata = {
"data": [
{
"SurveyDataRecordId": "604",
"ProductVolume": "150.0000",
"ProductCode": "CHE.NCH.FRC.004",
"SelectedProductId": "446"
},
{
"SurveyDataRecordId": "605",
"ProductVolume": "1200.0000",
"ProductCode": "CHE.NCH.FRC.004",
"SelectedProductId": "446"
}
],
"ProductDDList": [
{
"ProductId": "122",
"ProductName": "Cheddar - Chilli Curry"
},
{
"ProductId": "446",
"ProductName": "Fresh, Other"
}
]
}
[/code]

Better yet would be to modify the data structure slightly to be something like:


[code]
manufdata = {
"data": [
{
"SurveyDataRecordId": "604",
"ProductVolume": "150.0000",
"ProductCode": "CHE.NCH.FRC.004",
"SelectedProductId": "446"
},
{
"SurveyDataRecordId": "605",
"ProductVolume": "1200.0000",
"ProductCode": "CHE.NCH.FRC.004",
"SelectedProductId": "446"
}
],
"ProductDDList": {
"122": "Cheddar - Chilli Curry",
"446": "Fresh, Other"
}
}
[/code]

Although such a change might not be possible? Why is it better? Because a lookup can be done without a loop - so you can say what exactly the selected product is.

You might have something like:

[code]
"mData": "SelectedProductId",
"mRender": function ( data, type, row ) {
return manufdata.ProductDDList[ data ];
}
[/code]

in your DataTables initialisation. That will show the selected product in that column. You could do it with the current structure of course - it would just need a loop in it.

Then, to create the Editor select list you would have something like:

[code]
editor.field('ProductList').update( $.map( manufdata.ProductDDList, function ( val, key ) {
return { "label": val, "value": key };
} );
[/code]

i.e create an array of label / value pairs and use it to populate the `ProductList` field.

Regards,
Allan

kahlil312

Thanks for the help/suggestions Allan

I totally agree with your recommendations on returning a single instance of the ProductList, am trying to get this to work but initially attempting to reverse engineer the current data structure to show the Editor select (just so I have something to show the client :(

Just following on with the suggestion on the mapped select field i.e. the code below:

[code]
var manufdata = [
{
"SurveyDataRecordId": "604",
"ProductVolume": "150.0000",
"ProductCode": "CHE.NCH.FRC.004",
"ProductDDList": [
{
"ProductId": "122",
"ProductName": "Cheddar - Chilli Curry"
},
{
"ProductId": "446",
"ProductName": "Fresh, Other"
}
],
"SelectedProductId": "446"
}
]
for (var i = 0; i < manufdata.length; i++){
editor.field('ProductList').update( $.map( manufdata[i].ProductDDList, function ( val, key ) {
return { "label": val.ProductName, "value": val.ProductId };
}));
}
[/code]

This seems to work, but i had to make the (unconventional) changes to the $.map return, does this look ok to you or is there a better way. (again re-iterating that I am using the old approach where array is returned in
attribute:value format)

also, any idea how I'd return SelectedProductId to the ProductList field in Editor so it shows the currently selected value - currently shows the first value of the array

Thanks again
kG

allan

I would say that looks absolutely fine - with one exception. If `ProductDDList` is going to be the same every time, don't bother looping over `manufdata` . Just do it once on `manufdata[0].ProductDDList` - since its just eating CPU cycles doing the same thing every time at the moment!

> also, any idea how I'd return SelectedProductId to the ProductList field in Editor so it shows the currently selected value - currently shows the first value of the array

You should just be able to set the `SelectedProductId` property as the field name:

[code]
"fields": [
{
"label": "Product:",
"name": "SelectedProductId",
"type": "select"
},
[/code]

As the select will be populated with the values and `SelectedProductId` will be one of those values - it should be selected as needed.

Regards,
Allan

kahlil312

Thanks, this works fine now.

Another question - I currently have an external autocomplete/search plugin (JQuery Searchable DropDown) working in the Datatable on the ProductList DD field.
This is targeted using the class name like so:
[code]
$(".ddProductList").searchable({
maxListSize: 20,
exactMatch: false
}
[/code]

I've tried doing the same thing by assigning a "sClass":"" to the Editor product list, but this doesn't work.
i.e.
[code]
"label": "Product:",
"name": "SelectedProductId",
"type": "select",
"sClass":"ddEProducts"
}..
...
$(".ddEProducts").searchable({
maxListSize: 20,
exactMatch: false
});
[/code]
Would this work or is there a different way to do this?

thanks again.
kG

allan

What you would need is a field type plug-in here. There is a jQuery UI autocomplete plug-in already available at https://editor.datatables.net/fields/plugins . It basically just wraps up the plug-ins interface into API calls that Editor can understand and use. In this way you can create any plug-in form field type you want!

There is a tutorial on creating new field type plug-ins here:
https://editor.datatables.net/tutorials/field_types

Regards,
Allan

kahlil312

Implementing a field type plugin for me would be a bridge too far at this point given lack of time and my skill level with jQuery and JS! but maybe as I get more comfortable this may be an option.

As a part of the evaluation process (to select a CRUD capable grid) I am also required to explore the Datatables Editable plugin to see if it suits the client's purpose.
Can you provide some feedback (if thats ok) in this regard i.e. does Editor provide specific advantages over Editable in terms of features / options etc?

Is there an example somewhere with a demo of the Autocomplete working with the DT Editor? - hopefully with some implementation source code doc.
One of the requirements from my initial list in the first post was to have lookup fields on the Editor popup i.e. if the user selects a product name, the product code is automatically entered into another readonly field (on the popup form) - Would this need any custom development?

To ask a rather open ended question - how much customization is allowed on the Editor popup, can I for e.g. add a new custom button that allows me to go to a different form to add a new product?
One of the attractive features with Editable (as i understand it) is that the "Add" form is a local HTML form, so it would seem this can be customized at will..

Lastly, is there a roadmap for the Datatables and Editor toolset i.e. features planned or in the pipeline, likely direction etc etc.
..
Thanks Allan for all your answers, patience and help..
kG

allan

Hi,

> Can you provide some feedback (if thats ok) in this regard i.e. does Editor provide specific advantages over Editable in terms of features / options etc?

Yes, Editor is currently and actively supported. I don't believe the editable plug-in is any more - which is probably the first key point. Personally I believe the Editor API to be more stable and more intuitive as well as being much more complete. It also is fully integrated with DataTables, and will off course continue to be with DataTables 1.10+. In terms of interaction, the problem with inline editing is that it can completely destroy the table layout when you need to jam form controls into the table. To solve this, Editor 1.3 is going to introduce a bubble editing mode - a small editing bubble which is activated on click, double click or with KeyTable. This is, I believe, a much nicer solution than inline editing, while retaining the ease of use of inline editing.

> Is there an example somewhere with a demo of the Autocomplete working with the DT Editor? - hopefully with some implementation source code doc.

Can I drop you a mail at the e-mail address you used to sign up for your DataTables account with the AutoComplete source? I'd forgotten that it was only accessible to licensed users and you are still trailing Editor! The plug-ins all include examples of how they can be used and documentation of their available features.

> One of the requirements from my initial list in the first post was to have lookup fields on the Editor popup i.e. if the user selects a product name, the product code is automatically entered into another readonly field (on the popup form) - Would this need any custom development?

Yes, but all you need to do is listen for the `change` event from the select list, and use the `set()` method to set the value. Three lines (including closing brackets) would do it most likely.

> To ask a rather open ended question - how much customization is allowed on the Editor popup, can I for e.g. add a new custom button that allows me to go to a different form to add a new product?

Potentially you could yes. I don't have an example of that at the moment, but it would absolutely be possible using the API. There are probably a way ways of doing it, but the one that springs to mind would be to have two (or more) Editor instances on the page - one for each form type and show each as required.

> One of the attractive features with Editable (as i understand it) is that the "Add" form is a local HTML form, so it would seem this can be customized at will..

To some extent - it (currently) won't use any old HTML form - it uses the HTML form that it builds based on the initialisation options. You can customise that with display controller plug-ins, field type plug-ins and CSS.

> Lastly, is there a roadmap for the Datatables and Editor toolset i.e. features planned or in the pipeline, likely direction etc etc.

Not a published one (yet). But current the plan is:

v1.3 - August
- Bubble editing
- File upload plug-in
- C# server-side implementation
- Improved Join options
- Chaining for API methods
- WYSWIYG plug-in
- Field sets (although it might be bumped to v1.4)

v1.4 - Q1 2014
- Node.JS implementation
- Usable as a standalone form

I hope this s some help, but please feel free to fire away with any other questions.

Regards,
Allan

kahlil312

[quote]allan said: Can I drop you a mail at the e-mail address you used to sign up for your DataTables account with the AutoComplete source? I'd forgotten that it was only accessible to licensed users and you are still trailing Editor! The plug-ins all include examples of how they can be used and documentation of their available features.

[/quote]

this would be great Allan. and thanks for all the clarifications above.

regards
kG

kahlil312

Hi Allan
have just returned to the earlier discussion on simplifying the data / array structure for the ProductList Array
As per suggestion, my data structure now looks like -
[code]
var manufdata2 = {
"data": [
{
"SurveyDataRecordId": "604",
"ProductVolume": "150.0000",
"ProductCode": "CHE.NCH.FRC.004",
"SelectedProductId": "446"
},
{
"SurveyDataRecordId": "605",
"ProductVolume": "1200.0000",
"ProductCode": "CHE.NCH.FRC.004",
"SelectedProductId": "122"
}
],
"ProductDDList": {
"122": "Cheddar - Chilli Curry",
"446": "Fresh, Other"
}
};
$('#dTChildDataRows').dataTable({
"sDom": "Tfrtip",
"aaData": manufdata2.data,
"aoColumnDefs": [
{ "mData": "SurveyDataRecordId", "sName": "SurveyDataRecordId", "aTargets": [0] },
{ "mData": "ProductVolume", "sName": "ProductVolume", "sType": "Select","aTargets": [1] },
{ "mData": "ProductCode", "sName": "ProductCode", "aTargets": [2] },
{"mData": "SelectedProductId","aTargets": [3],
"mRender": function ( data, type, row ) {
return manufdata2.ProductDDList[ data ];
}},
{ "aTargets": ['_all'], "bSortable": false}]
});
[/code]
I am really confused now on how to set the select list up since the above naturally renders simply as text column, not a select list - do I still need a loop to go thru ProductDDList, if so how should this be targetted? guess I am missing something obvious here
ta
kG

allan

Hi,

I've just sent an e-mail with the auto-complete plugin. I did actually send it yesterday as well, but possibly it was swallowed by the spam filter, so I thought I'd just comment here to say as well.

I'm afraid I'm running out just now, so I'll look at and answer your other question when I get back!

Regards,
Allan

allan

Hi,

So to confirm, - the column in the DataTable is rendering the way you want is it? Which leaves reformatting the data for the select list.

You would do something like this:

[code]
editor.field('ProductList').update( $.map( manufdata.ProductDDList, function ( val, key ) {
return { "label": val, "value": key };
} );
[/code]

Or if you have the data when you are initialising Editor you could use the `ipOpts` input option:

[code]
{
"label": "Priority:",
"name": "priority",
"type": "select",
"ipOpts": $.map( manufdata.ProductDDList, function ( val, key ) {
return { "label": val, "value": key };
}
}
[/code]

Regards,
Allan