Select Dropdown issues

rob1st

Hi, probably simple questions, but I can't find the answer.

I have dropdowns in my editor table, the are currently ordered by name, how do I change it to order by the ID?

Field::inst( 'L.LocationName AS location' )
        ->options( Options::inst()
            ->table('location')
            ->value('LocationID')
            ->label('LocationName')
        )
        ->validator( Validate::dbValues() ),

and Question 2, when using edit, the dropdown is not holding the existing value, instead goes to the first value. I am loading the dropdown from the database.

Editor is below

var editor = new $.fn.dataTable.Editor( {
    ajax: "../ajax/controllers/assets.php",
    table: "#assets",
    fields: [ 
      { 
        label: 'ID', 
        name: 'assetID',
        type: 'hidden'
      }, { 
        label: 'Location', 
        name: 'location',
        type: 'select' 
      }, { 
          label: 'Design Tag',  
          name: 'designTag' 
      }, { 
          label: 'CTSC Tag',  
          name: 'assetTag',
          multiEditable: false
      }, { 
          label: 'Room',  
          name: 'room'
      }, {  
          label: 'Asset Type',  
          name: 'assetType'
      }, { 
          label: 'Entity',  
          name: 'entity',
          type: 'select'
      }, {  
          label: 'Drawing',  
          name: 'design'
      }, {  
          label: 'Status',  
          name: 'status'
      }, { 
          label: 'Old ID',  
          name: 'oldID',
          multiEditable: false
      }, {  
          label: 'Subsystem',  
          name: 'subsystem'
      }, {  
          label: 'PICO Status',  
          name: 'PR'
      }, {  
          label: 'SAT Status',  
          name: 'SR'
      }, {  
          label: 'PICO',  
          name: 'PICO'
      }, {  
          label: 'SAT',  
          name: 'SAT'  
      },
    ],
    formOptions: {
            main: {
                scope: 'cell' // Allow multi-row editing with cell selection
            }
        },
    
  } );

and control script is:

<?php

include("../lib/DataTables.php");

use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;

Editor::inst( $db, 'asset A', 'A.assetID' )
->field(
    Field::inst( 'A.assetID AS assetID' ),           // 0
    Field::inst( 'L.LocationName AS location' )
        ->options( Options::inst()
            ->table('location')
            ->value('LocationID')
            ->label('LocationName')
        )
        ->validator( Validate::dbValues() ),       // 9
    Field::inst( 'A.dwgTag AS designTag' ),            // 1
    Field::inst( 'A.MATPTag AS assetTag' ),           // 2
    Field::inst( 'A.Room AS room' ),              // 3
    Field::inst( 'T.assetType AS assetType' ),         // 4
    Field::inst( 'E.entityTLA AS entity' )
        ->options( Options::inst()
                ->table('entity')
                ->value('entityID')
                ->label('entityTLA')
            )
            ->validator( Validate::dbValues() ),         // 5
    Field::inst( 'A.dwgNo AS design' ),             // 6
    Field::inst( 'S.assetStatus AS status' ),       // 7
    Field::inst( 'A.oldID AS oldID' ),             // 8
    Field::inst( 'TR.reportStatus AS PR' ),  // 12
    Field::inst( 'TR1.reportStatus AS SR' ),   // 13
    Field::inst( 'SY.systemName AS subsystem' ),   // 13
    Field::inst( 'C1.procedureTag AS PICO' ),   // 13
    Field::inst( 'C2.procedureTag AS SAT' ),   // 13

)
->leftJoin( 'assettype T', 'T.assetTypeID', '=', 'A.assetType' )
->leftJoin( 'entity E', 'E.entityID', '=', 'A.entity' )
->leftJoin( 'location L', 'L.LocationID', '=', 'A.location' )
->leftJoin( 'assetstatus S', 'S.assetStatusID', '=', 'A.assetStatus' )
->leftJoin( 'testStatus TR', 'TR.reportStatusID', '=', 'A.picoReportStatus' )
->leftJoin( 'testStatus TR1', 'TR1.reportStatusID', '=', 'A.satReportStatus' )
->leftJoin( 'system SY', 'SY.systemID', '=', 'T.subsystem' )
->leftJoin( 'cxprocedure C1', 'C1.procedureID', '=', 'T.PICO' )
->leftJoin( 'cxprocedure C2', 'C2.procedureID', '=', 'T.SAT' )
->process( $_POST )
->json();

<?php
>
?>

because it's loading from a database I don't believe I can run a livetables example for you, and there are no issues shown on degugger.

Appreciate your help.

allan

Hi,

I have dropdowns in my editor table, the are currently ordered by name, how do I change it to order by the ID?

The Options class has an order method which you can use to do this.

and Question 2, when using edit, the dropdown is not holding the existing value, instead goes to the first value. I am loading the dropdown from the database.

That's take location here for this discussion. You have .LocationName AS location - which means you are trying to edit the name. But your select list is configured with the id as the value.

When working with select lists you need to keep in mind that there are two pieces of data per entry - the id, which is what you want to actually edit (normally anyway!) and the label which is what you want your end users to see.

Have a look at this example. You'll see we load both the id and label in the data from the server, so the label can be shown in the table and then the id used for the editing.

So it looks to me that you need to load the id as well as the label.

Regards,
Allan

rob1st

Thanks Allan,

Interesting information about the aliases. With the joins I was having issues when just using L.LocationName in the JS and the controller, when I used the aliases it worked. I'll go and try this today and report back. Appreciate your excellent support as always.

rob1st

ok, got it!

Just so other people understand from my thinking, the client side needs to call both sides of the join, instead of normal SQL where we just pull the information we want to display. (By the way the example using site vs sites really screwed with me for a bit, can you change that to more obvious table change )

I.e call the ID you are using in the primary table, and the label for the id from the secondary table.

Different, but now I know it will be easier! Thank Allan.

allan

By the way the example using site vs sites really screwed with me

Its a fair point that! I'll look into what we could do for that. Possibly as simple as using siteId for the reference column.

Regards,
Allan

rob1st

Yep, agreed .

Fingers crossed for Scotland in the Euros! (Am a Scot in Canada).