Populating an Extender Lookup Form with data from another table

In a recent project, we wanted to create a custom lookup to be used in Extender on various windows and forms. To achieve this, we used an Extender Form.

Out of the box, users would normally populate the form manually with the values they wish other users to select from. In our case, there was a large amount of data – and it was already being maintained within the WennSoft Job Costing module. From a user experience standpoint, as people to maintain the same data in 2 different spots just wasn’t a good idea.

After creating the Extender Form to be populated, we were ready to start writing code. In this case, our Form ID was EXTCCLOOKUP, and our source data was coming from JC40202 in the same company database. You should be able to modify the code to use your own Form ID and source queries. You can also download the entire code.

The first thing to do was populate the form with existing data from the source table:


-- This query inserts the Extender "ID Field" value
INSERT EXT00201(Extender_Form_ID, UD_Form_Field_ID, UD_Form_Field_ID_2,PT_UD_Number,STRGA255)
SELECT 'EXTCCLOOKUP', LTRIM(RTRIM(Cost_Code_Alias)), '', -12, ''
FROM JC40202
WHERE LTRIM(RTRIM(Cost_Code_Alias)) NOT IN(SELECT UD_Form_Field_ID FROM EXT00200 WHERE Extender_Form_ID = 'EXTCCLOOKUP')

— This query inserts the Extender “Description” value
INSERT EXT00201(Extender_Form_ID, UD_Form_Field_ID, UD_Form_Field_ID_2,PT_UD_Number,STRGA255)
SELECT ‘EXTCCLOOKUP’, LTRIM(RTRIM(Cost_Code_Alias)), ”, -11, LTRIM(RTRIM(Cost_Code_Description))
FROM JC40202
WHERE LTRIM(RTRIM(Cost_Code_Alias)) NOT IN(SELECT UD_Form_Field_ID FROM EXT00200 WHERE Extender_Form_ID = ‘EXTCCLOOKUP’)

— This Query inserts the Extender Form ID’s table record
INSERT EXT00200(Extender_Form_ID, UD_Form_Field_ID, UD_Form_Field_ID_2,UD_Form_Field_Desc, CREATDDT, CRUSRID, MDFUSRID, MODIFDT, NOTEINDX)
SELECT ‘EXTCCLOOKUP’, LTRIM(RTRIM(Cost_Code_Alias)), ”, LTRIM(RTRIM(Cost_Code_Description)), MODIFDT, ‘CONVERSION’, ”, MODIFDT, 0
FROM JC40202
WHERE LTRIM(RTRIM(Cost_Code_Alias)) NOT IN(SELECT UD_Form_Field_ID FROM EXT00200 WHERE Extender_Form_ID = ‘EXTCCLOOKUP’)

Next, we needed to add SQL Triggers to the source table so that as users added, removed, or modified Cost Codes, the Extender Form was being updated accordingly:


USE TWO
GO

IF EXISTS(SELECT * FROM sysobjects WHERE name = ‘bwsJC40202_TR_I’)
DROP TRIGGER bwsJC40202_TR_I
GO

CREATE TRIGGER bwsJC40202_TR_I
ON JC40202
FOR INSERT
AS

/*****************************************************************************************************
** bwsJC40202_TR_I
** Created By: Rod O’Connor, Briware Solutions
** Created Date: 11-Nov-2013
**
** Comments: In order to accommodate the Burden Custom application, we created a Cost Code Lookup
** form using Extender. This trigger will populate that lookup as new cost codes are
** added by the users.
**
*****************************************************************************************************/
BEGIN — TRIGGER

— Insert the Keys record
INSERT EXT00200(Extender_Form_ID, UD_Form_Field_ID, UD_Form_Field_ID_2,UD_Form_Field_Desc, CREATDDT, CRUSRID, MDFUSRID, MODIFDT, NOTEINDX)
SELECT ‘EXTCCLOOKUP’, LTRIM(RTRIM(Cost_Code_Alias)), ”, LTRIM(RTRIM(m.Cost_Code_Description)), m.MODIFDT, ‘TRIGGER’, ”, m.MODIFDT, 0
FROM inserted m
WHERE LTRIM(RTRIM(Cost_Code_Alias)) NOT IN(SELECT UD_Form_Field_ID FROM EXT00200 WHERE Extender_Form_ID = ‘EXTCCLOOKUP’)

— Create the Cost Code ID record
INSERT EXT00201(Extender_Form_ID, UD_Form_Field_ID, UD_Form_Field_ID_2,PT_UD_Number,STRGA255)
SELECT ‘EXTCCLOOKUP’, LTRIM(RTRIM(Cost_Code_Alias)), ”, -12, ”
FROM inserted i
WHERE LTRIM(RTRIM(Cost_Code_Alias)) NOT IN(SELECT UD_Form_Field_ID FROM EXT00201 WHERE Extender_Form_ID = ‘EXTCCLOOKUP’ AND PT_UD_Number = -12)

— Create the Description Record
INSERT EXT00201(Extender_Form_ID, UD_Form_Field_ID, UD_Form_Field_ID_2,PT_UD_Number,STRGA255)
SELECT ‘EXTCCLOOKUP’, LTRIM(RTRIM(Cost_Code_Alias)), ”, -11, LTRIM(RTRIM(i.Cost_Code_Description))
FROM inserted i
WHERE LTRIM(RTRIM(Cost_Code_Alias)) NOT IN(SELECT UD_Form_Field_ID FROM EXT00201 WHERE Extender_Form_ID = ‘EXTCCLOOKUP’ AND PT_UD_Number = -11)

END — TRIGGER

IF EXISTS(SELECT * FROM sysobjects WHERE name = ‘bwsJC40202_TR_U’)
DROP TRIGGER bwsJC40202_TR_U
GO

CREATE TRIGGER bwsJC40202_TR_U
ON JC40202
FOR UPDATE
AS

/*****************************************************************************************************
** bwsJC40202_TR_U
** Created By: Rod O’Connor, Briware Solutions
** Created Date: 12-Nov-2013
**
** Comments: In order to accommodate the Burden Custom application, we created a Cost Code Lookup
** form using Extender. This trigger will ensure that as users update the descriptions
** of the cost codes, the lookup is kept up to date.
**
*****************************************************************************************************/
BEGIN — TRIGGER

— Update the Keys record
UPDATE e
SET e.UD_Form_Field_Desc = LTRIM(RTRIM(i.Cost_Code_Description))
FROM EXT00200 e INNER JOIN inserted i ON e.UD_Form_Field_ID = LTRIM(RTRIM(Cost_Code_Alias)) AND e.Extender_Form_ID = ‘EXTCCLOOKUP’

— Update the Description record
UPDATE e
SET e.STRGA255 = LTRIM(RTRIM(i.Cost_Code_Description))
FROM EXT00201 e INNER JOIN inserted i ON e.UD_Form_Field_ID = LTRIM(RTRIM(Cost_Code_Alias)) AND e.Extender_Form_ID = ‘EXTCCLOOKUP’ AND e.PT_UD_Number = -11

END — TRIGGER

IF EXISTS(SELECT * FROM sysobjects WHERE name = ‘bwsJC40202_TR_D’)
DROP TRIGGER bwsJC40202_TR_D
GO

CREATE TRIGGER bwsJC40202_TR_D
ON JC40202
FOR DELETE
AS

/*****************************************************************************************************
** bwsJC40202_TR_D
** Created By: Rod O’Connor, Briware Solutions
** Created Date: 12-Nov-2013
**
** Comments: In order to accommodate the Burden Custom application, we created a Cost Code Lookup
** form using Extender. This trigger will ensure that as users delete any of the cost
** codes, the lookup is kept up to date.
**
*****************************************************************************************************/
BEGIN — TRIGGER

— Delete the Keys record
DELETE EXT00200
WHERE Extender_Form_ID = ‘EXTCCLOOKUP’
AND UD_Form_Field_ID IN(SELECT LTRIM(RTRIM(Cost_Code_Alias)) FROM deleted)

— Delete the ID and Description records
DELETE EXT00201
WHERE Extender_Form_ID = ‘EXTCCLOOKUP’
AND UD_Form_Field_ID IN(SELECT LTRIM(RTRIM(Cost_Code_Alias)) FROM deleted)

END — TRIGGER

The users now have a self maintaining custom lookup in Extender!

Our Blog