SQL*Forms Tech Tip - Using Color
Date: Fri, 22 May 1992 00:13:20 GMT
Message-ID: <1992May22.001320.3527_at_oracle.us.oracle.com>
COLORING FIELDS DYNAMICALLY BY ATTRIBUTE Steve Muench, Tools Sup.
It is often desirable to communicate information to the SQL*Forms operator visually, by using different video attributes. For example, one might wish to have the enterable fields in Reverse Video, while the fields which the operator cannot change in Bold. The DISPLAY_FIELD packaged procedure allows us to dynamically alter the display attributes for given fields in our form, but what we seek is an intelligent mechanism to systematically be able to "color" fields based on certain attributes.
We begin by remarking that the packaged function FIELD_CHARACTERISTIC gives us any information that we might like to know about how a particular field is defined. In particular, if we know the name of a field, say 'EMP.EMPNO', FIELD_CHARACTERISTIC can return the name of the next field in sequence in the current block. It can also tell us whether or not 'EMP.EMPNO' is an enterable field, has a List-Of-Values associated to it, is a database field, and much, much more. We'll take advantage of this in the procedure we develop below.
Let's assume that we are developing a form based on the EMP table, in which the SAL and COMM fields are non-enterable. Our goal is to display the employee records to the operator in such a way as to have the non-enterable fields (SAL and COMM) be in Bold, while all of the enterable fields remain in their normal video display attribute.
Since video attributes are individual to each particular record, we will need to color each record retrieved from the database. A POST-QUERY trigger would be a good place for such behavior. We could write a procedure which colors the SAL and COMM fields Bold, and then call that procedure from our POST-QUERY trigger. Such a procedure might take the form:
PROCEDURE color_non_enterable_fields IS
BEGIN
DISPLAY_FIELD('emp.sal', 'Bold');
DISPLAY_FIELD('emp.comm','Bold');
END;
However, this procedure will only work in our current form, and in particular will only work in the EMP block of our current form. If the need arose to color non-enterable fields bold in another form, we would be forced to rewrite a form/block-specific procedure like the one above. This takes time and again would only be solving a particular problem for a single form.
It is apparent that a more generic procedure is necessary. We will set our sights on developing a single procedure which will work in any form we might ever create -- with any blocks, and any fields that the future may have in store. It's noble purpose will be that of coloring all the non-enterable fields in the current block to Bold.
The algorithm that we shall adopt is straightforward:
(1) Determine the first field of the current block. (2) For each field in the current block
| (2a) Determine if the field in question is ENTERABLE.
| (2b) If (No), then color the field in question Bold.
|_(2c) Determine the next field in sequence to consider
For step (1), we can use the BLOCK_CHARACTERISTIC packaged function. In fact, if 'EMP' is the name of the current block, then BLOCK_CHARACTERISTIC('EMP',FIRST_FIELD) gives the name of the first field in the 'EMP' block.
Using the FIELD_CHARACTERISTIC packaged function, we can perform steps (2a), (2b), and (2c). If 'EMP.EMPNO' is the name of the field currently under consideration, then FIELD_CHARACTERISTIC('EMP.EMPNO',ENTERABLE) will evaluate to the string 'TRUE' if EMPNO is enterable, and the string 'FALSE' if not. This solves (2a) and (2b).
For (2c) we note that FIELD_CHARACTERISTIC('EMP.EMPNO',NEXTFIELD) gives the name of the next field in sequence in the 'EMP' block immediately following 'EMPNO'. In the case that we ask for the NEXTFIELD for the field which is the last in sequence, FIELD_CHARACTERISTIC will return NULL, because technically speaking there is no 'Next' field.
So our procedure appears to be coming together. Take the following example below:
PROCEDURE color_non_enterable_fields IS /*
|| Declare local variables
*/ cur_field CHAR(80); cur_block CHAR(40); BEGIN /*
|| Store the name of the current block, and use
|| it to determine the first field in that block.
*/ cur_block := :SYSTEM.CURSOR_BLOCK; cur_field := BLOCK_CHARACTERISTIC(cur_block,FIRST_FIELD); LOOP /* || If the current field we're considering is || NOT Enterable, then color it bold. */ IF FIELD_CHARACTERISTIC(cur_field,ENTERABLE) = 'FALSE' THEN DISPLAY_FIELD(cur_field,'Bold'); END IF; /* || Determine the name of the next field to || consider, and EXIT the LOOP if NULL. */ cur_field := FIELD_CHARACTERISTIC(cur_field,NEXTFIELD); IF cur_field IS NULL THEN EXIT; END IF; END LOOP; END; ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
We did it! Using the procedure above, we can color all of the non-enterable fields bold in any block in any form. We make no assumptions about the name of the current block, and we have carefully avoided hardcoding the names of any of our fields into the procedure itself. We've saved ourselves time and needless rewriting of code to perform a useful task in an arbitrary number of occasions.
However, with this noble milestone in our rearview mirror, we might be tempted to ask ourselves, "Have we really written our procedure in the most generic way possible?" Perhaps we set our sights too low, and a still nobler aspiration lies within our reach? Of course we can do better.
There is nothing sacred about the 'ENTERABLE' field attribute which we have chosen for our example above. In fact, this is just one of many possible field attributes which might interest us. The complete list includes:
AUTO_HELP FIXED_LENGTH UPDATEABLE AUTO_SKIP LIST UPDATE_NULL BASE_TABLE PRIMARY_KEY UPPER_CASE ECHO QUERYABLE ENTERABLE REQUIRED
Our procedure above is written in a generic fashion, but still only allows us to color fields based on whether they are ENTERABLE or not. Since for any particular field, the FIELD_CHARACTERISTIC function can give us information about any or all of the above list of attributes, we should not make the assumption that our future needs will be satisfied by just using the ENTERABLE attribute. We will make our procedure work for any of the possible cases.
While we are thinking of ways to make the procedure even more flexible and generic, let's consider why we hardcoded the product attribute 'Bold' into our procedure. This was not necessary. Why not let the designer specify what attribute to color the fields? This way we could use the same procedure to color fields 'Bold-Inverse-Underline' in one case, and simply 'Bold' in another case without changing the procedure itself.
Also, the curious reader may have questioned our choice of coloring the field(s) when they did NOT possess a particular attribute -- being not ENTERABLE, in our initial problem. We have found another piece of logic which could be generalized. We should allow the designer to specify whether to color fields WITH a particular attribute, or WITHOUT it as the need arises.
Therefore, our list of "enhancements" has quickly grown to include:
(1) Allow the designer to specify the name of the field attribute
upon which to base our coloring decision.
(2) Allow the designer to specify whether to color fields which
POSSESS or DO NOT POSSESS the attribute specified in point (1)
(3) Allow the designer to specify the name of the product
attribute with which to "color" the fields identified by the
criteria established in points (1) and (2)
By taking advantage of the fact that PL/SQL procedures support the passing of parameter values, we can "parametrize" all three of our features above. We will rewrite the procedure above to include three parameters:
(1) The name of the attribute to test.
(2) A boolean value of TRUE or FALSE which will determine whether to color fields which possess the attribute named in (1) or those which DO NOT possess it. (3) A string containing the name of the product attribute name to use when "coloring" the fields in question.
The names of the attributes are actually internally declared, numeric PL/SQL constants. That is, the SQL*Forms developers have assigned an arbitrary number to each of the possible field attributes and pre-defined the numeric constants AUTO_HELP, AUTO_SKIP, BASE_TABLE, etc. So the first parameter will be of type NUMBER to allow the passing of this numeric constant.
The second parameter will be of type BOOLEAN, because we only need to pass a True or False. The third parameter will need to be of type CHAR, to pass the product attribute name.
Our much-enhanced, more generic procedure assumes the form:
PROCEDURE color_fields (attrib_const NUMBER, color_condition BOOLEAN, prod_attrib CHAR ) IS /*
|| Accepts parameters:
||
|| NAME TYPE DESCRIPTION
|| -------------- -------- ------------------------------
|| attrib_const NUMBER Numerical value of PL/SQL
|| constant representing one
|| of the valid field attributes
||
|| color_condition BOOLEAN Either True or False
||
|| prod_attrib CHAR Name of Product Attribute
|| || ||
|| Declare local variables
*/ cur_field CHAR(80); cur_block CHAR(40); field_possesses_attrib BOOLEAN; BEGIN /*
|| Store the name of the current block, and use
|| it to determine the first field in that block.
*/ cur_block := :SYSTEM.CURSOR_BLOCK; cur_field := BLOCK_CHARACTERISTIC(cur_block,FIRST_FIELD); LOOP /* || Using the PL/SQL numerical constant passed in || the parameter 'attrib_const', set the BOOLEAN || variable 'field_possesses_attrib' to True if the || field under consideration possesses the given field || attribute. Otherwise, 'field_possesses_attrib' is || set to False. */ field_possesses_attrib := (FIELD_CHARACTERISTIC(cur_field,attrib_const) = 'TRUE'); /* || If the BOOLEAN 'field_possesses_attrib' matches that || specified by the Boolean parameter 'color_condition', || then color the field according to the product || attribute name supplied in the 'prod_attrib' parameter */ IF (field_possesses_attrib = color_condition) then DISPLAY_FIELD(cur_field,prod_attrib); END IF; /* || Determine the name of the next field to || consider, and EXIT the LOOP if NULL. */ cur_field := FIELD_CHARACTERISTIC(cur_field,NEXTFIELD); IF cur_field IS NULL THEN EXIT; END IF; END LOOP; END; ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
After adding our more ambitious procedure above to one of your favorite forms, experiment away! For example, I created a simple form based on the EMP table, typed in the procedure above, and added the following POST-QUERY trigger on the EMP block:
color_fields(AUTO_HELP,FALSE,'Underline'); color_fields(UPPER_CASE,TRUE,'Bold'); color_fields(LIST,TRUE,'Bold-Inverse');
This changes the display attributes of any of the fields in the current block to:
UNDERLINE if they DO NOT have an Auto Hint message, BOLD if they are marked as Uppercase Only, BOLD-INVERSE if they have an associated List of Values.
In this bulletin, we've seen that we can save development time in the long run by devising ways to generalize useful functionality into Form-Level procedures which accept parameters. When teamed with the mechanism which SQL*Forms 3.0 provides to Reference objects from one form stored in the database to any other form you create, the time saved can really add up.
REFERENCES:
(*) "Packaged Functions" -- SQL*Forms Designer's Reference,
Version 3.0, Chapter 17
(*) "PL/SQL Procedures" -- SQL*Forms Designer's Reference,
Version 3.0, Chapter 15
(*) "Product Attributes" -- Oracle*Terminal User's Guide,
Version 1.0, Appendix B
- Dan
Daniel Druker
Senior Consultant
Oracle Corporation
| Dan Druker | work 415.506.4803 | | oracle*mail ddruker.us1 | internet: ddruker_at_us.oracle.com | -------------------------------------------------------------------------------
Disclaimer: These are my opinions and mine alone, and don't reflect the views or position of my employer. Received on Fri May 22 1992 - 02:13:20 CEST