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.