Some neat APEX tricks while building a Session Like application for our Conference image 31

Some neat APEX tricks while building a Session Like application for our Conference

Some neat APEX tricks while building a Session Like application for our Conference ConclusionOnStageLogoAMIS 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:

  1. 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
  2. Create a Form page on table COLLEAGUES. Set the default value for the P1_ID item to SYS_GUID()`
  3. When the Create button is clicked,
  4. the current value of P1_ID is saved to the Session State
  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)
    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
  7. the item P4_COL_ID is set from P1_ID
  8. the value of item P4_COL_ID is stored in APEX session state
  9. 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)
  11. 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
  12. P4_LIKE_ID is a hidden item
  13. 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.
  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, the record is Liked or Unliked.

image

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.

image

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.

image

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.

image

The form looks like this:

image

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

image

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)

SNAGHTML2950b3b

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:

image

The result looks like this:

image

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

image

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

image

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.

image

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.

image

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.

image

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/