Lookup Query

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.