Editor, primary keys, and updates

EotS

I have a single table that is being added to or edited, but there is a column that represents a part number only by its primary key (unique identifier.) I can put a select drop-down on the key value and populate an array using NAME and VALUE using the actual part number as NAME (so it is relevant to the user,) or I can perform a join and bring the part number in from the part_master table ...

But I can't figure out how to allow the edit that will update the table with the key value, while allowing the user to work with the part number value that is relevant to them.

This is a newbie question, so I'll need some kind of example to help me understand how this works.

EotS

OK, I think I'm getting on the right track. I had been searching quite a bit before posting, but since then I found http://editor.datatables.net/release/DataTables/extras/Editor/examples/join.html

I was able to reconstruct this example copying and pasting code and using the example database I built somewhere in the process of setting up DataTables. One note: I'm pretty sure this example is already built-in to the download package, but I couldn't find it by filenames. I was looking for something with "join" in the title, or something corresponding to the example on the editor.datatables site. Maybe the examples could point me to the specific file in my download package? That would help...

allan

Hi,

You might find this tutorial useful, as well as the example: http://editor.datatables.net/tutorials/php_join .

The file structure used on the Editor web-site is the same as the download package - so in this case the example is in `examples/join.html` . That example was added in Editor 1.2, so if you have an older version the example wouldn't be there, but with your license you can always download the latest version from http://editor.datatables.net/download :-)

Regards,
Allan

EotS

Now that I've gotten through the example and tutorial, I see that the tables are updated differently than my table structure requires, so I'm still stuck.

I have a production records table like such:

production_id___count____part_id
970____________500________1
971____________700 _______2
972____________500________1
973____________300________3


And then a part master table that holds the customer part number for the unique identifier we use in part_id:

part_id________part_number
1______________204B
2______________200A
3______________210C

I can do the join with no problem, but the idea is that I need to display the part_number in the table and in the form (because the part_id is irrelevant to the user.) But I need to allow them to change the part number for a given production_id. So the production records table will be updated with a new part_id, and the part master table will be untouched. It's only used to represent the part as the user understands it.

Can you give me an idea of the proper way to approach this?

allan

It sounds like you might want something like this:

[code]
$editor = Editor::inst( $db, 'production' )
->field(
Field::inst( 'count' ),
Field::inst( 'part_id' )
)
->join(
Join::inst( 'part', 'object' )
->join( 'part_id', 'part_id' )
->set( false )
->field(
Field::inst( 'part_number' )
)
);
[/code]

Then in the DataTables initialisation you can use `part.part_number` in mData to get the part number for each row.

Not that I've added `set( false )` to the `Join` instance - this tells the classes only to read from the join, not to write to it. So you can freely change `part_id` in the `production` table and the reference will be updated as needs be.

Regards,
Allan

EotS

That's it Allan, thanks. I had the join and the set->false all done correctly, but still had part_id lingering around in mdata along with part_number. Once I excluded part_id from mdata and from the HTML, it worked!

btw, this line "this tells the classes only to read from the join, not to write to it..." taught me a valuable lesson.

Thanks again!

allan

Heh - yes, Editor can actually be quite dangerous when working with data - in the same way that any SQL processing can be! With an incorrect configuration data could be lost, so it is always important to do development with data fully backed. I've learned that lesson to often myself... :-)

Allan

EotS

I've been looking at the join example, and wondering if the methodology you use to populate drop-down lists would apply to a table structure like we have discussed above.

I'm looking at these sections of code...

From join.php:

[code]
if ( !isset($_POST['action']) ) {
// Get department details
$out['dept'] = $db
->select( 'dept', 'id as value, name as label' )
->fetchAll();

$out['access'] = $db
->select( 'access', 'id as value, name as label' )
->fetchAll();
}
[/code]

From the .js:

[code]
{
"label": "Department:",
// The 'id' value from the property is used to set the value
// of the select list.
"name": "dept.id", // see bottom of page. the table is user_dept and alexandria is dept_id 4 (marketing)
"type": "select"
},
..............................

"fnInitComplete": function ( settings, json ) {
// Set the allowed values for the select and radio fields based on
// what is available in the database
editor.field('dept.id').update( json.dept );
editor.field('access[].id').update( json.access );
}

[/code]

I've been experimenting with it, but can't see how to use it to *populate* the drop-down while only *updating* the parent table.

EotS

I've been able to make it work by pulling in the part numbers from another php page, and adding this function to my js:
[code]
var getPartNumbers= new Array({"label" : "a", "value" : "a"});

function partNumSelect(){
getPartNumbers.splice(0,1);
$.ajax({
url: 'tables/Production/AddEditRecord/getPartNumbers.php',
async: false,
dataType: 'json',
success: function (json) {
for(var a=0;a

allan

What is possible is to listen for the `onSubmitComplete` event - that has access to the JSON returned from the server, so you could add the extra data (using something like the `select` as you suggest). The `onSubmitComplete` event handler would then update the select list using it's `update` method (I am right in saying that you basically just want to update the select list to the latest data on each submit?).

Allan