X

The Oracle APEX blog is your source for APEX news, technical tips and strategic direction

Build, test and deploy apps on Oracle Cloud. Start Now

[Forms to APEX] Customizing the APEX Application - Customers Form

Monica Godoy
Principal Product Manager

In the last post in this series, I created the APEX application and showed you various options for the UI in the Customers and Orders Forms.
In this blog post, I'm going to focus on over the logic from the Customers Form and then I’ll make some additional enhancements to the application. 

Migrating the Business Logic

Let’s start by reviewing the following components in the Customers Form to see what actually needs to be migrated (applicable) vs. what was specific to Oracle Forms (not applicable):

  • DB Blocks
  • Items
  • Triggers
  • List of Values 
  • Alerts
  • Programs Units

After you review the objects, remember to set them as Complete.

DB Blocks

There are five blocks but only two are database blocks.
S_CUSTOMER1 and S_CUSTOMER are using the S_CUSTOMER table. The first block is query-only so it's not applicable. On the other hand, the second block allows us to insert, update and delete rows and definitely it's applicable. The region form was created by the wizard in the APEX Application.
CONTROL, NAV_CONTROL and NAVIGATOR blocks were used to control the tree region and prompt the title of the columns, so they aren't applicable to the APEX Form.

Items

There are 30 items listed distributed by the blocks:

The only applicable block is S_CUSTOMER, which has 12 items corresponding to the columns in the S_CUSTOMER table. Since the form was created by the wizard, those items were included in the form.

Triggers

There are 23 triggers in total but only two triggers associated with the S_CUSTOMER block.
One of these triggers is POST-QUERY, this trigger logic is not applicable since the APEX form automatically creates the Form - Initialization Process. This process initializes form region items. Initialization can either be fetching data from the region source, using the primary key value(s) or simple initialization of the form region items.
The other trigger is WHEN-MOUSE-DOUBLECLICK, used to execute the procedure EDIT_TEXTITEM. This procedure invokes a pop-up window editor for the COMMENTS column. This procedure can be omitted in the APEX Form.

List of Values

The only list of values created in the original form is SALES_REP_LOV:

Since the form was created by the wizard, this list of values was also created but the name is different and the query is incorrect. So let's fix that in the APEX Application: 

  1. Click App Builder and click your application.
  2. Click Shared Components.
  3. Click List of Values.
  4. Click the list of values and update the Name and SQL Query.

Keep in mind that you can define additional display columns for item types that support multiple display columns. 

Alerts

There are two alert messages: DELETE_ALERT and CONFIRM_REVERT.

DELETE_ALERT message was automatically created when you create the form using the wizard, but the message is quite different. Follow these steps to create a customized message:

  1. Click App Builder and click your application.
  2. Click Shared Components.
  3. Click Shortcuts.
  4. Click Create.
  5. Create Shortcut From Scratch.
  6. Click Next.
  7. For Name, enter DELETE_ALERT.
    For Type, select Text with JavaScript Escaped Single Quotes.
    For Shortcut, enter the original message.
  8. Click Create.

The message is created and now you need to update the shortcut in the APEX Form.

  1. Go to the Customers Form page. 
  2. In the first pane, click the page name.
  3. In the third pane, go to Function and Global Variable Declaration.
  4. Update to var htmldb_delete_message='"DELETE_ALERT"';

As to CONFIRM_REVERT, every APEX page has the Warn on Unsaved Changes property that can warn users when they try to navigate away from the page that has unsaved changes. Just make sure to enable this property.

Program Units

There are seven program units, some to control the tree region and some others to control the relation between the S_CUSTOMER1 and S_CUSTOMER blocks. None of them are applicable to the APEX Form. 

Summary

The following objects were applicable and included in the APEX Form:

  • Created by the Wizard:
    • DB Block: S_CUSTOMER.
    • Items: 12 columns from the S_CUSTOMER table.
  • Created by the Wizard and customized:
    • List of Values: SALES_REP_LOV
    • Alert: DELETE_ALERT.

 

Additional Enhancements

Now that the APEX Form contains the business logic from the original form, it's a good time  to enhance the application by making the following changes:

  • Configure Credit Rating to use a Static LOV.
  • Customize the Customer Form.
  • Customize the Faceted Search.
  • Customize the Interactive Report.

Configure Credit Rating to use a Static LOV

The table S_CUSTOMER has a column named CREDIT_RATING with a check constraint. The column can only contain the following values: EXCELLENT, GOOD, POOR.
It's important to create a list of values to prevent end-users from entering invalid options.

To create the ​​​​​​list of values:

  1. Click App Builder and click your application.
  2. Click Shared Components.
  3. Click List of Values.
  4. Click Create.
  5. Create List of Values from Scratch.
  6. Click Next.
  7. For Name, enter S_CUSTOMER.CREDIT_RATING.
    For Type, select Static.
  8. Click Next.
  9. Enter Display Values and Return Values.
  10. Click Create List of Values.

To learn more about creating a List of Values, please read Beginners Guide to List of Values in Oracle APEX by David Peake.

Customize the Customer Form

When you create a form using the wizard, it automatically creates an item for each column in the table. Depending on the column type, it creates a Text Field, Textarea, Date Picker or Number Field item.

Once the page is created, you can go to the page and make the changes you need. Select a different type of element, edit the label and more.
Plus, you can organize items by dragging and drop them.

To use the list of values previously created,

  1. Click App Builder and click your application.
  2. Go to the form page.
  3. In the first pane, go to PX_CREDIT_RATING.
  4. In the third pane, change the type to Radio Group.
  5. In the Settings section, for the number of columns enter 3.
  6. In the List of Values section, for type select Shared Component.
    For List of values, select S_CUSTOMER.CREDIT_RATING.
    For Display Extra Values, set Off.
    For Display Null Value, set Off.
  7. Click Save.

Customize the Faceted Search

You can define which facets are going to be used to filter the data, some are defined automatically, but you can create or delete them as needed. Also, you can drag and drop facets to change their display order.

  1. Click App Builder and click your application.
  2. Go to the Faceted Search Page.
  3. Look for Search Region and organize the facets in the following order:
    1. PX_REGION_ID
    2. PX_COUNTRY
    3. PX_CITY
    4. PX_NAME
    5. PX_SALES_REP_ID
  4. Click Save.

To learn more about Faceted Search, please read APEX 19.2 - Faceted Search by Carsten Czarski.

Customize the Interactive Report

Define how end-users are going to read the report. Explore all the options by clicking the Actions button when you run the Interactive Report.
Keep in mind that you can run the Report as a:

  • Developer: To define and save primary or alternative reports.
  • End-user: To define and save private reports.

 

To define the primary report:

  1. Run the application as a developer.
  2. Go to the Interactive Report Page.
  3. Click Actions.
  4. Click Columns.
  5. Remove the columns Comments, ID, Region, Zip Code from "Display in Report" region. There are two ways to do it:
    1. Go to the column to remove and double click on the column.
    2. Click the column and remove it by clicking the icon Remove (<).
  6. In "Display in Report" region, select the column Credit Rating and click the icon Up (↑) to move it up to the second place.
  7. Click Apply.
  8. Click Actions.
  9. Click Format, Control Break.
  10. For Column, select the column Name.
  11. Click Apply.

To save the primary report:

  1. Click Actions.
  2. Click Report, Save Report.
  3. For Save, select As Default Report Settings.
  4. For Default Report Type, select Primary.
  5. Click Apply.

In the following video, you can see in detail all the customizations made in the Customers Form:

In this blog post, I showed you how to migrate the business logic from the original form to the APEX Application, but also how to enhance it by adding some customizations to the options created by the wizard.

Note: Please remember that the value "X" in "PX_ITEM" depends on the APEX page you're working on.

Previous                                                                                               Next

Recent Content