This blog post is the sixth in a series. It builds on the work done in previous chapters including the first chapter where we created the schema.
Links to previous blogs include:
We are going to now wrap up our application development project as the project timeline is ending and budget is drying up (that never happens in real life though).
So, in this final chapter, we will look at building a form for expense entry. Right now, we have an expense report that an administrator would use. The expense report for a regular user would be filtered by user (remember the “APP_USER” variable?) and role. Now let us imagine how such a user would enter expenses.
The average user would see a form where they can enter one expense at a time, with data entry aids (like select boxes) that are available where we need to enter codes. So, the end-user could select “Accounting” in a pop-up or drop-down, and the form would save the code 101 to the table. We will pull this form up from the report and, after the expense is saved, control goes back to the report.
Here’s a simple view of the process:
We can add a form to the existing expense report, but it is just as easy to set up a whole new page with a form. So, let us explore that instead.
- Create a new page. We saw how to do this back in Chapter 2. Login to APEX, go to the “App Builder,” and select your application (the “Expenses App” from the previous chapters). Then click on “Create Page.”
- Select “Report,” and then click on “Report with Form on Table.”
- On this page, we will specify details for two pages. Page #7 includes the report showing a list of expenses (like the one we have now) and Page #8 includes a new form, which we will use to edit and create expenses. Enter the names for the two pages appropriately (notice that I am calling them “Expenses” and “Maintain Expense” respectively), and then click on “Next.”
- We will create a new entry in the navigation menu; let’s call it “Expenses.” Make the entries as in the screenshot and then click on “Next.”
- Specify the data source as we have done before, and then click “Next.” On this screen, you can also control which fields are to be displayed on the reports (we have selected all fields).
- Specify the fields we need on the form. We don’t need user name and status on the form, as these can be defaulted in our update logic. We know the logged-in user and that the status of any new expense must be “Submitted” so we can simply include that in our PL/SQL code without needing to expose it to the user. Select the fields as in the screenshot and then click on “Create.”
- That brings you to the “Page Designer.” Let’s test out the page. Click on the button with the “Run” icon in the top right corner.
- Click on the pencil icon to edit a record.
- Notice how the “Expense Type” and “Cost Center” are incomprehensible numbers? Well, they are incomprehensible to our hypothetical end-user Sandy as well. To help her out, we’ll provide some help. Click on “Edit Page 8” at the bottom of the form to open it for editing in the “Page Designer.” Click on “P8_EXPENSE_TYPE” to open it in the “Property Editor” in the right pane (see screenshot for guidance).
- We are going to change this one into a select box so the user can choose from drop down options. In the property editor change “Type” under “Identification” (highlighted above) from “Number Field” to “Select List.” Then scroll down the “Property Editor” to “List of Values” and set the “Type” field to “SQL Query.” This will expose a field called “SQL Query.” Enter the following query into that field:select DESCRIPTION as DISPLAY_VALUE, TYPE as RETURN_VALUE
- Then click “Save” and “Run.” You will now see that “Expense Type” shows a description instead of the number that it did before.
- Select a different description (say change it from “Hotel” to “Airfare”) and click on “Apply Changes.” It will take you back to the report which should show you the code for the expense type has now been changed:
- To complete this exercise (and pacify Sandy) do this…on the form, change the cost center field from “Number Field” to “Popup LOV.” The setup is like what we did for the “Select List,” but this type works better when we have many options to choose from as it also offers a search box.
- Finally, using the steps we saw in Chapter 5, modify the report to replace the codes with descriptions.
That’s the application for now. Of course, it is incomplete in many ways. For one, it is still in development and needs to be migrated to production. We also have not addressed authentication options and authorizations. But hopefully this serves as a good starter tutorial and we’ll follow up with other aspects later.