Web Tutorial: Oracle APEX Mailing Registration Form (Part 4/4)

So far, we have been working on the MAILING_LIST table. Now, how do we update the MAILING_LIST_TERMS table? It is a one-to-many relationship, so many rows in the MAILING_LIST will refer to one row in the MAILING_LIST table. Interests and Descriptions are all part of the MAILING_LIST_TERMS table, differentiated by the TYPE column.

We will begin with Interests. Go to Page 4. Make sure that "Page is Public".

In it, go to the options at the bottom row and select a List from the Items tab of the bottom row. Drag it into the Top Navigation region. Set the Source as follows. This is the Shared Component we created in Part 2 of this tutorial.

After that, we Create a Process under Preprocessing, to ensure that this page does a check for the EMAIL session value.

This is the SQL.

IF (:EMAIL IS NULL) THEN apex_util.REDIRECT_URL(1); END IF;

Now select a List View from the Items tab of the bottom row. Drag it into the page. The List View will be based on MAILING_LIST_TERMS. In the Source section, the Type is "Table/View". The Table Name is "MAILING_LIST_TERMS". Also define the SQL.

This is the SQL. You search for the correct rows based on the value of EMAIL. The TYPE column's value must be "INTERESTS".

upper(EMAIL) = upper(:EMAIL) AND TYPE = 'INTERESTS'

There's more to be done with this, but leave it alone for now. We will get to it later.

What we will do next, is create a Form in the body like we did for some of the other pages. The source should be MAILING_LIST_TERMS. Once that is changed, you should see three fields appear in your Form. Remove EMAIL and TYPE. We only want to have TERM in the Form. Then create a Button. 

For TERM, Create a Validation. 

This is the SQL for the validation. We want to ensure that any new term of type "INTERESTS" under the current user email does not already exist in MAILING_LIST_TERMS.

upper(:P4_TERM) not in (SELECT upper(TERM) FROM MAILING_LIST_TERMS WHERE EMAIL = :EMAIL AND TYPE = 'INTERESTS')

The Button is simple - all it does is submit the Form.

In the Processing section on the left panel, Add a Process. This is what will be triggered when the Form is submitted

This is the SQL. We use this to add the value of P5_TERM as a new row, with EMAIL and "INTERESTS" as the other values.

INSERT INTO MAILING_LIST_TERMS (EMAIL, TYPE, TERM) VALUES (:EMAIL, 'INTERESTS', :P4_TERM);

Now let's get back to work on the List View! Thus far, in the right panel, we have been dealing with the Regions tab. Now go to the Attributes tab. In the Settings section, change Text to "TERM". This means that we only want the TERM column to be displayed. Set Link Target to "Page 6", which we will work on later. Then, under Messages, include a message to be displayed if no records are found.

When you set the Link Target to "Page 6", you should also add this in the parameters. This basically sets the P6_TERM field of that page to the value of TERM.

Let's test this! When you log into your application and go to Interests, you should see this message. That's because you have not yet defined any Interests. If you try to click the ADD NEW INTEREST button before entering any value, you'll see an error message.

Add this in the textbox and click the ADD NEW INTEREST button...

...and the Interest appears!

I'm going to add another one.

See?

And the database should show this now, after I add a few more.

Now we've created the functionality to add Interests. How to delete them? For this, we go to Page 6, which is Interest (note the singular). This is a Modal page. In it, add some static HTML to provide instructions, and a Form. 

Set the Form's source to be MAILING_LIST_TERMS. Delete all fields that appear in the Form except for TERM, which is now P6_TERM.

The Form should now contain a text field and a button. However, the text field should be set to Readonly. The Button should submit the Form.

Then Create a Process to delete that Interest. And maybe include a Success Message.

The SQL should be straightforward.

DELETE FROM MAILING_LIST_TERMS WHERE EMAIL = :EMAIL AND TYPE = 'INTERESTS' AND TERM = :P6_TERM;

I created a new Interest, "swimming", in the meantime. Now let's click on it. What happens? The Modal pops up!

Click on DELETE INTEREST. And now, no more "swimming"! You should see the Success Message.

I shouldn't have to elaborate further on the Descriptions module. It's basically more of the same. And if I'm gonna go through the trouble of producing screenshots, there should be at least some value in that exercise. Suffice to say, if you've been paying attention, you should be able to get that done without any further hand-holding.

Conclusion

This was my maiden effort with Low-Code platforms,  and there's probably a lot of other features I have not yet tapped into. In this tutorial, I have included not just the absolutely necessary, but also some nice labor-saving features that should hopefully be really useful.

Formal regards,
T___T