Dynamic Selection with another field update

PHPMaker General Discussion (PHPMaker)
1

Hi, I need help on dynamic selection list
Here are my setup, similar to Tutorial - Dynamic Selection List
I want to fill SerialNo on Add Form, once Trademarks/Models (dynamic selection) selected … from seq_no table
I can do that with row_inserting, but i need on the fly … so its viable to verify
each serial no correspond with trademarks+model
the seq_ID on seq_NO table is not auto increment, but manually updated using script

ibb.co/QrRSR3N (sample form)thank youThis is sql scriptUSE test_cars;CREATE TABLE trademarks (
ID int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
name varchar(50) DEFAULT NULL,
PRIMARY KEY (ID)
)
ENGINE = INNODB,
AUTO_INCREMENT = 4,
COLLATE utf8_general_ci;CREATE TABLE seq_no (
prefix char(5) DEFAULT NULL,
seq_ID int(11) UNSIGNED DEFAULT NULL,
serialNo char(20) DEFAULT NULL
)
ENGINE = INNODB,
COLLATE utf8_general_ci;CREATE TABLE models (
ID int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
trademark_ID int(11) UNSIGNED DEFAULT NULL,
name varchar(50) DEFAULT NULL,
PRIMARY KEY (ID)
)
ENGINE = INNODB,
AUTO_INCREMENT = 6,
COLLATE utf8_general_ci;CREATE TABLE cars (
ID int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
TradeMark_ID int(11) UNSIGNED DEFAULT NULL,
Model_ID int(11) UNSIGNED DEFAULT NULL,
SerialNo char(20) DEFAULT NULL,
PRIMARY KEY (ID)
)
ENGINE = INNODB,
AUTO_INCREMENT = 2,
COLLATE utf8_general_ci;INSERT INTO trademarks VALUES
(1, ‘Toyota’),
(2, ‘Honda’),
(3, ‘Suzuki’);INSERT INTO seq_no VALUES
(‘TI’, 322, ‘TI000322’),
(‘HC’, 443, ‘HC000443’),
(‘HA’, 121, ‘HA000121’),
(‘TP’, 22, ‘TP000022’),
(‘SE’, 99, ‘SE000099’);INSERT INTO models VALUES
(1, 1, ‘Innova’),
(2, 2, ‘Civic’),
(3, 2, ‘Accord’),
(4, 1, ‘Prius’),
(5, 3, ‘Ertiga’);INSERT INTO cars VALUES
(1, 1, 1, ‘TI000322’);

2

The trademarks and models in your table do not determine which serial number.

3

arbei wrote:
The trademarks and models in your table do not determine which serial
number.
yes it does, it takes from Trademarks and Models combine (first letter only) and then continue sequence number as a new serial number
does it make sense ?
please advise
thank you

4

Your “seq_no” table schema does not make sense if you want to use it and implement Auto fill in PHPMaker.

Here is my suggestion (and it should work as I’ve already tested it):

  1. You should add a new field in your “seq_no” table; let’s say “model_ID” that linked to “ID” field in “models” table,
  2. Then, create a database View that joins “models” and “seq_no” tables; let’s say its name is “models_seq_no”,
  3. Use that database View as “Lookup Table” for “Model_ID” field of your “cars” table,
  4. After that, you may simply define the Dynamic Selection List for that “Model_ID” field,
  5. And then, enable “Auto fill” option from that Lookup Table pane, and choose “serialNo” from “Source Field (Lookup Table - models_seq_no)” column that belongs to the “SerialNo” that belongs to the “Target Field (Table - cars)” column,
  6. Re-generate ALL the script files again,
  7. Done and enjoy! :slight_smile: