Pass values between pages in Oracle Apex

Question

I am developing a mobile application that should allow a student to search for job vacancies. I have used the wizard to create a form with a list view. The list view will only show job titles which are not past their closing date (available jobs)

When a job title is clicked it redirects to the form where the student can view further job details. These values are passed automatically by the wizard.

Now while this whole thing is great I need information from 3 different tables and the wizard won't help me with that.

I have created a list view based on an sql query and also a form based on an sql query. I have tried to create automatic fetch processes to pass the values from my list view to the form view but nothing I have tried has worked. I carefully analysed the forms created by the wizard to see how it could be done but nothing worked for me and I would really love to do it in this way.

For reference this is the sql code I used for the list view (and it's the same for the form view except for the where clause )

     T1.JOB_TITLE,
        T1.SALARY,
        T1.JOB_DESCRIPTION,
        T1.START_DATE,
        T1.CLOSING_DATE,
        T1.METHOD_ID,
        T3.METHOD_NAME,
        T1.SITE_ID,
        T2.CITY,
        T2.ADDRESS_FIRST_LINE,
        T2.EMAIL,
        T2.COMPANY_NAME

FROM JOB T1

JOIN SITE T2 ON (T2.SITE_ID = T1.SITE_ID)

JOIN APPLICATION_METHOD T3 ON (T3.METHOD_ID = T1.METHOD_ID)
Where (T1.Closing_Date >(Select Current_Date from dual)) 

Answer 1

There are really two ways you could solve this:

1) If you can simply pass the values you need to the form page, edit the values of your Form region, and open up the "Link Target" attribute (this is assuming you're using APEX 5 Page Designer). There, you will be able to pass in multiple values to items on your Form page.

2) If, instead, you need to derive these values on your Form page, add an After Header process on your form page and do the lookups from your other tables in this process, using PL/SQL. You can use the bind variable syntax to reference your items and update session state. For example:

begin
    for c1 in (select val1, val2 from my_other_table where id = :P3_ID) loop
        :P3_ITEM1 := c1.val1;
        :P3_ITEM2 := c1.val2;
        exit;
    end loop;
end;

Answer 2

I managed to add the additional columns by adding more items and selecting an Sql-query that returns a single row from the item attributes (Source).

So in order to get method_name rather than an ID which would be irrelevant for the end user I used this code:

SELECT METHOD_NAME FROM APPLICATION_METHOD
WHERE (METHOD_ID = :P3_METHOD_ID)

I am fairly certain that it might not be a great solution if you have a lot of columns that need to go through but it was easy to understand and implement for a few additional columns.