In the last article, we created a simple APEX application fetching data about English football from the Football Web Pages site which provides an authenticated REST API.
However, all I really want to do is to quickly look at Kingstonian’s forthcoming fixtures for the next month. Fortunately, there is an FWP API providing that information.
Endpoint: https://football-web-pages1.p.rapidapi.com/fixtures-results.json
- Matches - The current list of matches for a competition/team
- The following parameters may be set:
- comp - The ID of the competition (note: one of “comp” or “team” is required)
- team - The ID of the team (note: one of “comp” or “team” is required)
We already have created an APEX report listing all the available Competitions (including the numeric ID values) so it would be useful to have a similar report listing all the Teams.
Teams
Endpoint: https://football-web-pages1.p.rapidapi.com/teams.json
- Teams - A list of the teams covered
- The following parameters may be set:
- comp - The ID of the competition
Create a new REST Data Source called ‘FWP Teams’ with this endpoint using the ‘Football Web Pages’ authentication method.
Create a new page ‘Teams’ with an Interactive Report fetching data from this REST Data Source.
Search for ‘Kingstonian’ to find out the numeric identifier for this club (236)
Fixtures
REST Data Source
The Fixtures API can take a query parameter which is either ‘competition’ or ’team’.
Thus far, we have only used a ‘Header’ to supply the credentials to access the API.
API query parameters are name-value pairs introduced by a ‘?’. This is analogous to database queries which filter the results returned by a SQL query in the ‘WHERE’ clause.
select *
from emp
where name = 'JONES';
The APEX fixtures page should support both of these API query parameters (‘competition’ and ’team’) and enforce that either one or the other is supplied.
Create a REST Data Source for ‘Fixtures’
- Rest Source Name: FWP-Fixtures
- Endpoint: https://football-web-pages1.p.rapidapi.com/fixtures-results.json
- Authentication: Football Web Pages
If you click ‘Discover’, APEX will return an error
ORA-20987: No data found in uploaded file.
This is because the REST Data Source is expecting a parameter and we have not specified one. To fix this, click ‘Advanced’ and add the required parameter.
- Parameter Type: Query String variable
- Parameter Name: team
- Value: 236 (Kingstonian)
Leave the HTTP method as the default (GET) and click ‘Discover’. APEX should display a list of Kingstonian’s results and forthcoming fixtures.
Add a second query parameter named ‘comp’ with no value.
Apply the changes to this REST Data Source.
Fixtures Page
Navigate back to Application Builder and create a blank page called ‘Fixtures’.
Search region
Create a region named ‘Search’. Change the template of the ‘Search’ region to ‘Collapsible’. The region template is in the ‘Appearance’ section.
Create two page items on the Search region
- Competition
- Team (uncheck ‘Start New Row’)
Create a button named ‘Go’ with the default action of ‘Submit Page’. Check the ‘Hot’ checkbox under ‘Appearance’.
Report region
Create a new region called ‘Fixtures’. This is simply an Interactive report based on the REST source ‘FWP Fixtures’.
Run the page. Hopefully, your screen should vaguely resemble this.
Clearly, this is just a checkpoint and not fully functional yet but I like to ‘release early and release often’ (if only to myself).
Joking apart, this approach is actually useful to demonstrate to an end user what the APEX UI will look like and how you can use standard APEX functionality to search and filter within the Interactive report.
No need to spend days mocking up wire frames of the proposed UI. In APEX, you can present a meaningful prototype using APEX early on.
Plus you can endlessly argue over the title, size, placement and colour of the ‘Go’ button.
Return to the ‘Fixtures’ page in Application Builder. Expand the ‘Fixtures’ region. You will see that APEX has helpfully added a section called ‘Parameters’. Expand this and you will see the two query parameters for this REST Data Source exposed here.
Edit the ‘comp’ parameter. Under ‘Value’, change the Type to ‘Item’ and select ‘P4_COMPETITION’ from the pop-up menu.
Similarly, change the value for the ’team’ parameter to the APEX page item P4_TEAM.
Save and run the page. You get an error but this is expected as we have not supplied any APEX page parameters (yet).
Enter ‘236’ into the Search region for ‘Team’ and click ‘Go’. You will see Kingstonian’s results and fixtures displayed.
Check this is actually working by changing the team to ‘1’. This will display results and fixtures for ‘Arsenal’ in the English Premier League.
This looks promising. APEX truly is a low code solution.
Now let’s look at the ‘Competition’ parameter.
Enter ‘2’ for ‘Competition’. This is for the English Championship (neither Kingstonian nor Arsenal play in this league).
Nullify the ‘Team’ parameter and click ‘Go’.
List of Values
This report is improving but needs more work. End users typically don’t know that they have to enter ‘1’ to get data for Arsenal. The ‘Competition’ and ‘Team’ parameters are clearly List of Values so we will implement that now.
Navigate to ‘Shared Components’ and add the following List of Values for ‘Competitions’
- Name: Competitions
- Type: Dynamic
- Data Source: REST Data Source
- Rest Data Source: FWP Competitions
- Return Column: ID
- Display Column: FULL_NAME
- Default Sort: ID
Once an APEX application has a REST Data Source available, it is available to all components (LOV’s, reports etc) - just like a conventional local database table.
Create a second LOV for ‘Teams’
- Name: Teams
- Type: Dynamic
- Data Source: REST Data Source
- Rest Data Source: FWP Teams
- Return Column: ID
- Display Column: FULL_NAME
- Default Sort: FULL_NAME
Navigate to the ‘Fixtures’ page and change the page items to use the newly created LOV’s.
Change the type of P4_COMPETITION to ‘Popup Lov’
Under ‘List of Values’, select
- Type: Shared Component
- List of Values: FWP-Competitions
- Display Extra Values: Unchecked
- Display Null Value: Checked
- Null Display Value: - Select -
Repeat this process for the P4_TEAM page item using FWP-TEAMS as the LOV.
Run the page. This looks better. Now we can select a Competition and a Team correctly.
You gleefully share your V2 prototype with a colleague for peer code review and her feedback is as follows:
- When clicking ‘Fixtures’, I get ‘ORA-20999: REST Data Source returned an HTTP error: HTTP 400: Bad request’
- If you enter a Competition only, it works fine.
- If you enter a Team only, it works fine.
- If you enter both a ‘Competition’ and ‘Team’, the results look weird. Should ‘Team’ be a cascading LOV based on the ‘Competition’ ?
- The column names and labels need tidying up. There are a lot of meaningless ID fields displayed.
- It would be nice to have the option to review past results separately from fixtures in the future.
- Performance - the Popup LOV’s for Competition and Team are sluggish. Why are they so S L O W ?
- The navigation menu looks chaotic and ugly.
In the next article, we will try to address this valid feedback.