Need to write data to ID column but display usernames on front end

Jensen010

Hello,
This is going to be a long post, but I need to make sure I'm explaining it all correctly

I'm so close to figuring this out, but lack the last piece. I am working with the PHP libraries, and I have an SQL table that tracks inventory assets. This table assigns a numerical owner to each asset which is then linked to an id in a separate Contacts SQL table, which holds The contacts alias, first name, and last name.

What I need to accomplish is this:
in my datatable, I need to display an 'Owner' column that allows users to change the numerical owner in the assets table, by clicking a dropdown list of concatenated names.

For example, asset 1 has an owner of John doe, and John doe's Owner ID (in the assets table) is 1234. I need the user to be able to change the Owner to Jane Doe (who's Owner ID is 5678), by clicking on her name in the Owner column dropdown. Datatables will then recognize this and write back 5678 to the Owners column for that selected asset.

To accomplish this, I've tried a handful of things.
First, I have a function that pulls the concatenated user names from SQL:

//PHP Query
$ownersMerged =  "SELECT DISTINCT CONCAT(ISNULL(Alias, First_Name), ' ', Last_Name) AS owner, ID FROM Contacts ORDER BY owner asc";

//Function
function getDropOptions(table, column) {
    switch(column) {
        //Assets Table
        case "owner":
            var ownersList=[];
            var ownersInit = <?php echo queryCallback($conn, $ownersMerged) ?>;
            ownersInit.forEach(function(owner) {
                ownersList.push(owner.owner)
            });
            return ownersList;
            break;
//function continues for other fields...
       }
}

This successfully gives me the concatenated names of each owner, as pulled from the Contacts table. I then use this function in my DataTables.Editor.Field declaration:

var assetsEditor = new $.fn.dataTable.Editor({
        ajax: "/expiremental/wp-content/plugins/datatables/php/table-data/assets-table.php",
        table: '#assets-table',
        fields: [ 
                    { label: "Owner",
                      name: "ID",
                      className: "col-sm-5",
                      type: "select",
                      options: getDropOptions("assets", "owner")
                    },//Fields continue after this...
                ]
});

Now I have a dropdown for every <td> in that column, however, I can't write back to the ID column in SQL, and I'm assuming it's because I need a way to tie the actual ID to every Owner in the list. To try and accomplish this, I have this in my Datatable declaration:

var assetsDataTable = $('#assets-table').DataTable( {
        ajax: { url: "/expiremental/wp-content/plugins/datatables/php/table-data/assets-table.php", type: "POST" },
        dom://Stuff here.....
        columns: [
                        { data: "ID",
                           render: function(data, type, row){
                               if (data.alias === undefined || data.alias === null) {
                                    if (data.Last_Name !== null){
                                        return '<span class="id-number">'+data.ID+'</span> '+data.First_Name+' '+data.Last_Name;
                                    }
                               }
                           }
                       }
                     ]  //More stuff below...
});

This was an attempt at appending the Owner ID to each asset (which works), but it still doesn't tell Datatables that I only want the ID number changed, and it can disregard the names. The if statements are there to strip nulls out of the names as they appear initially for the client. They may be unnecessary.

Can anyone help me? Does what I'm trying to do make any sense? I can clarify if the above is confusing

Jensen010

I left one thing out, I have my PHP editor object set up this way:

```php
<?php
//Includes for php libraries here (omitted in this post)

use
DataTables\Editor,
DataTables\Editor\Field,
DataTables\Editor\Format,
DataTables\Editor\Join,
DataTables\Editor\Mjoin,
DataTables\Editor\Upload,
DataTables\Editor\Validate;

Editor::inst( $db, 'Assets')
->fields(
Field::inst( 'Contacts.ID', 'ID' )->setFormatter( Format::ifEmpty( null ) ),
Field::inst( 'Contacts.Alias', 'Alias' )->setFormatter( Format::ifEmpty( null ) ),
Field::inst( 'Contacts.First_Name', 'First_Name' )->setFormatter( Format::ifEmpty( null ) ),
Field::inst( 'Contacts.Last_Name', 'Last_Name' )->setFormatter( Format::ifEmpty( null ) ),
Field::inst( 'Assets.BarcodeNum', 'BarcodeNum' )->setFormatter( Format::ifEmpty( null ) ),
Field::inst( 'Assets.SerialNum', 'SerialNum' )->setFormatter( Format::ifEmpty( null ) ),
Field::inst( 'Assets.Model', 'Model' )->setFormatter( Format::ifEmpty( null ) ),
Field::inst( 'Models.Manufacturer', 'Manufacturer' )->setFormatter( Format::ifEmpty( null ) ),
Field::inst( 'Models.Device_Type', 'Device_Type' )->setFormatter( Format::ifEmpty( null ) ),
Field::inst( 'Assets.Last_Seen_Date', 'Last_Seen_Date' )
->getFormatter( Format::dateSqlToFormat( 'm/d/Y' ) )
->setFormatter( Format::dateFormatToSql( 'm/d/Y' ) )
->setFormatter( Format::ifEmpty( null ) ),
Field::inst( 'Assets.OrderNum', 'OrderNum' )->setFormatter( Format::ifEmpty( null ) ),
Field::inst( 'Assets.Item_Status', 'Item_Status' )->setFormatter( Format::ifEmpty( null ) ),
Field::inst( 'Assets.Equipment_Type', 'Equipment_Type' )->setFormatter( Format::ifEmpty( null ) ),
Field::inst( 'Assets.Comments', 'Comments' )->setFormatter( Format::ifEmpty( null ) ),
Field::inst( 'Assets.AssignedLevel', 'AssignedLevel' )->setFormatter( Format::ifEmpty( null ) ),
Field::inst( 'Assets.Acquired_Date', 'Acquired_Date' )
->getFormatter( Format::dateSqlToFormat( 'm/d/Y' ) )
->setFormatter( Format::dateFormatToSql( 'm/d/Y' ) )
->setFormatter( Format::ifEmpty( null ) ),
Field::inst( 'Assets.Issued_Date', 'Issued_Date' )
->getFormatter( Format::dateSqlToFormat( 'm/d/Y' ) )
->setFormatter( Format::dateFormatToSql( 'm/d/Y' ) )
->setFormatter( Format::ifEmpty( null ) ),
Field::inst( 'Assets.Warranty_Exp_Date', 'Warranty_Exp_Date' )
->getFormatter( Format::dateSqlToFormat( 'm/d/Y' ) )
->setFormatter( Format::dateFormatToSql( 'm/d/Y' ) )
->setFormatter( Format::ifEmpty( null ) ),
Field::inst( 'Assets.Surplus_Date', 'Surplus_Date' )
->getFormatter( Format::dateSqlToFormat( 'm/d/Y' ) )
->setFormatter( Format::dateFormatToSql( 'm/d/Y' ) )
->setFormatter( Format::ifEmpty( null ) )
)
->leftJoin( 'Contacts', 'Contacts.ID', '=', 'Assets.Owner' )
->leftJoin( 'Models', 'Models.Model', '=', 'Assets.Model' )
->process( $_POST )
->json();

<?php > ``` ?>

allan

I might be misunderstanding, but aren't you basically just looking for a left join here? You already have that for the Contacts to Assets tables, so in the DataTable display the contact name, and in the Editor form use the id, with a select list that shows the names in the options, but has the ids as the values.

The documentation here has more detail on that.

Regards,
Allan

Jensen010

The issue was with inline editing. I ended up solving it using the ->options() constructor for Field::inst

        Field::inst( 'Assets.Owner', 'Owner' )->setFormatter( Format::ifEmpty( null ) )
            ->options( 
                Options::inst()
                ->table('Contacts' )
                ->value( 'Contacts.ID')
                ->label( array( 'Contacts.Alias', 'Contacts.Last_Name', 'Contacts.Email_Address' ))
                ->order( 'Contacts.Alias asc')
            ),

Also, I used the editField and Render parameters for the column definitions:

{ data: null,
  editField: "Owner", 
  render: function(data, type, row, meta) {
    return data.Alias+' '+data.Last_Name;
  },
  "defaultContent": "<i>Not set</i>"
},

The combo of these two seem to have done the trick. Now my dropdowns for the owners field display the concatenated name, perform a lookup on the contacts.ID column, and write the resultant integer to the Assets table.
Hooray!

allan

Fantastic - thanks for letting me know. Good to hear you've got it working now.

Allan