Create Apps from a Spreadsheet

Setup: Setting up your APEX environment (5 minutes)

You first need to create an APEX Service instance and then create an APEX workspace, which is a logical domain where you define APEX applications.

1. From within your Oracle Cloud environment, you will first create an instance of the Oracle APEX Application Development Service.

From the Cloud Dashboard, select the navigation menu icon in the upper left-hand corner and then select APEX Application Development then select APEX Instances.

2. Select a Compartment and Click Create APEX Service.

3. Enter SecretPassw0rd for the ADMIN password, then click Create APEX Service.

4. After clicking Create APEX Service, you will be redirected to the APEX Application Development Details page for the new instance.

Continue when the status changes to AVAILABLE (you might need to refresh the page).

Within your new APEX Application Development Service, you must now create an APEX Workspace.

5. Click Launch APEX.

6. Enter the password for the Administration Services and click Sign In to Administration. The password is the same as the one entered for the ADMIN user when creating the APEX instance: SecretPassw0rd

7. Click Create Workspace.

8. In the Create Workspace dialog, enter the following:

Click Create Workspace.

9. 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.

10. On the APEX Workspace log in page, enter SecretPassw0rd for the password, check the Remember workspace and username checkbox, and then click Sign In.

Lab 1: Creating an App from a Spreadsheet (5 minutes)

Now that you are logged into your workspace, you can start creating APEX applications by using data imported from a spreadsheet. To simplify the lab, you will use sample data that's built into APEX. However, the sequence will be the same when uploading your own data.

Step 1: Loading project and tasks data

1. From your APEX workspace home page, click App Builder.

2. Click Create a New App.

3. Click From a File.

When creating an application from a file, APEX allows you to upload CSV, XLSX, XML, or JSON files and then build apps based on their data. Alternatively, you can also copy and paste CSV data or load sample data.

4. Within the Load Data wizard, click the Copy and Paste option at the top.

5. Select Project and Tasks from the sample data set list and then click Next.

6. Review the parsed data. Enter PROJECT_TASKS for Table Name and Click Load Data.

After clicking Load Data you will see a spinner until the wizard finishes loading the data. Continue to Part 2 at that point.

Step 2: Creating an application

The Data Load wizard has created a new table and populated that table with the records from the sample data. Now you can create an app based on this new table.

1. In the Load Data dialog, verify that 73 rows have been loaded into the PROJECT_TASKS table, then click View Table.

2. In the Object Browser, review the table structure. In the Table toolbar, click Create App.

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

4. In the Create Application page, review the pages listed. Click the Edit button for a page to review more details. Click Check All for Features, and then click Create Application.

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

Step 3: Running and exploring the new app

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

2. Enter your user credentials and click Sign In.
Note: Use the same Username and Password you used to sign into the APEX Workspace.

3. Click on the navigation menu icon to expand the navigation menu

4. Explore the application a little. Click Dashboard (in the home menu or the navigation menu) to view the charts created. Click Project Tasks Search, in the navigation menu, to play with the faceted search. Click Project Tasks Report to view an interactive report, then click the edit icon for a record to display the details in an editable "form" page. Next, navigate to the Calendar page and review the data displayed ( Note: You may need to scroll back several months to see data ). Finally, review the options available under Administration.

Lab 2 (Optional): Improving the Faceted Search (10 minutes)

In this lab, you will get first-hand experience with enhancing the application 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.

If you review the Project Tasks Search page you can check and uncheck various facets. When you check a facet, the counts on all of the remaining facets will be updated to show the number of records that meet the new criteria.

STEP 1: Enhancing the Status Facet

Reviewing the facets, you will notice that Status is down the page and you can choose multiple values.

1. From the runtime application, go to the search page by clicking Project Tasks Search. Given 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.

In the Developer Toolbar click Edit 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 - Project Tasks Search.

You should now be in 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.

2. Status is a very common search criteria, so you want to reorder the facets such that Status is between Project and Assigned To.

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

3. Currently the Status facet is a series of checkboxes. However, it is unlikely that users will want to select more than one at a time, so you will convert it to a radio group.

Continuing with P3_STATUS selected, within the Property Editor (right pane), for Identification > Type, select Radio Group. Scroll down the properties in the Property Editor, and for List Entries > Zero Count Entries select Show Last.

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

5. In the runtime environment, click one of the statuses. 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 statuses or click Clear, within the Status facet, to clear your selection and again show all the counts for statuses.

6. Click on the chart icon next to the Project facet. A popup window with a chart of the total counts shows up. You can toggle between the bar and the pie chart by clicking on the chart type icon.

STEP 2: Collapsing Facets

1. 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.

Navigate back to the development environment (APEX App Builder) by using the edit link in the Developer toolbar, or manually navigating to the appropriate browser tab.

In the Rendering tree (left pane), under Search, within Facets, click P3_ASSIGNED_TO, hold down the Shift key and then click P3_COST, such that three facets are selected. In the Property Editor (right pane), within the filter at the top, enter collap, so that only two attributes are displayed. For Advanced > Initially Collapsed, switch it to On.

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

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

Lab 3 (Optional): Improve the Report and Form (20 minutes)

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

STEP 1: Enhancing the Interactive Report

The Project Tasks Report page utilizes an Interactive Report to display the records. Interactive Reports are exceedingly 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 Project Tasks Report.

2. Click the Project column heading, and then select Control Break.

3. You can 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.

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

4. On Compute, enter the following and click Apply.

This adds a new column Budget V Cost to your interactive report.

5. To add up the Budget versus Cost for each project, click Actions, select Data, and then select Aggregate.

6. On Aggregate, for Column select Budget V Cost, then click Apply.

7. Even though you can see the total budget versus cost for each project, it would be much easier to view the totals on a chart to get an overall picture of all of the projects.

In the report click Actions, select Chart.

8.In the Chart dialog, select / enter the following and click Apply.

After you click Apply, the chart will be displayed. Hover over individual records to view the project and value. Toggle between the report view and chart view using the icons at the top of the report.

9. You want to ensure that all of the records are ordered by Start Date and then End Date, within each project.

At the top of the report, click the View Report icon, click Actions, select Data, and then select Sort.

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

11. Now that you have made all of 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. {Note - As a developer you can remove the ability for end users to save Public Reports by going to the report attributes in Page Designer.}

As a developer you also have the ability to save the report as the Primary (default) Report, or an Alternative Report. For this exercise you will save the report as a named Alternative Report.

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

12.In the Save Report dialog, for Save(Only displayed for developers) select As Default Report Settings.

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.

In the Save Default Report dialog, select Alternative, for Name enter Budget Review, and click Apply.

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

13. To go back to how the end user will see the report when they first access the report, at the top of the report, select 1. Primary Report, and then click Reset.

STEP 2 - Enhancing 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, and 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 Project Tasks takes up significant real estate. This could easily be improved by placing the dates on the same line, and the cost and budget on the same line. Lastly, the status allows any values to be entered, so you should restrict data entry by adding a list of allowed statuses.

1. In the Runtime environment, on the Project Tasks Report page, click the edit icon for any record.

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

In the Developer Toolbar, at the bottom of the screen, click Edit 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 - Project Tasks. If already on another page in Page Designer, use the page selector in the toolbar to navigate to page 5.

3. 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.

In Page Designer, with Page 5 loaded, within Layout (middle pane), click P5_END_DATE and continue to hold the mouse down. Drag the item up and to the right, until it is directly after P5_START_DATE, and a dark yellow box is displayed. Release the mouse to drop the item in the new location.

4. As an alternative to using drag-and-drop you can also reposition items using attributes in the Property Editor.

In Page Designer, within Layout (or the Rendering tree in the left pane), select P5_BUDGET. In the Property Editor (right pane), deselect 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.

5. 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.

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

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

select distinct status d, status r

from project_tasks

order by 1 

6. 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.

7. Navigate back to the runtime environment and refresh the browser, or click Cancel on the modal page to return to the Project Tasks Report page.

8. On the Project Tasks Report page, click the edit icon for any record.

Review the improved layout and click on Status to see the list of available statuses.

Lab 4 (Optional): Link to Calendar (5 minutes)

In this lab, you will learn how to improve a calendar and link an existing page to a modal form page.

STEP 1 - Improving the Calendar

The Calendar page currently displays the project, rather than the task name, and only shows the start date, instead of displaying duration-based events.

1. In the runtime environment, click Calendar.

Note - You may have to use the arrows at the top of the calendar to navigate backward or forward months to see data displayed.

2. In the Developer Toolbar, click Edit Page 6. Alternatively, navigate back to the APEX App Builder browser tab and manually navigate to Page 6.

3. You can change the displayed column and make the events duration based by updating the calendar attributes.

In Page Designer, within the Rendering tree (left pane), find Calendar, and then click Attributes directly below that. Within the Property Editor (right pane), for Settings > Display Column select TASK_NAME, and for End Date Column select END_DATE.

4. In Page Designer, within the toolbar click Save and Run to review your changes.

5. Reviewing the revised calendar changing to duration-based events is not optimal. Therefore, you should remove the End Date Column added above.

In Page Designer, with Page 6 selected, within the Rendering tree (left pane), select Calendar. The Attributes tab appears next to the Region tab in the right pane.

Click Attributes. For Settings > End Date Column select - Select - to clear the END_DATE entry.

6. In Page Designer, click Save and Run to see the improved calendar.

STEP 2 - Linking the Calendar

When you click on a calendar entry then it shows event details. It would be far better to link to the Project Task form page and allow end users to update the details.

1. Navigate to the App Builder, and ensure Page Designer is displayed with Page 6 selected.

2. Define a view / edit link from the Calendar region to Page 5, the Project Tasks form page you modified in the previous lab.

In Page Designer, within the Rendering tree (left pane), find Calendar, and then click on Attributes tab in Property Editor (right pane). Within the Attributes tab, for Settings > View / Edit Link click No Link Defined.

In the Link Builder dialog, select / enter the following and click OK.

3. It is important that the calendar is updated whenever the Task Name or Start Date is changed using the new link. To ensure the page is updated appropriately you need to define a Dynamic Action to refresh the calendar region when the dialog (modal page) is closed.

A Dynamic Action is a declarative way to define client-side interactivity. Developers specify when an event fires, what actions are to be taken, and on what components the action is to be performed.

In Page Designer, within the Rendering tree (left pane), right-click on the Calendar region. Click Create Dynamic Action.

4. With the Dynamic Action selected, within the Property Editor (right pane), enter / select the following.

5. Within the Rendering tree (left pane), under the True action for the Dynamic Action, select Show. In the Property Editor (right pane), select the following.

6. In Page Designer, within the toolbar, click Save and Run to review the completed application.

In the runtime environment, from the Calendar, select an event. In the Project Task form page update the Task Name and Start Date. Click Apply Changes.

Confirm that the calendar refreshes to show the updated details.