« August 2006 | Main | October 2006 »

September 28, 2006

Overdue Orders Report Enhancement

One of SWAMP's reports tells you which items on order are overdue by more than a week. The report gives you the details on what was ordered and when it was due to be delivered. The problem with the existing report was that Marsha would check on the overdue items, but when she printed out the report several days or a week later, she had no way of knowing the status of items she had previously checked up on the last time the report was printed out. She needed some way to mark the overdue items that she had already checked up on with a code that indicated what the current status of the item was. If she could do this, then she would not have to waste time re-checking items.

Javascript and DHTML to the rescue! We decided on these codes:

The goal is to turn the existing report into a dynamic form that will allow for input of the new status codes. The first thing was to modify the report to display text areas for each displayed item. Each text item contained this html code (the value assignment is the way a PL/SQL variable is referenced in a loop):

<input type=text name=yo size=6 maxlength=6 value="'||j.bambi||'" onchange="upper(this),ed(this)">

A status variable and a hidden variable were added. The hidden variable is ed. It will be used to keep track of what's going on in the form. By the way, a new table was added for tracking the status variable.

It was pretty easy to allow for entry of a new status. Each time a commit was done, the form was checked for status values that weren't null and then the status was inserted into the form. So far, so good.

The next step is to figure out ed - an array that matches up with our text box arrays. Ed will tell us which status records have been edited since the form was loaded. It looks like this:

function ed(myEle){

var statEl = myEle.sourceIndex;
document.all[statEl+4].value = ''true'';
}

First, we get the source index number of the element that called ed. Then we set the ed array value to "true" by adding 4 to the index. 4 is the number that works in this situation, you will need to experiment to see what works in your situation.

So, now I have the status value for each record, and whether it has been edited. I'm ready to figure out whether to "insert", "delete", or "update".

if(edited(i) = 'true' and wstatus(i) is not null)

This is an insert condition (the status box was edited, and it has a value). Note that this also works for the case where a value that was not initially null was changed to another value (because I delete from the table first, then insert the current values).

elsif(wstatus(i) is null and edited(i) = 'true')

This is a delete condition, a record has been modified to null.

If a record was not edited since loading, it isn't even a test condition since we don't want to do anything with that record anyway.

Here's what the new version of the Overdue Orders form/report looks like:

One final consideration is value checking - we only want to allow the values to be entered that match up with the key. In this instance I decided not to actually check the values before insert, but at the very end of the form we will just delete any records that have bogus entries like this:

delete from fic30.eta where status not in ('D','R','BO','CP','J','M');

So there you have it, a nice little interactive report that is an improvement from just printing out a list of records.


Posted by rossm at 11:28 AM | Comments (0)