Selection field of concatenated data?

tron2101

In the table view I have a field displayed that is concatenated from other table join fields.

columns: [
            { //display of foreign key fkunit, display as "Unit Type # [District Name]", allow filtering of each element
                    label: 'unit',
                    render: function (data, type, row) {
                        return row.tblunittype.unittype + ' ' + row.tblunit.unitnumber + ' [' + row.tbldistricts.districtname + ']'
                    }
                },

How do I provide this list as the items in dropdown when editing?

fields: [
                { //reference foreign key fkunit, display as "Unit Type # [District Name]",
                    label: 'Unit',
                    name: 'tblcamping.fkunit',
                    render: function (data, type, row) {
                        return tblunittype.unittype + ' ' + tblunit.unitnumber + ' [' + tbldistricts.districtname + ']'
                    },
                    type: 'select'
                },

allan

There is no render option for the Editor fields - so I'm afraid that won't work like that.

I think the first thing we need to figure out is how you want this to be edited. Should the user edit all three values in a single field? If so then I would suggest using a get and set formatter at the server-side so the client-side only ever sees one value (i.e. the server will concatenate the values on get and split them on set).

If the user should be setting each value individually then simply have three fields - one for each.

Allan

tron2101

the concatenated field is the display for the foreign key of tblcamping.fkunit. The tblunit table which contains the unit number and contains 2 foreign keys, fkdistrict and fkunittype which reference the join tables districts and unittype.

SELECT 
  `tblunit`.`unitnumber`,
  `tbldistricts`.`districtname`,
  `tblunittype`.`unittype`,
  `tblunit`.`id`
FROM
  `tblunittype`
  INNER JOIN `tblunit` ON (`tblunittype`.`id` = `tblunit`.`fkunittype`)
  INNER JOIN `tbldistricts` ON (`tblunit`.`fkdistrict` = `tbldistricts`.`id`)

returns:

id  unitnumber  districtname           unittype
1,438   731           Cimarron                  Crew
2,642   2076      Learning for Life Group
1,406   96            Cimarron                  Pack
1,442   495           Cimarron                  Post
2,139   7478      Eagle Trails          Ship
1,630   239           Wisdom Trail          Team
1,426   96            Cimarron                  Troop

Which I'd like the user to select from a dropdown :

(id: 1438  display text) Crew 731 [Cimarron]
Group 2076 [Learning for Life]
Pack 96 [Cimarron]
Post 495 [Cimarron]
Ship 7478 [Eagle Trails]
Team 239 [Wisdom Trail]
Troop 96 [Cimarron]

Upon selection, the id is inserted into the tblcamping.fkunit field.

edited by allan Code highlighting

allan

Thanks for the clarification.

To show the list of options you'll want to use a the more advanced options for the Field->Options() method (specifically look at the last example in the "Parameters - easy database options" part).

Allan

tron2101

got it to work.

       Field::inst('tblcamping.fkunit')
            ->validator('Validate::notEmpty')
            ->options(function () {
                include("../config.php");
                $array = array();

                $sIndexColumn = 'id';

                /* DB table to use */
                $sTable = 'tblunit';

                /* Database connection information */
                $gaSql['user'] = $sql_details['user'];
                $gaSql['password'] = $sql_details['pass'];
                $gaSql['db'] = $sql_details['db'];
                $gaSql['server'] = $sql_details['host'];

                if (!$gaSql['link'] = mysqli_connect($gaSql['server'], $gaSql['user'], $gaSql['password'])) {
                    fatal_error('Could not open connection to server');
                }

                if (!mysqli_select_db($gaSql['link'], $sql_details['db'])) {
                    fatal_error('Could not select database ');
                }

                $sQuery = "SELECT
                      tblunit.id,
                      CONCAT(tblunittype.unittype, ' ', tblunit.unitnumber, ' [', tbldistricts.districtname,']') as dsp
                    FROM
                      tblunittype
                      INNER JOIN tblunit ON (tblunittype.id = tblunit.fkunittype)
                      INNER JOIN tbldistricts ON (tblunit.fkdistrict = tbldistricts.id)
                    ORDER BY
                      tblunittype.unittype,
                      CAST(tblunit.unitnumber as SIGNED INTEGER),
                      tbldistricts.districtname";

                $rResult = mysqli_query($gaSql['link'], $sQuery) or fatal_error('MySQL Error: ' . mysqli_errno());

                while ($row = mysqli_fetch_assoc($rResult)) {
                    $array[] = array('value' => $row['id'], 'label' => $row['dsp']);
                }
                return $array;
            }),