Category : Paradox DBMS
Archive   : TECH91.ZIP
Filename : TI698.ASC

PRODUCT : Paradox NUMBER : 698
VERSION : 3.5
OS : DOS
DATE : September 11, 1991 PAGE : 1/3

TITLE : Conditional Fields in Forms and Reports in Paradox




While the Paradox report generator when combined with queries has
great flexibility and power, frequently the ability to do IF -
THEN conditions in reports is desired. To a limited degree the
addition of logic to the report generator can be achieved through
skillful use of some of the PAL functions: search(), substr() and
len().

With all the variations of the below listed formula which are
possible, the key to achieving the desired result stems from
thoroughly understanding the search and substring functions.

NOTE: Knowledge of PAL is a prerequisite for attempting the ideas
contained in this document.

Search - returns the position of one string within another

syntax: Search(substring,string)
where substring is an expression containing
the substring you want to locate within
String.

examples: Search("in","mine") = 2
Search("ni","mine") = 0
Search("in","mining") = 2
(first occurrence)

Search(1,123456) = 1
Search([fieldname],[fieldname]) = 1

Substr - returns a subset or 'subtring' of a string

syntax: Substr("String",x,y)
where x=position of character to start at in
String and y=total number of characters to
extract.

examples: Substr("cat",2,2) = at
Substr("cat",0,2) = blank
Substr("cat",2,0) = blank
Substr("cat",1,255) = cat

Note that a "0" in either column of this function returns a














PRODUCT : Paradox NUMBER : 698
VERSION : 3.5
OS : DOS
DATE : September 11, 1991 PAGE : 2/3

TITLE : Conditional Fields in Forms and Reports in Paradox




blank. By setting the Search function (which can be set to
return a "0" or "1") equal to x in the substr function, one can
make the display of "String" conditional.

Len - returns the length of a string

syntax: Len("String")

examples: Len("String") = 6
Len("cat") = 3


Example 1:

Substr("Text",search([fieldname]),a),255
or more specifically,

Substr("Dangerous Level",search([level],5),255)

If the value for the field [level] in that record was
equal to 5, then the Search function would equate to 1, thus
displaying or printing the message "Dangerous Level". If [level]
is 0, then the text will not be printed.

Example 2: Multi-conditional formula

Substr("Dangerous Level",search([level],5),255) +
Substr("Safe Level",search([level],1),255)

This is simply example 1 concatenated with another substr
formula checking against a value of 1. Thus if [level]=5 then
"Dangerous Level"+blank yields "Dangerous Level". If [level]=5
then blank+"Safe Level" = "Safe Level". All other values of
[level] will result in no text being printed in a report or
displayed in a form.

Example 3: Multi-conditional formula using the Len function

Occasionally it may be desirable to print reports with labels
where one of the fields may be empty. Normally in Paradox this
can result in an extra space being printed. For example if the
data includes a salutation field which may in some cases be














PRODUCT : Paradox NUMBER : 698
VERSION : 3.5
OS : DOS
DATE : September 11, 1991 PAGE : 3/3

TITLE : Conditional Fields in Forms and Reports in Paradox




empty, the report will output a blank space even if field squeeze
is used; and a calculated field [Sal] + " " + [F] + " " + [L]
will not correct this. Unfortunately this is a scenario that
neither fieldsqeeze nor a calculated field can adequately handle.

The example below will remove the extra space encountered when
the salutation field is empty, use the following expression in a
calculated field.

substr([Sal]+" "+[F]+" "+[L],1-search(len([Sal]),0),255)+
substr([Sal]+[F]+" "+[L],search(len([Sal]),0),255)

The entire expression must be typed on a single line.

The string [Sal] represents the salutation field,
[F] represents the First name field, and
[L] represents the last name field.

Substitute in your field names.

These formulas can be placed in calculated fields in both
reports and forms. The limitations here are that the expressions
can only be 175 characters in length. There is a wide variety of
combinations possible with the substr function given that there
are three components to the expression that can be manipulated.
Whenever this is explored, be sure to test for all possible
values as it is very easy to come across limitations and
unexpected results.