Editor table with JOIN and SELECT Boxes

Editor table with JOIN and SELECT Boxes

cemlimitedcemlimited Posts: 36Questions: 9Answers: 0
edited November 2022 in Editor

Hi Guys,

I have an Inline whole row create table in editor.
Everything working fine.....Apart from the Create.....Will not update with the new entry.
I know this is associated with the selects in the field list....

Cannot figure this one out.

Thanks Steve


var editor; // use a global for the submit and return data rendering in the examples $(document).ready(function() { editor = new $.fn.dataTable.Editor( { ajax: "/DTEditor/controllers/stoppages.php", table: "#example", fields: [ { label: "PWO:", name: "downtime_records.pwo", type: "readonly", def: "P53063*58" , }, { label: "Manager:", name: "downtime_records.manager", type: "readonly", def: 1 , }, { label: "minutes:", name: "downtime_records.minutes"}, { label: "reason:", name: "downtime_records.reason", type: "select" }, { label: "costed:", name: "downtime_records.costed", type: "select" }, ] } ); // Activate an inline edit on click of a table cell $('#example').on( 'click', 'tbody td.row-edit', function (e) { editor.inline( table.cells(this.parentNode, '*').nodes(), { submitTrigger: this, submitHtml: '' } ); } ); // Delete row $('#example').on( 'click', 'tbody td.row-remove', function (e) { editor.remove( this.parentNode, { title: 'Delete record', message: 'Are you sure you wish to delete this record?', buttons: 'Delete' } ); } ); var table = $('#example').DataTable( { dom: "Bfrtip", ajax: "/DTEditor/controllers/stoppages.php", order: [[ 1, 'asc' ]], columns: [ { data: "downtime_records.pwo"}, { data: "downtime_records.manager"}, { data: "downtime_records.minutes"}, { data: "downtime_reasons.reason", editField: "downtime_records.reason" }, { data: "costed.costed", editField: "downtime_records.costed" }, { data: null, defaultContent: '', className: 'row-edit dt-center', orderable: false }, { data: null, defaultContent: '', className: 'row-remove dt-center', orderable: false }, ], select: { style: 'os', selector: 'td:first-child' }, buttons: [ { extend: "createInline", editor: editor, formOptions: { submitTrigger: -2, submitHtml: '' } } ] } ); } );
$table = "downtime_records"; 
$fieldquery = "SHOW Columns FROM `".$table."`";
$pwo = $_GET['pwo'];  

$result2 = $db->prepare($fieldquery);
$result2->execute();                                                      

foreach ($result2 as $row) {
    $fields[] =$row['Field'];   
}

$stoppage_excludes = array("id");
$stoppage_display = array("pwo","manager");
$stoppage_selects = array("reason","costed");
$stoppage_selects_tables = array("downtime_reasons","costed");
$stoppage_selects_labels = array("reason","costed");


// DataTables PHP library
include( $_SERVER["DOCUMENT_ROOT"]."/DTEditor/lib/DataTables.php" );

$db->sql("SET names 'utf8'");

// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;

// Build our Editor instance and process the data coming from _POST


$aaa =Editor::inst( $db, $table );

if($pwo == ''){

}else{
    $aaa->where('pwo', $pwo);
}

$aaa ->field(Field::inst( 'downtime_records.id'));

foreach(array_diff($fields, $stoppage_selects,$stoppage_excludes) as $field){
    $aaa ->field(Field::inst( $table.".".$field));
}

$cycle = 0;

foreach($stoppage_selects as $select){
    $aaa ->field(Field::inst($table.'.'.$select)->options( Options::inst()->table( $stoppage_selects_tables[$cycle] )->value( 'id' )->label( $stoppage_selects_labels[$cycle] ))->validator( Validate::dbValues()));
    $aaa ->field(Field::inst( $stoppage_selects_tables[$cycle].'.'.$stoppage_selects_labels[$cycle]));
    $aaa ->leftJoin( $stoppage_selects_tables[$cycle],$stoppage_selects_tables[$cycle].'.id', '=', $table.'.'.$select );
    $cycle = $cycle + 1;
}


$aaa->debug(true)->process( $_POST )->json();

This question has an accepted answers - jump to answer

Answers

  • cemlimitedcemlimited Posts: 36Questions: 9Answers: 0

    Additional Information:

    Here is the Controller output.

    {"data":[{"DT_RowId":"row_14","downtime_records":{"id":"14","pwo":"P53063*58","manager":"1","minutes":"45","record":"2022-11-19 15:26:12","reason":"28","costed":"2"},"downtime_reasons":{"reason":"Batch Change"},"costed":{"costed":"ADMIN"}}],"options":{"downtime_records.reason":[{"label":"Batch Change","value":"28"},{"label":"Breaks","value":"1"},{"label":"Capper \/ Pusher \/ Bonker","value":"19"},{"label":"Conveyor","value":"20"},{"label":"Date code","value":"23"},{"label":"De-pucker","value":"21"},{"label":"Filler","value":"16"},{"label":"Film Change","value":"26"},{"label":"Full changeover","value":"5"},{"label":"Guide rails","value":"22"},{"label":"Label change","value":"17"},{"label":"Labeller","value":"18"},{"label":"Line not Scheduled","value":"3"},{"label":"Mats issues","value":"13"},{"label":"Mix Issue","value":"11"},{"label":"Operator error","value":"29"},{"label":"Other","value":"30"},{"label":"Planned Maintenance","value":"2"},{"label":"Pull through","value":"7"},{"label":"Pump","value":"15"},{"label":"Quick C\/O","value":"8"},{"label":"Shrink wrapper","value":"25"},{"label":"Strapper","value":"27"},{"label":"Strip down \/ Dry Set","value":"6"},{"label":"Transfer belt","value":"24"},{"label":"Waiting for Engineer","value":"10"},{"label":"Waiting for QA","value":"9"},{"label":"Waiting for materials","value":"14"},{"label":"Waiting for mix","value":"12"},{"label":"Wash Out","value":"4"}],"downtime_records.costed":[{"label":"ACC","value":"1"},{"label":"ADMIN","value":"2"},{"label":"AGENCY","value":"3"},{"label":"AUDIT","value":"4"},{"label":"BRAND","value":"5"},{"label":"BUYING","value":"6"},{"label":"CLEAN","value":"7"},{"label":"CONTR","value":"8"},{"label":"CUST","value":"9"},{"label":"DEVON","value":"10"},{"label":"ENG","value":"11"},{"label":"F AID","value":"12"},{"label":"FIRE","value":"13"},{"label":"H&S","value":"14"},{"label":"INET","value":"15"},{"label":"IT","value":"16"},{"label":"LAB","value":"17"},{"label":"LINE START","value":"18"},{"label":"MAINT","value":"19"},{"label":"MEET","value":"20"},{"label":"MIX","value":"21"},{"label":"NO PROD","value":"22"},{"label":"PLAN","value":"23"},{"label":"PRINT","value":"24"},{"label":"PTECH","value":"25"},{"label":"PVT LAB","value":"26"},{"label":"QC","value":"27"},{"label":"R&D","value":"28"},{"label":"REWORK","value":"29"},{"label":"S SERV","value":"30"},{"label":"S TAKE","value":"31"},{"label":"SHOP","value":"32"},{"label":"STORES","value":"33"},{"label":"SUPER","value":"34"},{"label":"SUPP","value":"35"},{"label":"TECH","value":"36"},{"label":"TEST","value":"37"},{"label":"TRACK","value":"38"},{"label":"TRAIN","value":"39"},{"label":"TRIAL","value":"40"}]},"files":[],"debug":[{"query":"SELECT `downtime_records`.`id` as 'downtime_records.id', `downtime_records`.`pwo` as 'downtime_records.pwo', `downtime_records`.`manager` as 'downtime_records.manager', `downtime_records`.`minutes` as 'downtime_records.minutes', `downtime_records`.`record` as 'downtime_records.record', `downtime_records`.`reason` as 'downtime_records.reason', `downtime_reasons`.`reason` as 'downtime_reasons.reason', `downtime_records`.`costed` as 'downtime_records.costed', `costed`.`costed` as 'costed.costed' FROM `downtime_records` LEFT JOIN `downtime_reasons` ON `downtime_reasons`.`id` = `downtime_records`.`reason` LEFT JOIN `costed` ON `costed`.`id` = `downtime_records`.`costed` ","bindings":[]},{"query":"SELECT DISTINCT `id` as 'id', `reason` as 'reason' FROM `downtime_reasons` ","bindings":[]},{"query":"SELECT DISTINCT `id` as 'id', `costed` as 'costed' FROM `costed` ","bindings":[]}]}
    
  • allanallan Posts: 57,737Questions: 1Answers: 9,204 Site admin
    Answer ✓

    We resolved this by e-mail so, for anyone else who finds this thread, the key was to disable the writing of the primary key field by using:

    $aaa ->field(Field::inst( 'downtime_records.id')->set(false));
    

    In general there is no need to include the primary key, unless you want to show it to the end user. There are some rare cases where you might want the end user to write a value for the primary key, and if you do include the id in the fields list, Editor expects a value to be submitted for writing, unless ->set(false) is specified.

    Allan

  • cemlimitedcemlimited Posts: 36Questions: 9Answers: 0

    As always - Allan has sorted. Gentleman.

    Thanks again.

Sign In or Register to comment.