Overview
Building an app to solve a personal need is a great learning opportunity. Sometimes I plan multi-day trips and must decide among overlapping events. The annual animation festival in Annecy, France, is one example. Hundreds of talks and screenings span various venues in a lovely little lakeside locale. Another is a periodic pilgrimage to New York City to see as many musicals and museums as I can fit in a week. An APEX app helps me plan my trip activities. This year I celebrate Joel Kallman Day by sharing my app and explaining the smorgasbord of APEX tricks I learned by building it.
(Technical Note: to see a larger version of any figure, right click on it and choose Open Image in New Tab from the context menu…)
Recurring Trips with Overlapping Events
Both the Annecy Festival and my New York City trips happen periodically during a particular year over a series of days, so a trip name and year represent each edition of a trip (e.g. New York City, 2023). Each edition has a start and end day and consists of events to attend that may repeat multiple times during the week. These different event runs might be in distinct venues, like a movie that shows in different cinemas around Annecy. In contrast, all the event runs might be in the same Broadway theatre. In the case of the Annecy Festival, films can be from a particular country, in an original language, and have subtitles in another language, so as shown below my data model also includes countries and languages.
Gantt Chart Showing Per-Day Show Runs
To easily see the overlapping events on each day of the trip, I use a day selector and Gantt chart as shown in the figure below. The query for the Gantt chart region includes columns DAY_START and DAY_END whose values come from combining the selected day with user preferences for day start time and day end time. The query also includes STARTS_AT and ENDS_AT datetime values for each event run that falls on the selected day. It concatenates the name of the event and the name of the venue into a NAME column that’s used as the task name. To suppress the display of task name along the left edge of each row, I selected the Attributes tab of the chart region, and set Show Row Axis to OFF.
Day Selector’s Data-Driven Date Range
The EVENT_DAYS Shared Components List of Values (LOV) driving the day selector was an interesting query to write. In the EBA_DEMO_ANN_EDITIONS table for a trip edition we have a START_DATE and END_DATE for the consecutive sequence of days over which the trip edition takes place. I needed a query that returned all of the dates in order starting with the start date and ending with the end date for the current trip edition. The application item CURRENT_EDITION holds its ID value. The query ended up looking like this:
SELECT
to_char(FIRST_DAY + LEVEL - 1,'fmDy DD Mon') AS DAY_NAME_IN_PERIOD,
LEVEL AS DAY_NUMBER
FROM (SELECT FIRST_DAY, LAST_DAY
FROM EBA_DEMO_ANN_EDITIONS
WHERE ID = :CURRENT_EDITION)
CONNECT BY
FIRST_DAY + LEVEL - 1 <= LAST_DAY
ORDER BY
DAY_NUMBER ASCThis query drives the day selector below with an ordered list of formatted day names between start date and end date for the current trip edition:
Stretching Chart to Fill the Viewport
I could have hard-coded the height of the Gantt chart region on the Attributes tab of the property editor, but that didn’t meet my needs. In the weeks leading up to a trip, I use the app on different desktop computers with differently-sized monitors as well as on my iPad, so I explored how the Gantt chart could stretch to use whatever vertical space is available.
After many failed experiments, I landed on the following technique. It uses the CSS calc() function to compute the value of the height property. First, I defined a CSS class on the page called vertical-stretch and assigned this class to the Gantt chart region. Then I defined the class in the page-level CSS > Inline settings using the syntax:
.vertical-stretch {
height: calc(100vh - 17rem);
}The calculation references the full (100%) viewport height by using 100vh and subtracts from that the height of the rest of the elements on the page above the Gantt chart. Using CSS rem units that represent the height (i.e. font-size) of the root element on the page, through trial and error I discovered 17rem closely matched the space used by the other content on the page. Applying the vertical-stretch CSS class to the Gantt chart region means, “Your height is the viewport space remaining after accounting for the vertical space occupied by the elements above the Gantt chart”. This approach made the app look exactly how I wanted on every monitor size and iPad where I use it.
When resizing the window interactively, the chart also reacts as expected as shown in this short video:
Editing Events by Clicking on a Bar
The Event page (7) in the app lets the user create, edit, or delete an event and all its related event runs. When looking at a particular day’s events in the Gantt chart on the Schedule page (14), I wanted clicking on any bar to open the event related to that particular event run. So I set out to find how to obtain the event run ID related to each bar in the Gantt chart. I started by configuring the Gantt chart’s Task Id property to get its value from the event run ID column in the region’s query.
Next, I hoped to find the value of this task ID somewhere in the HTML markup in the page. By right-clicking on a Gantt bar rectangle and choosing Inspect Element, the browser developer tools showed me that each bar was a Scalable Vector Graphics (SVG) group element <g> containing two <path> elements and a <text> element like this:
<!-- Example SVG group of shapes for Task Id 162 "bar" -->
<g id="_dvtActiveElement43100180_162">
<path d="..." class="oj-gantt-task-backdrop"></path>
<path d="..." class="oj-gantt-task oj-gantt-task-bar"></path>
<text class="oj-gantt-task-label" ... >
Local Fauna Preview (Philippe Labaune Gallery)
</text>
</g>The <path> element containing the class="oj-gantt-task" looked like the right element to target for a click handler. So I defined a dynamic action to react to the Click event on the CSS class oj-gantt-task using the jQuery Selector with a dot before the class name as shown below:
oj-gantt-taskI also noticed the Gantt Chart Task Id value was part of the value of the id attribute on the <path> element’s containing parent <g> element. For example, for an event run with ID = 162, the id attribute of the <g> parent element for the clicked-on bar had a value like “_dvtActiveElement43100180_162“. I learned from my colleagues that the dynamic action event handler can access the triggering <path> element using the expression this.triggeringElement. So, my dynamic action event handler passes the triggering element to an eventRunIdForClickedGanttBar() helper function in my page. It returns the Task Id value by parsing it out of the parent element’s id attribute like this:
function eventRunIdForClickedGanttBar(p_this) {
return extractNumberFromUnderscoreString($(p_this).parent()[0].id);
}The calling dynamic action event handler looks like this. It sets the value of a hidden page item P14_SELECTED_EVENT_RUN_ID to hold the event run ID that was clicked on, sets the Request to EDIT_EVENT_RUN, and submits the page.
const lTaskId = eventRunIdForClickedGanttBar(this.triggeringElement);
// Set Task id to hidden page item, and submit
// the page with EDIT_TASK request
apex.page.submit({
request: "EDIT_EVENT_RUN",
set: {
"P14_SELECTED_EVENT_RUN_ID": lTaskId
}
});In the page’s Processing tab, an After Processing branch redirects to the Event page (7) when the Request = EDIT_EVENT_RUN, passing along this hidden page item value for the target page’s P7_EVENT_RUN_ID page item value. It also passes its own page number 14 as the value of the P7_CALLING_PAGE_ID page item. In contrast, when the Home page (1) calls the Event page to edit an event, its link passes in the value 1 for this page item.
The Event page uses the P7_CALLING_PAGE_ID item value to conditionally return to the right calling page when the user cancels or applies their changes in the Event page. The target Event page has a Pre-Rendering page process Get Event from Event Run Id that looks up the parent event ID to edit based on the “child” row’s event run ID passed in.
Saving Time During Event Data Entry
Each event has a duration in minutes, and each event run has a start time and end time. Using two dynamic actions on the Event page shown below, I speed up data entry by computing the duration if it’s null when a pair of Starts At and Ends At times are entered for an event run. In addition, once the duration is set, any other event runs entered benefit from another dynamic action that automatically computes the Ends At time once the user enters a Starts At and tabs out. This feature makes it very quick to enter all the dates and times a particular musical like “& Juliet” is offered during a trip edition.
The page contains the following two helper functions in its JavaScript Function and Global Variable Declaration section the the property editor:
function addMinutesToTime(time, minutes) {
return apex.date.format(
apex.date.add(
new Date("2023-01-01 "+time),
parseInt(minutes),
apex.date.UNIT.MINUTE),
"HH24:MI");
}
function minutesBetween(time1, time2) {
var date1 = apex.date.parse(time1, "HH24:MI");
var date2 = apex.date.parse(time2, "HH24:MI");
var diff = Math.abs(date2.getTime() - date1.getTime());
return Math.floor((diff / 1000) / 60);
}Then, the dynamic action on the interactive grid’s ENDS_AT column has a Set Value action step that executes JavaScript to call minutesBetween() to compute the duration of the event:
minutesBetween($v('STARTS_AT'), $v('ENDS_AT'))It only runs if the duration is currently null and both the STARTS_AT and ENDS_AT columns in the current row have a value using the client-side condition boolean expression:
$v('STARTS_AT') !== '' &&
$v('ENDS_AT') !== '' &&
$v('P7_DURATION_MIN') === ''Similarly, the dynamic action on the grid’s STARTS_AT column has a a Set Value action step that executes JavaScript to call addMinutesToTime() to compute the ENDS_AT time, based on the duration of the event:
addMinutesToTime($v('STARTS_AT'),
$v('P7_DURATION_MIN'))It only runs if the duration is not null, the STARTS_AT has a value, and the ENDS_AT is null using the client-side condition boolean expression:
$v('STARTS_AT') !== '' &&
$v('P7_DURATION_MIN') !== '' &&
$v('ENDS_AT') === '' Marking Event Runs as Starred/Ticketed
When reviewing a large list of events, there’s a good chance that you’re not interested in all of them. The app lets the user “star” a particular event run to mark it as a “favorite”. This results in setting the STARRED column value to “Y“. In addition, some trips involve a booking or ticketing process to confirm a seat for a particular event run. The app lets the user indicate they’ve confirmed their place by setting a Tickets switch to ON. In the figure below, I’m starring both Wednesday, September 20th performances of musical “& Juliet” and marking the 14:00 showing as ticketed.
Conditionally Coloring Selected Bars
It can be useful to conditionally color the Gantt chart bars based on application-specific criteria. In my app, I want “starred” events to show with one color that makes them distinct, while “ticketed” events show with another unique color. This was easy to accomplish. I first included the following SELECT list expression in the query for the Gantt chart region in the Schedule page (14):
case
when r.tickets = 'Y then 'tickets'
when r.event_starred = 'Y'
or r.event_run_starred = 'Y' then 'starred'
end as css_classesThen I set the Task CSS Classes property of the Gantt chart to &CSS_CLASSES. The final step was adding the following simple CSS class definitions to the page’s CSS > Inline section in the property editor:
.tickets {
fill: blue;
}
.starred {
fill: yellow;
}After the user applies changes on the Event page, it conditionally branches back to the calling page. Since the Schedule page passed its page number 14 into the P7_CALLING_PAGE_ID page item, the user returns to the Schedule page. As shown below, we immediately see conditionally colored event run bars reflected in the Gantt chart. As dictated by the above CSS classes, the bar for the ticketed event run is now blue while the bar for the starred one is now yellow.
Narrowing Focus: Hiding Clashing Events
When you have identified your starred event runs, it can be useful to narrow your focus to only see your favorites. This can help you decide which particular event runs to book or buy tickets for. The Schedule page has a switch at the top labeled Hide Starred Session Clashes. When this is switched on, as shown below the Gantt chart suppresses all other event runs that would overlap with any of your favorites. Very useful, since you can’t be in two places at once!
This feature is implemented directly in the SQL query for the Gantt chart region using an appropriate WHERE clause predicate. It uses the switch page item as a bind variable and uses a NOT EXISTS(…) clause to include any unstarred events that do not have clashes with any starred ones.
and (:p14_hide_starred_session_clashes = 'N'
or
(:p14_hide_starred_session_clashes = 'Y' and
((r.event_starred='Y' or r.event_run_starred='Y')
or
(r.event_starred='N'
and not exists (
select 1
from eba_demo_ann_event_runs_v starred_run
where (
starred_run.event_starred='Y' or
starred_run.event_run_starred='Y')
and ((
/* starts before and ends after current */
starred_run.starts_at < r.starts_at and
starred_run.ends_at > r.ends_at)
or (
/* starts after and ends before current */
starred_run.starts_at > r.starts_at and
starred_run.starts_at < r.ends_at)
or (
/* ends after current starts & before current ends */
r.starts_at <= starred_run.ends_at and
starred_run.ends_at <= r.ends_at)
or (
/* starts after current & before current ends */
r.starts_at <= starred_run.starts_at and
starred_run.starts_at <= r.ends_at)))))))Focusing Further: After Getting Tickets
After you booked your seat or bought your ticket for one particular showing, you no longer need to see all of the other available opportunities to see the same event. The Schedule page has a second switch at the top labeled Hide Already Ticketed Events. As shown below, the Tuesday 19th September showing of “& Juliet” does not appear since above we already marked the Wednesday 2pm showing as being ticketed.
Again, this feature is implemented with a different SQL WHERE clause predicate using this other switch page item as a bind variable. When the variable is set to “Y” it uses an event_id not in (…) with a subselect to exclude event runs for ticketed events from days other than the currently selected day (:p14_day).
and (nvl(:p14_hide_ticketed_from_other_days,'N') = 'N'
or (
nvl(:p14_hide_ticketed_from_other_days,'N') = 'Y'
and
event_id not in (
select distinct event_id
from eba_demo_ann_event_runs_v
where tickets='Y'
and event_day != :p14_day)
)
)Since the Gantt chart query references three different page items as bind variables, we have to ensure that its Page Items to Submit property mentions all their names so that their values are sent to the server whenever the region refreshes its query results. The figure below shows the three page item names in a comma-separated list.
Geocoded Date in the Venues Page
As shown below, the Venues page uses the techniques explained in my article Refitting Filtered Map Points to provide a filterable list of venues with an automatically-synchronized map that zooms and recenters to show the locations of the filtered venue list.
The companion Venue page for creating and editing venues uses a Geocoded Map page item to simplify finding the longitude and latitude for a given address. In order to have the Geocoded Map page item show the appropriate pin on the map when editing an existing venue, we use the following expression in the region query to create an appropriate SDO_GEOMETRY value based on the v.longitude and v.latitude values stored in the table. Importantly, the Query Only property of the corresponding P11_GEOCODED_ADDRESS page item is set to ON so that the APEX engine doesn’t try to update any SDO_GEOMETRY column in the underlying EBA_DEMO_ANN_VENUES table that has LATITUDE and LONGITUDE instead.
/*
* if latitude/longitude have values, then
* return sdo_geometry structure representing
* the point (longitude,latitude)
*/
case
when v.longitude is not null
and v.latitude is not null then
sdo_geometry(2001,
4326,
sdo_point_type(
v.longitude,
v.latitude,
null),
null,
null)
end geocoded_addressSince we’re not asking APEX to save the SDO_GEOMETRY value directly to an underlying SDO_GEOMETRY column, when the user enters a new address and triggers the geocoding, we need to pick out the longitude and latitude so they get stored correctly in the respective, underlying columns of the same name. The P11_GEOCODED_ADDRESS page item has a dynamic action named When Address Confirmed that reacts to the Result Selection [Geocoded Address] event to trigger two Set Value dynamic action steps to set the P11_LONGITUDE and P11_LATITUDE page items with the values the geocoding service has returned. They reference the this.data.longitude and this.data.latitude JavaScript expressions to get the job done.
APEX’s geocoding service depends on knowing the country code of the address the user is typing in. The Venue page has a Pre-Rendering page process in the Before Header section named Default Trip Info for Current Edition. It contains the following PL/SQL block that looks up the trip ID and country code based on the value of the current edition the user is working with. Note that it only assigns the P11_TRIP_ID if the primary key P11_CODE is null (meaning that we’re creating a new venue):
for j in (select e.trip_id,t.country_code
from eba_demo_ann_editions e
left join eba_demo_ann_trips t on t.id = e.trip_id
where e.id = :CURRENT_EDITION) loop
if :P11_CODE is null then
:P11_TRIP_ID := j.trip_id;
end if;
:P11_CURRENT_TRIP_COUNTRY_CODE := j.country_code;
end loop;Then, the Geocoded Address page item is configured to use the P11_CURRENT_TRIP_COUNTRY_CODE page item for geocoding country information. This ensures that when defining a venue for my Annecy trip it uses FR for France as the geocoding country and when creating one for my New York City trip, it uses US for the United States.
Let’s suppose we defined another trip for “Annecy Festival” with the country of France configured, then defined a new edition of this trip for the year 2024, and then finally set this new “Annecy Festival 2024” as the current trip event to work with using the Settings page explained in the next section. Then as shown below, the geocoding of venue addresses would be done with France as the country.
Settings Page
The Settings page (8) lets the user change four user preferences: the current trip edition to view/edit, whether to show countries and languages information, and the start time and end time to use in the Schedule Gantt chart page. The page shown below uses conditional Before Header computations to set the values of the page’s items based on first trying to use user preferences, then conditionally falling-back to using the same-named application items. The application item names (which match the corresponding user preferences) are CURRENT_EDITION, SHOW_COUNTRIES_AND_LANGUAGES, DAY_START_TIME, and DAY_END_TIME. I admit that I wasn’t super excited about the way I had to capture the Day Start Time and Day End Time using four separate select lists, but this was the user experience I had in mind for the task. Maybe a future APEX release will offer a time picker page item for this purpose.
To save the Settings page item changes back to the user preferences, the page has one page process in the Processing section for each user preference that each use the built-in User Preferences page process type to save the updated value to the corresponding same-named user preference. Another four Execute Code page processes in the same section set the preference values into the corresponding application items, since those are what get referenced in various SQL queries around the app.
Focusing Calendar on a Date Range
Once you’ve marked one or more event runs as ticketed, get a bird’s eye view of your trip on the Calendar page. This page focus the users attention on the current trip edition by ensuring the right start day is shown immediately. It sets the calendar to use the START_DATE of the current edition to be the current date displayed, and to start the week display with this date, too.
Assuming the calendar region has a static ID of calendar, setting its initial display date requires just one line of JavaScript in the Execute when Page Loads section of the page properties that calls the gotoDate() function on the calendar widget:
apex.region("calendar")
.widget()
.data('fullCalendar')
.gotoDate($v('P12_START_DATE'))With similar ease, after first computing the day of the week the START_DATE represents, we can set the first day of the calendar week in the calendar region’s Initialization JavaScript Function. The calendar expects its firstDay property to be a day number: 0=Sunday, 1=Monday, etc. I use a Pre-Rendering computation in the Before Header section to compute the value of a hidden P12_ZERO_BASED_FIRST_DAY using the following SQL query:
select to_number(to_char(first_day,'D'))-1
from eba_demo_ann_editions
where id = :CURRENT_EDITIONSetting the calendar’s firstDay property is essentially a one-line task. The code for the Initialization JavaScript Function on the Attributes tab of the property editor for the calendar region appears below. It assigns the value of the zero-based first day to the firstDay property of the pOptions object passed in.
function ( pOptions ) {
pOptions.firstDay = $v('P12_ZERO_BASED_FIRST_DAY');
return pOptions;
}Augmenting App Title with Page Name
It’s a personal preference of course, but as you can see in the previous screenshots, I like the “Top” style Navigation Menu layout since its options stay visible. I combine this style with the use of the After Logo position that lets me place additional context like the page name in the application navigation bar. This leaves more room for content on each page. For example, on the Calendar page I added a Page Title Static Content region with the following HTML Code:
<span class="apex-logo-text"> - Calendar</span>This uses the same font as the logo text, and adds a hyphen separator and the name of the current page. As shown below, I set its Template to None and its Position to After Logo. This combo gives me a result that feels clean and spacious, and I’ve done the same thing on every non-modal page of the app.
Grouping Event Runs by Event
The application’s Home page (1) has an Interactive Report showing a searchable list of all event runs for the current trip edition. To group the list of event runs by the event they belong to, I ran the page and used the Actions > Format > Control Break menu to add the Event break column as shown below.
Next, to ensure the event runs sort in the most logical sequence for the end user, I used the Actions > Data > Sort menu to configure the report to first sort by Event then sort by Event Day Number as shown here.
Finally, I saved the current state of the Interactive Report as the new default primary report for all users. As shown below, I used the Actions > Report > Save Report menu, choosing As Default Report Settings in the Save Report dialog, then ensured the Default Report Type was set to Primary in the Save Default Report dialog before clicking Apply.
Of course, the option to update the primary default report does not appear for end users. I was able to access it since I ran this page as a developer from the APEX Builder.
Formatting a URL as a Hyperlink
My colleague Jayson Hanes helped me figure out how to display an info icon on the Event page to open the event URL in a new browser tab. This should have been a simple task, but the first several approaches I tried didn’t work like I wanted. Luckily, Jayson gave me this helpful tip. The trick was to add a P7_URL_LINK page item of type Display Only to my page, set its Icon property to the fa-info-circle-o, then use the Advanced > Pre Text setting to add an enclosing, open tag for the hyperlink. It uses the substitution string &P7_URL!ATTR. in the value of the <a> tag’s href attribute. The special suffix !ATTR makes sure the value is escaped correctly to appear in an HTML attribute. I added a title attribute so the hyperlink would gain a “tooltip” when the end-user hovers over the info icon with their mouse.
<a href="&P7_URL!ATTR."
target="_blank"
title="Click to open this page in a new tab">Then I used the corresponding Post Text setting on the page item to add the closing </a> tag. This produced exactly the desired effect I wanted as shown below. Thanks, Jayson!
PWA: Instant Desktop/Tablet Use
I took most of the screenshots of the Activity Planner app in this article while running it as a Progressive Web App (PWA). This lets me launch the app directly from the dock of my Mac laptop whenever I need to use it. Combined with my enabling the Persistent Authentication feature at the APEX instance level, this produces a great user experience. I launch the planner app like any other app on my desktop or iPad and instantly continue my trip planning work.
Enabling an APEX app for PWA use takes just two clicks on the Shared Components > Progressive Web Page page in the APEX Builder. Just switch the Enable Progressive Web App switch to ON, as well as the Installable switch just below it. Enabling Persistent Authentication is an APEX instance administrator task in the Manager Instance > Security page.
Saving Quick SQL in Supporting Objects
The idea for this final tip was hatched after realizing every script in the Supporting Objects area of an APEX app could be made conditional. The feature uses the same options as a Server-side Condition elsewhere in the Builder. It offers a powerful way to control which scripts should run when. A simple corollary is that I can easily store the Quick SQL text for my application data model as a script with Condition = Never. This makes it easy to reference and maintain the QuickSQL as requirements change and enhancements get implemented, but ensures that APEX never tries to execute it.
Consistent Switch Value Labels in Grids
While doing final edits of this article I noticed that the Starred and Tickets switch columns in the Event page’s interactive grid would show values Yes and No for unmodified event run rows, but changed to show On and Off for rows I had edited. I resolved that by explicitly setting the On Label and Off Label properties for the Switch component settings in my app under Shared Components > Component Settings> Switch as shown below.
Planning Your Next Trip?
Just in case you might find my Activity Planner app useful, or even just educational to experiment with, you can download the app here. Its Supporting Objects scripts install the event run data for my most recent New York City trip, with none of the event runs starred or ticketed yet so you can experiment with those aspects yourself. Enjoy!
Dive Into APEX 