Building an App from a Spreadsheet using Oracle APEX

Introduction

Description

Oracle APEX is a feature of Oracle Database, including the Autonomous Data Warehouse (ADW) and Autonomous Transaction Processing (ATP), Autonomous JSON Database (JSON) and APEX Application Development (APEX) services. Oracle APEX is a low-code development environment with various features and tools, where you can create an application fast. In the future, rather than emailing spreadsheets around to gather information, you will know how to easily create an app in minutes using APEX and email the URL instead. The result will be a secure, scalable, multi-user app that provides a single source-of-truth for your data!

Lab Objectives

• Build your first app by uploading a spreadsheet
• Improve the Faceted Search page
• Improve the Interactive Report and form pages
• Add Maps region and install PWA
• Link pages

Lab Introduction Video

Link to YouTube Video

Intended Audience

• Cloud sales and engineering
• Developers

Prerequisites

• Basic cloud familiarity

Downloads

https://luna.oracle.com/api/v1/labs/69cf8318-59aa-44cd-b473-eddb1b1a78d8/gitlab/tutorial/files/spreadsheet-app.sql

Resources

• APEX on Autonomous
• APEX Collateral
• Tutorials
• Community
• External Site + Slack
• App Builder User's Guide

Contact us

Having an issue with the lab? Have an idea on how it could be made better? Want to tell us how awesome the lab is? Click the icon below to contact the team and let us know your feedback.

Task 1: Create an APEX Workspace

Introduction

Oracle APEX is a low-code application platform for Oracle Database. APEX Application Development, Autonomous Data Warehouse (ADW), Autonomous Transaction Processing (ATP), and Autonomous JSON Database are fully managed services, pre-integrated and pre-configured with APEX, for rapidly building and deploying modern data-driven applications in Oracle Cloud. Business users, citizen, and application developers can create enterprise apps 20X faster with 100X less code — without having to learn complex web technologies with just a browser. There are many ways in the Oracle Cloud to create APEX workspaces. Through ATP, ADW, directly on apex.oracle.com, or from the OCI Console in the Developer section. We will use the Developer section in OCI.

What is an APEX Workspace?

An APEX Workspace is a logical domain where you define APEX applications. Each workspace is associated with one or more database schemas (database users) which are used to store the database objects, such as tables, views, packages, and more. APEX applications are built on top of these database objects.

1. From the OCI console, click on the services menu on the upper-left corner of the screen. From the navigation menu, select Developer Services and then APEX Instances under the APEX Application Development heading.

2. Click on the List Scope drop down and select your assigned compartment. Click the + / - buttons to expose the assigned compartment and select it.

3. After selecting your assigned compartment, click the Create APEX Service button.

4. Accept all of the defaults in the resulting dialog. You will need to provide a password and confirm it before proceeding. APEX uses the same password complexity rules as the Oracle database. For convenience, use the following password. Open a text editor on the desktop and copy/paste this password for future reference. You'll need it to login to the instance. Click Create APEX Service.

SecretPassw0rd

5. Your APEX instance will begin provisioning and the icon will turn orange indicating it's in progress.

6. In a few moments the provisioning process will complete, the icon will turn green, and the title will change to Available.

Before using a new APEX Service you need to create the APEX workspace and create an APEX user account.

7. On the APEX Instance Details, click Launch APEX.

8. The Administration Services sign-in page appears. Enter the password you used to create the service and click the Sign In to Administration button.

9. Click Create Workspace.

10. Click the New Schema button to select how you'd like to create your workspace.

11. In the Create Workspace dialog, enter the following and click Create Workspace.

12. In the APEX Instance Administration page, click the DEMO link in the success message.

Note: This will log you out of APEX Administration so that you can log into your new workspace.

13. On the APEX Workspace log in page, verify the credentials provided and enter the password provided above. Check the Remember workspace and username checkbox, and then click Sign In.

14. Congratulations! You have created and signed into your APEX workspace as the DEMO user.

Summary

You now know how to create an APEX Workspace using the OCI Console and you are ready to start building amazing apps, fast.

Task 2: Creating an App from a Spreadsheet

In this lab, you will learn how to create an APEX application using App Builder and data imported from a spreadsheet. You will import a spreadsheet which consists of a list of High Schools in New York City.

APEX developers spend the majority of their time in App Builder, which is a low code, high productivity IDE built into APEX, you should also investigate other major features like:

• SQL Workshop: Create, view, and maintain database objects.
• Team Development: Track large APEX development projects.
• Gallery: Productivity and sample apps that can be installed within minutes.

Introduction

Now that you are logged into your workspace, it's time to create an APEX application. In this lab, you build a simple application based on a spreadsheet. Keep in mind that APEX is great for a variety of apps, from simple ones like this to large, sophisticated apps based on local database objects, REST enabled SQL objects, and even REST APIs.

Estimated Time: 5 minutes

Loading the Highschool data.

1. Click the Luna clipboard icon located on the floating panel.

2. Click on the Clear clipboard button to clear out any existing information on the clipboard. Then click the X to close the clipboard.

3. Right click the link below and choose Copy link address from the pop-up menu.

https://luna.oracle.com/api/v1/labs/69cf8318-59aa-44cd-b473-eddb1b1a78d8/gitlab//files/nyc_high_schools.csv

4. Open the Luna clipboard again, right click in the blank space and choose Paste to paste in the link. Then click on the X to close the clipboard

5. In Chrome on the Luna desktop, open up a new browser tab, click in the URI field and choose Paste and go to luna.oracle.com/...

6. The file will likely open in the browser because Chrome knows how to read .csv files. Right-click anywhere on the browser screen and choose Save as ... to save the file to the lab instance.

7. In the Save as ... dialog, Chrome will try to change the file extension to .txt. Change the file extension to .csv, then click the Save button. The file will be saved to the Downloads directory as nyc_high_schools.csv and should appear on the bottom bar of the browser.

8. Return to the APEX browser tab and from your APEX workspace home page, click App Builder.

9. In the Get Started Now section, click Create a New App.

10. In the Create an Application section, click From a File.

Note: When creating an application from a file, APEX allows you to upload CSV, XLSX, XML, or JSON files. Alternatively, you can copy and paste CSV data or load sample data.

11. Within the Load Data wizard, click Choose File then navigate to the Downloads directory.

12. Double click or press the Open button to select the nyc_high_schools.csv file that you downloaded in an earlier step.

13. Enter NYC_HIGHSCHOOLS in the Table Name field. Review the information on the screen and click the Load Data button.

Note: You can configure what columns to load from the spreadsheet by clicking the Configure button.

14. You will receive a confirmation when the data has loaded. In the Load Data dialog, verify that 427 rows have been loaded into the NYC_HIGHSCHOOLS table, then click View Table.

15. In the Object Browser, review the table structure then in the Table toolbar, click Create App.

16. On the Create Application page, click Create App.

17. On the Create Application page, correct the capitalization and change the name to NYC Highschools, then click on the icon next to the Name field. You're going to upload a custom image icon for your new application.

18. Leave the APEX window for a moment and click on the clipboard icon on the floating lab toolbar.

19. Click on the Clear clipboard button to clear out any existing information on the clipboard. Then click the X to close the clipboard.

20. Right click the link below and choose Copy link address from the pop-up menu.

https://luna.oracle.com/api/v1/labs/69cf8318-59aa-44cd-b473-eddb1b1a78d8/gitlab//files/nyc-highschools.png

21. Open the Luna clipboard again, right click in the blank space and choose Paste to paste in the link. Then click on the X to close the clipboard

22. In Chrome on the Luna desktop, open up a new browser tab, click in the URI field and choose Paste and go to luna.oracle.com/...

23. The image file will open in the browser. Right-click anywhere on the browser screen and choose Save Image as ... to save the file to the lab instance.

24. Click Save Image As .... The file will be saved to your Downloads folder.

25. Return to the APEX tab. You should still have the Choose Application Icon wizard visible. Click on the Upload your own icon button.

26. Navigate to the Downloads folder and double-click the nyc-highschools.png image file.

27. At this point you can crop or resize the image. Click Save Icon.

28. In the Create an Application page click the Edit button for Nyc Highschools Search adjust the name to NYC Highschools Search. Do the same thing for Nyc Highschools Report and change it to NYC Highschools Report you're just updating the titles for case.

29. Under the Features section, click the checkbox for Install Progressive Web App then click Create Application. You will learn about Progressive Web Apps in Task 4 of this workshop.

When the wizard finishes creating the application, you will be redirected to the application's home page in the App Builder. Run and explore the new app

30. Click the Run Application button. This will open the runtime application in a new browser tab, allowing you to see how end users will view the app.

31. Enter your user credentials and click Sign In. Notice the custom application icon displayed at the top.

Note: Use the same Username and Password you used to sign into the APEX Workspace.

32. Explore the application by clicking on the various buttons. Return to the home page with the action menu in the top left hand corner of the screen.

• Click Dashboard (in the home menu or the navigation menu) to view the charts.
• Click NYC Highschools Search, in the navigation menu, to experiment with the faceted search.
• Click NYC Highschools Report to view an interactive report, then click the edit icon for a record to display the details in an editable "form" page.

Summary

You now know how to create an application by loading a spreadsheet.

Task 3: Improving the Faceted Search

Introduction In this lab, you will get first-hand experience in application enhancement by improving the faceted search page for better clarity and improved usability. The search page includes facets on the left which can readily be used to limit the data shown in the report on the right.

Take note of APEX application behavior by reviewing the NYC Highschools Search page. A user can check and uncheck various facets. When a facet is checked, the counts on all of the remaining facets will be updated to show the number of records that meet the new criteria.

Estimated Time: 10 minutes

Reviewing the facets, you will notice that Safe is down the page and includes the ability to choose multiple range values from a checkbox group. We're going to alter the Safe search behavior.

1. From the runtime application, go to the search page by clicking NYC Highschools Search. Assuming that you ran this app from the APEX App Builder, a Developer Toolbar is displayed at the bottom of the screen. In the Developer Toolbar click Page 3.

Note: End users who log directly into the app will not see this toolbar.

2. Alternatively, you can also navigate back to the APEX App Builder tab in your browser manually by selecting the appropriate browser tab or window. Once in the App Builder click 3 - NYC Highschools Search.

You should now be in the Page Designer. Page Designer is where you will spend the majority of your time improving your application. There are three panes within Page Designer.

• The left pane initially displays the Rendering tree, with a list of page components.
• The middle pane displays the Layout, a representation of the page, and Gallery, from which you can drag and drop new components into the Layout.
• The right pane is the Property Editor, where you can change attributes for the selected component.

Note: Each pane can contain tabs depending on the selected item.

Safety of a Highschool is a very important search criteria while selecting a High School, so you want to reorder the facets such that Safe is between Borough and Interest.

3. In the Rendering tree (left pane), under Search, within Facets, click and hold P3_SAFE and drag it up until it is under P3_BOROUGH then release the mouse.

Currently the Safe facet is a checkbox group with range values. However, it is unlikely that users will want to select multiple safety percentage values, so you will convert it into a radio range group.

4. In the Property Editor (right pane), with P3_SAFE selected, under Settings, toggle the Select Multiple button off, so that multiple values cannot be selected.

5. At the top right of the page click the green Save and Run Page button to save the changes made to the facets and review the app.

6. In the runtime environment, click one of the Safe ranges. Review how the counts on the other facets are updated based on your selection. Also notice how you can still select one of the other Safe ranges or click Clear, within the Safe facet, to clear your selection and again show all the counts for all the safety ranges.

Note: As an optional step, you can follow the same steps in STEP 1 to customize the Attendance Rate facet.

7. You can view each of the facets and their respective counts as a chart. To view the chart, click on the context menu next to the Borough facet and select Show Chart.

8. A chart dialog window is displayed with the Borough names on the X-axis and the count on the Y-axis.

Collapsing Facets You may have noticed that the facets go off the bottom of the page. Therefore, it would be preferable to collapse the last three facets, making it easier to see all available facets.

9. Return to App Builder by using the Page 3 edit button in the developer toolbar, or manually navigating to the appropriate browser tab.

10. In the Rendering tree (left pane), under Search, within Facets, click P3_SAFE, hold down the Shift key and then click P3_ATTENDANCE_RATE, such that three facets are selected.

11. In the Property Editor (right pane), type the word collap into the Filter field. This will instantly bring up the field we want to edit, the Collapsible field under Advanced. Toggle the switch on for Collapsible. Initially Collapsed will appear. Toggle Initially Collapsed to on.

Note: Rather than entering a filter you can also scroll down the properties until you find the correct attribute to update.*

12. Click Save and Run Page to see the improved search page.

Filtering Facets Some facets may have multiple values to choose from and it can be difficult for the end user to scroll and select the values. APEX 22.1 introduces a new filter feature for facets which provides a search bar to search and filter facet values.

13. Click the context menu (three dots) next to the facet name Method. Select Filter.

14. A search filter is displayed below the facet name. Type Transf in the text box and all the facet values that contain the substring will be displayed. You can then select the checkbox for Transfer to be filtered in the report.

Summary

You have learned how to enhance a faceted search page and modify the properties using the Page Designer.

Task 4: Improving the Report and Form

Introduction

In this lab, you will gain an insight into APEX Interactive Reports and learn how to enhance a form page.

Estimated Time: 20 minutes

Enhancing the Interactive Report. The NYC Highschools Report page utilizes an Interactive Report to display records. Interactive Reports are extemely powerful, as they allow end users to modify what data is displayed, and various display characteristics. For this report, you will add a column break, a computational column, an aggregate, a chart, then order the data and save the report for everyone to see.

Note: All of the steps below, except for saving Alternative Reports, can be performed by end users. There are many additional capabilities available to end users which are not covered below.

1. In the runtime environment, click NYC Highschools Report.

2. The report displays all the columns in the table. Customize the report by choosing only selected columns in a report. Click Actions and select Columns.

3. In the Select Columns dialog, select the columns that you want to hide from the report by selecting a column name from the right side, and clicking on the left-chevron (<) in order to move it to the Do Not Display section. You can also double-click on a column in order to move it or SHIFT>Click to select multiples. Hide the following columns and click Apply.

• Dbn
• Latitude
• Longitude
• Language Classes
• Advanced Placement Courses
• School Sports

4. The interactive report is displayed by hiding the selected columns from the previous step. Click the Neighborhood column heading, and then select the icon for Control Break. (Farthest icon to the right)

Let's add a computational column, where a new column is defined based on a computation against one more existing columns in the report. Columns are selected within the computation using a letter associated with each column.

5. At the top of the report, click Actions, select Data, and then select Compute.

6. In the Compute dialog, enter the following and click Apply. Use the Keypad to enter the columns and mathematical operator.

7. This will add a new column, Application to Seat Ratio to your interactive report.

8. To calculate the average Application to Seat Ratio in each neighborhood, click Actions, select Data, and then select Aggregate.

9. In the Aggregate pop-up choose Average from the Function drop down and choose **Application to Seat Ratio from the Column selector and click Apply.

Even though you can see the average Application to Seat Ratio for each Neighborhood, it would be much easier to view the average on a chart to get an overall picture of the neighborhoods which have the best High Schools.

10. In the report click Actions then select Chart.

11. In the Chart dialog, use the drop down fields to select the following and click Apply.

12. Hover over individual bars to view the neighborhood and the average. Toggle between the report view and chart view using the icons at the top of the report.

13. Order the records in ascending order of School Name and descending order of seats within each neighborhood. At the top of the report, click the View Report icon, then click Actions, select Data, and then select Sort.

14. In the Sort dialog, select the following and click Apply.

Now that you have made these modifications to the Interactive Report, it is important to save the report layout. End users have the ability to save either Private Reports only they can view, or Public Reports, that other end users can also view. As a developer you can remove the ability for end users to save Public Reports by using the report attributes in Page Designer. Developers also have the ability to save reports as a Primary (default) Report, or Alternative Report. For this exercise you will save the report as a named Alternative Report.

15. In the report, click Actions, select Report, and then select Save Report.

16. In the Save Report dialog, for Save(Only displayed for developers) click the drop down field and select As Default Report Settings. The dialog will change.

Note: End users can save Private and Public reports, however, only developers (who have run the app from the App Builder) can save the Primary and Alternative reports.

17. On Save Default Report pop-up, select the radio button for Alternative, for Name enter Best Schools, and click Apply.

18. A new select list will be displayed at the top of the report which lists all of the saved reports available to the user.

19. Return to how the end user will see the report upon first access. At the top of the report, select 1. Primary Report, then click Actions, select Report and then select Reset.

20. Click Apply to confirm reset. The changes you have made to the report will be reset and the original report will be displayed.

Next you will enhance the form. Form pages allow end users to readily update a single record of information. It is very common for these pages to be modal pages, whereby they display over the top of the original page, and the original page is greyed out and not accessible without first closing the modal page. Advantages of modal pages include that the same page can be called from numerous other pages, they include all of the processing, validations, and so forth within the page definition, they are user friendly, and make for a very straight-forward user experience.

By default, the form pages generated by the Create Application Wizard are modal pages. The modal page for maintaining NYC Highschools takes up significant real estate. This could easily be improved by placing Graduation Rate, Attendance Rate and College Career Rate on the same line. Lastly, the Borough field allows any values to be entered, so you should restrict data entry by adding a select list of Borough Names.

21. In the Runtime environment, on the NYC Highschools Report page, click the edit icon for any record to display the modal form page.

22. In the Developer Toolbar, at the bottom of the screen, click the edit button for Page 5 to edit the modal form.

Note: If you have difficulties navigating using the Developer Toolbar, manually navigate to the browser tab for the APEX App Builder. If you are on the application home page, navigate to the page by clicking 5 - Nyc Highschool. If already on another page in Page Designer, use the page selector in the toolbar to navigate to page 5.

Items can readily be moved using drag-and-drop within Layout. If required you can also drag new components (regions, items, and buttons) from the Gallery, below Layout, into the Layout.

23. Left-click and hold P5_ATTENDANCE_RATE within the Layout section (middle pane) and drag the item up and to the right, until it is directly the the right of P5_GRADUATION_RATE. A dark yellow box will be displayed. Release the mouse to drop the item in the new location.

24. Another way to reposition the rows is by using attributes in the Property Editor. In Page Designer, within Layout (or the Rendering tree in the left pane), select P5_COLLEGE_CAREER_RATE. In the Property Editor (right pane), deselect the toggle for Layout > Start New Row.

Note: As soon as you deselect Start New Row, the item will move to be on the same line as the item above, within Layout.

To limit the data entry for an item, one of the simplest techniques is to convert a text item (which allows any data to be entered) into a select list.

25. In Page Designer, within the Rendering tree (left pane), select P5_BOROUGH. In the Property Editor (right pane), enter the following.

For List of Values > SQL Query enter the following code:

select distinct borough d, borough r
from nyc_highschools
order by 1

26. It is important to save your changes before testing. In Page Designer, within the toolbar (at the top), click Save.

Note: If you press Save and Run in the toolbar a message will be displayed, rather than the page being run. This is expected behavior, as modal pages can not be run directly from Page Designer.*

27. Navigate back to the runtime environment by returning to App Builder and clicking on the icon for your application. Then select Run Application. Return to the NYC Highschools Report page.

28. On the Project Tasks Report page, click the edit icon for any record. Review the improved layout. Click on Borough to see the list of available values. Can you think of other improvements you might make to this form?

The next steps on enhancing the user experience (UX) of the form are optional. In the previous task, you explored ways to enhance the form. In this task, we further enhance the user experience of the form by splitting the form fields into multiple tabs. Currently, observe that the form contains a number of fields and the user needs to scroll through the form to view all the fields.

29. In the Runtime environment, on the NYC Highschools Report page, click the edit icon for any record.

The modal form page for the selected record will be displayed.

30. In the Developer Toolbar, at the bottom of the screen, click the edit icon for Page 5.

Note: If you have difficulties navigating using the Developer Toolbar, manually navigate to the browser tab for the APEX App Builder. If you are on the application home page, navigate to the page by clicking 5 - Nyc Highschool. If already on another page in Page Designer, use the page selector in the toolbar to navigate to page 5.*

31. Click Nyc Highschool in the rendering tree. In the Property Editor, find the Appearance section, for Template select Tabs Container.

32. To create a tab, right-click on Nyc Highschool on the left and select Create Sub Region.

33. Create 4 Sub Regions and change their Titles from new to the following values:

• General Information
• Metrics
• Location
• Other Course Details

34. For each of the four Sub Regions, find the Appearance section in the Property Editor. Click on the Use Template Defaults, Scroll - Default button. A new screen will open.

35. Use the Header drop down to select Hidden but accessible and click OK. Remember to do this for all four sub-regions.

36. The Sub Regions created represent tabs in the report. Drag and drop the form fields from the Content Body section into their respective Sub Regions as directed in the following table. You can multi-select using the CTRL and SHIFT keys along with a left mouse click.

    Sub Region Title	Form Fields
    General Information	P5_BOROUGH P5_SCHOOL_NAME P5_NEIGHBORHOOD P5_INTEREST P5_METHOD
    Metrics	P5_TOTAL_STUDENTS P5_GRADUATION_RATE P5_ATTENDANCE_RATE P5_COLLEGE_CAREER_RATE P5_SEATS P5_APPLICANTS P5_SAFE
    Location	P5_LATITUDE P5_LONGITUDE
    Other Course Details	P5_DBN P5_LANGUAGE_CLASSES P5_ADVANCED_PLACEMENT_COURSES P5_SCHOOL_SPORTS

37. When you're done the tabs should look like the below screen shot.

38. Click Save to save your changes.

39. Return to Application Builder and run the application.

40. Open the NYC Highschools Report page and click the edit icon for any record. Click on various tabs to view the improved form fields.

Summary

You have learned how end users can make customizations using Interactive Reports, and you can easily improve form pages.

Task 5: Using Maps and Progressive Web App

Introduction

In this lab, you will add a map region to the search page in order to visually enhance the page. The search page includes facets on the left which can readily be used to limit the data shown in the map on the right. Once the final application is built, you will download and use the application as a Progressive Web App (PWA).

Estimated Time: 10 minutes

Create a copy of an existing page. In order to implement the map, we will copy the existing NYC Highschools Search page and modify the new page to include the Map region.

1. From the runtime application, go to the search page by clicking NYC Highschools Search. Assuming that you ran this app from the APEX App Builder, a Developer Toolbar is displayed at the bottom of the screen.

Note: End users who log directly into the app will not see this toolbar.*

2. In the Developer Toolbar click Page 3 to begin editing the page.

3. Alternatively, you can also navigate back to the APEX App Builder tab in your browser manually by selecting the appropriate browser tab or window. Once in the App Builder click 3 - NYC Highschools Search.

4. To create a page similar to the existing page, click the + icon next to the redo button at the top right corner. Select Page as Copy.

5. The Copy Page dialog appears. Select Page in this application and click Next.

6. For the Page to Copy section, enter the following and click Next.

• New Page Name - enter NYC Highschools Map
• Breadcrumb - Choose Breadcrumb from the drop down
• Entry Name - accept the default value or enter NYC Highschools Map

7. On the Navigation section, select Create a new navigation menu entry and click Next. This creates a new entry in the left side navigation menu of your application.

8. In the New Names section, you could enter new names for the items and buttons. Accept the default names and click Copy.

A new page called NYC Highschools Map is created as Page 6.

9. In the Rendering pane in the Body section, select the Nyc Highschools region. In the Property Editor, change the type to Map.

10. To customize a map region, you need to add layers. Under Layers, click on New.

11. In the property editor, update the following fields:

• For Name, enter Schools
• For Source > Location, select Region Source. This indicates that the layer fetches the data from it's parent region (Nyc Highschools) whose source is the local database table NYC_HIGHSCHOOLS.

12. In order to display the points on the map, you need to provide the coordinates. In the Property Editor, go to the Column Mapping group. Enter the following values:

13. On the left hand side of the page, click on Search under the Left Column heading. In the property editor Locate the Source field and change Filtered Region to Nyc Highschools

14. Click on Save. Then click on the Application xxx breadcrumb at the top of the page to navigate to the application home page.

15. Set an icon for the newly created NYC Highschools Map. Click on Shared Components.

16. Under Navigation, select Navigation Menu.

17. Under Lists, select Navigation Menu.

18. Click the edit icon next to NYC Highschools Map list entry.

19. In the edit page, for Image/Class enter fa-map-marker and click Apply Changes.

20. Click te Run Page button.

21. The NYC Highschools Map is displayed with a faceted search on the left and a map region on the right. Filter the values using faceted search and observe how the map changes accordingly.

APEX allows customization of the map in a declarative way. In this step, you will enhance the map by adding a tooltip and custom pin colors on the map.

22. From the NYC Highschools Map page in the application, click Page 6 in the developer toolbar to edit the page.

23. In the Rendering tree, navigate to Nyc Highschools under Body and select the Schools layer.

24. In the Property Editor, adjust the following fields:

• Under Tooltip, toggle the button to switch on Advanced Formatting.
• In the HTML Expression text area, enter the following HTML code block:

<strong>&SCHOOL_NAME.</strong><br>
Borough: &BOROUGH.<br

25. Click Save and Run Page. The NYC Highschools Map is displayed. Hover over a pin in the map to display the custom tooltip. The tooltip displays the Highschool name and the Borough name.

26. Next, you will customize the map by adding a condition such that the value of the GRADUATION_RATE will determine the pin color. To do so, click Page 6 in the developer toolbar to edit the page.

27. In the Rendering tree, navigate to Nyc Highschools under Body. In the Property Editor, under the Source section, in the Type field select SQL Query. Click on the Code Editor button next to SQL Query.

28. In the Code Editor, modify the SQL query to add a CASE statement to determine the color based on the value of GRADUATION_RATE. Replace the existing query with the following query and click OK.

select ID,
      BOROUGH,
      SCHOOL_NAME,
      NEIGHBORHOOD,
      INTEREST,
      METHOD,
      TOTAL_STUDENTS,
      GRADUATION_RATE,
      ATTENDANCE_RATE,
      COLLEGE_CAREER_RATE,
      SAFE,
      SEATS,
      APPLICANTS,
      DBN,
      LATITUDE,
      LONGITUDE,
      LANGUAGE_CLASSES,
      ADVANCED_PLACEMENT_COURSES,
      SCHOOL_SPORTS,
      CASE WHEN GRADUATION_RATE BETWEEN 0 and 50 then 'red'
      WHEN GRADUATION_RATE BETWEEN 50 and 75 then 'yellow'
      WHEN GRADUATION_RATE BETWEEN 75 and 100 then 'green' END as PIN_COLOR
from NYC_HIGHSCHOOLS

29. Select Schools layer in the Rendering pane. In the Property Editor, do the following:

• For Name, enter Schools - Red
• Under Row Assignment,
  • For Column, select PIN_COLOR
  • For Value for this Layer, enter red
• Under Appearance, for Fill Color enter &PIN_COLOR.

Note: Be sure to include the dot at the end of the &PIN_COLOR. directive above.

30. In the Rendering Pane, right-click on Schools - Red and select Duplicate.

31. In the Property Editor, do the following:

• For Name, enter Schools - Green
• Under Row Assignment, for Value for this Layer, enter green

32. In the Rendering Pane, right-click on Schools - Green and select Duplicate.

33. In the Property Editor, do the following:

• For Name, enter Schools - Yellow
• Under Row Assignment, for Value for this Layer, enter yellow

34. Click Save and Run page. The NYC Highschools Map displays different colors for the pins.

• The schools with a Graduation rate between 0-50 are displayed in red
• The schools with a Graduation rate between 50-75 are displayed in yellow
• The schools with a Graduation rate between 75-100 are displayed in green

Progressive Web Applications (PWA) are designed to be fast, responsive, installable to a home screen, reliable (online or offline), and engaging (providing support for push notifications). Recall that you created a Progressive Web App by running the Create Application Wizard and enabling the Install Progressive Web App feature in Lab 1 of this workshop. In this step, you will learn to install and use a PWA.

Note: To enable Progressive Web App, an Oracle APEX application must be served over a secured HTTPS environment, or localhost. If using an unsecured environment, PWA features will not be rendered. PWA is available in Google Chrome, Microsoft Edge and Safari browsers. Firefox does not support PWA.

35. To install a PWA, go to your application and click Install App on the navigation bar.

36. A pop-up dialog appears in your browser asking you whether you want to install the app. Click Install.

37. After a few seconds, the application icon is created on your desktop/home screen. Double-click the application icon.

38. The application is now open as a PWA without the need for a browser. This provides a customizable offline page when users are offline and cannot request the network. It optimizes page loading speed on the mobile device. It also improves the page load rendering time.

Summary

You have learned how to embed a maps in your application and to install a Progressive Web App (PWA).

This is the final step in this lab. Congratulations, you've created a useful application from a spreadsheet, and learned how to enhance it to make it more useful for your business. Contact us

Having an issue with the lab? Have an idea on how it could be made better? Want to tell us how awesome the lab is? Click the icon below to contact the team and let us know your feedback.