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.

GUI Development

Advanced GUI Development: Developing Beyond GUI

Multi-form Applications

Advanced Forms Programming

Error-Message Handling

Object-oriented Methods in Forms

Intelligence in Forms

Additional Interesting Techniques

Working with Trees

Oracle 8 and 8i Features in Forms Developer

Oracle Developer Forms Techniques
Oracle Developer Forms Techniques
ISBN: 0672318466
EAN: 2147483647
Year: 2005
Pages: 115

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net