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.
- 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
- set title attribute for the cells – to show the session abstract when
hovering over a cell
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
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 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.
This is the end result – that I will consider my MVP:
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
jQuery Fundamentals for APEX PL/SQL programmers – https://oracle-patches.com/en/web/3405-jquery-fundamentals-for-apex-pl-sql-programmers
W3 Schools CSS color picker – https://www.w3schools.com/colors/colors_picker.asp