The app shell and page flow

In this part you create Turnout in App Builder and build its first five pages: Discover events, Clubs, a Club page, an Event page and Sign in. Visitors can browse without an account. Signed-in members can join a club and RSVP, and every one of those actions goes through the packages you wrote in Part 3.

Before you start

You need the twelve tables, the sample data and the seven packages from Parts 2 and 3. To check, run this in SQL Workshop › SQL Commands:

select count(*) as packages from user_objects
 where object_type = 'PACKAGE' and object_name like 'TO\_%' escape '\';

It should say 7. If it says 0, or you're starting here, download the Start here script below and run it in SQL Workshop › SQL Scripts (Upload, then Run). It builds everything Part 4 needs in about a minute.

Each lesson has a Build it section with numbered steps. Do them in order; each ends with a check, so you know it worked before you move on. If anything goes wrong, the Finished app in the downloads is exactly what you should have at the end of this part.

Lesson 4.1 — Create the app and plan page numbers

Outcome: you create the Turnout application with the right settings from the start, and number its pages so the number says who can open them.

Why an almost empty app? The Create App wizard can generate dashboards, forms and reports on your tables. But generated forms write straight to the tables, which skips every rule in your Part 3 packages. So you start with a shell and add each page on purpose.

The page numbering plan (from Lesson 1.4):

Gaps (10, 20, not 3, 4) leave room to add related pages later without renumbering.

Build it

1. Open App Builder, click Create, then New Application.
2. Name: Turnout.
3. Click Appearance (the pencil next to it). Set Navigation to Top, then Save Changes.
4. Leave Pages as the single Home page. Leave every Feature unchecked.
5. Under Settings, check that Authentication is Oracle APEX Accounts. You'll switch to email codes and Google in Part 5.
6. Click Create Application. APEX builds two pages: 1 Home and 9999 Login Page.
7. Open Shared Components › Application Definition. Set Application Alias to TURNOUT. Then open the Error Handling section and set Error Handling Function to to_err.apex_error_handler (Lesson 3.6). Click Apply Changes.
8. Still in Application Definition, open Properties and switch Friendly URLs on. Apply Changes.
9. Open Shared Components › Navigation Menu, then the list Navigation Menu. Click the Home entry and change its List Entry Label to Discover. Apply Changes.

Check: click Run Application. You sign in with your workspace username, then see a page called Home with Discover in the top menu. Your address bar shows /r/<your workspace>/turnout/home, not f?p=.

Lesson 4.2 — Public pages and friendly URLs

Outcome: you build pages 1, 2, 10 and 20 as public pages that anyone can open from a shared link.

The decision. Discovery pages are public: a link Priya posts in her running group's chat opens straight away, and signing in is asked for only when someone acts. Three settings make that safe:

Build it: page 1, Discover events

1. Open page 1 in Page Designer. In the Property Editor on the right, with the page selected: Name Discover Events, Alias DISCOVER, Title Discover events.
2. Scroll to Security: Authentication Page Is Public, Page Access Protection Unrestricted.
3. If the page has any regions the wizard made, right-click each in the Rendering tree and Delete it.
4. Right-click Breadcrumb Bar in the Rendering tree, Create Region. (If the tree doesn't show Breadcrumb Bar, right-click Body instead, then set Layout › Slot to Breadcrumb Bar: a page only lists the slots it already uses.) Set Name and Title Find your people, Type Static Content, Source › HTML Code Running, cycling, chess, book clubs and more. See what's on, then join the clubs you like. Under Appearance, set Template to Hero.
5. Right-click Body, Create Region. Name and Title Upcoming events, Type Cards. Under Source, set Type SQL Query and paste:

select e.id,
       e.title,
       c.name as club_name,
       cat.name as category,
       -- Shown in the club's own time zone (Lesson 2.5)
       to_char(e.starts_at at time zone c.time_zone,
               'fmDy, Mon DD "at" HH:fmMI AM') as event_when,
       nvl(v.name, 'Meeting point to be announced') as venue_name,
       case
           when e.capacity is null then 'Open to all'
           when to_rsvp_api.spots_left(e.id) = 0 then 'Waitlist'
           else to_rsvp_api.spots_left(e.id) || ' spots left'
       end as spots_label
  from to_events e
  join to_clubs c        on c.id = e.club_id
  join to_categories cat on cat.id = c.category_id
  left join to_venues v  on v.id = e.venue_id
 where e.status     = 'PUBLISHED'
   and c.status     = 'ACTIVE'
   and c.visibility = 'PUBLIC'
   and e.starts_at  > systimestamp
 order by e.starts_at

6. Click the region's Attributes tab. Set Card › Primary Key Column 1 ID, Title › Column TITLE, Subtitle › Column CLUB_NAME, Body › Column EVENT_WHEN, Secondary Body › Column VENUE_NAME, Icon and Badge › Icon Source Initials, Icon Column CATEGORY, Badge Column SPOTS_LABEL. Set Pagination › Type to Scroll.
7. Save and run the page.

Check: you see a hero saying "Find your people" and a page of event cards, each with a club, a time, a meeting point and a badge like "3 spots left". Two details come from earlier parts: times are in the club's time zone (Lesson 2.5), and "spots left" comes from to_rsvp_api.spots_left, so the page and the RSVP rules can never disagree.

Build it: page 2, Clubs

1. Click Create Page (the + at the top of Page Designer), choose Blank Page. Page Number 2, Name Browse Clubs, Page Mode Normal. Under Navigation, switch Use Breadcrumb off and Use Navigation on, with a New Navigation Menu Entry called Clubs. Create Page.
2. Page properties: Alias CLUBS, Title Clubs, Authentication Page Is Public, Page Access Protection Unrestricted.
3. Create a Cards region in Body, Name and Title Clubs, with this query:

select c.id,
       c.name,
       cat.name || nvl2(c.city, ' · ' || c.city, null) as category_city,
       c.description,
       (select count(*)
          from to_club_members m
         where m.club_id = c.id
           and m.status  = 'ACTIVE') || ' members' as member_count,
       case c.join_policy
           when 'OPEN'     then 'Anyone can join'
           when 'APPROVAL' then 'Organizers approve members'
           else 'Invite only'
       end as join_policy_label
  from to_clubs c
  join to_categories cat on cat.id = c.category_id
 where c.status     = 'ACTIVE'
   and c.visibility = 'PUBLIC'
 order by c.name

4. Attributes: Primary Key Column 1 ID, Title NAME, Subtitle CATEGORY_CITY, Body DESCRIPTION, Secondary Body JOIN_POLICY_LABEL, Icon Source Initials, Icon Column NAME, Badge Column MEMBER_COUNT, Pagination Scroll.

Check: run it. The top menu shows Discover and Clubs, and the Clubs page lists the active public clubs, Tampa Runners and Bay Chess Club among them.

Build it: page 10, Club

1. Create Page › Blank Page, number 10, name Club, Use Breadcrumb off, Use Navigation off. Page properties: Alias CLUB, Title &P10_NAME., public and unrestricted as before.
2. Create a Static Content region in Body: Name Club, Title &P10_NAME. (leave Text empty).
3. Right-click the Club region, Create Page Item, five times:

4. Under Pre-Rendering, right-click Before Header, Create Process. Name Load club, Type Execute Code, and paste:

select c.name,
       c.description,
       cat.name || nvl2(c.city, ' in ' || c.city, null)
         || '. ' || case c.join_policy
                       when 'OPEN'     then 'Anyone can join.'
                       when 'APPROVAL' then 'Organizers approve new members.'
                       else 'Invite only.'
                    end
  into :P10_NAME, :P10_ABOUT, :P10_DETAILS
  from to_clubs c
  join to_categories cat on cat.id = c.category_id
 where c.id     = :P10_CLUB_ID
   and c.status = 'ACTIVE';

-- The signed-in person's membership, if any
begin
    select initcap(status)
      into :P10_MY_STATUS
      from to_club_members
     where club_id = :P10_CLUB_ID
       and user_id = to_security.current_user_id;
exception
    when no_data_found then
        :P10_MY_STATUS := null;
end;

5. Right-click Body, Create Region again, so it lands below Club: Name and Title Upcoming events, Type Cards. Under Source, set Type SQL Query and paste the query below. Set Source › Page Items to Submit to P10_CLUB_ID, and the Attributes as on page 1 but with no Subtitle or Icon (Title TITLE, Body EVENT_WHEN, Secondary Body VENUE_NAME, Badge SPOTS_LABEL, Primary Key ID).

select e.id,
       e.title,
       to_char(e.starts_at at time zone c.time_zone,
               'fmDy, Mon DD "at" HH:fmMI AM') as event_when,
       nvl(v.name, 'Meeting point to be announced') as venue_name,
       case
           when e.capacity is null then 'Open to all'
           when to_rsvp_api.spots_left(e.id) = 0 then 'Waitlist'
           else to_rsvp_api.spots_left(e.id) || ' spots left'
       end as spots_label
  from to_events e
  join to_clubs c       on c.id = e.club_id
  left join to_venues v on v.id = e.venue_id
 where e.club_id   = :P10_CLUB_ID
   and e.status    = 'PUBLISHED'
   and e.starts_at > systimestamp
 order by e.starts_at

6. Join comes in Lesson 4.4, once there's someone to sign in as.

Build it: page 20, Event

1. Create Page › Blank Page, number 20, name Event, Use Breadcrumb off, Use Navigation off. Alias EVENT, Title &P20_TITLE., public and unrestricted.
2. A Static Content region in Body: Name Event, Title &P20_TITLE..
3. Page items in the Event region:

4. A Before Header process, Name Load event, Type Execute Code:

declare
    l_left number;
begin
    select e.title,
           c.name,
           to_char(e.starts_at at time zone c.time_zone,
                   'fmDay, Month DD "at" HH:fmMI AM')
             || ' (' || c.time_zone || ')',
           nvl(v.name || nvl2(v.notes, '. ' || v.notes, null),
               'Meeting point to be announced'),
           e.description
      into :P20_TITLE, :P20_CLUB, :P20_WHEN, :P20_WHERE, :P20_DESCRIPTION
      from to_events e
      join to_clubs c       on c.id = e.club_id
      left join to_venues v on v.id = e.venue_id
     where e.id     = :P20_EVENT_ID
       and c.status = 'ACTIVE';

    l_left := to_rsvp_api.spots_left(:P20_EVENT_ID);
    :P20_SPOTS := case
                      when l_left is null then 'Open to all'
                      when l_left = 0     then 'Full: new RSVPs join the waitlist'
                      else l_left || ' left'
                  end;

    begin
        select status,
               case status
                   when 'GOING'      then 'You''re going'
                                          || case when guests > 0 then ' with ' || guests || ' guest(s)' end
                   when 'WAITLISTED' then 'On the waitlist, number ' || waitlist_position
                   when 'CANCELLED'  then 'You cancelled'
                   when 'ATTENDED'   then 'You were there'
                   when 'NO_SHOW'    then 'You RSVPed but didn''t check in'
               end
          into :P20_MY_STATUS, :P20_MY_STATUS_TEXT
          from to_rsvps
         where event_id = :P20_EVENT_ID
           and user_id  = to_security.current_user_id;
    exception
        when no_data_found then
            :P20_MY_STATUS      := null;
            :P20_MY_STATUS_TEXT := null;
    end;
end;

Build it: link the cards to the pages

1. On page 1, right-click the Upcoming events region, Create Action. Type Full Card. Click Link › Target: Page 20, set item P20_EVENT_ID to &ID., Clear Cache 20. OK.
2. On page 2, the same on the Clubs region: target page 10, P10_CLUB_ID &ID., Clear Cache 10.
3. On page 10, the same on Upcoming events: target page 20, P20_EVENT_ID &ID., Clear Cache 20.

Check: sign out (or open a private browser window) and go to Discover. Click Saturday Long Run: the Event page shows Tampa Runners, a time like "Saturday, October 24 at 7:00 AM (America/New_York)", five days after the day you ran the seed, the meeting point and the spots. Go to Clubs › Tampa Runners: its events are listed, and clicking one opens it. No sign-in was asked for. Look at the address bar: it says /turnout/event?p20_event_id=…, a link anyone can open.

Going further. Event links still carry a number. As an exercise, give page 20 two hidden items, P20_CLUB_SLUG and P20_EVENT_SLUG, for the two slugs (not P20_CLUB: that name is taken by the Club display item), and look the id up in a Before Header process. The link becomes …/event?p20_club=tampa-runners&p20_event=saturday-long-run: this is exactly why Lesson 2.3 made slugs unique.

Lesson 4.3 — Normal pages vs modal dialogs

Outcome: you can decide whether a Turnout page should be a full page or a dialog that opens on top of another.

The question to ask: does the person need to come straight back to where they were?

In APEX a dialog is just a page whose Page Mode is Modal Dialog. Three things change: any link to it opens it on top of the current page; it uses the dialog template, with buttons in its footer; and a Close Dialog process ends it and tells the page underneath (Lesson 4.4).

Turnout's first RSVP is a button on page 20 itself, because that's simpler to learn. You'll build the dialog version as an exercise once RSVP works.

Lesson 4.4 — Branches, redirects and closing dialogs

Outcome: members can join a club and RSVP, and land back on the same page with a message that says what happened.

Grace taps RSVP on the Saturday Long Run. What should she see next? The same event page, refreshed, with a message in words: going or waitlisted. That takes three pieces working together: a process that calls the package and turns its answer into a message, a branch back to the page carrying the event id, and the Before Header process you already have, which reloads her status so the buttons change.

If the package raises an error (RSVPs closed, too many guests), the branch never runs. APEX stays on the page and shows the message from TO_ERR (Lesson 3.6). You write no error handling on the page at all.

Build it: RSVP on page 20

1. Add a Number Field item to the Event region: Name P20_GUESTS, Label Guests, template Optional - Above, Default › Static Value 0, and this Server-side Condition (Expression, PL/SQL):

:APP_USER <> 'nobody' and nvl(:P20_MY_STATUS, 'NONE') not in ('GOING', 'WAITLISTED')

2. Right-click the Event region, Create Button, three times:

3. On CANCEL_RSVP, under Behavior, switch Requires Confirmation on and set the Message to Cancel your RSVP? If you had a spot, the next person on the waitlist gets it. and Style Warning
4. Click the Processing tab (the second icon at the top of the left pane). Right-click Processes, Create Process: Name RSVP, Type Execute Code, Server-side Condition › When Button Pressed RSVP:

apex_application.g_print_success_message :=
    case to_rsvp_api.rsvp(:P20_EVENT_ID, nvl(:P20_GUESTS, 0))
        when 'GOING' then 'You''re going. See you there.'
        else 'The event is full, so you''re on the waitlist. We''ll email you if a spot opens.'
    end;

5. Another process: Name Cancel RSVP, When Button Pressed CANCEL_RSVP, Success Message Your RSVP is cancelled.:

to_rsvp_api.cancel_rsvp(:P20_EVENT_ID);

6. Right-click After Processing, Create Branch. Behavior › Type Page or URL (Redirect), Target: page 20, set P20_EVENT_ID to &P20_EVENT_ID.. Switch Include Process Success Message on.

Build it: Join on page 10

1. On page 10, two buttons in the Club region, both Layout › Slot Edit and Hot on:

2. A process Join club, When Button Pressed JOIN:

apex_application.g_print_success_message :=
    case to_club_api.join_club(:P10_CLUB_ID)
        when 'ACTIVE' then 'Welcome! You''re a member.'
        else 'Request sent. An organizer will approve it.'
    end;

3. A branch After Processing to page 10, with P10_CLUB_ID &P10_CLUB_ID. and Include Process Success Message on.

Build it: people to sign in as

First, refresh the sample data. The sample events are dated from the day you ran the seed, and Tuesday Track Intervals is only two days after that. In SQL Workshop › SQL Scripts, run turnout_seed.sql again now, so the walkthrough below matches.

Until Part 5, Turnout signs people in with your workspace's own accounts. Turnout knows who you are by matching your username to an email in TO_USERS, so create accounts named after two sample people.

1. Open the Administration menu (the person-and-key icon at the bottom of the dark bar on the left) and choose Manage Users and Groups, then Create User.
2. Username grace.kim@example.com, Email Address the same, a Password you'll remember. Uncheck Require Change of Password on First Use. Create User.
3. Do the same for omar.haddad@example.com.

Check: run Turnout and sign in as grace.kim@example.com.

• Open Tuesday Track Intervals. Grace is "On the waitlist, number 1", so the button says Cancel my RSVP. Click it and confirm: "Your RSVP is cancelled." Now click RSVP: the event is still full, so she's back on the waitlist, but at number 2. Lucas moved up when she left. That's Lesson 3.5 working through your page.
• Open Clubs › Bay Chess Club and click Join this club: "Request sent. An organizer will approve it.", because Bay Chess Club approves its members (Lesson 3.3).

Sign out and sign in as omar.haddad@example.com.

• Open Saturday Long Run, set Guests to 3, and click RSVP. You get "You can bring up to 2 guests." from TO_ERR, and you stay on the page with your 3 still in the box. Change it to 1 and RSVP again: "You're going. See you there.", and Your RSVP says "You're going with 1 guest(s)".

You changed the sample data. Before you run the Part 3 tests again, run turnout_seed.sql again to put it back.

Dialogs close instead of branching. If you build page 35 as a dialog (the exercise in Lesson 4.5), its last step is a Close Dialog process instead of a branch, and page 20 refreshes itself with a Dialog Closed dynamic action (Part 8).

Lesson 4.5 — Deep links: sign in and come back

Outcome: turn on Deep Linking, and know when APEX brings someone back to the page they asked for after they sign in.

Try the problem first. Sign out, open an event, tap Sign in to RSVP, and sign in. You land on Discover, and have to find the event again. That's the moment people give up.

APEX can bring them back, when two things are true:

1. Deep Linking is enabled. When someone asks for a page that needs sign-in, APEX remembers the page and its items, shows the sign-in page, then sends them on.
2. The link points at a page that needs sign-in, not at the sign-in page itself. Linking straight to page 9999 throws the destination away, because the sign-in page is public: there's nothing to come back to.

Turnout's Sign in to RSVP button links straight to page 9999, so for now it still lands on Discover. Part 7 adds pages that need sign-in, such as My events, and that's where you'll see deep linking work.

Build it

Shared Components › Security Attributes. Under Session Management, set Deep Linking to Enabled. Apply Changes.

Check: the setting shows Enabled. In Part 7, sign out, paste the address of My events into the browser, and sign in: you land on My events, not Discover.

Exercise (not in the finished app). Make Sign in to RSVP bring people back. Create page 35, RSVP, as a Modal Dialog with Authentication Page Requires Authentication: a hidden P35_EVENT_ID, a number field P35_GUESTS, an RSVP button, a process calling to_rsvp_api.rsvp(:P35_EVENT_ID, nvl(:P35_GUESTS, 0)) when it's pressed, then a Close Dialog process. Point page 20's SIGN_IN button at page 35 with P35_EVENT_ID set to &P20_EVENT_ID.. A visitor now signs in and lands on the dialog for the same event. Keep this in a copy of your app: the later parts assume SIGN_IN still goes to page 9999.

Lesson 4.6 — Breadcrumbs: a way back up

Outcome: the Club and Event pages show where you are, "Clubs \ Tampa Runners \ Saturday Long Run", and each step is a link back.

The decision. The top menu takes you to Discover and Clubs, but once you're on a club or an event there's no visible way back up. A breadcrumb fixes that. In APEX it's a shared component, a tree of entries, one per page, each pointing at its parent. A Breadcrumb region on a page draws the path from the top of the tree down to that page's entry.

One wrinkle: the Club entry has to show the club's name, and the Event page needs it too, for the middle step. So the name lives in two application items, small variables that belong to the session rather than to one page. Each page's load process sets them, and the breadcrumb reads them with &CURRENT_CLUB_NAME..

Your app may already have a Clubs entry; Turnout's does. If yours doesn't, you'll make it first.

Build it: the application items

1. Shared Components › Application Items › Create. Name CURRENT_CLUB_ID, Scope Application, Session State Protection Restricted - May not be set from browser (the default). Create Application Item.
2. Again for CURRENT_CLUB_NAME.

Restricted means a visitor can't set them from a link: only your PL/SQL can.

Build it: the breadcrumb entries

1. Shared Components › Breadcrumbs, open Breadcrumb (the Create Application wizard made it). If the tree on the left has no Clubs, click New Entry and make it: Page 2, no parent, Short Name Clubs, Target page 2, Static ID clubs, Create Entry.
2. Click Clubs in the tree, then New Entry:

Create Entry.

3. Click the new entry in the tree, then New Entry again: Page 20, Parent Entry the club entry (Page 10), Short Name &P20_TITLE., Synchronize unchecked, Target page 20 with P20_EVENT_ID / &P20_EVENT_ID., Static ID event. Create Entry.

Build it: the regions

1. Page 10. In Page Designer, right-click Body, Create Region, then set Layout › Slot to Breadcrumb Bar (a page only lists the slots it uses). Name Breadcrumb, Type Breadcrumb, Source › Breadcrumb Breadcrumb, Appearance › Template Title Bar. The Title Bar template's default option, Use Current Breadcrumb Entry, shows the club's name as the page heading.
2. The breadcrumb now carries the club's name, so the Club region doesn't need to repeat it: set its Title to Club details.
3. Open the Load club process and add two lines at the end:

-- For the breadcrumb: which club we're in
:CURRENT_CLUB_ID   := :P10_CLUB_ID;
:CURRENT_CLUB_NAME := :P10_NAME;

4. Page 20. The same Breadcrumb region, in the Breadcrumb Bar, Title Bar template. Set the Event region's Title to Event details.
5. In page 20's Load event process, the first select already joins the club. Add two columns and two items to it, so the end of the column list and the into read:

           e.description,
           e.club_id, c.name  -- for the breadcrumb
      into :P20_TITLE, :P20_CLUB, :P20_WHEN, :P20_WHERE, :P20_DESCRIPTION,
           :CURRENT_CLUB_ID, :CURRENT_CLUB_NAME

Check: sign out and open Clubs › Tampa Runners. Above the page you see "Clubs \" and the heading Tampa Runners. Open Tuesday Track Intervals: the path reads "Clubs \ Tampa Runners \" with the event as the heading. Click Tampa Runners in the path and you're back on the club. Now open Bay Chess Club from Clubs and one of its events: the middle step says Bay Chess Club, because each load process set the items afresh.

Checkpoint

Turnout runs: four public pages anyone can browse from a shared link, with breadcrumbs on the club and event pages, a sign-in page, and Join and RSVP for signed-in members, all through your Part 3 packages.

Compare with the finished app. Open the downloaded turnout-app-part-4.zip and read pages/p00020-event.apx in any text editor. It lists every region, item, button and process on the page, with the same names you used. If your page behaves differently, the difference is usually in there.

Catching up instead? Import turnout-app-part-4.zip in App Builder › Import. When APEX offers to install supporting objects, click Install Supporting Objects only if your schema has no Turnout tables yet; they install the tables, sample data and packages for you. Then do "People to sign in as" in Lesson 4.4.

The exercises from Lessons 4.2 and 4.5 (slug links and the RSVP dialog) aren't in the finished app: they're yours.