Saturday 21 December 2019

Missing grid layout attributes for APEX items

Whilw preparing for a new presentation I created a new application using the APEX Create Application Wizard and started to create pages and adding items.
While doing so I noticed I missed the usual Grid layout attributes in the Layout section of the item attributes:


No column info etcetera. :-(.
I have spent at least an hour looking for a setting that governs this behavior. Without a result...
Also Google did not provide me with the right answers.

Until I created a new application with all the optional pages. When I set out to examine one of these pages I noticed that the region in the Breadcrumb position did not have the grid layout attributes either.
Going back to my other application I saw that all the regions were created in the Breadcrumb Bar position...
So moving the regions to the Content Body give me back my Grid Layout options (I really missed them).

Happy APEXing


Thursday 7 November 2019

Internet Explorer does not show content of Modal Dialog

In my current assignment I develop using either Firefox or Chrome. But the organization also uses Internet Explorer 11. 

So this morning one of the testers calls me and shows an empty Modal Dialog in Internet Explorer. When using Firefox or Chrome the Modal Dialog shows the expected content. So where is the difference. 



So I reproduce the situation in the Development environment and there the same behavior shows when using Internet Explorer: an empty modal dialog. After opening the Developer Tools the cause shows in the console window: a Javascript error. 

Invalid character

The error points to the JS code below:

$( `[headers="${id}"] input[type="checkbox"]`).each(function() {
    if ( $(this).prop('checked') != checked_status ) {
        $(this).click(); 
    }
});

In the above code snippet a template string is used enclosed with the backtick character (`). You can find more information on this technique on https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Template_literals. And at the end of the page there is a compatibility table, in which it is clear that Internet Explorer does not support this feature. 

 
It is clear that this feature is not supported by IE :-(. 

After a change of the code to: 

$( '[headers="'+id+ '"] input[type="checkbox"]').each(function() {

the modal window showed its content again in IE. 

So when coding for IE be sure not to use too modern JavaScript. Or use a tool like Babel that supplies backward compatibility. 

Pff...

But still, it is quite tricky that a JavaScript error on startup prevents the whole APEX page to be displayed. 

Happy APEXing


Friday 2 August 2019

Oracle APEX Plug-in to retrieve GPS location

Your smartphone is aware where it is using its GPS capabilities. From HTML it is possible to retrieve this GPS location. For this you will have to write some JavaScript.
The new Store location plug-in relieves you from writing the Javascript code. Use it by:

  • downloading the plug-in from apex.world
  • importing the plug-in into your application
  • create two items on a page for the GPS coodinates (lattitude and longitude). They may be hidden items
  • creating a Page Load dynamic action on the page referencing the plug-in in the action
  • filling in the names of the GPS coordinate items in the plug-in attributes
That's all! Run your page on a smart phone and the items will be filled with the GPS coordinates of your current position. For security reasons the smartphone might ask you for permission to access the GPS sensor. 

Acting upon the GPS location

Retrieving the GPS location is an asynchonous process. That means that the process is started and at some later point in time the result is stored in the items. This means that adding another in the Page Load dynamic action will access empty GPS location items because the process is not finished. 
To act upon the coordinates you need to define a custom dynamic action with the following specification:


In the actions you can for example define a PL/SQL action with the GPS coordinate items as inputs. 

In this blogpost you find a full example for applying the plug-in. 

Happy APEXing

Creating a mobile app with APEX - Part 10: Use your location

Your phone is packed with all kind of sensors. Many of them you can not use in JavaScript, but you can access the GPS sensor to retrieve your location. In this blogpost we will access the GPS location and use it in our application.

On the Activity page the GPS Location will be retrieved. When the record is entered when the user is still on the location of the activity she can check Use location. With this item checked the GPS location is stored with the activity.
When the page is opened to enter a new activity the GPS location is retrieved. It is compared to the GPS locations in the database and if possible it will provide a default value for the Location item.

We will:

  • update the database to add columns to store the location
  • import a plug-in to retrieve the GPS location
  • add items on the Activity page to store the location
  • add Dynamic Actions on the Activity page to act on the GPS location
  • use the stored location to provide a default value for the column Location

Update the database objects

There are some changes needed in the database to accommodate the location data. 
The columns act_lattitude and act_longitude are added to the table ttm_activity. We also add a column act_sdo_location which is of the type sdo_geometry to be able to use Oracle Spatial functions. 
The trigger on ttm_activity is changed to construct the column act_sdo_location from act_lattitude and act_longitude.
The view ttm_activity_vw and it's instead of trigger are changed to support the new columns. 
The package ttm_alg is expanded with a function to yield a default value for location

Download the change script from here and execute it. 

Import the Store Location plug-in

Download the Store Location plug-in from APEX World. (Go to the Plug-ins page and search for store location)

Load into the application plug-ins:
  • Go to Shared Components > Plug-ins
  • Press Import
  • Select the plug-in file
  • Hit Next
  • Press Next again
  • Hit the button Install Plug-in
The plug-in is installed and ready for use. 

Add items to the Activity page

Items are needed to store the GPS coordinates that are retrieved by the plug-in. 
Furthermore we need a check box to indicate whether the record is entered on the location of the activity. 

  • Go to page 15
  • Go to P15_ACT_LOCATION and press the right mouse button
  • Select Create Page Item
    • Name: P15_USE_LOCATION
    • Type: Checkbox
    • Appearance > Template: Hidden
    • List of Values:
      • Type: Static Values
      • Static Values: STATIC:Use location;Y
  • Create another item:
    • Name: P15_ACT_LATTITUDE
    • Type: Text Field
    • Label: Lattitude
    • Source:
      • Type: Database Column
      • Database Column: ACT_LATTITUDE
  • And another one:
    • Name: P15_ACT_LONGITUDE
    • Type: Text Field
    • Label: Longitude
    • Source:
      • Type: Database Column
      • Database Column: ACT_LONGITUDE

Adding the Dynamic Actions

Two dynamic actions will be added. One to retrieve the GPS location and another to react on the retrieval of the location. These are separate actions because the retrieval of the GPS location is an asynchronous process. 

  • Go to page 15
  • Go to the Dynamic Actions tab
  • Right click on Page Load and click Create Dynamic Action
    • Name: Page Load - Retieve GPS location
    • Select the Action
      • Change Identification > Action to Store Location [Plug-In]
      • In Settings:
        • Lattitude Item: P15_ACT_LATTITUDE
        • Longitude Item: P15_ACT_LONGITUDE
  • Right click on Page Load and click Create Dynamic Action
    • Name: Location retrieved
    • When:
      • Event: Custom
      • Custom Event: location-retrieved
      • Selection Type: jQuery Selector
      • jQuery Selector: html
    • Select the Action
      • Action: Execute PL/SQL Code
      • Settings > PL/SQL Code:
:P15_ACT_LOCATION := nvl(:P15_ACT_LOCATION
       , ttm_alg.default_location(:P15_ACT_LATTITUDE,:P15_ACT_LONGITUDE));
      • Items to Submit: P15_ACT_LATTITUDE,P15_ACT_LONGITUDE
      • Items to Return: P15_ACT_LOCATION
The last Dynamic Action retrieves a default value for the field Location based on the GPS location. The default value is retrieved in the function ttm_alg.default_location that uses an Oracle Spatial query to find activities that have been recorded close to the current GPS location. The query is discussed in the last paragraphs of this post. 

Eating the pudding

The proof is in..., well you know. 
So let's try it. 
  • log in to the application on your phone
  • press New activity
  • after the page has been opened your permission to use the location is asked. Confirm it.
  • now scroll down to the bottom
  • (after some time) you will see the GPS coordinates
  • fill in the items of the activity
  • enter in  Location: test GPS location
  • check Use Location, else the GPS location will not be stored
  • accept the changes
  • press New again
  • scroll down to the bottom
  • (after some time) you will see the GPS coordinates
  • Location should then contain the value you entered for the previous record: test GPS location
If things do not work as expected use the Chrome Inspector (or any other Developer tools) to examine what is wrong. 

Background on selecting the default value

Based on the GPS coordinates the default value for the Location field is determined. This is done using a spatial query:

      select act_location
      from   ttm_activities
      where  act_lattitude is not null 
        and  act_longitude is not null
        and  act_use_location = 'Y'
        and  sdo_geom.within_distance(act_sdo_location,0.2
                , sdo_geometry(2001,8307
                              ,sdo_point_type(p_longitude,p_lattitude,null)
                              ,null,null),1,'unit=km')   
             = 'TRUE'
        order by sdo_geom.sdo_distance(act_sdo_location
                , sdo_geometry(2001,8307
                              ,sdo_point_type(p_longitude,p_lattitude,null)
                              ,null,null),1,'unit=km')   
      ; 

This query uses the Oracle Spatial function sdo_geom.within_distance to find all the records with a GPS location within 200 m's (the value 0.2) from the given GPS coordinates. This proves to be a reasonable distance to correct for the bias in GPS location. 
The records are sorted based on the distance to the coordinates which is retrieved using the function sdo_geom.sdo_distance. This way if the query delivers more than one row the one closest to the the given point is selected. 


Using Oracle Spatial functions

The Oracle Spatial functions expect arguments in the  form of a SDO Geometry objects. The column act_sdo_location is already stored as such an object. The GPS coordinates are numbers and need to be converted to a SDO Geometry object:

sdo_geometry(2001,8307
            ,sdo_point_type(p_longitude,p_lattitude,null)
                              ,null,null),1,'unit=km')

Without going to deep into the complex geometry subjects: 
  • the first parameter 2001 indicates that the shape is a point. You can also have lines, polygons etc. 
  • the second parameter 8307 references the coordinate system
  • you notice the values for longitude and lattitude in the call to sdo_point_type
  • the last parameter indicates that the unit used is kilometers. 
  • more detailed information can be found in the Oracle documentation
To be able to compare values the coordinate system and the unit used should be consistent in database and code. 


Tuesday 30 July 2019

Creating a mobile app with APEX - Part 9: Put the app on your phone's home screen

Now you have a cool app. But to use it you still have start your phone's browser and type an URL or select a bookmark.
In this post I will show you how to put an icon for the app on the home screen of your phone. The steps are:
- get suitable icons
- prepare your application to use the icon(s)
- put your app on the home screen

For the last step only iOS is described.
Because of the lack of an suitable Android device I was not able to describe the process for Android.
However I expect the process on Android to be similar.

Get suitable icons

The first step is to get suitable icons. Apple sets strict standards around application icons. Among others they need to come in various prescribed sizes, like 16x16, 32x32 etc.
Luckily you can find many sites on the internet to do create such an icon set for you. I have used realfavicongenerator.net. On this site you provide an image as base for the icons and the whole set is generated, even including the HTML code to add to your page.

  • go to realfavicongenerator.net
  • upload your image
  • set the options:
    • you can specify a background color
    • specify the location for the files: #APP_IMAGES#/img All the way at  the bottom of the page
    • generate the files
  • download the ZIP file with icons 
  • cut the HTML code and paste it into a file

Prepare your application

Now we will implement these icons to our application. First we load the icons in the Static Application Files:
  • go to Shared Components > Static Application Files
  • click on Upload Files and fill in the values:
    • Directory: img
    • Files: Choose the ZIP file with icons
    • Unzip File: Yes
    • Press the Upload button
  • all the icons are now available in the img directory in the Static Application Files
Then we can reference these files:
  • go to Shared Components > User Interface Attributes
  • fill Favicon > Favicon HTML with the HTML code from the previous paragraph
That's it. 
When running your application in the desktop browser, you will notice that the image in the tab next to the page name has changed to the new image. 

Put your app on the home screen (iOS)

To put your application on the home screen of your iPhone: 
  • open your application in Safari on you phone
  • log in
  • press the Share Button
  • chose Add to Home Screen
  • you will see a form with the icon. The title of the icon can be changed here
  • press Add  and the icon will be added to the Home Screen
This process is illustrated in the image below:



img 4

This way you will have easy access to your application to enter data fast. 

Happy APEXing

Wednesday 24 July 2019

Creating a mobile app with APEX - Part 8: Implementing autologin

In the mobile app I usually enter only a few records of data like an expense or a few activities.
It is pretty annoying when I have to enter username and password each time I use the app for a few seconds. 

In most native apps the authentication is asked once and then stored safely to be reused at further use. The security lies in the presumption that the authorization has been done by unlocking the phone. For most applications this will be sufficient. Only apps with a high stake or high risk like banking apps will need additional authentication. 

So I want this scenario also for my mobile APEX apps: 
  • enter username and password the first time
  • checking the 'Stay logged in' checkbox
  • next time when starting the app the login process is done automatically and I will be led to the starting page
We can accomplish this by using cookies to store client side data. This idea has been described by Christian Rokita in this blogpost

I have created a bit different implementation without the need of an extra page:
  • a custom authentication scheme is created based on a tables with users and passwords
  • a sessions table is created to store tokens and user names
  • a package will accommodate the code needed to read and write the cookies and perform the autologin
  • on the login page the autologin procedure is called to read the cookie. If the cookie points to a valid user a session is created for this user and the session is redirected to the starting page of the application
  • on the login page a Stay logged in checkbox is added
  • an Before Header application process writes the token to the Stay logged in cookie and creates an entry for the token and the user name of the current user

Creating the database objects

You can download the file to create the tables here
Execute the script in your favorite SQL console. 
In your schema you should see:
  • the table aut_users
  • the table aut_sessions
  • the package aut_pck
The table aut_users  contains one record for a user user with a password secret. You can use this data to login to the application. Add your own users in this table.
The implementation of the authentication is very basic and just for demonstration purposes. For serious use at least the passwords should be stored encrypted!

Creating a new authentication scheme

To obtain autologin functionality we need to create a custom authentication scheme:

  • go to the Shared Components > Security > Authentication Schemes
    • press Create
    • chose Based on a pre-configured scheme from the gallery
    • press Next
      • Name: Custom
      • Scheme Type: Custom
      • Authentication Function Name: aut_pck.authenticate
    • hit Create Authentication Scheme
After creating the scheme it is automatically the current scheme.

Changing the login page

Now we will adapt the login page:
  • open the login page 9999
  • add a new process:
    • Name: autologin
    • PL/SQL code:
begin
  aut_pck.autologin
       ( p_app_id    =>  :APP_ID
       , p_page_id   =>  10
       );
end; 
    • Executing Options > Point: Before Header
    • Server-side Condition:
      • Type: Request != Value
      • Value: LOGOUT
  • add a another process:
    • Name: autologout
    • PL/SQL code
begin
  aut_pck.autologout;
end; 
    • Executing Options > 
      • Sequence: 0
      • Point: Before Header
    • Server-side Condition:
      • Type: Request = Value
      • Value: LOGOUT
  • select the Login Region
    • select the item P9999_REMEMBER
    • Change Label to Stay logged in
The last step in processing is clearing the page's session state. We need to limit that to the username and password items in order to have the value of the Stay logged in checkbox available on subsequent pages. 
  • Go to the processing tab
    • Open the Clear Page(s) Cache
    • In the attributes change Settings:
      • Type: Clear Items
      • Item(s): P9999_USERNAME,P9999_PASSWORD  This is done to keep the value of the P9999_REMEMBER in session state
  • Save the page

Adding the Application Process

We will create an application process that will fire on each page before the header on condition that the user is authenticated and P9999_REMEMBER = 'Y'. In this process the Stay logged in cookie will be written, unless there is a valid cookie.
Go create the application process:

  • go to the Shared Components > Application Processes
  • click the button Create
  • Enter 
    • Name: Write autologin cookie
    • Point: On Load: Before Header
    • Press Next
  • Enter the PL/SQL code:
begin
  -- set autologin cookie
  aut_pck.set_username_in_cookie
         ( p_username      =>  :APP_USER
         , p_remember      =>  :P9999_REMEMBER
         );
  :P9999_REMEMBER := 'N';
end;
    • Press Next
  • Enter the condition:
    • Condition Type: PL/SQL Expression
    • Expression 1: 
:APP_USER != 'nobody' and
:P9999_REMEMBER = 'Y'
  • Press Create Process
After writing the cookie the value of P9999_REMEMBER is set to 'N'. This ensures that the process only fires once after login. 

Enabling logging out

As all is set up now you will be automatically logged in until the cookie or the aut_session record expires. To give the user the possiblity to end the autologin we will adapt the logout URL. 

  • go to Shared Components > Navigation > Navigation Bar List
  • select Desktop Navigation Bar
  • click on Sign Out 
  • change Target:
    • Target Type: Page in this Application
    • Page: 9999
    • Request: LOGOUT
  • press Apply Changes
Now chosing Sign Out will result in navigating to the login page with the request LOGOUT.
Previously we have created a logout process on page 9999 which is triggered by the request LOGOUT. This process erases the cookie and removes the corresponding record from aut_sessions thus disabling the autologin. 

Testing the autologin functionality

Now you can test the functionality by logging in with the Stay logged in item checked. 
You can test the autologin by changing the session ID in the URL. Normally the session would be recognized and you would be returned to the login page. Now you just stay logged in. 

Behind the screen you can check on the existence of the cookie STAY_LOGGED_IN_xxx, where xxx is the application number using developer tools like the Chrome Inspector. Likewise you can inspect to content of the table aut_sessions, where a record should exist with the token stored in the cookie and the name of the user. 

Test the Sign out functionality. You should be returned to the login page. 

Sunday 21 July 2019

Creating a mobile app with APEX - Part 7: Working with Time items

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!






Wednesday 17 July 2019

Touch Time Input - New version

In the cause of writing my blog series on mobile development I needed to adapt the Touch Time Input plug-in for the floating labels in APEX 18.1.
So I created a new version for APEX 18.1 and up.
This version:

  • supports APEX 18.1 floating labels
  • is called from a Page Load Dynamic Action
  • applies to all items with the class has-time-picker 
  • some bugfixes
This makes the plug-in even easier to use as the marked items automagically receive a clock icon analog to the date picker:



You can download the plugin from apex.world.

For pre 18.1 APEX versions the old version of the plug-in can still be used.


Happy APEXing :-)

Thursday 11 July 2019

Creating a mobile app with APEX - Part 5: Refining the Form page

After refining the List View now the form will be improved for mobile use:

  • screen space usage will be optimized
  • the buttons will be reorganized
  • default values will be provided
  • success messages will be made to disappear after a few seconds
  • validations for time items are created

Bugfix for trigger

There was an error in the original trigger on the view. Download a new create script here and run the script to replace the trigger.

Screen space optimization

The previous actions to improve the use of screen space also have effect on the form page. The only thing that remains to be done here is to hide the region title:
  • Open region Activity
  • Hide header by settting Appearance > Template Defaults > Header to Hidden

Buttons

For this step a new version of the CSS file is needed. Download apex_mobile.css and upload to the Application Static Files.

Below the form are three buttons, the Cancel, Delete and Create/Apply Changes button. 
The Cancel button is not needed because the List View can be reached using the Menu Bar. So this button can be Deleted.
  • delete the Cancel button
The Create/Apply Changes button will be replaced by the Save action in the Menu Bar. The CREATE and SAVE buttons are mutually exclusive. The CREATE button is shown for a new rows, the SAVE button for existing rows. 
The connect the Menu Bar Save action to the buttons a CSS save_button class is applied:
  • select the SAVE button
  • set Appearance > CSS Classes to save_button
  • select the CREATE button
  • set Appearance > CSS Classes to save_button
The action on Menu Option Save is defined as: javascript:$('.save_button').click();
This means that the element with class save_button is selected and the click action associated with this element is performed. This way either the SAVE or the CREATE button action is executed depending on which one is present on the page. 

So only the Delete button remains. This buttons will be styled in a more mobile fashion:
  • select DELETE button
  • change Layout > Button Position to Below Region
  • set Appearance > Template Options:
    • Size: Large
    • Type: Danger White font on red background
    • Width: Stretch Makes button use full width
  • set Appearance > CSS Classes: delete_button
Run the application and notice the changes. Save a new or existing activity using the Menu Bar Save option. 


Default values

Entering data on the on-screen keyboard is not easy or fast, so it is an advantage when data is prefilled. We will provide some defaults for the items.

We will use functions from the package ttm_alg. For this functionality a new version of the package ttm_alg is needed. Download the create file ttm_alg.sql and execute this file on the schema.

The first one is the date. The obvious default for this is the current date:
  • open P15_ACT_START_DATE
  • in the Default section:
    • Type : PL/SQL Expression
    • PL/SQL Expression: sysdate
The Project of the last entered Activity can serve as default value for Project:
  • open P15_ACT_PRJ_ID
  • in the Default section:
    • Type : PL/SQL Expression
    • PL/SQL Function Body: ttm_alg.last_used_project
The Start time can be filled with the last End time of today, or a default value like 9:00 if this is your usual starting time. 

  • open P15_ACT_START_TIME
  • in the Default section:
    • Type : PL/SQL Expression
    • PL/SQL Function Body: ttm_alg.default_start_time
Run the application and enter a new activity. 

We will review the possibility to use the GPS location to determine a default value for Location in a coming post. 

Success message

Standard the APEX success messages that are displayed at the top of the page must be removed manually. On mobile this is not logical. The success message indicates that there were no errors so after a short time the message can disappear.  
For this function a new version of apex_mobile.js must be uploaded to the Application Static Files. After this:

  • open Page 10
  • add to JavaScript > Execute when Page loadsset_success_message_fade();

Now if an activity is created or changed the success message will show for 2 seconds and then disappear.

Checking input

Of course the user input has to be checked.

The date value will be checked automatically.
Because the project is implemented as a select list it need not be checked.
The Description and Location are character items and need not be checked.

That leaves us both the Time items. They should be filled with a string in the form hh24:mi. Furthermore the End Time should be after the Start Time.

After this the validations can be created:
  • Open the Processing column on page 15
  • Right click on Validating > Validations and select Create Validation
  • Change the Validation:
    • Name: Check start time
    • Validation > Type: PL/SQL Function (returning Error Text)
    • Validation > PL/SQL Function returning Error Text: 
      • return(ttm_alg.check_time(:P15_ACT_START_TIME,'Start time'));
  • Right click again on Validating > Validations and select Create Validation
  • Change the Validation:
    • Name: Check end time
    • Validation > Type: PL/SQL Function (returning Error Text)
    • Validation > PL/SQL Function returning Error Text: 
      • return(ttm_alg.check_time(:P15_ACT_END_TIME,'Start time'));
  • Right click another time on Validating > Validations and select Create Validation
    • Name: Check Start Time before End Time
    • Validation > Type: PL/SQL Function (returning Error Text)
    • Validation > PL/SQL Function returning Error Text: 


Now the format of the time items and the relation between start and end time are checked. If an invalid value is entered on a meaningful error message is displayed.



Wednesday 10 July 2019

Creating a mobile app with APEX - Part 6: Avoid autmatic zooming on iOS

If you use an iPhone to access our application you probably will have noticed the automatic zooming.
If the focus is set to an item, Safari (and Chrome also) will zoom in to this item. After zooming not all of the page is visible any more. When for example the date item is selected after zooming the date picker icon shifts out of sight. So it is desirable to avoid the auto zooming.

After some googling it is clear that the autozoom is applied to items with a font size less than 16px.

The easiest solution to prevent this zooming is to size up the font size of all items to 16px. We can attain this by applying the following CSS:

input, select, textarea {
    font-size: 16px!important;
}

Now all font size 16px is applied to all items. You can see the differences in the images below.
Left is the page with autozoom, right is the page with the fix applied.


As you can see after autozoom the page is partly invisible. The user needs to pinch to view the full page.

The !important postfix in the CSS is a bit blunt. It is used to avoid complex and extensive CSS specifications with references to Universal Theme classes.

Solution specific for iOS

Now putting this CSS on the page just like that it will affect the application for all platforms.
A more elegant solution is to apply this CSS only on iOS. That can be done by creating a region on the global page 0 and creating a PL/SQL Dynamic Region that only writes the style specification for iOS:

  • open Page 0
  • right click on Content body and select Create Region
  • select the new region
    • Name: Prevent Autozoom
    • Type: PL/SQL Dynamic Content
    • Template: - Select -
    • PL/SQL Code:

declare
  l_agent     varchar2(1000);
begin
  l_agent := lower(owa_util.get_cgi_env('user-agent'));
  if l_agent like '%iphone os%' then
    htp.p('<style>input,select,textarea {font-size:16px!important;}</style>');
  end if;
end;
With this solution the  CSS is only applied to iOS. The font sizes on other platforms are unaffected.

The user-agent CGI variable contains information about the browser and platform used. When the string iphone os is present in the lower case user-agent string the page is displayed on iOS. 

Alternative solution

There is another smart, but rather complex solution:

  • setting the font size to 16px
  • sizing the item relatively to the change in font size
  • use CSS scaling to resize the items back to the original size
This solution is described in the blog No input zoom in Safari on iPhone, the pixel perfect way by Jeffry To. 

The first solution is sufficient for our purpose, in mobile APEX applications the layout usually remains good enough.



Tuesday 9 July 2019

Creating a mobile app with APEX - Part 4: Refining the List View

After having taken care of the general layout this post will focus on the refining of the List View page. The following actions will be performed:

  • loading a new version of the CSS file
  • adding a date picker to filter the activities
  • changing the List View query to react on the date picker
  • adding a Dynamic Action to synchronize the List View
  • optimizing screen space
  • removing the create button
  • applying advanced formatting on the List View elements

Loading new version CSS

A new version of the apex_mobile.css file should be downloaded here and uploaded to the Application Static Files.

Adding the date picker

We will create a new region and add a date picker item. The background color of the date picker region will be green. 
  • open Page 10
  • right click on Content Body and select Create Region
  • change the region:
    • Name: Parameters
    • Appearance > Template Options:
      • check Remove Body Padding
      • set Header to Hidden
    • Advanced > Static ID: parameters this will style the region
  • drag this region above the Activities region
  • right click on the region Parameters and chose Create Page Item
  • make the following changes to the new item:
    • Name: P10_DATE
    • Type: Date Picker
    • Label: Date in week
    • Default:
      • Type: PL/SQL Expression
      • PL/SQL Expression: sysdate
    • Warn on unsaved changes: Ignore Prevent warning when leaving page

Changing the List View query

The source query of the List View should be changed to reflect the values of the date item. 
Add the following where-clause to the query:


from   ttm_activities_vw     act
where  trunc(act_start_date,'iw') = trunc(to_date(:P10_DATE,'dd-mm-yyyy'),'iw')
order by trunc(act_start_date) desc, act_start_time

With this where clause only activities in the same week as P10_DATE are shown.

Dynamic Action to synchronize List View

When at this point the date is changed there is no effect on the List View.
We will need to define this behavior with a dynamic action:

  • Right click on the item P10_DATE
  • Pick Create Dynamic Action
  • Change the Name of the DA to P10_DATE change
  • Change the first True Step:
    • Action: Execute PL/SQL Code
    • PL/SQL Code: null;
    • Items to Submit: P10_DATE this is needed to load the value into session state
  • Right click on this step and chose Create Action
  • Change the new action:
    • Action: Refresh
    • Affected Elements > Selection Type: Region
    • Affected Elements > Region: Activities
Now a change of the date item results in a requery of the Activities region.

Optimizing screen space

There still is some screen space that is not used effectively. We do not need the region title as it shows in the Top Bar. The Create button can be deleted as this function is provided in the Menu Bar. And there still is excess of white space...
So let's get going:
  • Open the region Activities:
    • Edit Template Defaults:
      • Check Remove Body Padding
      • Set Header to Hidden
      • Set Style to Remove borders
    • Edit the region's Attributes
      • Uncheck Settings > Inset List 
  • Delete the Create button
You can clearly see the difference between the page now and before:



Even though the date item has been added the vertical space taken up by the List View is less than before.

Advanced formatting

In the design in Part 1 of this series we have defined that the List View elements should have a colored bar left of the text. 

Also the Location should be part of the information shown, behind the Project name. But location is not as important as the Project, so we need to decrease the emphasis on Location, or increase it on Project. 
Increasing emphasis can be done by using Bold text, decreasing can be accomplished by a lighter font color, usually a grey tone or a smaller font size.
To apply this on one line we need to use Advanced Formatting within List View.

The base query for the List View has to change because we need separate elements which were combined in the previous view:

select act.act_id
     , to_char(act_start_date,'fmDay, fmMonth fmddth yyyy ')    
                                     as  start_date
     , act_prj_name                  as  project_name
     , case act_prj_id
           when 1 then 'cornflowerblue'
           when 2 then 'green'
           when 3 then 'yellow'
       end                           as  project_color
     , act_location                  as  location
     , act_start_time
       || ' - ' ||act_end_time       as  period
     , act_description               as  description
from   ttm_activities_vw     act
where  trunc(act_start_date,'iw') = trunc(to_date(:P10_DATE,'dd-mm-yyyy'),'iw')
order by trunc(act_start_date) desc, act_start_time

To apply the advanced formatting:

  • Open the Activities region
  • Apply the new query
  • Go to Attributes
  • In Settings:
    • check Advanced Formatting You see that the available items change
    • Fill in the values from the table below:
List Entry Attributesstyle="margin-left:15px; border-left:5px solid &PROJECT_COLOR."
Text Formatting
Supplemental Information Formatting


The columns from the query are referenced by name preceeded by an ampersand and with a trailing dot, like &PROJECT_NAME.. For clarity the styling is done in-line, normally you would CSS classes. 
When running the application you can notice the difference between the various parts of the List View elements.
The colored bar is created by the List Entry Attributes. Note that you can also reference query columns here.

The List Divider has been styled. In order to provide more contrast with the elements it has received a white font on a dark grey background. This is done by applying CSS in the apex_mobile.css file:

li.a-ListView-divider.ui-bar-inherit {
    background-color: rgba(0,0,0,.55);
    color: white;
}

The Universal Theme classes are used to reference the List Divider. 

The List View page now looks and functions according to the specifications:



In the next Episode we will refine the Form page.



Sunday 7 July 2019

Creating a mobile app with APEX - Part 3: Adapting the layout

In this post the general layout of the application will be tweaked:

  1. Another Theme Style will be chosen
  2. The landing page for the application is changed
  3. The menu will be replaced by a top/bottom menu bar. The menu will be adapted to the pages
  4. The List View page is set as landing page
  5. Unused space will be reclaimed
  6. The page title will be shown in the top bar

Theme Style


We will select another Theme Style for the application:

  • Run the application from the App Builder
  • Select Theme Roller from the Developer Toolbar
  • Select Vita - Slate for the Style
  • Press the button Set as Current

Now we have other (darker) colors in our application. The dark grey top bar will enable us to use other colors as background in the application. The background color for the date picker region in the List View will be set in the post 'Refining List View'.

Landing page

The default landing page is Page 1. We created our Activity List on Page 10 and this should be the new landing page:

  • Open the Shared Components > User Interface Attributes > User Interface > Desktop
  • In the tab Attributes:
    • Set Home URL to f?p=&APP_ID.:10:&SESSION.
Run the application and you are taken directly to page 10.

Menu

The default left menu is not so very useful for an smartphone app with limited pages. Therefore we will use a top/bottom menu bar:

  • Open the Shared Components > User Interface Attributes > User Interface > Desktop
  • In the tab Navigation Menu:
    • Set Position to Top
    • Select Top Navigation Tabs for List Template
    • In Template Options set Mobile to Do not display labels

With these changes the Left Menu is replaced by a menu bar. This bar is shown just below the top bar on desktop. On mobile it is displayed at the bottom of the screen.
With the last option the Menu Bar will only show icons, thus preserving vertical space. 

Menu items

Now we will add menu items:
  • Go to Shared Components > Navigation Menu > Desktop Navigation Menu
  • Delete the first menu item Home
  • Open the option Activities:
    • Set Image/Class to fa-home
  • Create new List Entry:
    • Image/Class: fa-plus
    • List Entry Label: New Activity
    • Target > Page: 15
    • Target > Clear Cache: 15 Empty the page items for new record
    • Conditions > Condition Type: Current Page = Expression 1
    • Conditions > Expression 1: 10 Only show on page 10
  • Create new List Entry:
    • Image/Class: fa-save
    • List Entry Label: Save
    • Target > Target type: URL
    • Target > URL Target: javascript:$('.save_button').click();
    • Conditions > Condition Type: Current Page = Expression 1
    • Conditions > Expression 1: 15 Only show on page 15
These menu options will be shown in the menu bar. Some options will only be shown on one page.
Run the application and observe the behavior of the menu bar. The Save action will be enabled when the Form page is refined. The Save option will execute the action connected to the button with the class save_button.

JavaScript and CSS

In following steps some JavaScript and CSS code is added. This code is wrapped in files which are loaded into the Static Files. Download the JavaScript and CSS file using the links and upload them to the Application Static Files in the Shared Components. Then create references to these files in order for the code to be available on all pages:

  • Open the Shared Components > User Interface Attributes > User Interface > Desktop
  • In the Tab JavaScript:
    • Set File URLs to #APP_IMAGES#apex_mobile.js
  • In the Tab Cascading Style Sheets:
    • Set File URLs to #APP_IMAGES#apex_mobile.css

Reclaiming unused space

The Universal Theme regularly uses white space. On the desktop this avoids a clogged interface. 
On mobile however screen space is valuable and should be used as effective as possible.
Therefore we will reclaim unused screen space as much as possible. One of the possibilities is to 
remove the padding of the inner content of the body: 

.t-Body-contentInner { padding: 0; }

With this CSS we remove the white border around the regions. 
The CSS is included in the CSS file apex_mobile.css. As we have loaded this file in the last paragraph
the change is already active. 
In the next posts will reclaim more unused space using the Region Template Options.

Page title in Top Bar

Another space user is the Breadcrumb which is used to indicate which page is shown. This information can also be shown in the Top Bar. 
For this we need to apply some JavaScript. We will place the code in a Page Load Dynamic Action on the Global Page. This way the code will be executed on each page:
  • Open page 0 (Global Page - Desktop)
  • Goto Dynamic Actions The tab with the Lightning Icon in the Left Pane
  • Left Click on Page Load and chose Create Dynamic Action
  • For this Dynamic Action change the title to Page Load - General
  • Change the True action New:
    • Action: Execute JavaScript Code
    • Code: TitleInTopbar();
The referenced code gets the page title from the page header and displays it in the Top Bar. 





Creating a mobile app with APEX - Part 2: The basic app

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.

About the next post

In the next episode we will look at the general layout of the pages. 
  • Our own Theme Style will be applied
  • The menu will be changed to a navigation bar
  • Screen space will be used more efficient
  • Page title will be placed in the top bar