In the last post we uploaded our Excel data into Apex. It was an important part of the process, but it didn’t really help our business users as now their data is hidden within a scary SQL table. So to make their life easier, lets create an application that will allow them to add and edit the data in that table.
Go to the ‘Application Builder’ and click ‘Create’. Once there select ‘Database’ as the type of application we want to create. We could of used the ‘Websheet’ option, as it is meant for simple excel style applications, but we want to build a full-blow application as we know that eventually the needs of our users will outgrow websheets. I’ll probably do another post where we use the ‘Websheet’ option so you can compare.
On the next screen provide a name for the application. We’ll call it ‘Sales’. Ignore the Application ID as this is auto-generated and we don’t need to change it. The schema controls where our data is (leave as default). The create options allow us to create an app with a home page, or from scratch. In this case we want to have a home page (we’ll add some graphs to it later). The user interface controls whether we are building a desktop app or mobile app.
On the next page we have the pages for the application. By default this will start with a Home page (as we selected that on the previous page). We can always create more pages later, but lets get a head-start and add a ‘Report and Form’ page. We’ll use this to allow users to view the sales data and create new entries.
Select ‘Report and Form’ and then set the ‘Table Name’ to ‘SALES’ and then press ‘Add Page’. This will create two new pages, both called Sales (we’ll change that later). The first one is the report (or list view) of the data and the second is the data creation form.
Click ‘Next’ and then ‘Next again’ on the Shared components page. Shared components allow us to bring plugins we created in one application across to another. Click ‘Next’ again as we don’t need to change anything here. You could modify the data formats, but as we don’t have any data data this isn’t a problem for us. Finally we get to the ‘Theme’ page where we can choose the look and feel of our application. Apex comes with over 20 different themes and organisations can create their own custom themes to match their branding. I suggest starting out with the ‘Blue Response (Theme 25)’ theme as it looks good, and will scale down to a mobile device nicely.
Once you’ve selected your theme, click ‘Next’ and then click ‘Create Application’. Well done, you’re app is built! We aren’t done yet, but if you click the run the app now (and after you log in) you will see your sales page set up where you can view all the sales data and even add new data.
At this point we could allow business users to start using the system, they have all the main functionality they need. But first there a few little things missing, namely the ‘Total’ column is not there and we there are some UI things we’d like to fix up.
To add the ‘Total’ column back in we need to go back and edit the sales page and then right click the ‘SALES’ element under the body tag and then click ‘Edit’
In the region source section, change the SQL to add the ‘Total’ column by adding ‘(“HARDWARE” + “SOFTWARE” + “SERVICES”) “TOTAL” to the select part of the query. You will have to know a little bit of SQL to be able to use Apex effectively, but you shouldn’t need more than a passing knowledge of SQL to do most things. More complex things can be done with knowledge of PL/SQL, but best to get IT involved for that.
Click ‘Apply Changes’. This will add the column to the definition, but if you run the report you will notice the column has not appeared. This is because the report can be different to the underlying data source. To add it in, just click the ‘Select Columns’ under the ‘Actions’ button. Add the ‘Total’ column to the ‘Display in Report’ area and select ‘Apply’. Voila, your total column appears.
Users can actually use this functionality to make their own custom reports based on the same underlying data. A very powerful feature!
Finally I’d like to add a tab at the top of the page that links to the sales report, just like how the ‘Home’ tab goes back to the home page. To do that, go to ‘Shared Components’ area for our app and then select ‘Tabs’. Then select the ‘Manage Tabs’ page. This controls all the tab sets and the tabs within in them for the app. We want to add a new tab to link to the Sales report, so click the ‘Add’ button next to the Home tab.
Give the tab a name, in this case we’ll call it ‘Sales and then click ‘Next’
Now we need to link that to a page in our application. Click the up arrow next to ‘Tab Current for Page’ and then select the ‘2 Sales’ page.
Click ‘Create’ and then the tab set is complete. If we go back to our app we can see the tab has been added
We are so close, but now we need to get rid of the ‘Navigation’ area as we have the tab to be able to get us to the sales report. Edit the page again and then right-click the Navigation element and select ‘Edit’
Click ‘Delete’ and then confirm that you want to delete the region. If you go back to the home page you will see that the Navigation element is now gone
We now have a functioning application that our business users can use to manage the sales data. Give them the URL and a log in and they are set to go. In the next post we will add some charts to our home page so it’s not so bare and useless.