CODEWITHSUNDEEP
oracle-apex
Eyei
Eyei

Reputation: 1

How to display and update the records I have added/insert in the Apex interactive report

I've created an interactive report to add/update/delete employee info records in custom db table.

My end user requirement is upon entering the employee number in the EMPNO field, all the details from the oracle standard table such as employee name, marital status, gender, bday must be auto generated in the form and they will only manually input the location name and mode of exit.

Now I created 3 pages: 1.home page, 2.add page, 3.update page

On page 1 (home page) I have here the select SQL with checkbox

On page 2 (add page), In the page processing portion I have

EventAddRecord Source: db , plsql code Here's my sample code:

BEGIN

INSERT INTO EMPINFOTBL
(PERSON_ID,
EMPLOYEE_NUM,
EMPLOYEE_NAME,
MARITAL_STATUS,
BDATE,
GENDER,
LOCATION_NAME,
MODE_OF_EXIT
)
VALUES
(SELECT PERSON_ID FROM
PER_PEOPLE_X WHERE EMPLOYEE_NUM = :P2_EMPLOYEE_NUM),
(SELECT EMPLOYEE_NUMBER ID FROM
PER_PEOPLE_X WHERE EMPLOYEE_NUM = :P2_EMPLOYEE_NUM),
(SELECT FULL_NAME FROM
PER_PEOPLE WHERE EMPLOYEE_NUM = :P2_EMPLOYEE_NUM),
(SELECT MARITAL_STATUS FROM
PER_PEOPLE WHERE EMPLOYEE_NUM = :P2_EMPLOYEE_NUM),
(SELECT DATE_OF_BIRTH FROM
PER_PEOPLE WHERE EMPLOYEE_NUM = :P2_EMPLOYEE_NUM),
(SELECT GENDER FROM
PER_PEOPLE WHERE EMPLOYEE_NUM = :P2_EMPLOYEE_NUM),
:P2_LOCATION_NAME,
:P2_MODE_OF_EXIT
);
END;

Buttons SAVE with dynamic action When click add button if True submit page

Now my problem is how to do the update when I click the check box, I want my record to be displayed in the form because currently when I click the check box the form is null

Upvotes: 0

Views: 702

Answers (2)

Koen Lostrie
Koen Lostrie

Reputation: 18630

@Littlefoot has given a perfect answer already, here are just some extra steps that might guide you to a solution (it's the "or start over with the wizard" piece from Littlefoot's answer). I'd suggest looking how apex generates its pages when you do it out of the box. Just for testing, follow these steps

  • Create a new page of type "Interactive Report" and make sure to check the "Include Form Page" attribute
  • Give the form page a name and select "PER_PEOPLE" as table/view name
  • In the 2nd page of the dialog, select the primary key column of PER_PEOPLE: person_id
  • Click "Create Page"

You now have a working form and report that you can further customize to your specific requirements. It should give you a good idea of how a form and a report is generally configured in APEX and it saves you a ton of time

Notices how in the report page:

  • The edit link has the form page as target and passes the id
  • The CREATE button has the form page as target without the id

In the form page

  • No custom code is needed to initialize the form data for the current record. Instead the native process of type "Form - Initialization" is used.
  • No dynamic actions are used to perform the inserts - for a form that is a bad practice. Avoid it.
  • No custom code is needed to perform the inserts or update. Instead the native process of type "Form - Automatic Row Processing" is used.

Study these pages and apply similar logic to your own pages. It'll be a better app.

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142705

To create a new record (using Page 2), you'd create a select list item which is based on per_people table; you'd display any information you want, but you'd return employee ID:

select full_name    as display_value,
       employee_num as return value
from per_people
order by full_name;

After selecting desired person, enter values into P2_LOCATION_NAME and P2_MODE_OF_EXIT items. When you hit the "Save" button (which submits the page), run the process (I modified what you wrote; should be way simpler):

INSERT INTO empinfotbl (person_id,
                        employee_num,
                        employee_name,
                        marital_status,
                        bdate,
                        gender,
                        location_name,
                        mode_of_exit)
   SELECT person_id,
          employee_number,
          full_name,
          marital_status,
          date_of_birth,
          gender,
          :P2_LOCATION_NAME,
          :P2_MODE_OF_EXIT
     FROM per_people
    WHERE employee_num = :P2_EMPLOYEE_NUM;

As of updating existing values: I'd again suggest you to use the Wizard as it creates everything you need - form page is based on empinfotbl table, while "Edit" button in Interactive report sends the ID value to form page whose pre-rendering process fetches data related to employee identified by passed ID.

If you created your own page, you'll have to do it all yourself.

You said:

I want my record to be displayed in the form because currently when I click the check box the form is null

Form items are empty because Apex didn't know what to fetch. As I said: pass ID value, create pre-rendering process. Or start over with the Wizard (I prefer that option).

Upvotes: 1

Related Questions