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:
:employee.dept_name := populate_dept_name(:employee.dept_id);
Advanced GUI Development: Developing Beyond GUI
Advanced Forms Programming
Object-oriented Methods in Forms
Intelligence in Forms
Additional Interesting Techniques
Working with Trees
Oracle 8 and 8i Features in Forms Developer