In this chapter, we will show how to create a drop-down list element that gets its data by querying a table.
Let's create HR department form which will have drop-down list of HR employees. Below is the SQL command we used to create table and form (we don't need grid). By now, you should be able to create a table and then a form from that table . You can consult previous chapters for help if you need.
create table hr_department (department_id integer not null,department_code character varying(1024) not null,department_name character varying(1024) not null,responsible_personnel_id integer not null,version_no integer NOT NULL DEFAULT 1,insert_user_id integer NOT NULL DEFAULT 1,insert_dttm timestamp without time zone NOT NULL DEFAULT ('now'::text)::timestamp without time zone,version_user_id integer NOT NULL DEFAULT 1,version_dttm timestamp without time zone NOT NULL DEFAULT ('now'::text)::timestamp without time zone,CONSTRAINT pk_hr_department PRIMARY KEY (department_id));create sequence seq_hr_department;
We need to create lookup query from
hr_employee table. Go to DB Tables, right click on hr_employee, Convert, and select Table -> Lookup Query for Form Element.
Make a slight change to select part and click save.
x.name dsc, employee_id id
Go to Query Fields of
lookup_hr_employee and click on burn button to generate query fields.
Now, go to Form Elements of
frm_hr_department1 and double click
responsible_personnel_id. Set UI Component property as Combo: Lookup Query and select
lookup_hr_employee as Lookup , then click update.
hr_employee table has two records: iCode and Better. When you open preview of
frm_hr_department form you can see them in Personnel (responsible_personnel_id) drop-down list.