Select Dropdown issues

Select Dropdown issues

rob1strob1st Posts: 84Questions: 22Answers: 0

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(); ?>

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.

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    Answer ✓

    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

  • rob1strob1st Posts: 84Questions: 22Answers: 0
    edited June 2021

    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.

  • rob1strob1st Posts: 84Questions: 22Answers: 0

    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.

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    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

  • rob1strob1st Posts: 84Questions: 22Answers: 0

    Yep, agreed :).

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

Sign In or Register to comment.