8 Replies Latest reply on Oct 8, 2008 6:34 PM by 656867

    Pre-populating form fields - data from multiple tables

    656867
      Hello:

      I'm having trouble pre-populating a form with fields that are derived from two tables which will allow my users to update the information.

      I have two pages. One page is a report which displays information from two tables and has an edit link for each record. The second page is a form which should have the fields pre-populated when the edit link is clicked.
      The form pre-populates only one field.

      Table 1 is a department table with the following fields: deptno(PK), deptname, location.
      Table 2 is an employee table with the following fields: empid(PK), deptid, empname.

      My report page has the following select statement to display the information from both tables:
      select a.deptno,
             a.deptname,
             a.location,
             b.deptid,
             B.empid,
             b.empname
      from test1 a, test2 b
      where a.deptno=b.deptid
      In my Report Attributes tab, the Edit link is set on DEPTNO.

      My form page has a process defined to fetch rows from a table. Apparently I can't seem to figure out if there is a way to fetch from multiple tables. Therefore, I’m using the department table as my table, for Item Containing Primary Key is set to P2_DEPTID, and Primary Key Column is deptno. The process point is After Header.

      For simplicity, I will use only one item as an example from the Items section in Page Rendering. The item is P2_LOCATION: Under Source, I have Always Replacing…for Source Used; Database Column for Source Type, and LOCATION for Source Value.

      I have no difficulty in pre-populating form fields coming all from one table. But, when I need to pre-populate a form which has two or more tables, I cannot get the data into the fields.

      Is there a “trick” I’m missing to get this accomplished?

      I hope someone can help me out.
      Thanks.
        • 1. Re: Pre-populating form fields - data from multiple tables
          628347
          Hi nyc,

          * Go to the details of the items that are not populated, and go to the "Source" section. In there change the source to Database column.
          * Next, type in the 1st Expression the name of the database column you want to map in the item.
          * Make sure that the Page Rendering Process also fetches the right columns, so that it can be matched with the items on your screen.

          This should work for you,
          Greetings,
          Rutger
          • 2. Re: Pre-populating form fields - data from multiple tables
            636928
            Hi,

            You can try creating a manual tabular form, which will give you more control over the form page and you can populate the form based upon your input values.

            Have a look at this http://www.oracle.com/technology/products/database/application_express/howtos/tabular_form.html

            Regards,
            Manish
            • 3. Re: Pre-populating form fields - data from multiple tables
              656867
              Hi,

              Everything you suggested is set. All of my Items are set to use the Database Column as the Source. And the value in the 1st Expression is set to the database column I want to map.

              The issue is that the process to fetch the rows asks for only one table; therefore only the fields found in that one table are populated. The fields that come from the second table are not because the process isn't looking for the second table.

              How can I write a process which will allow me to see both tables?
              • 4. Re: Pre-populating form fields - data from multiple tables
                628347
                Yeah I see,

                Then I really suggest you use a view for this. Create the view for the two tables, and create a BEFORE INSERT,UPDATE,DELETE trigger that manages your DML actions accordingly on the database. Then you can create a form on a report based on the view.

                Greetings,
                Rutger
                • 5. Re: Pre-populating form fields - data from multiple tables
                  656867
                  If I create a view which holds the fields from both tables and then I create my trigger for each DML action I want, will the data be affected in the view or in the actual source tables?

                  For example, if I want to update a record, will the record be updated only in the view or can I write the update statement for the actual table?

                  Also, if records in the source tables get updated nightly, will my view also be updated or do I have to refresh the view all the time?
                  • 6. Re: Pre-populating form fields - data from multiple tables
                    664103
                    Hi there

                    Answering your question, yeah you can write INSTEAD OF triggers on the view, within which you can then do your inserts or updates on the actual tables.

                    CREATE OR REPLACE TRIGGER updTrgTriggerA
                    INSTEAD OF UPDATE
                    ON <Schema.VIEWNAME>
                    REFERENCING NEW AS New OLD AS Old
                    FOR EACH ROW
                    DECLARE
                    ...

                    BEGIN
                    ..
                    ..
                    ..
                    ..
                    EXCEPTION
                    WHEN integrity_error
                    THEN
                    raise_application_error ("ERROR");
                    WHEN OTHERS
                    THEN
                    -- Consider logging the error and then re-raise
                    RAISE;
                    END;

                    ==================


                    CREATE OR REPLACE TRIGGER insTrgTriggerB
                    INSTEAD OF INSERT
                    ON <Schema.VIEWNAME>
                    REFERENCING NEW AS New OLD AS Old
                    FOR EACH ROW
                    DECLARE
                    ...

                    BEGIN
                    ..
                    ..
                    ..
                    ..
                    EXCEPTION
                    WHEN integrity_error
                    THEN
                    raise_application_error ("ERROR");
                    WHEN OTHERS
                    THEN
                    -- Consider logging the error and then re-raise
                    RAISE;
                    END;

                    However the issue with such triggers is that, if you modify your view, these triggers get automatically dropped and you will have to recreate them again... so better have the backup scripts with you.

                    Muzammil
                    • 7. Re: Pre-populating form fields - data from multiple tables
                      656867
                      Hello:

                      Thank you for the information. I agree that I would need to be careful with views changing. That's why I was hoping not to go that route.

                      It is quite frustrating that a simple task of being able to edit a record, where info comes from different tables, is difficult to do.
                      • 8. Re: Pre-populating form fields - data from multiple tables
                        656867
                        Hi,

                        I think I figured out a way to accomplish this. Since I'm new to APEX, I'm not sure whether this is a good way to go.

                        For simplicity, I'm using the tutorial tables that came with APEX.

                        In my app, I clicked on the item which comes from another table in the Items section.
                        Under Source, I did the following:
                        Source Used: Always, replacing......
                        Source Type: SQL Query
                        Source Value/Expression:
                        select department_name
                        from oehr_departments
                        where manager_id = :P4_employee_id
                        I applied these changes, ran my report, clicked the Edit icon, and the fields were all pre-populated.

                        So, I achieved what I wanted to achieve but I don't know whether this is a correct method.