Datatables Editor, populate foreign key of table using a drop down of values.
Datatables Editor, populate foreign key of table using a drop down of values.
christoefar
Posts: 9Questions: 0Answers: 0
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
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
This discussion has been closed.
Replies
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
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
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
I will have a good play in the next few days.
Sounds much better than the Kendo UI Grid already!
Chris
Would that update be included if I bought Editor now?
Chris
> Would that update be included if I bought Editor now?
Absolutely yes. All 1.x releases are available with an Editor license.
Regards,
Allan
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
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
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
@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);
}
} );
} );
$( "#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