Building a Conference Session Agenda with Oracle APEX – notes on Pivot, Modal Popup and jQuery

AMIS is part of the Conclusion ecosystem of over 20 companies, each with their own specialty and identity. Several times per year, we organize Conclusion on Stage – a conference that spans across the ecosystem. Presenters from most companies under the Conclusion umbrella submit session proposals. Close to 30 sessions are staged in five rooms and close to 200 participants attend these sessions.

I want to provide a Conference Agenda App that offers the audience easy access to the current agenda with information about which sessions take place when and where as well as details about the sessions. The would have to look like this:

and when the user clicks or taps on one of the cells, a popup should appear with the details about the session:

In a previous article, I have told about my first steps around the APEX app for the Conclusion on Stage conference.

In this article I will build on that story and show the steps I had to take to implement this app. The app is running on apex.oracle.com hosted and publicly accessible.

The steps:

• create tables sessions, rooms and slots; foreign keys from sessions to both rooms and slots
• create an APEX interactive grid for editing the session details (the initial data was loaded from an Excel document); the room and slot assignments are recorded through this grid – in the database
• create a database view that uses PIVOT to turn the five rows for one time slot (we have five rooms for the conference) into a single row with five columns (for each of the rooms and sessions taking place at that time)
• create an APEX interactive report agenda on top of that view – showing the sessions in a table-grid
• set the column headings to the names of the rooms
• create a modal page with a form on table region to show the de details from a single session
• create a link from the agenda to the modal page, passing the session id for the cell to the popup with the session details
• apply background color to all sessions starting at the same time

• set a CSS class per slot
• define the CSS styles for these classes, resulting in a color per time
  slot
• set title attribute for the cells – to show the session abstract when
  hovering over a cell
• add JavaScript to apply the CSS class defined on the cell contents to the
  cell’s parent TD element ; for sessions that last for more than a single slot,
  also apply the class to the corresponding TD element in the next row

Sources for the application described in this article are on GitHub: https://github.com/lucasjellema/conference-management 

1. Create tables sessions, rooms and slots

Three database tables are used in this application: one to hold all session details such as a speakers, title, abstract, tags, duration (loaded from an Excel file) and two for rooms and slots respectively. The slots table holds the start_time for each slot, the rooms table has the names for the rooms and a seq column that determines the sort order (based on the physical location of the rooms). The DDL file is nothing special – you can find it in the GitHub repo: https://github.com/lucasjellema/conference-management/blob/master/db.ddl

2. Create an APEX interactive grid for editing the session details

(the initial data was loaded from an Excel document); the room and slot assignments are recorded through this grid – in the database

Even though this is a very powerful page for inspecting and manipulating data, in terms of APEX development effort it is almost a piece of cake.

The interactive grid is created on the sessions table. I have decided which columns to show – in which order = and which ones to hide. I have specified the columns for Room and Session slot to be of type selectlist. I have specified how the values for these select lists are retrieved, using database queries:

and the Shared LOV component:

Using the Session Manager, I can define all session details including their slot and room allocations.

3. Create a view to produce rows per timeslots with columns per room

The Agenda overview I am looking for shows the data in a grid. Each cell in the grid corresponds to a session, each column to a room and each row to a slot. The sessions table has a row per session. My challenge: turn five rows into a single row with five columns.

This is where the SQL PIVOT operator comes into play. I use to pivot my five rows (grouped by time slot) into one row with columns per room. The SQL statement below joins sessions with rooms and slots, then pivots the session id values over the rooms. The other columns (start_slot and starttime) are used for an implicit group by. The session records are pivoted in groups with the same start_slot and starttime – maximum five rows. Their session id values are assigned to the column for their corresponding room assignment

select *
from  (select s.id sessie_id, start_slot, r.seq room , to_char(sl.start_time,’HH24:MI’) starttime from sessions s join rooms r on (s.room=r.id) join slots sl on (s.start_slot = sl.id ))
pivot  (  max( sessie_id) sessie_id
           for room in ( 1 as r1,2 as r2,3 as r3,4 as r4,5 as r5 )
        )

With this query at its core, I have created a Database View sessie_rooster (Session Schedule) that produces rows per timeslot with columns for the id, titel & speakers, slot_count and abstract for each session in that timeslot.

This view clearly is not meant for data manipulation – not without Instead Of trigger anyhow. It is a great way to underpin a visualization of the agenda data.

4. create an APEX interactive report agenda on top of that view – showing the sessions in a table-grid

Creating a new page with an interactive report on a table or view is very straightforward in APEX.

I have set most columns to hidden – only STARTTIME and the five R#_SESSIE columns are visible.

The column headings for those five columns are defined using the names of the five rooms where sessions take place, based on data in table ROOMS but hard coded into the application. It feels a little unfortunate but unavoidable.

This is what the agenda looks like to the end user at this stage:

I want the users to be able to click or tap on a cell and then bring up a popup window that provides details on the session.

5. create a modal page with a form on table region to show the de details from a single session

I create a new page – with a form on a table as a modal dialog. The page is public (no authentication required).

The wizard is straightforward enough:

The page does a fetch of a single row from table sessies, based on the value of item P5_ID. We have to make sure that this item is set with right session id when this modal page is loaded from a cell in the agenda.

…

6. create a link from the agenda to the modal page, passing the session id for the cell to the popup with the session details

It is remarkably simple to turn the cells in the report into links to the modal window popup. Change the type to Link for each of the five items R#_SESSIE

Define the Link details for each of these five items:

The page to link to is the modal page, page 5 in my application. The page item P5_ID in that page should be populated with the id of the session whose cell is clicked. This value is referred to with #R1_ID# for the R1_SESSIE item, and #R<number>_ID# for all items.

With this definition in place – and this is really all it takes – I can run the agenda again, and open the popup:

 

7. apply background color to all sessions starting at the same time

I am not a UX designer so maybe it is not a very good idea. But I thought it would be nice to have all sessions that start at the same time share a visual attribute – such as a background color. To that end, I define the link attributes for each of the five link items – using CSS classes per time slot:

The link will have a class set of slot_1, slot_2,… slot_9 – depending on the value of the start slot item. A second class that is assigned is slotcount_1, slotcount_2,… – depending on the duration in slots for the session. Finally, the title attribute is set, using the expression #R1_ABSTRACT#, #R2_ABSTRACT#, … , referring to the current abstract value. This will result in a the abstract of a session being displayed in a hover text.

The CSS classes have to be associated with specific style characteristics. This is done in the CSS | Inline Styles property of the Page:

With these definitions in place, I can run the Agenda once more:

Colors for all slots, and a hover text with the session abstract:

It is a good first step. However, I would like the entire cell to have the background color, not just the text. So instead of the <a> element rendered for the link, I have to get to the parent TD element and apply the same CSS class to that element.

Using a little JavaScript to apply the CSS class defined on the cell contents to the cell’s parent TD element, defined at Page level, this is quite simple:

Using jQuery to look for all elements with a certain slot_<slot sequence>  class and then set that same class on their parent element, I quickly have the result I wanted:

However, I want a little bit more. For sessions that last for more than a single slot, also apply the class to the corresponding TD element in the next row. Sessions that are longer than one slot have a slotcount value of 2 or 3 and a corresponding class assigned: slotcount_2 or slotcount_3.

A little additional JavaScript allows me to apply the slot_<slot sequence> class to the TD element right below the one holding the session:

This is the end result – that I will consider my MVP:

Resources

Some of the resources I have used for understanding APEX mechanisms and other trickery:

My own two articles with previous steps on the Conference Manager:

• Some Notes on my small steps with Oracle APEX – https://technology.amis.nl/2019/02/02/some-notes-on-my-small-steps-with-oracle-apex/
• Some neat APEX tricks while building a Session Like application for our Conference – https://technology.amis.nl/2019/02/04/some-neat-apex-tricks-while-building-a-session-like-application-for-our-conference/ 

All Things SQL – Chris Saxon – How to Convert Rows to Columns and Back Again with SQL (Aka PIVOT and UNPIVOT) – https://blogs.oracle.com/sql/how-to-convert-rows-to-columns-and-back-again-with-sql-aka-pivot-and-unpivot 

APEX Docs – Application Express App Builder User’s Guide 9.6.2 Managing Interactive Report Attributes – https://docs.oracle.com/database/apex-18.1/HTMDB/managing-interactive-report-attributes.htm#HTMDB-GUID-2CC35334-D1D8-459F-849A-6849CEB5820A 

Jeff Kemp on Oracle – articles on JavaScript: https://jeffkemponoracle.com/tag/javascript/

APEX Docs – JavaScript Reference (18.2) – https://docs.oracle.com/en/database/oracle/application-express/18.2/aexjs/toc.html

jQuery Fundamentals for APEX PL/SQL programmers – https://oracle-patches.com/en/web/3405-jquery-fundamentals-for-apex-pl-sql-programmers

Stack Overflow Oracle APEX format a cell based on users input  – https://stackoverflow.com/questions/17784633/oracle-apex-format-a-cell-based-on-users-input

Modal Dialog Dynamic Title – https://community.oracle.com/thread/4156461

W3 Schools CSS color picker – https://www.w3schools.com/colors/colors_picker.asp