Lookup Fields in TextFields and ComboBoxes
Displaying Lookup Values in Two TextFields
Displaying Lookup Values in One ComboBox
Displaying Lookup Fields in ComboBox and TextField
Changing the ComboBox List Values Dynamically and Conditionally
Lookup Fields Overview
Lookup Fields - the fields in the form that display the values using the Primary Key and Foreign Key relationship. Lycia LowCode can use both TextField and ComboBox for this purpose so you can create Lookup Fields where you need them.
There are three common ways of showing Lookup Fields (descriptive text) in forms / windows:
- Two TextFields (for example, [contact.id] and [contact.name]);
- One ComboBox (for example, ComboBox with [contact.name] or [contact.id], or [contact.name-contact.id], or [contact.id-contact.name];
- One ComboBox and one TextField (for example, with [contact.id] in ComboBox and [contact.name] in TextField).
To control the Lookup Fields, add a corresponding screen record to the form.
Displaying Lookup Values in Two TextFields
Step 1. Specify the main table screen record for Lycia LowCode interaction in form .fm2 file:
<ScreenRecord identifier="$primary$contact" fields="contact.cont_id,...,..,company.comp_name"/>
Code snippet 1: Specifying the main screen record
Step 2. Add a screen record for each Lookup Field:
Screen record format:
<ScreenRecord identifier=<field to be populated> fields=<FK field><lookup table: PK column>/>
Example program: db_cms_company_lookup1.
<ScreenRecord identifier="operator.name" fields="company.acct_mgr,operator.operator_id"/>
<ScreenRecord identifier="industry_type.itype_name" fields="company.comp_industry,industry_type.industry_type_id"/>
<ScreenRecord identifier="company_type.ctype_name" fields="company.comp_type,company_type.company_type_id"/>
<ScreenRecord identifier="contact.cont_name" fields="company.comp_main_cont,contact.cont_id"/>
Code snippet 1: Populating the TextFields with Lookup Values
Image 1: Displaying the Lookup values in TextFields
Displaying Lookup Values in One ComboBox
Example Program: db_cms_company_lookup2
Example form: db_cms_company_lookup2/db_cms_company_lookup2_rec.fm2
<form.screenRecords>
<ScreenRecord identifier="$primary$company" fields="company.comp_id,company.comp_name,company.comp_addr1,company.comp_addr2,company.comp_addr3,company.comp_city,company.comp_zone,company.comp_zip,company.comp_country,company.acct_mgr,operator.name,company.comp_link,company.comp_notes,company.comp_industry,industry_type.itype_name,company.comp_priority,company.comp_type,company_type.ctype_name,company.comp_main_cont,contact.cont_name,company.comp_url"/>
<!-- ComboBox Value -->
<ScreenRecord identifier="operator.name" fields="company.acct_mgr,operator.operator_id"/>
<ScreenRecord identifier="industry_type.itype_name" fields="company.comp_industry,industry_type.industry_type_id"/>
<ScreenRecord identifier="company_type.ctype_name" fields="company.comp_type,company_type.company_type_id"/>
<ScreenRecord identifier="contact.cont_name" fields="company.comp_main_cont,contact.cont_id"/>
<!-- Populate the list for 4 comboBoxes -->
<ScreenRecord identifier="company.acct_mgr" fields="operator.operator_id,operator.name"/>
<ScreenRecord identifier="company.comp_industry" fields="industry_type.industry_type_id,industry_type.itype_name"/>
<ScreenRecord identifier="company.comp_type" fields="company_type.company_type_id,company_type.ctype_name"/>
<ScreenRecord identifier="company.comp_main_cont" fields="contact.cont_id,contact.cont_title,contact.cont_fname,contact.cont_lname"/>
</form.screenRecords>
Code snippet 2: Populating the ComboBoxes with Lookup Values
Image 2: Displaying the Lookup values in TextFields
Displaying Lookup Fields in ComboBox and TextField
You can set the Foreign Key value display in a folded ComboBox, Foreign Key and / or Lookup text in the unfolded list, and the Lookup text in a separate TextField.
Example Program: db_cms_company_lookup3
Example form: db_cms_company_lookup2/db_cms_company_lookup3_rec.fm2
<form.screenRecords>
<ScreenRecord identifier="$primary$company" fields="company.comp_id,company.comp_name,company.comp_addr1,company.comp_addr2,company.comp_addr3,company.comp_city,company.comp_zone,company.comp_zip,company.comp_country,company.acct_mgr,operator.name,company.comp_link,company.comp_notes,company.comp_industry,industry_type.itype_name,company.comp_priority,company.comp_type,company_type.ctype_name,company.comp_main_cont,contact.cont_name,company.comp_url" />
<ScreenRecord identifier="operator.name" fields="company.acct_mgr,operator.operator_id" />
<ScreenRecord identifier="industry_type.itype_name" fields="company.comp_industry,industry_type.industry_type_id" />
<ScreenRecord identifier="company_type.ctype_name" fields="company.comp_type,company_type.company_type_id" />
<ScreenRecord identifier="contact.cont_name" fields="company.comp_main_cont,contact.cont_id" />
<!--Populate the list for 4 comboBoxes-->
<ScreenRecord identifier="company.acct_mgr" fields="operator.operator_id,operator.name" />
<ScreenRecord identifier="company.comp_industry" fields="industry_type.industry_type_id,industry_type.itype_name" />
<ScreenRecord identifier="company.comp_type" fields="company_type.company_type_id,company_type.ctype_name" />
<ScreenRecord identifier="company.comp_main_cont" fields="contact.cont_id,contact.cont_title,contact.cont_fname,contact.cont_lname" />
</form.screenRecords>
Code snippet 3: Populating the TextFields and ComboBoxes with Lookup Values
Image 3: Displaying the Lookup values in a ComboBox and a TextField
Changing the ComboBox List Values Dynamically and Conditionally
The Foreign Key Lookup ComboBox items (dropdown list values) can also be changed conditionally / dynamically in two ways:
- Function call to change the contents / data;
- Conditional change based on a value of another field of the same form (within the same INPUT / DISPLAY scope).
Function Call
Here, we’ll be using a method PopulateComboBoxWhere(arg1,arg2), which provides a WHERE clause:
CALL iform.PopulateComboBoxWhere(<comboBox identifier>,<table.column> <where clause>)
Example Program: db_cms_aaa_dynamic_combo
FUNCTION before_field_comp_type(iform InteractForm INOUT) RETURNS BOOL
DEFINE priority INT
DEFINE dlg ui.Dialog
DEFINE regex util.REGEX
DEFINE match util.MATCH_RESULTS
DEFINE l_comp_industry LIKE company.comp_industry #comp_industry
LET dlg = ui.Dialog.GetCurrent()
LET l_comp_industry = iform.GetFieldValue("company.comp_industry")
CASE l_comp_industry
WHEN 2 #Software Reseller
CALL iform.PopulateComboBoxWhere("company.comp_type", "company_type.company_type_id IN (1,3,8,9)") #1Reseller 3=Distributor 8=Supplier 9=VAR
WHEN 8 #Manufacturing
CALL iform.PopulateComboBoxWhere("company.comp_type", "company_type.company_type_id IN (2,4)") #2=Enduser 4=Educational
WHEN 13 #Software Distributor
CALL iform.PopulateComboBoxWhere("company.comp_type", "company_type.company_type_id IN (1,3,8,9)") #1Reseller 3=Distributor 8=Supplier 9=VAR
OTHERWISE
CALL fgl_winmessage("ERROR","Invalid Company Industry\n(Internal 4gl error)","ERROR")
END CASE
RETURN TRUE
END FUNCTION
Code snippet 4: Populating the TextFields and ComboBoxes with Lookup Values
When the user changes the field Industry Type, the dropdown list of Company Type will change accordingly.
|
Industry Type (Key / Label) |
Possible Company Types Displayed in the Dropdown List |
4GL Code |
|
2 #Software Reseller |
(1,3,8,9) #1Reseller 3=Distributor 8=Supplier 9=VAR |
iform.PopulateComboBoxWhere("company.comp_type", "company_type.company_type_id IN (1,3,8,9)") |
|
8 #Manufacturing |
(2,4) #2=Enduser 4=Educational |
iform.PopulateComboBoxWhere("company.comp_type", "company_type.company_type_id IN (2,4)") |
|
13 #Software Distributor |
(1,3,8,9) #1Reseller 3=Distributor 8=Supplier 9=VAR |
iform.PopulateComboBoxWhere("company.comp_type", "company_type.company_type_id IN (1,3,8,9)") |
Table 1: Function call with WHERE clause
Image 4: Selecting the keys for comp_type filtering
Image 5: The list of available comp_type options based on a WHERE clause
Conditional Change
The conditional change utilises an additional screen record for a condition based on a field value in the form.
Example program: fk_lookup_conditional.
<!--Dynamic Conditional FK Lookup - in our case, we link states based on the country-->
<!--Key is the $FILTER$ in the screen record identifier-->
<ScreenRecord identifier="company.comp_country" fields="country2.country_code,country2.country_text" />
<ScreenRecord identifier="company.comp_zone" fields="state.state_code_iso3661,state.state_text_enu" />
<ScreenRecord identifier="$filter$company.comp_zone" fields="company.comp_country,state.country_code" />
Code snippet 5: Creating a conditional change in the .fm2 form file
Example: comp_country and comp_zone
Image 6: Selecting a Lookup value
Image 7: Values filtered based on a condition
See next: Virtual / Phantom Fields Support