Programmatically setting values in the Interactive Grid

Recently I've been able to spend some quality time with the Interactive Grid(IG).  I've had two different requests from my users to programmatically set column values in the IG using a button. I decided to use my blog to help document the solution for future use.  Coincidentally, a user on the APEX forum asked for a combined solution from both of my user requests(which I'll demonstrate below).

 Here were the two methods requested:

1. Set the column value in all rows to the value of another column in the IG
2. Set the column value of selected rows using the value from a separate form

I'm still getting comfortable with the IG and it's capabilities, so luckily I was able to use a great resource created by Marko Goricki of APEXbyG on Github to cobble together solutions for both requests.

If you want to try it out, follow these step to create a demo IG:

1. Create an IG on the emp table of the sample database application provided with APEX
2. Set the Static ID property of your IG = emp
3. Click on the attributes link of the newly created IG and set Enabled = Yes in the Edit section of your IG
4. Right click on your IG and select Create button
5. Set the Action = Defined by Dynamic Action
6. Right click on your button and select Create Dynamic Action
7. Set the true action to Execute Javascript Code

And you're ready to go!  You can use the below code samples in the code property of your dynamic action to try out the solutions.

The key to both solutions is first to access the model by getting to the IG grid view:

1. var widget = apex.region('emp').widget();
2. var grid = widget.interactiveGrid('getViews','grid');
3. var model = grid.model;

'emp' being the static id of your IG

Once you have access to the model object you can get and set the value of a column in a record by using the getValue and setValue methods of the model object when you pass in the record to the methods.

1. Set the column value in all rows to the value of another column in the IG

This requirement entails looping through all rows of the IG to set the value of one column using the value of another.

Using the above to fetch the IG model object we're able to loop through the IG rows and set the value of the COMM column to the value of the SAL column :

1. var widget = apex.region('emp').widget();
2. var grid = widget.interactiveGrid('getViews','grid');
3. var model = grid.model;
4. 
   
5. model.forEach(function(r) {  
6.                 model.setValue(r,"COMM",model.getValue(r,"SAL"));  
7.         }  
8. );  

2. Set the column value of selected rows using the value from a separate form

The 2nd request was very similar to the 1st except you need to loop through only the selected rows.

1. var widget = apex.region('emp').widget();
2. var grid = widget.interactiveGrid('getViews','grid');
3. var model = grid.model;
4. var selRecs     = grid.view$.grid("getSelectedRecords");  
5.   
6. selRecs.forEach(function(r) {  
7.                 model.setValue(r,"COMM",model.getValue(r,"SAL"));  
8.         }  
9. ); 
10.  

Here I'm setting the value of one column using the value of another just like the previous example for consistency, but you could easily fetch the value from a UI field using the $v() function instead to fulfill the requirement.

I hope this helps others that are still getting to know the IG component like I am.

Cheers

Guru Status

Small accomplishment, but I can't lie...I am proud of this small achievement:

I must say that I gained much more from participating in the discussion forums than I've given and I encourage you all to do the same.

Cheers!

Substitution Strings for IR Headers

The other day I helped a user on the Oracle APEX discussion forum with a question which led to an interesting question.  The user was using substitution variables for their column headers in an Interactive Report(IR).

Here's a quick example if you'd like to try it out yourself.

1. Create a hidden item with the following properties (The static value will be our header value):

2. Create an IR region on any table in your schema.  Edit your IR report attributes and set the heading value for one of your columns to the substitution string of your hidden field:

3. Run your report and you should see something like the following:



Everything appears fine until you click on the column header and try to perform any try of built-in IR action such as sorting or filtering.

4. Click on a sort icon and the header disappears.

After doing a quick search, I couldn't really find anything on this topic.  I thought about it for a bit and decided that it was probably a session state issue.  Luckily since IR regions do AJAX requests they allow you to specify page items to submit to session on refresh.  If you add your hidden item's name to the "Page Items to Submit" attribute, the header no longer disappears.





Here's the interesting question that it led to...

Why does the header value appear when the page first loads and not when the region is refreshed?

This is a topic that I stumbled upon while trying to create a session state demo.  In my demo application I use an APEX view called apex_application_page_items to fetch the current values of page items on the current page.  Let's add an IR region querying this view to further explore this topic.

5. Add a new IR region to your page using the following query:

select item_name, component_comment
      ,apex_util.get_session_state(item_name) session_value
from  apex_application_page_items
where application_id = :APP_ID
and   page_id        = :APP_PAGE_ID

Run your page in a new session and you'll see some interesting results:



We can see that the view is reporting that our hidden item's value is in session state.  However, if you click on the "Go" button of your session state report, you'll see that your hidden page item's value really is not saved in session state.



So what's going on?  I can't tell you exactly why because I'm just theorizing, but maybe someone from the development team can chime in on the topic.  However, this is what I think.

When a page loads you have access to the value of page items for various things like your queries, default values, etc...  In our case, we can set the value of our column heading using the value of a page item.  However, when a page is done loading the default values, static values, and the values used from an automatic row fetch process(ARF) are not set in session state.  So how are they available to our components on page load?  I think that during the page lifecycle of an initial page load, these values are actually set in session state before the page components are loaded, then reset when the page is finally done loading.  That's why our region reporting page item values from the apex_application_page_items view displays values for page items in session state, but when refreshed, it shows that the same items have no value in session state.

Clear as mud?  Thought so...

Debugging 101: A case study

Recently I've spent most of my free time participating the in Oracle APEX Discussion Forum on OTN.  It's a way to give back to the Oracle community, but also to learn new things and hone my skills.  So if you ever get a reply from Pmon, that's me :)

Today I was trying to help a user that wanted to implement a slider to set a numeric value in a field.  They were using javascript samples from a couple of blog posts and had cobbled together a sample that wasn't working.  After a quick search I found that there was already a plugin someone had created.  Slider was an APEX plugin made for a minimum APEX version of 4.x.  I posted the link to the plugin as the solution and thought I had come up with another easy win.  A few hours later, the user updated the thread to let me know that the plugin wasn't working and the control wasn't displaying on the screen.  Truth be told, I had never worked with APEX plugins in my life.  I took a quick look at the link I sent and compared it with the user's example and could see that there were noticeable differences.  I updated the thread with my observations and told the user if all else fails, contact the developer.

One thing that I've found is that while trying to help people in the forums I become unintentionally emotionally invested in each issue that I'm helping people with.  I feel like I have a responsibility to carry them forth toward a solution even when my initial effort fails.  This is exactly how I felt in this situation.  What gnawed at me was that the developer of the plugin had a working example deployed to apex.oracle.com which meant that it could work in version 5.

I decided to try to resolve the issue knowing that it would be a good opportunity for me to learn about plugins and how they worked.  One thing that I was confident in was my debugging skills so I decided to share in this post a step by step case study of how I resolved this issue.

Triage:

The first step in debugging any issue is trying to figure out how severe the problem really is and what is going on.  Looking at the user's sample:

Anyone could plainly see that the text box was displaying, but the slider control was missing.  This is usually the indication of some type of error.  It could be a missing image file or some type of rendering issue.  To see if there were any script errors occurring I decided to use f12 tools:

If you haven't used it before, just hit f12 on your browser and prepare to be amazed.  Each browser has a different implementation, but most of them have about the same features that are essential to debugging errors.  In the console window there was a javascript error that occurred during the rendering of the page.

Interpreting your errors

If I have one essential piece of advice for anyone learning how to code and debug any language it is this....learn how to understand error messages!  Error messages can be cryptic, but they really are your best source for information to what's going on.  Unfortunately there really isn't a shortcut in learning how to interpret error messages.  The best way to learn what error messages are trying to say to you is to see a lot of them.  Luckily we make a lot of mistakes.  So code a lot, make a lot of mistakes, and learn.

With this particular message, it's complaining about e.widget.extend not being a function.  In my experience, if this works in an earlier version and doesn't work in a newer version, it usually means that either the method has been deprecated and no longer exists, or it's implementation has changed and needs to be used in another way.

Search efficiently

To figure this out I started my search like I always do.  Try to come up with a well formed specific google search.  Google searching is also an essential skill in debugging because let's face it, there's a world of information out there if we only knew how to find it.  I usually start with a search that is somewhat general and has enough details to get me in the ballpark of where I want to go.  I use it as a feeler to find specific search terms that will lead me directly to the answers I want.

This is what I came up with...

oracle apex e.widget.extend is not a function

The first hit that I navigated to was a blog by Tobias Arnhold:

He mentioned the exact error that was occurring and referenced that it was occurring in the Early Adopter's version of APEX 5.  It wasn't quite what I was looking for because there was no resolution, but it gave me enough information to refine my search.  Here was my next attempt:

oracle apex 5 e.widget.extend is not a function

It was a subtle change, but just adding 5 after apex for the search made all the difference.  The next link I checked was another blog entry from Mr. Arnhold:

Part II of his original post and this time he provided a resolution.  The issue was arising because of the reference to an older jquery library.  The resolution?  Remove it.

After reviewing the plugin code I found that the javascript libraries were being added via the PL/SQL of the plugin.  I commented out the section the referred to the offending library jquery.ui.1.8.  This would take care of the javascript error, but there were still missing html elements.

Compare with a working example

This is another essential skill in debugging.  When things don't work for you, strip your code down to the bare essentials and compare it to a working example of what you want to achieve.  In this case, I had the demo application that worked like a champ https://apex.oracle.com/pls/apex/f?p=84616:99903.  I compared it with the example provided by the user and found a couple of differences:

It might be hard to see in the images, but I immediately noticed that the div tag for the slider control was missing a class attribute.  It was also missing an html anchor as a child to the div tag.  To test the changes that I was going to make to the page I used the f12 tools to directly add the class attribute to the div tag and add the anchor as a child element to the div.  After doing these two things, I could immediately see that I would get the desired results.

To resolve this issue, I edited the plugin source and changed the original:

to..

by taking the html directly out from the page and adding it to the PL/SQL code.  Feeling pretty good about myself I decided to run the application again.  This time the slider control displayed, but I was still getting a javascript error:


A different error! Progress!  With my new error I just followed the same debugging process that I used with the first error:

• Triage
• Interpret your error(s)
• Search Efficiently
• Compare with a working example

This error stumped me a bit.  I checked all the javascript source of the working example and it was the same as the broken sample.  I knew that this error was essentially same as the first, so for some reason the method .slider was missing in our sample, but not in the demo application.  I determined that this was not an APEX specific error so I did a search:

javascript .slider is not a function

The first result that came up was a stackoverflow thread that had this answer:


Once I saw this answer in the thread I realized what had happened.  The javascript library that I had removed was just out-dated, but it was still essential.  So, I used the script reference provided to download a new version of the library.  I saved it to a file and uploaded it to the plugin:


then I added a reference to the file in the PL/SQL of the plugin:



When I ran the page again, the slider worked and there were no error messages:

Success!  As with any success, there were many failures along the way, but I did learn a lot during the process.

I hope that this example of how to debug an issue helps someone out there and remember to use these debugging steps/skills:

• Triage
• Interpret your error(s)
• Search Efficiently
• Compare with a working example

Happy debugging!


Update:

One thing that's cool about the internet is that it shrinks the world and it brings us closer to each other.  Because of this you never know who you might interact with.  Today, I got advice from Patrick Wolf, member of the APEX development team.  How cool is that?  Here's what he said:

"instead of loading the full jQueryUI library again (APEX already loads a number of jQueryUI components out of the box), I would suggest to use

apex_javascript.add_3rd_party_library_file (
p_library => apex_javascript.c_library_jquery_ui,
p_file_name => 'jquery.ui.slider' )in the plug-in. This has also the advantage that the plug-in automatically picks up the most recent jQueryUI version which ships with APEX, otherwise you might run into the same issues with the next version of APEX when we update jQueryUI to a newer release."

Two things of note in his comment.  Firstly, always try to keep your source code to a minimum.   It helps with page loading and performance.  Secondly, when you use external libraries and make customizations it's very common to have issues when patches and upgrades are released.  Be prepared to support them for the life of the application.  

Book Review: Pro jQuery for APEX

Pro jQuery in Oracle Application Express. Scott Wesley. 2015. APRESS. 221 pages. [Source: Safari Books Online]

I've been absent from the APEX world for about 4 years.  In that span I've missed quite a bit of Oracle APEX 4 including new features of AJAX and the use of plugins.  Now I find myself trying to play catch up in the whole new world of Oracle APEX 5.0.

Through my searches for solutions and participation in the Oracle APEX community forum I came across an excellent website grassroots-oracle.com which is the APEX blog of Scott Wesley.  Browsing through Scott's blog I found very helpful and instantly relevant posts such as Improving PL/SQL performance in APEX.  I also read the abstract for a training class that he was holding on jQuery and Dynamic Actions.  Although I wanted to attend the training course, Perth, Australia is a long way to travel from Hawaii for a 2 day training course.  So that's when I discovered that he wrote the above book which covered some of the same topics that are covered in his class.

Who should read this book?:  In my opinion, a working knowledge of APEX and PL/SQL is essential.  This is not a book for someone who doesn't know Oracle APEX at all, but more for someone who would like to gain better knowledge of and learn tips on how to interact with the APEX DOM model and javascript APIs. 

What's so good about this book?: Oh yeah, I forgot to mention that this is by far the best APEX book I've read.  Why?  I guess mostly because I fall into my description of who should read this book.  I'm an experienced PL/SQL & APEX developer, but I have long struggled with CSS and trying to control the layout and look and feel of APEX.  Scott does an excellent job of not only presenting examples of how to change certain things, but more importantly, he aims to teach the reader how to use debugging tools to figure it out for themselves.  Moreover, he also delves into the inner workings of the APIs behind the scenes that Oracle APEX uses in transactions.  A little more than I needed to know, but it's always nice to have that information in your back pocket for when the situation arises. The reading is easy and most of his topics covers solutions you probably were already looking for (ie highlight a report row on click, change the value or style of an item using a dynamic action, etc..). 

Not so good about the book?: At times I found that I wished there was a more comprehensive example during my reading.  That being said, I did appreciate that the examples were short and concise.  With my experience level I feel that I could definitely get his examples to work with a few extra minutes invested because of the lack of details in some areas.  But that's also why I wouldn't recommend this book for beginners.

Rating: 5 out of 5 stars

Hello World!

In my experience as a consultant, I've found that the perceived success or failure of any project most times comes down to properly or improperly setting the expectations of the client.  So what can you expect from my blog?  Honestly, I'm not really sure...Obviously I'm new to this blogging game so you can expect a lot of mistakes for sure, so please be kind.  OJT at it's finest.

I can tell you that if you've come for groundbreaking and innovative solutions using cutting edge technologies with Oracle APEX, you've come to the wrong place.  What I envision using this blog for is kind of an index of useful solutions and ideas not only for myself, but for other APEX developers out there.

I named this site Simply APEX as a reflection of my theory of APEX development.  That it's in everyone's best interest to try to remain within the constructs of the product as much as possible.

I spent a large part of my early career developing with a custom Oracle PL/SQL htp development framework called "The Framework" that in many ways worked just like Oracle HTMLDB.  It worked great and the clients loved the custom applications that I was able to build for them.  However, when you use a proprietary tool for development, they have no choice but to use your services for bugs and upgrades.  This poses a major problem for your clients when you decide to leave to pursue other opportunities.  When Oracle released Oracle APEX 3.0 I was blown away.  After evaluating the product and the new features I decided that it was a prudent decision to convert all my Framework clients to use Oracle APEX.  It was a no brainer.  All my colleagues had left Oracle Hawaii and I was a team of 1 supporting an application framework that provided the same functions as a product supported by a team of professional developers.  I never regretted this decision because I left all my clients in a position to continue using their custom applications for years beyond my time at Oracle.

I guess what I'm trying to say is that customizing is cool and fun, but hard to support.  So I've made a conscious decision to KISS (Keep It Simple Stupid).  And why not?  Have you seen APEX 5.0? Universal Theme?  Oracle certainly has a capable team of developers that will continue to build and enhance this wonderful product.

Hopefully, I can at least help the newbies out there and provide simple solutions for simpletons like me.