In part 5 of the Mobile App series the Form page was refined.
On this page there are two items containing a time value. This post will cover how to deal with these time values.
Time values are stored in the database as time fraction of a Date column. The time values in TTM are stored in the column
act_start_date and
act_end_date.
Three aspects will be discussed:
- retrieving time values
- processing time input
- entering time values
- special Time Input Control
Retrieving Time values
Oracle APEX does not have an item to display time values, like the Date picker does for date values.
So we will have to display the formatted time in a Text Item.
And the time values are not available directly in the table. So we will need a view to expose the time values from the Date columns:
create or replace force view ttm_activities_vw as
select act.act_id
, trunc(act_start_datetime) as act_start_date
, act.act_prj_id
, prj.prj_name as act_prj_name
, ttm_alg.date2time(act_start_datetime) as act_start_time
, ttm_alg.date2time(act_end_datetime) as act_end_time
, act_description
, act_location
from ttm_activities act
join ttm_projects prj
on prj.prj_id = act.act_prj_id;
This view (which is already created) exposes the start and end time in the format
hh24:mi.
You can find the source of the package ttm_alg here.
Processing time input
The view of the last paragraph is the base table for the APEX form page.
In order for this to function an Instead of trigger needs to be defined, performing the insert/update/delete action. Below the code of the trigger:
create or replace trigger act_io
instead of insert or update or delete
on ttm_activities_vw
for each row
begin
if inserting then
insert into ttm_activities
( act_prj_id
, act_start_datetime
, act_end_datetime
, act_description
, act_location
)
values
( :new.act_prj_id
, ttm_alg.time2date(:new.act_start_date,:new.act_start_time)
, ttm_alg.time2date(:new.act_start_date,:new.act_end_time)
, :new.act_description
, :new.act_location
);
elsif updating then
update ttm_activities
set act_prj_id = :new.act_prj_id
, act_start_datetime =
ttm_alg.time2date(:new.act_start_date,:new.act_start_time)
, act_end_datetime =
ttm_alg.time2date(:new.act_start_date,:new.act_end_time)
, act_description = :new.act_description
, act_location = :new.act_location
where act_id = :new.act_id
;
elsif deleting then
delete ttm_activities
where act_id = :old.act_id
;
end if;
end;
As you see the code is much of a one-on-one conversion from view to table except for the time values. The bold code converts the time values into dates with time fraction.
With this trigger insert, update and delete can be performed on the view and therefore the view can be source of an APEX form. So all logic is performed in the database.
Entering time values
The time values are displayed in APEX using a Text Item.
The time values can be entered using the format hh24:mi for example 9:15.
There is no need to add preceding zeros, so 9:05 can be entered as 9:5.
Also for the hours the minutes can be totally omitted, so 9:00 can be entered as 9.
Time values can also be entered with touch gestures using the Touch Time Input control.
Special Time Input control
Back in 2009, when I had my first Android phone, I wanted to do my time registration using the phone. Thinking about this I got the idea for a graphical time input control where a clock image is displayed on the phone and the user would use the touch screen to draw the hands on the clock, thus entering the time.
It would take some time before I would create this control. In february 2015 I wrote a
blogpost about my Time Input Control, and in 2017 I published the Touch Time Input APEX plug-in on
apex.world:
Recently I published a new version of the plug-in with support for floating labels. It is also easier to implement as the time picker icon is created by the plugin. The changes are described in this
blogpost.
We will add a time picker buttons to the time items and connect the Time Input DA to these buttons:
- Open Page 15
- Goto the tab Dynamic Actions
- Right click on Page Load and chose Create Dynamic Action
- Select the new dynamic action and set the Name to Page Load
- Select the action and set Identification > Action to Touch Time Input V2 [Plug-In]
This will take care that all items with the class has-time-picker will be added with a time picker icon. Now we will mark the time items:
- On Page 15 open the region Activity
- Select the item P15_ACT_START_TIME
- Set the value of Advanced > CSS classes to has-time-picker
- Repeat for item P15_ACT_END_TIME
That's it!
