Autofill for Dynamic Selection in SQL Server

This public forum is for user-to-user discussions of PHPMaker. Note that this is not support forum.
Post Reply
mobhar
User
Posts: 11660

Autofill for Dynamic Selection in SQL Server

Post by mobhar »

I've just found out an issue about Autofill to target fields that setup as Dynamic Selection in SQL Server does not working properly all the time. The target child field seems always displaying more than one values, instead of one value.

This issue is happened for SQL Server Database. For MySQL Database, it works as expected.

  1. Let's say, I have a "customer_orders" table.

  2. One of the fields in this table is "Customer_ID" which will lookup to the "customers" table.

  3. From this "Customer_ID" field, I setup Autofill the value of "First_Name", "Last_Name", "Address", "Country" and "City" from "customers" table to the same target fields in "customer_orders" table.

  4. For this purpose, then "Country" and "City" fields in the "customers_table" have been also setup as "Dynamic Selection List" to each related table. Both fields are setup also using "Use modal dialog for lookup".

  5. After regenerating all the scrip files, when I select a customer from that "customer_orders" table, then all those values above (First_Name, Last_Name, Address, and Country... is auto-filled into the target fields correctly).

  6. Unfortunately, the issue is... the selected value only in the "City" dropdown target field, will be displayed as three values with the same value. For example: when the country of customer is "Indonesia", then the selected value in "City" dropdown target field will be shown as "Jakarta, Jakarta, Jakarta" instead of "Jakarta".

  7. I confirmed again, that this issue is only happened in SQL Server Database, but not in MySQL Database.

  8. Thoughts?

  9. To reproduce the issue, then here is the tables schema:


-- Table structure for cities


DROP TABLE [test].[cities]
GO
CREATE TABLE [test].[cities] (
[Country] char(2) NOT NULL ,
[City_Code] varchar(10) NOT NULL ,
[City_Name] varchar(100) NOT NULL
)

GO


-- Records of cities


INSERT INTO [test].[cities] ([Country], [City_Code], [City_Name]) VALUES (N'US', N'CA', N'California')
GO
GO
INSERT INTO [test].[cities] ([Country], [City_Code], [City_Name]) VALUES (N'ID', N'JKT', N'Jakarta')
GO
GO
INSERT INTO [test].[cities] ([Country], [City_Code], [City_Name]) VALUES (N'MY', N'KL', N'Kuala Lumpur')
GO
GO
INSERT INTO [test].[cities] ([Country], [City_Code], [City_Name]) VALUES (N'ID', N'MDN', N'Medan')
GO
GO
INSERT INTO [test].[cities] ([Country], [City_Code], [City_Name]) VALUES (N'US', N'NY', N'New York')
GO
GO
INSERT INTO [test].[cities] ([Country], [City_Code], [City_Name]) VALUES (N'MY', N'PEN', N'Penang')
GO
GO
INSERT INTO [test].[cities] ([Country], [City_Code], [City_Name]) VALUES (N'ID', N'SBY', N'Surabaya')
GO
GO
INSERT INTO [test].[cities] ([Country], [City_Code], [City_Name]) VALUES (N'MY', N'SRW', N'Serawak')
GO
GO
INSERT INTO [test].[cities] ([Country], [City_Code], [City_Name]) VALUES (N'US', N'TN', N'Tennessee')
GO
GO


-- Table structure for countries


DROP TABLE [test].[countries]
GO
CREATE TABLE [test].[countries] (
[Country_Code] char(2) NOT NULL ,
[Country_Name] varchar(100) NOT NULL
)

GO


-- Records of countries


INSERT INTO [test].[countries] ([Country_Code], [Country_Name]) VALUES (N'ID', N'Indonesia')
GO
GO
INSERT INTO [test].[countries] ([Country_Code], [Country_Name]) VALUES (N'MY', N'Malaysia')
GO
GO
INSERT INTO [test].[countries] ([Country_Code], [Country_Name]) VALUES (N'US', N'United States')
GO
GO


-- Table structure for customer_orders


DROP TABLE [test].[customer_orders]
GO
CREATE TABLE [test].[customer_orders] (
[Order_ID] int NOT NULL IDENTITY(1,1) ,
[Product_ID] int NOT NULL ,
[Order_Date] datetime NULL ,
[Customer_ID] int NOT NULL ,
[First_Name] varchar(50) NULL ,
[Last_Name] varchar(50) NULL ,
[Address] text NULL ,
[Country] char(2) NULL ,
[City] varchar(10) NULL
)

GO
DBCC CHECKIDENT(N'[test].[customer_orders]', RESEED, 5)
GO


-- Records of customer_orders


SET IDENTITY_INSERT [test].[customer_orders] ON
GO
INSERT INTO [test].[customer_orders] ([Order_ID], [Product_ID], [Order_Date], [Customer_ID], [First_Name], [Last_Name], [Address], [Country], [City]) VALUES (N'2', N'1', N'2017-11-14 22:03:45.000', N'3', N'Dorce', N'Simatupang', N'Pejuang Jaya B 65', N'ID', N'JKT')
GO
GO
INSERT INTO [test].[customer_orders] ([Order_ID], [Product_ID], [Order_Date], [Customer_ID], [First_Name], [Last_Name], [Address], [Country], [City]) VALUES (N'3', N'1', N'2017-11-14 01:03:45.000', N'2', N'Elvis', N'Presley', N'Graceland', N'US', N'CA')
GO
GO
INSERT INTO [test].[customer_orders] ([Order_ID], [Product_ID], [Order_Date], [Customer_ID], [First_Name], [Last_Name], [Address], [Country], [City]) VALUES (N'5', N'2', N'2017-11-13 22:10:57.000', N'3', N'Dorce', N'Simatupang', N'Pejuang Jaya B 65', N'ID', N'JKT')
GO
GO
SET IDENTITY_INSERT [test].[customer_orders] OFF
GO


-- Table structure for customers


DROP TABLE [test].[customers]
GO
CREATE TABLE [test].[customers] (
[Customer_ID] int NOT NULL IDENTITY(1,1) ,
[First_Name] varchar(50) NOT NULL ,
[Last_Name] varchar(50) NULL ,
[Address] text NULL ,
[Country] char(2) NULL ,
[City] varchar(10) NULL
)

GO
DBCC CHECKIDENT(N'[test].[customers]', RESEED, 3)
GO


-- Records of customers


SET IDENTITY_INSERT [test].[customers] ON
GO
INSERT INTO [test].[customers] ([Customer_ID], [First_Name], [Last_Name], [Address], [Country], [City]) VALUES (N'1', N'Masino', N'Sinaga', N'Jln Cendrawasih I Blok B 65', N'ID', N'JKT')
GO
GO
INSERT INTO [test].[customers] ([Customer_ID], [First_Name], [Last_Name], [Address], [Country], [City]) VALUES (N'2', N'Elvis', N'Presley', N'Graceland', N'US', N'TN')
GO
GO
INSERT INTO [test].[customers] ([Customer_ID], [First_Name], [Last_Name], [Address], [Country], [City]) VALUES (N'3', N'Dorce', N'Simatupang', N'Pejuang Jaya B 65', N'ID', N'JKT')
GO
GO
SET IDENTITY_INSERT [test].[customers] OFF
GO


-- Table structure for products


DROP TABLE [test].[products]
GO
CREATE TABLE [test].[products] (
[Product_ID] int NOT NULL IDENTITY(1,1) ,
[Product_Name] varchar(100) NOT NULL
)

GO
DBCC CHECKIDENT(N'[test].[products]', RESEED, 3)
GO


-- Records of products


SET IDENTITY_INSERT [test].[products] ON
GO
INSERT INTO [test].[products] ([Product_ID], [Product_Name]) VALUES (N'1', N'Laptop')
GO
GO
INSERT INTO [test].[products] ([Product_ID], [Product_Name]) VALUES (N'2', N'Tablet')
GO
GO
INSERT INTO [test].[products] ([Product_ID], [Product_Name]) VALUES (N'3', N'Handphone')
GO
GO
SET IDENTITY_INSERT [test].[products] OFF
GO


-- Indexes structure for table cities



-- Primary Key structure for table cities


ALTER TABLE [test].[cities] ADD PRIMARY KEY ([City_Code])
GO


-- Indexes structure for table countries



-- Primary Key structure for table countries


ALTER TABLE [test].[countries] ADD PRIMARY KEY ([Country_Code])
GO


-- Indexes structure for table customer_orders



-- Primary Key structure for table customer_orders


ALTER TABLE [test].[customer_orders] ADD PRIMARY KEY ([Order_ID])
GO


-- Indexes structure for table customers



-- Primary Key structure for table customers


ALTER TABLE [test].[customers] ADD PRIMARY KEY ([Customer_ID])
GO


-- Indexes structure for table products



-- Primary Key structure for table products


ALTER TABLE [test].[products] ADD PRIMARY KEY ([Product_ID])
GO


Webmaster
User
Posts: 9425

Post by Webmaster »

If you are a registered user of v2018 you can click Tools -> Update Template to update to the latest template.


mobhar
User
Posts: 11660

Post by mobhar »

Thank you. Now it works properly.

Make sure you have already done the hard-refresh/reload from your browser, especially the generated "ewp14.js" file.


Post Reply