Ordering by Foreign Key Items in Oracle D2k Forms
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:
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.
CREATE a nonbase item DEPT_NAME in the EMPLOYEE block (which is based on the EMPLOYEE table).
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.
Create a POST-QUERY trigger for the EMPLOYEE block as follows:
:employee.dept_name := populate_dept_name(:employee.dept_id);
On querying, it can be seen that the records in the block are ordered by DEPT_NAME.