Ordering by Foreign Key Items in Forms

Items based on foreign key look-ups often figure in Forms applications. One of the features most admired in such applications is a sort order option for users to choose the data item to base the sorting order. This is often a feature required by end users and MIS managers alike. For example, an MIS manager might want to choose a sort order by the department name while tracking changes to his employees, department-wise. This seems so trivial on first thought but is not, when it comes to actual coding. The department name is generally a foreign key look-up item in the Employees screen and, therefore, a direct ORDER BY is not possible. This section presents a special technique to implement such ORDER -ing.

Use a stored function in the database, which retrieves the look-up value based on the foreign key column; specify this function in the ORDER BY clause for the corresponding block.

Consider a block based on the EMPLOYEE table with a nonbase table item DEPT_NAME in it. You want to order by DEPT_NAME, which is a look-up column (a nonbase table item in the block under consideration), from the DEPT table based on DEPT_ID in the EMPLOYEE table. The follow ing steps will do the job:

1. CREATE a stored function named POPULATE_DEPT_NAME(ip_dept_id IN NUMBER) that returns the DEPT_NAME corresponding to the parameter ip_dept_id.
2. CREATE a nonbase item DEPT_NAME in the EMPLOYEE block (which is based on the EMPLOYEE table).
3. In the block properties, for the ORDER BY clause specify POPULATE_DEPT_NAME(DEPT_ID). Note that the argument passed is the actual column name (that is, DEPT_ID ) in the DEPT table.
4. Create a POST-QUERY trigger for the EMPLOYEE block as follows :

   :employee.dept_name := populate_dept_name(:employee.dept_id);
   

5. On querying, it can be seen that the records in the block are ordered by DEPT_NAME.