I just found an issue regarding Auto Fill that causes the value in a field (which has been setup as Lookup Table and uses Select control) in second, third, and so forth record in the Grid-Add section of Master/Detail Add; cannot be displayed at all. The field that setup as Lookup Table is a child, which has already been connected to a field in Master table as a parent.
Let me explain it in more detail by using the following tables schema, and I need step by step in order to give all the information to be clear.
#1. STEP ONE: Please create a new database, and run the following scripts to create tables schema including some records in it.
-- Table structure for purchases
DROP TABLE IF EXISTS purchases;
CREATE TABLE purchases (
Purchase_ID int(11) NOT NULL AUTO_INCREMENT,
Purchase_Number varchar(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
Purchase_Date datetime(0) NOT NULL,
Supplier_ID varchar(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
Notes varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
Total_Amount double(20, 0) NULL DEFAULT 0,
Total_Payment double(20, 0) NULL DEFAULT 0,
Total_Balance double(20, 0) NULL DEFAULT 0,
PRIMARY KEY (Purchase_ID) USING BTREE,
INDEX TSupplierTBeli(Supplier_ID) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 8 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Dynamic;
-- Records of purchases
INSERT INTO purchases VALUES (1, 'Purchase-00000000001', '2015-02-14 11:47:05', 'Supplier-00000000001', NULL, 182500000, 180000000, 2500000);
INSERT INTO purchases VALUES (2, 'Purchase-00000000002', '2015-02-14 11:48:20', 'Supplier-00000000002', NULL, 65250000, 61700000, 3550000);
INSERT INTO purchases VALUES (3, 'Purchase-00000000003', '2015-02-14 11:49:36', 'Supplier-00000000003', NULL, 40000000, 36400000, 3600000);
INSERT INTO purchases VALUES (4, 'Purchase-00000000004', '2015-02-14 11:54:01', 'Supplier-00000000004', NULL, 10000000, 8300000, 1700000);
INSERT INTO purchases VALUES (5, 'Purchase-00000000005', '2015-02-14 11:55:39', 'Supplier-00000000001', NULL, 52640000, 50400000, 2240000);
INSERT INTO purchases VALUES (6, 'Purchase-00000000006', '2015-02-14 11:58:46', 'Supplier-00000000002', NULL, 50000000, 45800000, 4200000);
INSERT INTO purchases VALUES (7, 'Purchase-00000000007', '2015-02-14 12:01:04', 'Supplier-00000000001', NULL, 100000000, 88700000, 11300000);
-- Table structure for purchases_detail
DROP TABLE IF EXISTS purchases_detail;
CREATE TABLE purchases_detail (
Purchase_ID int(11) NOT NULL AUTO_INCREMENT,
Purchase_Number varchar(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
Supplier_Number varchar(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
Stock_Item varchar(15) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
Purchasing_Quantity double(20, 0) NOT NULL DEFAULT 0,
Purchasing_Price double(20, 0) NOT NULL DEFAULT 0,
Selling_Price double(20, 0) NOT NULL DEFAULT 0,
Purchasing_Total_Amount double(20, 0) NOT NULL DEFAULT 0,
PRIMARY KEY (Purchase_ID) USING BTREE,
INDEX TBarangTDBeli(Stock_Item) USING BTREE,
INDEX TBeliTDBeli(Purchase_Number) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 18 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Dynamic;
-- Records of purchases_detail
INSERT INTO purchases_detail VALUES (1, 'Purchase-00000000001', 'Supplier-00000000001', 'Stock-000000001', 1000, 100000, 110000, 100000000);
INSERT INTO purchases_detail VALUES (2, 'Purchase-00000000001', 'Supplier-00000000001', 'Stock-000000002', 25000, 3300, 3500, 82500000);
INSERT INTO purchases_detail VALUES (3, 'Purchase-00000000002', 'Supplier-00000000002', 'Stock-000000003', 500, 12500, 15000, 6250000);
INSERT INTO purchases_detail VALUES (4, 'Purchase-00000000002', 'Supplier-00000000002', 'Stock-000000007', 10000, 2000, 2300, 20000000);
INSERT INTO purchases_detail VALUES (5, 'Purchase-00000000002', 'Supplier-00000000002', 'Stock-000000009', 2000, 7000, 7900, 14000000);
INSERT INTO purchases_detail VALUES (6, 'Purchase-00000000002', 'Supplier-00000000002', 'Stock-000000008', 5000, 5000, 5800, 25000000);
INSERT INTO purchases_detail VALUES (7, 'Purchase-00000000003', 'Supplier-00000000003', 'Stock-000000004', 50000, 200, 250, 10000000);
INSERT INTO purchases_detail VALUES (8, 'Purchase-00000000003', 'Supplier-00000000003', 'Stock-000000005', 10000, 1500, 1800, 15000000);
INSERT INTO purchases_detail VALUES (9, 'Purchase-00000000003', 'Supplier-00000000003', 'Stock-000000006', 5000, 3000, 3200, 15000000);
INSERT INTO purchases_detail VALUES (10, 'Purchase-00000000004', 'Supplier-00000000004', 'Stock-000000010', 2000, 5000, 5400, 10000000);
INSERT INTO purchases_detail VALUES (11, 'Purchase-00000000005', 'Supplier-00000000001', 'Stock-000000001', 500, 100000, 110000, 50000000);
INSERT INTO purchases_detail VALUES (12, 'Purchase-00000000005', 'Supplier-00000000001', 'Stock-000000002', 800, 3300, 3500, 2640000);
INSERT INTO purchases_detail VALUES (13, 'Purchase-00000000006', 'Supplier-00000000002', 'Stock-000000003', 2000, 12500, 15000, 25000000);
INSERT INTO purchases_detail VALUES (14, 'Purchase-00000000006', 'Supplier-00000000002', 'Stock-000000007', 3000, 2000, 2300, 6000000);
INSERT INTO purchases_detail VALUES (15, 'Purchase-00000000006', 'Supplier-00000000002', 'Stock-000000008', 1000, 5000, 5800, 5000000);
INSERT INTO purchases_detail VALUES (16, 'Purchase-00000000006', 'Supplier-00000000002', 'Stock-000000009', 2000, 7000, 7900, 14000000);
INSERT INTO purchases_detail VALUES (17, 'Purchase-00000000007', 'Supplier-00000000001', 'Stock-000000001', 1000, 100000, 110000, 100000000);
-- Table structure for stock_items
DROP TABLE IF EXISTS stock_items;
CREATE TABLE stock_items (
Stock_ID int(11) NOT NULL AUTO_INCREMENT,
Supplier_Number varchar(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
Stock_Number varchar(15) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
Stock_Name varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
Unit_Of_Measurement varchar(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
Category int(11) NOT NULL,
Purchasing_Price double(20, 0) NOT NULL DEFAULT 0,
Selling_Price double(20, 0) NOT NULL DEFAULT 0,
Notes varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
Quantity double(20, 0) NOT NULL DEFAULT 0,
PRIMARY KEY (Stock_ID) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 11 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Dynamic;
-- Records of stock_items
INSERT INTO stock_items VALUES (1, 'Supplier-00000000001', 'Stock-000000001', 'First Stock Item', 'Item', 1, 100000, 110000, 'Keterangan untuk barang pertama.', 2469);
INSERT INTO stock_items VALUES (2, 'Supplier-00000000001', 'Stock-000000002', 'Second Stock Item', 'Item', 2, 3300, 3500, 'Keterangan barang kedua.', 25702);
INSERT INTO stock_items VALUES (3, 'Supplier-00000000002', 'Stock-000000003', 'Third Stock Item', 'Item', 3, 12500, 15000, 'Keterangan untuk barang ketiga.', 2432);
INSERT INTO stock_items VALUES (4, 'Supplier-00000000003', 'Stock-000000004', 'Fourth Stock Item', 'Item', 4, 200, 250, 'Keterangan untuk barang keempat.', 49850);
INSERT INTO stock_items VALUES (5, 'Supplier-00000000003', 'Stock-000000005', 'Fifth Stock Item', 'Item', 1, 1500, 1800, '-', 9910);
INSERT INTO stock_items VALUES (6, 'Supplier-00000000003', 'Stock-000000006', 'Sixth Stock Item', 'Item', 2, 3000, 3200, '-', 4968);
INSERT INTO stock_items VALUES (7, 'Supplier-00000000002', 'Stock-000000007', 'Seventh Stock Item', 'Item', 1, 2000, 2300, 'This is only another notes.', 12834);
INSERT INTO stock_items VALUES (8, 'Supplier-00000000002', 'Stock-000000008', 'Eighth Stock Item', 'Item', 2, 5000, 5800, 'Another notes again.', 5970);
INSERT INTO stock_items VALUES (9, 'Supplier-00000000002', 'Stock-000000009', 'Ninth Stock Item', 'Item', 3, 7000, 7900, 'Again another notes haha.', 3967);
INSERT INTO stock_items VALUES (10, 'Supplier-00000000004', 'Stock-000000010', 'Tenth Stock Item', 'Item', 1, 5000, 5400, 'Another note for the tenth stock item.', 1956);
-- Table structure for suppliers
DROP TABLE IF EXISTS suppliers;
CREATE TABLE suppliers (
Supplier_ID int(11) NOT NULL AUTO_INCREMENT,
Supplier_Number varchar(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
Supplier_Name varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
Address text CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
City varchar(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
Country varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
Contact_Person varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
Phone_Number varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
Email varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
Mobile_Number varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
Notes text CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
Balance double NULL DEFAULT 0,
Is_Stock_Available enum('N','Y') CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT 'N',
Date_Added datetime(0) NULL DEFAULT NULL,
Added_By varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
Date_Updated datetime(0) NULL DEFAULT NULL,
Updated_By varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
PRIMARY KEY (Supplier_ID) USING BTREE,
UNIQUE INDEX KodeCust(Supplier_Number) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 5 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Dynamic;
-- Records of suppliers
INSERT INTO suppliers VALUES (1, 'Supplier-00000000001', 'First Supplier', 'Address for the first supplier', 'Bandung', 'Indonesia', 'John Mc. Enroe', '022124415093', 'john.mcenroe@gmail.com', '0824132048929', 'Just a note', 16040000, 'Y', '2015-02-14 11:38:08', 'Administrator', '2015-02-14 11:39:05', 'Administrator');
INSERT INTO suppliers VALUES (2, 'Supplier-00000000002', 'Second Supplier', 'Address for the second supplier', 'Jakarta', 'Indonesia', 'Martina Navatrilova', '02148272080', 'martina.nav@gmail.com', '081232442840', 'Just a note for Martina.', 7750000, 'Y', '2015-02-14 11:39:16', 'Administrator', '2015-02-14 11:40:00', 'Administrator');
INSERT INTO suppliers VALUES (3, 'Supplier-00000000003', 'Third Supplier', 'Address for the third supplier.', 'Surabaya', 'Indonesia', 'Joko Sentul', '03142348293', 'joko.sentoel@gmail.com', '081242009827', 'A note for third supplier.', 3600000, 'Y', '2015-02-14 11:40:03', 'Administrator', '2015-02-14 11:41:39', 'Administrator');
INSERT INTO suppliers VALUES (4, 'Supplier-00000000004', 'Fourth Supplier', 'Address for the fourth supplier.', 'Yogyakarta', 'Indonesia', 'Siapa Sajalah', '0213248290', 'siapa.saja@gmail.com', '081242932890', 'Another note for the fourth supplier.', 1700000, 'Y', '2015-02-14 11:41:44', 'Administrator', '2015-02-14 11:42:42', 'Administrator');
#2. STEP TWO: Setup the project, please do the following steps carefully.
#2.1. Create a new project uses PHPMaker 2019 and make sure connect to the Database above.
#2.2. Setup a Master/Detail relationship between "purchases" (as Master) and "purchase_details" (as Detail)
#2.3. Now go to "purchases" table, click on "Supplier_ID" field, setup as "Lookup Table" as follows:
Table name: suppliers
Link field: Supplier_Number
Display field #1: Supplier_Name
#2.4. Go to "purchase_details" table, then enable the following three items:
- "Master/Detail Add (as Detail)",
- "Master/Detail Add (as Edit)", and
- "Master/Detail Add (as View)",
#2.5. Still in the "purchase_details" table, now click on "Supplier_Number" field, setup as "Lookup Table", too:
Table name: suppliers
Link field: Supplier_Number
Display field #1: Supplier_Name
Parent/Filter #1:
- Parent table: purchases
- Parent field: Supplier_ID
- Filter field: Supplier_Number
#2.6. Still in the "purchase_details" table, now click on "Stock_Item" field, setup as "Lookup Table" with the following options:
Table name: stock_items
Link field: Stock_Number
Display field #1: Stock_Name
Parent/Filter #1:
- Parent table: (blank)
- Parent field: Supplier_Number
- Filter field: Supplier_Number
Auto fill: (Enabled), then click on ... button, then create a mapping between "Target Field (Table - purchase_details)" ---> "Source Field (Lookup Table - stock_items)": - Purchasing_Price ---> Purchasing Price
- Selling_Price ---> Selling_Price
#2.7. Save the project, and then generate ALL the script files.
#3. Run the generated web application via browser (see #3.4 and #3.5 below for the issue I found).
#3.1. Go to "Purchases" menu, then click on "Add Purchases/Purchase Details" button.
#3.2. Now you should see a Master/Detail Add page, then select "Second Supplier" value from "Supplier ID" field of Master section,
#3.3. After that, now scroll down the page to see the Detail table (that displayed in Grid-Add section) below that Master section, and from the first row of that Grid-Add, select "Second Supplier" item from "Supplier Number" column, after that from "Stock Item" column, select "Third Stock Item", then you will see in the "Purchasing Price" and "Selling Price" columns, the corresponding value respectively will be auto-filled-in. So far so good until this step.
#3.4. Now here is the issue... please pay attention at the second row in the Grid-Add... when you try to select a value from "Supplier Number" column, then nothing is to be displayed in that Select (Combobox) control.
#3.5. The same issue also happened for the third, fourth, fifth row in that Grid-Add, there is nothing to be displayed in "Supplier Number" column.
In order to make sure that the cause of that issue above is "Auto Fill", then I have already tried to disable the "Auto Fill" in the project, regenerate ALL the script files again, and try the step #3.4 and #3.5 above, now the issue is gone.
In other words, I can see all the items that displayed in "Supplier Number" column of the "Grid-Add" section of that "purchase_details" table, if I disable the "Auto Fill".
So, the conclusion is, Auto Fill will cause an issue in Master/Detail Add page.