How to populate a select element from a different database

TomBajzek

I'm building an application for managing a fleet of vehicles. I need to populate a select element from a table of users that lives in a database that is separate from the fleet database. No relationship is needed other than recording the person's name in the fleet database, and it is not appropriate to move the table of people into the fleet database. I know that I need to use an Options method to do this, but the data for the users is not in the fleet database. Is it possible to do this? I can't see how to refer to the "external" database of users from within the Ajax PHP code that handles the fleet data, because it's not in the table being edited, nor in a joined table.

allan

Can you make an Ajax call to an external script to get the list of options? If so, you'd just bypass the Editor server-side libraries for getting the list of options and use something like:

$.getJSON( '/getPeople', function ( json ) {
  editor.field('people').update( json.options );
} );

Allan

TomBajzek

Allan,

Thanks for the suggestion. I knew I'd have to do something like you described in prose, but I had no idea how to tie that external reference into the Editor. You code sample gives me the guidance I need for this. I'll give it a try.

Thanks,
Tom

TomBajzek

Allan,

Now I have Ajax code that will deliver the JSON I need. When I tried your example code, I found that field.type() is not a function. Apparently, that is in a plugin, but I have not been able to find that plugin. Please advise.

Thanks,
Tom

allan

Can you show me the code you are using please? There isn't a field.type() method in either DataTables or Editor.

Allan

TomBajzek

Allan,

I'm sorry, but I meant to say that field.update() is what I couldn't find. My code segment is:

    $.getJSON(url,function(json) {
        editor.field('assignments.person').update(json.options);
        } );

What I get is a Javascript error:
TypeError: editor.field('assignments.person').update is not a function.

I could not find the field.update() description in the documentation, but I did find somewhere that it required a plugin, which I couldn't find.

Tom

kthorngren

I saw this post the other day and tried Allan's solution without the need to add a plugin. Tried recreating your error in my page and found that if I don't have type: 'select' assigned to that field I get the same error.

Kevin

TomBajzek

Kevin,

Thanks for that suggestion! I no longer get the Type error. It looks like I'm not supplying the correct JSON, but that's a different problem that I can work on now that the error is gone.

Thanks,
Tom

TomBajzek

I'm having a problem finishing this off. I can't figure out how to construct the exact JSON that Datatables needs.

I have a fairly simple PHP file to deleiver the JSON to the Javascript:

// First get the users to populate "assignment" dropdown
$personsListQry = $ua->query('select fName,lName from roles where vehicleOK = "yes" order by lName, fName')->fetchAll(PDO::FETCH_ASSOC);

$personsArray = array();
$personsArray[] = array('label' => '','value' => '');

foreach ($personsListQry as $row) {
        $name = $row['fName'] . ' ' . $row['lName'];
        $personsArray[] = array('label'=>$name,'value'=>$name);
}

$personsJSON = json_encode($personsArray);
error_log('gDL.p: personsJSON= ' . $personsJSON);

// Now send back the JSON for thelist of eligible drivers
header('Content-Type: application/json;charset=utf-8');
echo $personsJSON;

The JSON that this PHP delivers to Datatables is:

[{label: "", value: ""}, {label: "Floaty Floater", value: "Floaty Floater"}, {label: "Purcy Purchasing", value: "Purcy Purchasing"}, {label: "Test User", value: "Test User"}, {label: "Ulf User", value: "Ulf User"}]

The Javascript snippet that handles this is as follows:

    $.getJSON(url,function(json) {
        persons = {"assignments.person":json};
        editor.field('assignments.person').update(persons);
        truth = true;
        } );

This seems to be close to what I need, but it's not quite right, and I'm not sure what to do to get it right. This gves me a select element in my editor that has 'assignments.person' as the first and only option. I just haven't figured out what needs to be different.

I'd appreciate some suggestions here.

Thanks,
Tom

allan

Regarding field().update(), yes, only certain fields (select, radio and checkbox provide that method).

Looking at your code above you can just use:

$.getJSON(url,function(json) {
    editor.field('assignments.person').update(json);
    truth = true;
} );

There is no need when using field().update() to encapsulate the field name since you've already selected the field from the field() method. Only if you were using dependent() or the initial DataTables JSON would you need to do that to reference a specific field.

Allan

TomBajzek

Allan,

Thank you! I had fiddled with so many other issues that I didn't realize that fixing them would bring me back to your original recommendation. It works correctly now.

Thanks,
Tom