Datatables Editor, populate foreign key of table using a drop down of values.

christoefar

Hello,

As an example say I have a customers table and an orders table. Each order is related to a specific customer.

Using the Datatables Editor, would it be possible to create the relationship from a drop down list of customer names?

Thanks,
Chris

allan

Hi Chris,

Yes indeed this is very much possible. The very simplest case is to create a list which is populated with the names for the second table entry and the value given the foreign key value. That value would then be submitted to the server and entered into the table, giving the relationship. The "hardest" part to that is getting the values for the select list, and that's just a case of doing a select on the table and then returning the found values in an array for Editor to use.

I'm actually working on making this and more complex joins really easy in Editor at the moment, and hope to have it in Editor 1.2 in the not to distant future. I'll have demos and am planning a tutorial on the topic as well :-).

Allan

christoefar

That sounds good to me, I have been trying to work with Kendo UI but have been having to luck at all. The support from Telerik was really quite bad as well.

So is it quite easy to specify a template for the "create" button. I would like to use the auto complete widget form the jQuery UI to use AJAX calls to pull the customer name from the server.

Could I display the name of the customer on the orders grid for example. If I pass this info in JSON format to the grid. A nested object is how I imagine .NET sends the data.

Thanks for your reply!
Chris

allan

> So is it quite easy to specify a template for the "create" button.

You mean default values set in the fields? Absolutely - just use the "default" option for the field you specify :-)

> I would like to use the auto complete widget form the jQuery UI to use AJAX calls to pull the customer name from the server.

There isn't an 'auto-complete' input control built into Editor, but with the ability to easily create field type plug-ins, setting up an auto-complete field with jQuery UI is not particularly difficult at all :-). You use the field type plug-in to create the input element and initialise auto-complete, just as you would with any other input element. There is a tutorial on creating field type plug-ins here: http://editor.datatables.net/tutorials/field_types .

> Could I display the name of the customer on the orders grid for example.

Absolutely - what I would suggest is that you return the data in a JSON format something like:

[code]
"customer": {
"value": 12,
"name": "Allan"
}
[/code]

Then DataTables can use the 'name' parameter ("customer.name" in mDataProp) and Editor can use the value parameter ("customer.value" in dataProp). Editor would then submit the value to the server from the list to be populated in the database (you would need to use the onSubmit event to add the 'name' back into the data object, for DataTables to use the updated name as well - or again a field type plug-in would work :-)

Its not as trivial as uploading a file, but it does reduce the time for development a huge amount.

Let me know how you get on with it, or if you have any further questions.

Regards,
Allan

christoefar

Thanks for both your replies, Allan.
I will have a good play in the next few days.

Sounds much better than the Kendo UI Grid already!

Chris

christoefar

Is there a time frame for the 1.2 release of Editor?
Would that update be included if I bought Editor now?

Chris

allan

Editor 1.2 is scheduled to be released by the end of this month. The majority of the work involved with it is on the PHP side of things (so I'm not sure how much assistance that will be to you?), but there are a number of updated on the client-side to assist with dealing with joined tables.

> Would that update be included if I bought Editor now?

Absolutely yes. All 1.x releases are available with an Editor license.

Regards,
Allan

arieldistefano

I implemented an ajax call to populate an array used after in the editor.

here the example:

$.ajax( {
"type": "POST",
"url" : "../datasource/execute_action.php",
"data": filter,
"contentType" : "application/json",
"dataType": 'json',
"processData": false,
"async": false,
"cache": false,
"success": function (json) {

if (typeof json.error != 'undefined') {
//Error
alert(json.error);
}
else {
//Array for the select in DataTable

for (i=0;i

allan

> "async": false,

This won't be needed in Editor 1.2. select, radio and checkbox field types all have an 'update' method that can be called at any time with the data retrieved from the server :-).

Allan

arieldistefano

Great to Know!. Until then, it keep working ;)

ftgroup

RE: allan's comment: "There isn't an 'auto-complete' input control built into Editor, but with the ability to easily create field type plug-ins, setting up an auto-complete field with jQuery UI is not particularly difficult at all :-). You use the field type plug-in to create the input element and initialise auto-complete, just as you would with any other input element. There is a tutorial on creating field type plug-ins here: http://editor.datatables.net/tutorials/field_types ."

I tried to follow the tutorial but having a hard time getting autocomplete to work. Can you help me out with some sample code.

allan

Sure - can you post what you've currently got?

Allan

ftgroup

Thanks! I'm trying to get custom field type "todo" to work within the editor.




@import "DataTables-1.9.4/media/css/demo_page.css";
@import "DataTables-1.9.4/media/css/jquery.dataTables.css";
@import "DataTables-1.9.4/extras/TableTools/media/css/TableTools.css";
@import "DataTables-1.9.4/extras/Editor-1.2.1/media/css/dataTables.editor.css";







Contact Type
First Name
Last Name









var editor;
//Custom field
$.fn.DataTable.Editor.fieldTypes.todo = $.extend( true, {}, $.fn.DataTable.Editor.models.fieldType, {
"create": function ( conf ) {
var that = this;
conf._enabled = true;
conf._input = $(
''+
''+
'');
return conf._input;
},

"get": function ( conf ) {
},
"set": function ( conf, val ) {
}
} );

var availableTags = [
"ActionScript",
"AppleScript",
"Asp",
"BASIC",
"C",
"C++",
"Clojure",
"COBOL",
"ColdFusion",
"Erlang",
"Fortran",
"Groovy",
"Haskell",
"Java",
"JavaScript",
"Lisp",
"Perl",
"PHP",
"Python",
"Ruby",
"Scala",
"Scheme"
];
$( "#tags" ).autocomplete({
source: availableTags
});
//end custom field

$(document).ready(function() {

editor = new $.fn.dataTable.Editor( {
"ajaxUrl": {
"create": "POST api/contacts/datatables",
"edit": "PUT api/contacts/_id_/datatables",
"remove": "DELETE api/contacts/_id_/datatables"
},
"ajax": function ( method, url, data, successCallback, errorCallback ) {
if ( data.action === 'create') {
$.ajax( {
"type": method,
"url": url,
"data": JSON.stringify(data.data),
"contentType": "application/json",
"dataType": "json",
"success": function (json) {
successCallback( json );
},
"error": function (xhr, error, thrown) {
errorCallback( xhr, error, thrown );
}
} );
} else if (data.action === 'edit') {
$.ajax( {
"type": method,
"url": url,
"data": JSON.stringify(data.data),
"contentType": "application/json",
"dataType": "json",
"success": function (json) {
successCallback( json );
},
"error": function (xhr, error, thrown) {
errorCallback( xhr, error, thrown );
}
} );
} else if (data.action === 'remove') {
$.ajax( {
"type": method,
"url": url,
"data": JSON.stringify(data.data),
"contentType": "application/json",
"dataType": "json",
"success": function (json) {
successCallback( json );
},
"error": function (xhr, error, thrown) {
errorCallback( xhr, error, thrown );
}
} );
}
},
"domTable": "#contacts",
"fields": [
{ "label": "Contact Type:", "name": "contactType.id", "type": "select"},
{ "label": "First Name:", "name": "firstName"},
{ "label": "Last Name:", "name": "lastName"},
{
"label": "Status:",
"type": "todo" // Using the custom field type
}
],
} );
$('#contacts').dataTable( {
"sDom": "Tfrtip",
"sAjaxSource": "api/contacts/datatables",
"aoColumns": [
{ "mData": "contactType.name", "sDefaultContent": "" },
{ "mData": "firstName" },
{ "mData": "lastName" }
],
"oTableTools": {
"sRowSelect": "single",
"aButtons": [
{ "sExtends": "editor_create", "editor": editor },
{ "sExtends": "editor_edit", "editor": editor },
{ "sExtends": "editor_remove", "editor": editor }
]
},
"fnInitComplete": function(settings, json) {
editor.field('contactType.id').update(json.contactType);
}
} );


} );

allan

[code]
$( "#tags" ).autocomplete({
source: availableTags
});
[/code]

A little difficult to tell without formatting (you can use http://live.datatables.net to post code blocks with syntax highlighting), but I'd suggest that the autocomplete initialisation code should not be run when the script loads but rather inside the field's 'create' method - i.e. once a #tags element actually exists. I'd also suggest you don't use the ID for the selection (drop the id al together) and use something like `$('input', conf._input)` so it gets just that element. Remember id's must be unique :-)

Allan

ftgroup

Thank you! I followed your instructions and it worked.