This article is reprinted from the February 1991 edition of TechNotes/dBASE
IV. Due to the limitations of this media, certain graphic elements such as
screen shots, illustrations and some tables have been omitted. Where
possible, reference to such items has been deleted. As a result,
continuity may be compromised.

TechNotes is a monthly publication from the Ashton-Tate Software Support
Center. For subscription information, call 800-545-9364.

Etc.

SQL Commands In UDFs

Page 4-28 of dBASE IV 1.1 Getting Started with dBASE IV Developer's Edition
states, "A UDF can contain only the dBASE IV commands and functions that
are allowed in SQL mode. A UDF cannot contain any SQL command."

This is not always true. Some recent testing indicates there are some
limitations. You can only execute an SQL command in a UDF when it isn't
called by the VALID or WHEN clause of the @...GET command. Otherwise, an
expression such as this works with no problem:

memvar = NewID()
FUNCTION NewID
SELECT MAX(Memberid) INTO maxid FROM Members;
newid=IIF(SQLCNT>0,maxid+1,1)
RETURN newid
APPEND FROM Axiom

Here's a truism that many people miss even though it's been a published
fact for some time: If you want to append all records that are not marked
for deletion from one file to a new file, follow these simple steps:

SET DELETED ON
USE
APPEND FROM

This brings in all records that are not marked for deletion in .

Note the following, however:

1. SET DELETED must be ON (if it's not, ALL records from will
be appended).
2. You'll only be able to bring in records that aren't marked for
deletion.

This feature is documented under APPEND FROM in Language Reference.

Postscript Improvement

A new printer driver for Postscript that allows printing of Avery labels is
available through our support lines and on the BBS. The new Postscri.DLD
file enhances the capability of the PostScript support in dBASE IV 1.1. It
adds the capabilities to print to Avery labels, variable point size and
variable number of lines per page.

You will now be able to include the following macro names in the starting
control codes to print to a particular Avery label:

A5160
A5163
A5164
A5162
O5162 (for older version of 5162; 7 rows & 9 lines per label)

You will be able to print a font at any point size. For example, to
print a 5 point font, put the following macro in the starting control
codes:

5 point

You can take full advantage of the variable point size feature by
specifying the number of lines per page. For example, to print 100 lines
per page, enter the following macro in the starting control code:

100 LPP

This macro will adjust the size of the VMI so that 100 lines would fit in
the current paper size. For proper pagination, set the lines per page
setting on the print menu to 100 lines as well.

You can combine the macros together. For example, to print a 6 point
Helvetica font and 90 lines per page, set the starting control codes to the
following:

2font 6 point 90 LPP

To use the enhanced Postscri.DLD, copy the Postscri.dld file supplied with
the disk (available through Software Support) over the existing
Postscri.DLD in your dBASE directory on your hard disk.

Understanding Order of Precedence

dBASE IV uses an order of precedence in which to determine what order
report fields are calculated. There are six types of calculated or summary
fields possible in a report:

Named hidden calculated
Named hidden summary
Named calculated
Named summary (SUM, MAX, MIN, etc)
Unnamed calculated
Unnamed summary

The order of field calculations are as follows:

1. Hidden
2. Non-hidden named
3. Unnamed

Hidden named calculated and named summary fields are calculated first.
They are calculated in the order created. This can cause potential
problems.
For instance, creating or modifying a hidden field that references a
non-hidden field.
This results in the value of the hidden field always being one record
behind and leads to further problems when the hidden field is then
referenced in another hidden or non-hidden field.

Non-hidden named calculated and named summary fields are calculated after
hidden fields. These fields are calculated in a top-to-bottom,
left-to-right fashion within the band. Here again, a problem arises when
you create a field that relies on another calculated or summary field that
is to the right or below. This also results in the value of the field
being always one record behind. This will lead to further problems if the
field is then referenced in other calculated or summary fields.

Unnamed calculated and summary fields are calculated last, at the time they
are printed out. The only problems these fields may have is if they
reference a hidden or non-hidden field as discussed above.

Common problems and what to look for

Most often, a calculated or summary field isn't correct or a Group Summary
field is one group behind. Here's what to look for:

1. Is the field named or unnamed?
2. If a calculated field, what exactly is the expression of the
calculation? (You want to be looking for other calculated or summary
fields contained in the expression)
3. If a summary field, what field is the operation being done on? Is
this field a calculated or summary field?

A few solutions

If the field is named and not hidden:

1. Try unnaming the field. This will put it last in the order of
precedence. This solution will only work if the field is not used in
another field's calculation (otherwise the other field will be referencing
a non-existent field).
2. Move the field to below or to the right of all the fields it
depends on. It will then be calculated after all the fields it depends on
have been calculated.

If the field is hidden:

1. If the expression relies on another hidden field, delete the field
and recreate it. Since hidden fields are calculated in the order they were
created, this field will now be calculated last.
2. If the expression relies on a non-hidden field:
a. Unhide the hidden field
b. Place the now visible field after all the fields on which
it relies.
c. Give it a picture consisting of just spaces.

For example, suppose you SUM a price field in the Report Summary Band and
then create a calculated field that displays how much tax there will be.
You must put the TAX field after or below the SUM field.

Taking another example, you have a hidden field that relies on a non-hidden
field and the hidden field is being summed in a third non-hidden field.
The SUM, of course, will come out wrong.

You must un-hide the field and place it after the field it relies on.

What to watch out for:

1. If you unname a field and that field is used in other calculations,
when the report is run the error "Variable not found" will occur.
2. If, in a group summary band, you unname a calculated field that
uses an actual database field, the database field used will be from the
first record of the next group instead of the last record from the current
group. (This becomes a little trick: If you want to use a value from the
last record's value in a group summary, make sure the calculated field is
named!) s