How to Maintain State in an Oracle ApEx Form

Question

I have hit a snag and I'm unsure on what the best way to tackle this issue using Oracle ApEx.

Basically I have a form that is based off Table1 but at the same time, a section/region within this form branches to another page where it then adds details into Table2.

To expand on this with a better example, let's say that I have a table called CUSTOMER which holds personal details of the customer, such as CUST_ID, FIRSTNAME, SURNAME, DOB and then I also have another table called PURCHASES with the following columns, PURCHASE_ID, CUST_ID, ITEM, ITEM_DESCR, COST with CUST_ID being a foreign key to CUSTOMERS table.

I now want to create an Oracle Apex form based off the table CUSTOMER but within this same form, prior to the user submitting and committing the form to the database, I also want to allow the user to add all the purchases the customer was buying at the store by a means of some sort. Once the user has entered the customer details together with say 5 purchase records (in some way), all is submitted and committed to the database for both the CUSTOMER and PURCHASES tables.

Is there a way around this or possibly other alternatives around this type of situation?

To further clarify, when you say "a form based off the table CUSTOMER" do you mean a simple data entry form for creating one customer? — Tony Andrews
Yes Tony but on this same form, I would like to have a region of some type to allow the user to enter one or more purchases a customer may have but I want this information always displayed within this region in the customer form and then the user commits/submits once with captures CUSTOMER and PURCHASES tables. Hope this makes sense. — tonyf
Have a look at apex.oracle.com/pls/otn/f?p=36342:6 - is this what you want? The first page is just to show the data created by page 7 (press Create button to go there). Page 7 is "your" page. When you have entered a customer and some purchases, press Submit. You should see the new customer you created; click on their name an the purchases will be shown below. — Tony Andrews
If that's what you want then tomorrow I can go through how I did it (I have to go now). — Tony Andrews
Hey Tony - really appreciate your help and thanks for the example. Based on your example, I am assuming you have used a Master-Detail setup within ApEx - is this correct? Based on your example, how would I go about editing a purchase record? If you have not used a Master-Detail setup then I would really appreciate what approach you used? Thanks again Tony. — tonyf

Tony Andrews Tony Andrews · Accepted Answer · 2011-05-18T11:19:48

Instead of redirecting to page 2 to enter the Table 2 info you need to submit page 1 and branch to page 2. This will save the session state for page 1. You don't want the page 1 data updated into Table1 yet, so ensure that the process that performs the insert/update is not invoked by this submit - e.g. make it conditional on the Save button or whatever.

Alternatively, do it all on one page like my example here. The page of interest is page 7 which you reach via the Create button. Page 7 has a kind of master/detail form, but I didn't build it using the Apex wizards - I'm not sure whether you could, but I tend not to use the wizards and built-in form-on-table tools in my work, so I am not sure.

This is what I did for page 7:

1) Created a blank page

2) Added an HTML region "New Customer" and added a text item for Surname and a date item for DOB.

3) Created a report region "Purchases" with the following SQL:

select apex_item.text(1,'') item_desc
,      apex_item.text(2,'') cost
from   dual
connect by level <= 5

The CONNECT BY clause is a trick to generate 5 dummy rows from DUAL. The apex_item package generates tabular form items - in this case 2 text items for item_desc and cost, with '' as the initial value.

4) Created 2 buttons: SUBMIT - submits the page CANCEL - redirects to page 6

5) Created a PL/SQL process to run on submit as follows:

declare
  l_cust_id integer;
begin
  -- Insert the new customer
  insert into so_customers (surname, dob)
  values (:p7_surname, :p7_dob)
  returning cust_id into l_cust_id;

  -- Insert the purchases (if data has been entered)
  for i in 1..apex_application.g_f01.count loop
    if apex_application.g_f01(i) is not null then
      insert into so_purchases (cust_id, item_desc, cost)
      values (l_cust_id, 
              apex_application.g_f01(i),
              apex_application.g_f02(i)
             );
    end if;
  end loop;
end;

I made that process conditional on the SUBMIT button. The apex_application package contains array variables g_f01, g_f02 etc. that correspond to the tabular form columns.

6) Created a branch that goes back to page 6 when SUBMIT is pressed.

If you want functionality that deviates from the "out of the box" behaviour provided by Apex's page and region wizards you have to write more complex code, as you can see. But pretty much anything you want to do can by done this way.

How to Maintain State in an Oracle ApEx Form

1 Answers