APEX 5: Deleting from an Interactive Report using Dynamic Actions

Disclaimer:  Everything I do is on Oracle Cloud Services Managed Schema (S5) and provided with that in mind.  If it won't work on that platform, then you won't see it here.

So you'd like to do a very simple add/remove design for your basic APEX 5 (or 4) application.  You've read several blog posts that include extended javascript functions to make things pretty all while wondering why it has to be so hard.  Perhaps you even thought you'd search for information on Oracle's site(s) and found yourself wanting to punch their entire organization in their corporate jejunum.

Well here is the stupid-simple way to do exactly what you want without going outside the box.  You can stay comfortable completely within the cozy framework that Oracle provides without risking having to know anything beyond the basics.  Of course, since this is intended for beginners, I'll be walking all the way through this from start to finish.

For this we're going to use nothing more than a simple form, a basic interactive report, a custom icon, and a dynamic action.  Other than the icon (which can be solved w/  quick google image search), everything is completely provided by Oracle to you out of the box.  We'll change the functionality of the icon to the left of each item to actually delete the item instead of the standard of edit/view.

I'm starting with a very basic table that I'll build upon for clarity that includes an ID, an event ID and a Product Name field (because I'm dealing with Open Records requirements for healthcare right now).  We're going to have Events that have an array of Products that will be covered during them.  In the US, we're required to report on these to the federal government so users need to put these in or else.

Let's say you want to give your users the ability to add Products to a chosen event and then display that list on the page.  That's simple enough with a basic form and interactive Report, but the defaults when adding a Form and Report page gives you two pages vs. one.  The assumption here is that you'll be showing a report where you can see an array of values, but you go to another page to add or edit anything in that list.

This works really well for things like adding a list of clients, addresses or items to an order.  You have a master/searchable list and then a detailed add/edit page.  But it all breaks down when you just want to have a very simple combo page where things won't be edited.  My example above fits that requirement.  Users will just add products to the list or remove them.  Nothing else.  So why make them jump to other pages and click twice to delete something instead of just deleting it on the spot?

For our purposes of combining all of this onto one page, we will just create a basic Interactive Report via the wizard, then manually add the Form above it.

Within the Application Builder screen, click Create to start a new application, select Desktop and Next.  You can change the Name of the application (I used SampleEventProductList for mine) then click Next again. Click Add Page, select Report and change the Table Name field to whatever you named your table from above (SAMPLEPRODUCTS in my example).  Leave the Report Type as Interactive but you can change the Parent Page as needed.  Click Add Page.

Click Next.  Click Next.  Click Create Application.

Whew.

At this point you can run your application and take a look (I went ahead and created a single record directly in the database just so there is something to display in the image below).

Interactive Reports are actually intended primarily to be combined with forms for a multi-page solution.  Instead of this default use case, in this scenario we're going to first combine the "Add" functionality on this form and then tweak our report so that everything happens on this page.

Go back into the Page Designer and click on the name of the second page in your application (SamePageAddDelete in my screenshots below).  Right-click on the Content Body field in the left-hand window and select Create Region:

The new region is automatically selected (don't get used to it being this easy).  Information on the new region will be displayed to the right-hand side of the page now.  This is a pattern that repeats throughout the process.  Select on the left, edit on the right.  Lather, rinse, repeat.

In the left-hand window edit the Title field to something applicable (e.g. Add Product), feel free to look around and tweak a few other settings.  I altered the Item Display Position value to be Below Content and added information to the Text field so I could add some words above any controls I include in this region.  None of this is required though.

When new regions and items are created they always go to the bottom.  In this case we want our new region above the existing one.  Left-click-hold on the new region name in the left-hand window and drag/drop it above the original Interactive Report so it looks more like this:

Now if you run it you get a small region above the original interactive report that we can drop some form items into. 

Next let's add a basic List Box with a few products and pop in a Submit button so that people could add items to the list.  Right-click on the region you just created/moved and click Create Page Item.

This is where a novice might get confused.  

Remember above how nice it was that a new region was automatically selected for you?  New Items aren't necessarily selected after creation, so you'll have to click on the new item to see the details on it.  The screen is cluttered but you can see it in the middle (and left) of the screenshot above indicated as P4_New.  Yours will be P2_New if you're following along with the steps above. Click that and then details will appear to the right-hand side of the screen.  

In many of the steps below I'll refer to P4 this or that.  In yours they will all likely be P2 (unless you change your page number).  Throughout APEX applications, fields are commonly referred to by the page number followed by an underscore, then the field name.  So P3_ID and P4_ID are distinct fields that are held/referenced on individual pages.  There isn't much stopping you from ditching this and renaming everything like a crazy person.  

Please don't.  Just leave it be.  But to keep you on your toes I'm going to keep referring to P4 on everything where you should be probably doing P2.  ;)

I edited the Name and Label fields (right-hand side of page) on my new page item to reflect more meaningful information as seen below.

So now we have a free-form Product field and allows users to enter whatever text they want because users never do anything stupid and put in garbage.  Users are simply wonderful people.  Yay users!

But just in case somebody goes crazy, let's change that to a drop-down list.  If you click on Type and change it from Text to Select List then that will give you what we want.  

Normally we'd link this to a lookup table (e.g. States, Countries, Blood types, etc.) but to again, keep things simple, let's just use a Static List.  So if you scroll down to the section titled List of Values and the Type field (because having two fields identically named under different headers is completely intuitive to Oracle) and choose Static Values.

This will display and pre-populate the Static Values field below it with the following:

STATIC:Display1;Return1,Display2;Return2

What this junk means is that it will give you a drop down of two values (i.e. Display1, Display2) and if a user selects them then it will store in the variable or associated table for this field different values (i.e. Return1, Return2).  You don't have to do this, but it is good to understand that commas separate distinct elements while semicolons separate values within each element.  In this case I'm going to throw a couple of products in and ignore the Return values because again...simplicity first.  Also, I'm going to add a wrinkle

STATIC2:Product A,Product B, Product C

Note that using the keyword STATIC2 vs. STATIC.  STATIC2 makes the list display exactly in the order I typed in in vs. STATIC which sorts alphabetically.  In this case it's meaningless as I already typed them in alphabetical order, but I find myself using this method most commonly for simple lists.

This leaves us with a page that looks similar to this (note that I minimized some of the items so that the key values would be shown on the right-hand screen).

So now if we run the application we have a top area that contains a drop down of the values listed above (Product A, B, C) but we need a way to Submit this and write it to the database.

It is worth noting that we could have approached this the opposite way.  Specifically that we could have created an automatically generated Form linked to a table and then added an Interactive Report to that page manually.  Either would work, but sometimes one way is easier than the other.

Is this way easier?  It depends.  YMMV.  This happens a lot in APEX.

Anyway, right-click on your Content Body region in the left-hand pane again (Add Product in my example) and select Create Button.

A new item will be created (but not selected) called New (how very descriptive).  Click on that either in the middle of the screen or on the left-hand pane and then you'll get some options in the right-hand window.

Edit the Button Name, Label and Database Action fields similar to mine.

Now you have a button that submits the page to insert the value into the table.  Or do you?

We haven't really connected all of this to a particular table yet and this is where the statement a few paragraphs above comes in per whether this is the easy way or the hard way.  Doing this with the form first and then manually creating the Interactive Report (the opposite of what we did here) means the SQL INSERT happens via DML (a discussion for another time) and it all works via HPFM.  In this scenario however, we have to explicitly tell the system what to do with the data we're submitting.

Right now, our button "submits" the data, but then...  There is literally nothing handling a button that submits this form at present.

To fix this, right-click Processes in the left-hand window and then select Create Process which will create a new process again named New.  If you click on where it appears under Processes now in the left-hand window then you'll get the details again in the right-hand window (this is starting to feel repetitive, which is a good thing).

Edit the Name, Type, PL/SQL Code and Point (under Execution Options) values similar to below.

Notice that I hard-coded some test values in here as a starting point.  I didn't actually link the values in the form to the Insert statement yet.  I used the following PL/SQL:

insert into SAMPLEPRODUCTS (EVENTID,PRODUCTNAME) values (1,'test');

This of course works, but is near useless except to test your functionality.  Go ahead and give it a go by saving and running your application.

When you click the Add Product button now it should submit the page.  This new Process we created above will now handle the Submit event and run the PL/SQL above.  Since we don't have a path of where to go after a submission, we simply return here and the entire page is refreshed.  Not particularly efficient from a web point of view, but it works for a basic starting point.

If you go back to the Application Builder now it is worth taking a peek at the left-hand pane.  Notice that the Process you created is nowhere to be seen in the left-hand pane.  When we changed the Point value above to be After Submit that caused it to move elsewhere.  Where is it you might ask? Silly developer.  It is now shown under the Processing tab in the left-hand pane.  Select the weird looking circular/square arrows 3rd from the left and you'll see your process again.

We probably should fix that hard-coded PL/SQL and actually link it to the drop-down.  Oh if only we'd done this Form-first vs. Report-first...but I digress.

Update the PL/SQL Code to be:

insert into SAMPLEPRODUCTS (EVENTID,PRODUCTNAME) values (1,:P4_PRODUCTSELECTION);

Note the difference here in the last value that is no longer hard-coded.  Using a colon (:) followed by a field name links the value of that field to a value in your PL/SQL so you can have dynamic queries (at least in these fields...don't get me started).  So if you save/run this now you can select from the drop down list, hit the Add Product button, and you'll see the value you selected added to the list.  Wheee!

This is almost starting to seem like a real application.  A horribly inefficient one, but a real application none the less.

But we started this entire conversation on how to do a form with a list and then to delete items from that list on the same page.  How in the world have I made it this far into the article and not actually told you how to do this?  Well, this is intended for imbeciles (like me!), so I do try to be thorough.

Now that we've built the basics, let's get to the real work.  

Time to clean up that report.  First off, the whole search bar at the top of the list needs to go.  When you're adding things to a list on the same screen you're mostly wanting to see everything on that list.  Therefore, we don't need all the search tools/filters etc. on there that will just confuse users.  KISS.

Back in the Application Builder screen on the left-hand window you'll see the original Interactive Report created oh so long ago.  Left-click on it and you'll soon find that...you can't get there from here (or can you?).  

This is one of those moments where you just know somebody from Oracle had to ship something that wasn't quite...well...thought through.  

One might imagine altering the header on that report through perhaps right-clicking and selection options or something or other.  Or perhaps it is listed on the right-hand pane as so many things before have been.

Hey, who knows.  There may even be an icon on this screen that takes you right where you want to go now.  I haven't found it (yet), so who knows.  For now, just punt like I do and switch over to Component View.  

From my perspective, the Oracle APEX team is being given some conflicting goals here and this is one of those compromises made to support them.  The view we're looking at by default is the Page Designer view.  Switching to Component View gets into some of the nitty gritty items.  Theoretically, you're just looking at the same information but in a different format.  

To switch over, look in the upper-right hand side of the screen.  Just to the right of the little wrench icon is the Component View icon which kind of looks like a series of books standing up with a dead Oracle Product manager lying across the top of them.  

Click it and your screen will change similar to the following:

This is just a different view of the same page that you were working on before.  However, it gives you access to some additional items that are handy.  

On the left-hand side under Regions you'll see your Interactive Report listed.  Click on it's name and you'll arrive at a very curious page.  While everything here seems pretty redundant and for the most part you already saw in the right-hand detail box on the Page Designer view, the sneaky bit of information that we're after is hidden right near the top.  Look at the very top row above likely where your eye would go and you'll notice 4 distinct items listed: Region Definition, Report Attributes, Saved Reports and Print Attributes.  

The one we're looking for is Report Attributes.  Click that and you'll wind up where we want to be.

The first thing we want to do here is nix the Search Bar.  So scroll down to the field titled Include Search Bar and change that to No.  This cleans up our report so it looks more natural for the purpose of having a combined data entry and displayed list page.

If you Save/Run this now you'll see that the incredibly powerful but confusing in this context search bar is now gone.

But we aren't finished on the Report Attributes page so return there.

A little further down is the key to all that is holy in creating what we sought out to do at the beginning.  The Link Column setting is what we want.

By default, the Link Column is essentially sending you to another page and sending the ID of the row you clicked along to that page so that it displays the correct information.  We're going to alter this so it runs a PL/SQL DELETE function (eventually).

First step is changing the Link Column value from Link to Single Row View to Link to Custom Target which gets us much closer to our goal.  With this, we can do all kinds of whacky things with that little icon to the left of each row.  

First off, notice the Link Icon setting that now appears.  With this, we can change the icon so that it looks more like a trash can or something else that will make sense to your users.  We'll get back to this though at the end.

Below that you will see the Link Attributes and Target fields.  Depending upon who you are listening to, folks will tell you to modify one or both of these.  In our simple example we're just going to do the one.  Modify the Target field to be URL.  A new field URL will appear and this is where we do our tiniest bit of coding.  Change that field to the following code:

javascript:$.event.trigger('delEventProd','#ID#');

Now, what in the world is this doing?  Let's break it down.  

The whole first part is just that we want to fire off a javascript event.  After that we say what the name of that event is and then follow it with data that we're passing to that event.

To be specific:

javascript:$.event.trigger('delEventProd','#ID#');

The blue text is the name of the event in question (name it whatever you choose, just remember it for later, perhaps you can tell me how I'm not following "fill in the blank" programming conventions with my naming below in the comments).  The green text is the particular column that you want to pass along with the event.  

If we jump back to the data on the sample application screen when running

Notice that we have 3 columns of data: ID, EventID and ProductName.  If the above JavaScript is used then we'll fire off an event named delEventProd and we will pass it the value of ID from whatever row is clicked.  

NOTE: In this example the row #'s are identical to the IDs, but just know that it is the value of the field included in the JavaScript command (ID in our example), not the row number that is passed.

What we wind up with now on the Report Attributes screen Link Column section is:

So now it will all work, right?  Nope.  We don't have anything listening for the event we just created above: delEventProd.

It is worth noting that this is where some folks start getting grumpy.  For the second time in our walk through we just kind of throw this event and data over the wall and have no idea if anyone is going to catch it.  Previously it was a little more obvious because it was the Submit button.  As it sits right now, this application will "work" in that it will not throw an error.  But nothing will happen when you click the icons next to the list items.  Welcome to the world of event-based programming!

So let's go back and create a new Dynamic Action that will listen and handle this event.

I will hopefully write a whole section on Dynamic Actions in the future but for now just know that these represent the Oracle way of doing some fancy dynamic things on pages without having to know how to write a bunch of gobbledygook in various languages/methods.  Sure we did the tiniest bit of JavaScript above, but using Dynamic Actions allows you to get things done w/o having to really know such things beyond a couple of minor commands.

Web developers can/do spend a good chunk of their career knowing how to make controls context-sensitive and be responsive and efficient.  But the reason you're using Oracle APEX is because you just want crap to work and you don't want to learn the "next big thing".  You're lazy.  Me too.  So let's just do this w/ a Dynamic Action and let the true professionals all write their own custom libraries.

First off, we need to get back to the Page Designer.  Well, we actually can do it from the Component View screen but I'd feel bad not telling you how to get things back to default.  

On the right-hand side of the screen, yet curiously in a slightly different location than on the other screen, you'll find the icon for Page Designer view which looks kind of like a pencil and a ruler crossed over each other or a stake stabbed straight through the heart of an Oracle Cloud Support representative.

Click that icon and your screen will return to the other horrible mess of information that you will struggle to sort through until Oracle completely changes it again in version 6.  In other words, start learning how to search for things vs. memorizing where everything is.

Which brings us to an important point.  Have I mentioned how handy it is to use the Page Search tab on the Page Designer view yet?  No?  Well, it is awesome.  

At some point Oracle Cloud Support or some idiot w/ a blog is going to tell you to go change the Horcrux setting on a page and you're going to spend 60 minutes digging through menus before realizing they've just scammed you.  It is much easier to just type whatever value you're trying to find into that little search field above and hit enter.  You'll then have a clickable list of items that display where this value appears. 

For instance, remember how I couldn't for the life of me figure out how/where to get to the Report Attributes via the Page Designer view?  Well, if we type in the name of our little event we created above (you haven't forgotten about that, right?) and hit enter then we'll see something similar to the following:

So now if we just click on the link listed in the search results it takes us right to where this is located within the Page Designer view, which is actually under the Interactive Report we created and the subheading Attributes (Regions | Content Body | SamePageAddDelete|Attributes in my example above).

Why didn't we just do that before?  Why send you over to the Component View application vs. just doing this at first?  Why are you punishing me?!

Sure I could say that I just did it as a lesson to show you how there are multiple ways to achieve the same goal within APEX (which is an understatement).  I could also try telling you that the way I indicated is far superior.  The truth is that I had no idea it was there.  Honestly.  And that's what you need to get used to in this interface and in APEX as a whole.  There is just...so...much...crap in here.  It makes sense right up until it doesn't.  Suddenly you'll go looking for something and it just isn't where you think it should be.  Before long you'll find yourself in strange screens and have trouble getting back where you started.

So in truth, while I could have just as easily edited all of this out and took you back to the perfect solution up-front, I've left this in for exactly the reasons I pretended was my goal all along.  It helps to understand that this is a product in flux and that sometimes you just have to decide which method makes more sense to you.

Anyway, let's wrap this up now by creating our Dynamic Action.  

There's a bunch of different ways to start creating one, which again, can lead to lots of confusion.  One way that works fairly consistently is to click on the Dynamic Action icon (looks like a little lightning bolt) at the top of the left-hand pane:

Clicking that gives you a list of items in the left-hand box titled Events with sub-items of Page Load, Change, Click and Dialog Closed.  It doesn't specifically matter where you do as these are all just shortcuts to creating common items.  Right-click on Events and choose Create Dynamic Action.  It will create a new item (called New again...genius).  Click on the New item in the left-hand box and you'll get some options in the right-hand one.

Again, Dynamic Actions are the lazy way of getting some fancy stuff done.  Think of them like macros that you can build upon step by step.  If A is true, then do B, C, D else do E, F.

Edit the left-hand window items Name, Event, Custom Event, Selection Type and JavaScript Expression as shown below:

The important fields are the Event that must be Custom, the Custom Event that must be the same name as the event we created previously (you still remember that, right - delEventProd in my example) and for whatever reason we also must make the Selection Type be JavaScript and the JavaScript Expression be document.  

That will make our Dynamic Action fire when that event is raised.  Now we just have to make it actually do something.

Notice the reddish/pink line through Show on the page above and the red circled 1 on the Messages tab.  These are essentially telling us that we're not quite ready to save this and we still have work to do.

Click on the Show item in the left-hand box.  This is where we will create our steps in the macro to be run.

First off, we aren't going to "show" anything.  We're going to run some PL/SQL to get rid of that list item I promised you we'd do forever ago.

So let's change the Action to be Execute PL/SQL Code and the new PL/SQL Code setting to the following:

delete from SAMPLEPRODUCTS where ID=:P4_DEL_ID;

Also, change the Page Items to Submit to be P4_DEL_ID and make sure that Fire On Page Load is No.

Now hang on a second you might say.  Something there looks...strange.  What's that :P4_DEL_ID thing?  Well, I skipped a step here for a moment to show you that somethings things aren't exactly 100% straightforward.  You see, that green value that we threw over the wall waaaay back up the page:

javascript:$.event.trigger('delEventProd','#ID#');

It was all done in JavaScript.  Unfortunately, APEX is smart, but it isn't so smart that it can handle incoming JavaScript values within PL/SQL.  We can do it manually though, so we have to do it one step at a time.  First we have to throw in a step before this one in the Dynamic Action to get the data somewhere that we can use it.  That means (generally) dumping it into a hidden field on this or another page (you can reference values on other pages too) before you reference it in a PL/SQL statement.  

So for now, trust me.  We're going to create a hidden field afterward and fix this mess.

Let's finish the macro by adding two more steps.  One before this one and one after.

Right-click on True in the left-hand screen and choose Create TRUE Action.  Change the Action field from Show to Set Value.  Change the Set Type to JavaScript Expression.  Change the JavaScript Expression field to this.data the Selection Type to Item(s) and the Item(s) value to P4_DEL_ID.  Make sure that Fire On Page Load is No

Essentially what we've done here is used a touch of JavaScript (the this.data line above) to take the value that was passed into us and to drop it into a hidden field on this page.  So if a user clicked on a line that had an ID of 3, then we would store 3 in the hidden field P4_DEL_ID (that we still have to create) on this page.  

It winds up looking like this:

Of course we want this to run BEFORE the PL/SQL line so we have to click/hold/drag the Execute JavaScript Code item on the left-hand screen above the Execute PL/SQL Code line.

Finally, we should refresh the Interactive Report since we've just deleted a line and the users might like to see that.  

Right-click on True in the left-hand window and click Create TRUE Action.  Change Action from Show to Refresh, Selection Type to Region and Region to the name of your Interactive Report region (SamePageAddDelete in my example).  Make sure that Fire On Page Load is No

So now we have:

Now this is looking very good.  So close.  Maybe.  Don't get cocky.

Let's finish up this Dynamic Action now by fixing that P4_DEL_ID hidden field.  Right-click on your form name in the left-hand window (Add Product in my example) and choose Create Page Item.  Select P4_New in the left-hand window and in the right-hand window change the Name field to P4_DEL_ID.  This name must match whatever you used inside the Dynamic Action.  Change the Type to Hidden and the Value Protected to No.  

Normally I don't do hidden fields on the working page and I tend to leave Value Protected as Yes.  In this example we're being extra lazy.  I prefer global/environment variables which is primarily just a preference.  In a secured environment you'd want to consider the effects of some crazy user hacking at hidden page values and if that's a risk to you or not.

At this point, the application is working.  Feel free to go treat yourself.  Perhaps A5 from the candy machine.  Or E2 if you're feeling saucy.

We still have a couple of items to make this a little cleaner, but I want to first point out a couple of nice troubleshooting options you have.

First off, don't be afraid to add some extra steps into the Dynamic Action if you want to make sure it is running.  A popup or hiding/showing a field is an easy way to ensure that the thing is at least firing and hearing the event.

Secondly, the beauty of starting w/ a hidden field on-page is that you can un-hide it to ensure you haven't screwed something up along the way.  Once you're comfortable that everything's working, then you can secure it better (if necessary).

Lastly, you can always fall back on the standard browser tools (Ctrl-Shift-C in Chrome) to just see if any blatant errors are going on JavaScript-wise.  While the steps above are fairly light JS-wise, you can still see some silly errors using such tools.

So let's put this thing to bed by cleaning up that report.  It's ugly.

Go back to the Application Builder and go back to Component View (I know...I know).  Click on your Interactive Report name (SamePageAddDelete in my example) and click on Report Attributes. At the top of that page under Column Attributes change the fields ID and EVENTID's values for Display Text As to be Hidden.  Nobody cares about seeing the IDs and they will still work for us programmatically.  

Lastly, scroll down to the Link Column section and look at the Link Icon field.  This is the our final hurdle.  That little pencil thing doesn't look like we're deleting stuff, it looks like an edit.  Now, one would hope that there's a handy delete icon included in APEX.  There might be.  I have no idea.  So let's go get one and reference it.

For now, go ahead and change the Link Icon field to read:

<img src="#APP_IMAGES#document_delete.png"  border="0" alt="Delete">

This will reference a new image we're going to upload named document_delete.png.  

If you ran the app now it would work, but the icons wouldn't display because we still need to add a Shared Component to this application.  In the Application Builder, click on the name of your application (e.g. Application 400500001).  The large icons across the top of the screen have Shared Components in the middle.  Click that and then click Static Application Files.  These are application-specific files that might differ across apps.  You could also install this as a Static Workspace File but the tag above that says #APP_IMAGES# is referring to the Application vs. the entire Workspace file structure.  Go bother somebody else to find out what you'd replace that with for Workspace files.  ;)

Click Upload File on the right-hand side of the page and select a file to use as an icon.  Change the name of the file before you upload it (document_delete.png) or change the line above to reflect the filename.  

NOTE:  Images that are 16x16 will work well.

You don't need to add a Directory or change any other items so click Upload.

Running the application now will give you a fully functional page where you can add items to the list using a form above and delete items from the list using icons within the list itself.

Now...finally...you are done.

Or are you?  The name of that column is squished together and needs a space.  Plus the size of the icon column should be thinner.  We still hard-coded the EventID and I bet you could add a second drop-down Select List for Events.  Plus we did a static lookup on products vs. looking that up from a table.  Also, maybe we should try this by creating the form first and then the report manually.  

I bet if you went into the Component View and the Interactive Report and the Report Attributes page...