How to convert your spreadsheet to web application

Converting your spreadsheet to a web application: Connecting tables, creating pages and forms

Co-Author: Piotr Kurzynoga

After reviewing Actions and Events, in this part we are connecting the PEOPLE and VACATION tables. This way we can store and process people and vacation days in separate tables. We use ID from PEOPLE table as reference which VACATION entries belong to which PEOPLE entry.

We will need the following:

• a dynamic list of people from PEOPLE table
• at least one page and a form to display, record and edit VACATION table entries

Dynamic list

Create a list in Shared Components/Other Components/List of Values

Don't forget select "Dynamic" Type:

Add this simple SQL Query:

SELECT * FROM PEOPLE;

Return Column is "ID" and Display Column is "NAME".

Page and form

We set up VACATION table inside the APEX, so no any related pages were created automatically like during the import of our initial .xls. Fortunately, adding new pages and/or forms are very easy in APEX.

Select "Report", then "Report with Form".

Add a title of the page and the form. Select "Modal Dialog" for Form Page Mode. We would like to open the form on top of the current screen.

As we would like to have this report on the main page, we need to create menu entry.

Select the required columns for Page and Form as well.

There is no entry in the VACATION table, our newly created page is empty.

Build the form with the following items:

• Name - dynamic list of people
• Start date
• End date
• Difference

Open "New Vacation" form, add "Select list" from Items menu and connect it with "People" dynamic list.

Check it in the application.

We have a report and a form of VACATION table, we need to add the last missing component, the SQL script.

We need:

• to calculate the difference from the first and last day
• identify the person by her/his ID who is taking the vacation
• update the PEOPLE table TAKEN and REMAINING columns

During the development of our original application I introduced a new column in the VACATION table to make me follow easier what happening inside the DB. My new column is "ISPROCESSED" with two values:

• "1" if the entry is waiting to be processed
• "2" if the entry already processed

Set this new column to be hidden with a Static value "1".

After adding this to your VACATION table the SQL scripts become very easy.

In the Processing tab create a new process and add the following script.

EGIN

-- 1.
    UPDATE
        VACATION
    SET
        DIFFERENCE = EDATE - SDATE + 1,
        PEOPLEID = (SELECT ID FROM PEOPLE WHERE PEOPLE.NAME = :PNAME)
    WHERE
        ISPROCESSED = 1;

-- 2.
    UPDATE 
        PEOPLE 
    SET 
        TAKEN = TAKEN + (SELECT DIFFERENCE FROM VACATION WHERE VACATION.ISPROCESSED = 1)
    WHERE 
        ID = (SELECT PEOPLEID FROM VACATION WHERE VACATION.ISPROCESSED = 1);
 
 -- 3.
    UPDATE
        VACATION
    SET
        ISPROCESSED = 2
    WHERE
        PEOPLEID = (SELECT ID FROM PEOPLE WHERE PEOPLE.NAME = :PNAME);
 
 -- 4.
    UPDATE
        PEOPLE
    SET
        REMAINING = CARRIED_OVER_DAYS + NEW_VACATION_DAYS - TAKEN;

END;

1. We calculate the difference with a very simple way (we will show better script for this process later) and connect the tables by adding "ID" as discussed at the beginning.
2. We add the previously calculated difference to "TAKEN" column.
3. We update the recently introduced "ISPROCESSED" to 2. This means the entry is processed/closed.
4. We update the "REMAINING" vacation days with the new "TAKEN" value.

After creating the first entry, we will see that Vacation Report is not empty anymore. Although the way how the result is displayed now the most convenient.

On the next Monday we will improve the Vacation Report page and create a new update page. Stay tuned.