Add a “Who’s Online Now” box to your APEX app
Something to file under “pointless fun”…
Allow your users to feel like they’re part of a community by letting them know who else is using the app at the same time.
- Open APEX Application Builder, and open the page you want to add this to.
- Click the Create Region icon.
- Choose Report, then SQL Report.
- Enter a title, e.g. “Who’s Online Now”. Click Next.
- Copy the following for the SQL Query (sorry about the poor formatting):
SELECT apex_user || ' (' || minutes_ago || DECODE(minutes_ago,1,' min ago)',' mins ago)') FROM ( SELECT DISTINCT apex_user, FIRST_VALUE(TRUNC(seconds_ago/60)) OVER (PARTITION BY apex_user ORDER BY seconds_ago) AS minutes_ago FROM apex_workspace_activity_log WHERE application_id = :APP_ID AND seconds_ago < 3600 AND apex_user NOT IN (:APP_USER, 'nobody') ORDER BY 2);
- Click Next. Click Create Region.
You can customise the region as much as you like. For example, I use:
- Template (under Region Definition) = “Sidebar Region”
- Report Template (under Report Attributes) = “template: 16. One Column Unordered List”
- Pagination Scheme = “- No Pagination Selected -“
- Headings Type = “None”
The query is based on the APEX view apex_workspace_activity_log which is supplied with the product. It looks at all session activity within the last hour by users other than the current user, and returns a list showing their most recent activity.
The only slight problem is that it doesn’t detect when someone logs out – they’ll stay in the report for up to an hour.