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 stages in five rooms and close to 200 participants attend these sessions.
My challenge in this article: I want to learn how many of my colleagues have a special interest in each of the proposed sessions. In order to prepare the right schedule for the event, I need to know which sessions are most popular and should have the largest rooms and not be scheduled at the same time. My solution is to create a simple web application that presents details on all submitted sessions and allows each colleague to indicate their likes for these sessions. I select APEX as my platform and apex.oracle.com as the hosted environment where it will run.
In this article, I will describe the steps I went through in order to create and publish this little application – and how I dealt with some of the challenges.
The following figure – complex as it may appear – provides a summary of the final result and how the users negotiates the flow through it.
Steps:
- Create an APEX application from an Excel sheet that contains all sessions – title, speakers, description, tags, duration and more
This will create a SESSIONS database table. I manually create – through Quick SQL – tables COLLEAGUES and LIKES. I also create View V_SESSION_LIKES to join LIKES with SESSIONS. More on this view a little later - Create a Form page on table COLLEAGUES. Set the default value for the P1_ID item to SYS_GUID()`
- When the Create button is clicked,
- the current value of P1_ID is saved to the Session State
- the new Colleague record is inserted into the Database Table COLLEAGUES
- navigation takes place to a second page (based on a link that also copies the value of P1_ID into P4_COL_ID)
Page 4 is of type List View, its contents derived from the View V_SESSION_LIKES and used in a custom markup; note that this page also contains a hidden item called P4_LIKE_ID - the item P4_COL_ID is set from P1_ID
- the value of item P4_COL_ID is stored in APEX session state
- in an after header, pre region process, a PL/SQL block is executed that generates LIKES records for the current colleague (from P4_COL_ID) and all Sessions
- when the region of type List View is rendered, its queries from view V_SESSION_LIKES; the where clause of this query filters on the LIKES records that are associated with the colleague record with the id value equal to the value stored in P4_COL_ID and retrieved using the APEX_UTIL.GET_SESSION_STATE function
the view returns the name of an image to render – depending on whether the state of a LIKES record is Y or not (Y being a like) - An onClick event handler is associated with the like icon; it invokes a JavaScript function. This function toggles the image – from liked to not particularly liked or vice versa. It also sets the value of the id for the clicked LIKES record in the P4_LIKE_ID item
- P4_LIKE_ID is a hidden item
- A Dynamic Action is associated with item P4_LIKE_ID; this action is triggered when the value of P4_LIKE_ID is changed. In our case, that means that when the like image is clicked, the invoked JavaScript function uses the APEX JavaScript library to set the item’s value apex.item().setValue() . This in turn triggers an AJAX call from the browser to the server.
- The dynamic action executes a PL/SQL function that updates a LIKES record in the database table. Depending on the state of the like icon, the record is Liked or Unliked.
This may see complicated. However, it is not all that hard. And perhaps I could have done all this in a simpler way – that I have not yet uncovered.
Let’s take a closer look at some of the steps (note: some code is found here in GitHub )
1. Create an APEX application from an Excel sheet
This really is not a challenge at all. Save the Excel sheet as a CSV file. Then just use the wizard.
This will create a SESSIONS database table.
I manually create – through Quick SQL – tables COLLEAGUES and LIKES. I also create View V_SESSION_LIKES to join LIKES with SESSIONS.
Look at the somewhat contrived way to sorting the records in a random order – to have every colleague see the sessions in a different order. Also not the where clause reference to the APEX_UTIL.GET_SESSION_STATE function used to filter the LIKES records by the current colleague’s id. Finally, note that like_icon expression – a CASE expression to return the image name depending on the like status.
2. Create a Form page on table COLLEAGUES.
Again, this is not a challenge. Just a simple step.
Set the default value for the P1_ID item to SYS_GUID(). I have also added a heading and a logo.
The form looks like this:
3. When the Create button is clicked and 4. the current value of P1_ID is saved to the Session State and 5. the new Colleague record is inserted into the Database Table COLLEAGUES
6. Navigation takes place to a second page (based on a link that also copies the value of P1_ID into P4_COL_ID)
The Processes tab contains the entry to causes the navigation to Page 4 when the Create button is click on. The value of item P1_ID is copied to P4_COLLEGA_ID (in Page 4)
Page 4 is of type List View, its contents derived from the View V_SESSION_LIKES and used in a custom markup; note that this page also contains a hidden item called P4_LIKE_ID
The custom markup for the List View region is not trivial at all:
The result looks like this:
7. the item P4_COL_ID is set from P1_ID and 8. stored in APEX session state and 9. used to generate LIKES records
in an after header, pre region process, a PL/SQL block is executed that generates LIKES records for the current colleague (from P4_COL_ID) and all Sessions
10. when the region of type List View is rendered, its queries from view V_SESSION_LIKES; the where clause of this query filters on the LIKES records that are associated with the colleague record with the id value equal to the value stored in P4_COL_ID and retrieved using the APEX_UTIL.GET_SESSION_STATE function
the view returns the name of an image to render – depending on whether the state of a LIKES record is Y or not (Y being a like);
The HTML mark up for rendering the Like image is this: <span aria-hidden=”true” class=”&LIKE_ICON.” onclick=”thumbimage(‘&THE_LIKE.’, ‘&ID.’, this) “></span>. Note the substitution variables for LIKE_ICON, THE_LIKE and ID – all replaced by APEX upon rendering with the values queried from the V_SESSION_LIKES view.
11. An onClick event handler is associated with the like icon – calling function thumbimage
The onClick handler invokes a JavaScript function. This function toggles the image – from liked to not particularly liked or vice versa. It also sets the value of the id for the clicked LIKES record in the P4_LIKE_ID item
Note how the APEX JavaScript library is used to programmatically set the value of item P4_LIKE_ID – using the id value from the session record that was clicked. Note how an n(ot paticularly like) or l(ike) is prepended to the id value.
12. P4_LIKE_ID is a hidden item and 13. A Dynamic Action is associated with it
item P4_LIKE_ID; this action is triggered when the value of P4_LIKE_ID is changed. In our case, that means that when the like image is clicked, the invoked JavaScript function uses the APEX JavaScript library to set the item’s value apex.item().setValue() . This in turn triggers an AJAX call from the browser to the server.
14. The dynamic action executes a PL/SQL function that updates a LIKES record in the database table
Depending on the state of the like icon – reflected in the first character in the string passed in P4_LIKE_ID – the record is Liked or Unliked.
Through the Items to Submit property, I have specified that the new value assigned in the browser to P4_LIKE_ID should be passed to this PL/SQL code – though no other items’ values are required.
Summary
I set both pages to be accessible by unauthenticated users. I can now share the URL to all my colleagues and have them indicate the sessions they like. I have created my own simple report to check on the best liked sessions.
It is quite satisfying to find how well the application looks and functions on mobile devices. Without any effort on my part.
Resources
Icons – including Awesome – available with APEX: https://apex.oracle.com/pls/apex/f?p=42:icons
In the client set value for hidden page item when the event of interest occurs
https://apex.oracle.com/pls/apex/germancommunities/apexcommunity/tipp/6341/index-en.html
Docs on JavaScript in APEX: https://docs.oracle.com/en/database/oracle/application-express/18.2/aexjs/toc.html
JQuery in APEX: https://oracle-patches.com/en/web/3405-jquery-fundamentals-for-apex-pl-sql-programmers
List View with custom layout and embedded JavaScript – https://krutten.blogspot.com/2018/06/46-list-view-in-apex-181.html
Client Side item manipulation – https://apex.oracle.com/pls/apex/germancommunities/apexcommunity/tipp/6341/index-en.html
Application Context and APEX – https://jeffkemponoracle.com/2015/11/apex-5-application-context/
