Master - Detail Grid

Let's say we want to have detail grid under HR employee grid which shows engineering job candidates that this particular HR personnel is interviewing (1-N relationship).

Open SQL DDL Command for VCS and write this command for creating eng_candidate table. Click save (don't forget to check if Run Local? is ticked).

create table eng_candidate(
candidate_id integer not null,
employee_id integer not null,
name character varying(1024) not null,
surname character varying(1024) not null,
lkp_gender integer,
birthdate_dt date,
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_eng_candidate PRIMARY KEY (candidate_id));
create sequence seq_eng_candidate;

As you might have noticed, employee_id is foreign key that connects eng_candidate to hr_employee. Go to DB Tables and click burn button to pull this newly created table from database. It prompts for table name, write eng_candidate and press ok.

Using Form Wizard create form and grid from table eng_cadidate. You can skim chapter Form and Grid to remember how it's done.

There is a problem with our form frm_eng_candidate1. We must remove employee_id input from UI.

While we are creating a new candidate record we don't want employee id to be shown on UI. We still need it to be included in CRUD operation but its value must not be taken from user.

Go to Form Elements, double click on employee_id, change its UI Component to none and click update.

We should also remove it from grid. Go to Grid Columns of grd_eng_candidate1, select employee_id and click on button.

Another important thing we must do is to make employee_id not updateable. Go to Table Fields of eng_candidate, double click employee_id, and uncheck Can Update? property.

We will get list of candidates in bottom (detail) grid according to employee_id, master grid's primary key. So, let's add employee_id as query parameter to qry_eng_cadidate1. Go to Query Parameters of qry_eng_candidate1 , click on , then Insert Query Params From Query Fields.

Select employee_id, click insert, then close.

Now, we need to add candidate grid as detail grid to our page HR_personnel. Go to Page Objects of HR_personnel and click button. grd_hr_employee1 is automatically selected as parent because it is first and only grid there. Select grd_eg_candidate1 as object, employee_id as master field and then click save.

We have two grids with Master - Detail relationship.