About Oracle: Creating a mobile app with APEX

In the second part of this series we will create the basic app. This is the out of the box app which is already fully usable to insert, update and delete data.

Before creating the app we need to create the database objects. The object creation script should be run in a schema with at least the privileges to create tables, views, sequences, procedures and triggers. In Oracle APEX add a workspace assignment for this schema.

Creating the application

Now we can create the application:

log into the APEX workspace
open the App Builder
hit the Create button
Chose New Application
Enter a Name for the application
Change the Application ID if desired
Select the schema with the objects as value for Schema
Hit Create Application

Creating the pages

Create the List View and Form pages using the create page wizard:

Press Create Page
chose Forms
hit Report with List View on Table
Fill in the following data:

Report Page Number: 10
Report Page Name: Activities
Form Page Number: 15
Form Page Name: Activity

Press Next
Check Create a new navigation menu entry and hit Next
Chose the table ttm_activities_vw and press Next. We will replace this with a query later on.
Check Select Primary Key Column(s) and select all columns in the shuttle.

Select act_id for Primary Key Column 1
Remove the column act_prj_name

Hit the Create button

We can run the application and see how it looks:

Now the pages have been created, but they do not function very well:

the list view only shows ID’s
the form page contains large fields with bad labels and a numeric project reference

Note that it is already possible to insert and update activities using the form page.

Improving the List View

To refine the list view open page 10:

Select the region List view
Change the following values:
Title: Activities
Source > SQL Query:

select act.act_id
, to_char(act_start_date,'fmDay, fmMonth fmddth yyyy ')
as start_date
, act_prj_name as project_name
, act_start_time
|| ' - ' ||act_end_time
|| ' ' ||act_description as details
from ttm_activities_vw act
order by trunc(act_start_date) desc, act_start_time

Open the Attributes of the Activities region. In Settings:

check Show List Divider
uncheck Enable Search Search will be implemented later
Text Column: PROJECT_NAME
Supplemental Information Column: DETAILS
List Divider Column: START_DATE

Run the application and notice how much better the content looks already.

We have clear information about the activities and they are grouped and sorted in a useful way.

Some remarks about the changes on the list view page:

the base query of the List View region provides the elements to be shown. For the second line the start time, end time and description are combined into one string, supplying a lot of information about the activity
it shows how easy it is to define a list divider. Just check the Show List Divider box and supply the column
the Search option is also very easy to define. Just check Enable Search and provide the column to be searched.It is possible to define whether the search is exact and how it handles case. The search column can be an existing column or a new column combining to be searched. The search option is disabled because the List View will show one week of activities based on a date picker item.

Refining the form page

Now we will improve the form page:

Open Page 15.
Select the region Form on TTM_ACTIVITIES_VW
Set Title to Activity.
Change values for the items:

P15_ACT_START_DATE:

Label: Date

P15_ACT_PRJ_ID:

Type: Select List
Label: Project
List of Values:

Type: SQL Query
SQL Query: select prj_name, prj_id from ttm_projects order by 1

P15_ACT_START_TIME

Type: Text Field
Label: Start time

P15_ACT_END_TIME

Type: Text Field
Label: End time

P15_ACT_DESCRIPTION

Label: Description
Appearance > Height: 2

P15_ACT_LOCATION

Type: Text Field with autocomplete
Label: Location
List of Values:

Type: SQL Query
SQL Query: select distinct act_location from ttm_activities order by 1

Added on 28-07-2019: In APEX 19.1 we need to change the Save process because the base table is a view:

On page 15 open the Process Tab
Select Process form Activity

In Settings:

Lock Row: No
Return Primary Key(s) after insert: No

When the form page is run the improvements become clear:

The labels are improved and the page fits on the screen. The Project name is shown and can be chosen using a select list. Entering the Location is supported by the auto complete functionality.

Some remarks about the changes on the form page:

APEX uses the column names as labels. They usually need to be changed to some more useful text
The form wizard creates Textarea items of 4 lines for most varchar2 columns. This takes a lot of vertical space and is not needed in most cases. In this case only the description is expected to need 2 lines. The type of the other items is changed to Text Field.
To show the project name for selecting the project the item type is changed to Select List. For reasons of simplicity the LOV query is defined with the item instead of in the Shared Components.
To implement the specified auto complete functionality the type of the location item is changed to Text Field with autocomplete. Now the location can be chosen from previously entered locations filtered by the text already entered. Note that the query only returns one column.