(RPG) Two related dropdowns on an offline form view

[LEBAS]

On an offline form view, I want to maintain an employee (which belongs to a department/section).

To facilitate the choice of the department/section to the user, I can display one dropdown with existing departments and one dropdown with existing sections. These dropdowns are populated/binded using local SQLite tables (Department and Section):

EZI_BindProperty( P1_DEPTMENT : 'VALUE' : 'FLD' : 'DEPTMENT');
EZI_BindProperty( P1_SECTION : 'VALUE' : 'FLD' : 'SECTION');
…
EZI_BindProperty( P1_DEPTMENT : 'ITEMTEMPLATE.VALUE'
: 'FLD' : 'DEPTMENT' );
EZI_BindProperty( P1_DEPTMENT : 'ITEMTEMPLATE.TEXT'
: 'FLD' : 'DEPTDESC' );
EZI_BindProperty( P1_DEPTMENT : 'ITEMLIST' : 'SQL'
: 'SELECT ROWID, DEPTMENT, DEPTDESC FROM Deptment');

EZI_BindProperty( P1_SECTION : 'ITEMTEMPLATE.VALUE'
: 'FLD' : 'SECTION' );
EZI_BindProperty( P1_SECTION : 'ITEMTEMPLATE.TEXT'
: 'FLD' : 'SECDESC' );
EZI_BindProperty( P1_SECTION : 'ITEMLIST' : 'SQL'
: 'SELECT ROWID, SECTION, SECDESC +
FROM Section'); // this display all Section records…

But I want to display in the section’s dropdown only the related sections of the displayed department. Any ideas how to achieve it?

Full RPG source is attached.

Yann

[tsupartono]

Provided that the user/employee table is called EMPLOYEE:
In the section dropdown, we want to include only those sections that match the department of the employee you are currently editing.

The where clause would look something like this:

SELECT rowid, section, secdesc
FROM section
WHERE deptment = &(FLD:EMPLOYEE.deptment)

The first part (EMPLOYEE) in the notation &(FLD:employee.deptment) specifies where the field DEPTMENT should come from.
In this case, you want the field DEPTMENT to comes from a binding context called EMPLOYEE.
Binding context is formed when you bind a table or view with an element.
Any elements defined within another element (e.g elements within a form) can refer to the binding contexts of any of its parent elements.
Binding contexts are identifiable using their names. By default, when you bind a table to an element, the name of the binding context would be the name of the table. That's the reason why we can specify 'EMPLOYEE' in the &(FLD:EMPLOYEE.deptment) expression without having to explicitly specify a binding context name.

You can also designate a different name (alias) to a binding context. Setting up an alias is also mandatory if your view is a query (SELECT statement).
To designate an alias, use the 'AsView' binding property.

Example, we want to name our binding context USER (as opposed to the default EMPLOYEE).
LRNG_SetProperty('/Form.&.TBL' : Employee');
LRNG_SetProperty('/Form.&.AsView' : 'User');

You can then later refer to this binding context as 'USER' as opposed to 'EMPLOYEE':

WHERE deptment = &(FLD:USER.deptment)

[LEBAS]

Thanks Tony. it works well.