Select with more options than shown in database

martinconnollybart

Hi,
I have a field in my database that has 9 possible options. However the current values in the table (after some data cleaning) only use 2 of those options. I would like my select in the editor form to show all 9 options but reflect the one shown in the database.

In the php file I can tie the select to the database thus:

Field::inst( 'orgtype' )
            ->options( Options::inst()
                ->table( 'TFunders')
                ->value( 'orgtype')
                ->label ('OrgType')
                ->validator( Validate::dbValues())

And in the javascript I have:

{
                    label: "Org Type:",
                    name: "orgtype",
                    type:"select",
                    options: orgTypes
                },

orgTypes is defined:

 var orgTypes = [
            { label: "Commercial", value: 1},
            { label: "Defunct", value: 2},
            { label: "Grant-making Body", value: 3},
            { label: "Individual", value: 4},
            { label: "Local Authority", value: 5},
            { label: "National Government", value: 6},
            { label: "Not a grant provider", value: 7},
            { label: "Organisation", value: 8},
            { label: "Rotary", value: 9},

        ];

My issue is that only the two values shown in the database are displayed in the select field. If I take out the options section from the php, all the values are shown but only the first value is selected regardless of the database.

What is the best approach to show all 9 values but highlight the value in the database?

allan

If you are using the Options class, it will be getting all of the values available from the TFunders table and then populating the list with that. It will overwrite client-side defined options if this approach is used.

So to be clear, are you saying that TFunders only has 2 options in it at the moment? Or that the host table (whatever it is) only uses 2 options and TFunders has 9 options?

Allan

rf1234

What is the best approach to show all 9 values but highlight the value in the database?

That should be done automatically with a "select" field. And you don't need an options instance on the server because you set the available options locally - regardless of the current database content. For a "select" field you usually don't need a validator either - unless you want to double check whether the user manipulated the selectable options somehow. I never use a validator with a "select" field.

Field::inst( 'orgtype' ),

var orgTypes = [
           { label: "Commercial", value: 1},
           { label: "Defunct", value: 2},
           { label: "Grant-making Body", value: 3},
           { label: "Individual", value: 4},
           { label: "Local Authority", value: 5},
           { label: "National Government", value: 6},
           { label: "Not a grant provider", value: 7},
           { label: "Organisation", value: 8},
           { label: "Rotary", value: 9},
 
       ];
......

{
   label: "Org Type:",
   name: "orgtype",
   type:"select",
   options: orgTypes
 },

Now if you want to render "orgTypes" in your data table you don't have to make a renderer that repeats the above labels. jQuery can do this for you like this - based on the field content of "orgtype".

columns: [
  { data: "orgtype",
    render: function (data, type, row) {
       return $.grep(orgTypes, function(obj){return obj.value == data;})[0].label;
     }
  },

martinconnollybart

@allan there are only 2 values in TFunders currently, but I want to show 9 options in the select. I will try the suggestion made by @rf1234 . Cheers.

martinconnollybart

Hi @rf1234 I tried the suggestion and the select is still defaulting to the first value in orgTypes, not the value stored in the database. Would it be cleaner to have orgTypes in a separate table and use those values in the select?

rf1234

Hi @rf1234 I tried the suggestion and the select is still defaulting to the first value in orgTypes, not the value stored in the database.

That's strange, Martin. Well, in case of a "create" scenario it should default to the first value, but not in case of an "edit" scenario.

In an "edit" scenario the "select" field will always display the database content on "open". I have just double checked it with a few of my use cases.

So if your db field "orgtype" contains one of the numbers 1 through 9 it should work. If it contains something else it won't work of course. Is your db field defined as "TINYINT(1)"? If so it should work ... If it is a CHAR field there might be a problem (e.g. leading spaces etc.)

Do you have any errors in the console? Have you checked that?

Roland

Would it be cleaner to have orgTypes in a separate table and use those values in the select?

To be honest in your fairly simple use case and without having dynamic options that depend on user input, I wouldn't do that. I have both scenarios. But in this case I don't see a requirement for putting the options in a db table.

rf1234

If the reason is really an inadequate db field type, you could do this:

Field::inst( 'orgtype' )
    ->getFormatter( function ( $val, $data, $opts ) {
        return (int)trim($val);
    } ),

rf1234

If all of that doesn't help they're might be another cause that creates the "illusion" the first list item was selected even when just editing a record. If your "select" field is the first field in your form, Editor will focus on it by default. This may cause the drop down to open. I found that really irritating when opening the Editor form.

In case I have a "select", "selectize" or "select2" field as the first one in the form I always turn "focus" off like this:

formOptions: {
    main: {
        focus: null 
    }
},

martinconnollybart

@rf1234 @allan the comment about leading spaces got me investigating. All of the values in the dB had trailing spaces. Something to do with exporting from one dB to a csv file and back into another dB, I imagine! Thanks both.

allan

Cool - great to hear you found the issue.

Allan