
This article is reprinted from the December 1990 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.

MacroMan II
Adam L. Menkes 

In this sequel, I want to look at a few functions that give us information
about field and record positions.  With dBASE IV version 1.1, the majority
of restrictions on commands used in UDFs have been removed, leaving a
number of opportunities for some fancy programming maneuvers.

Although dBASE IV has a FIELD() function that returns the name of the field
in the active database when a number is passed as the parameter, there is
no function that does the opposite, that is, returning the position number
of a field when passed the name of that field.  The position of a field is
important to know when using interrupt routines such as ON KEY LABEL while
in the midst of a full screen edit.  In addition, I have also created a
function which returns the total number of FIELDS in the active database,
another handy tidbit of information when you're off on some interrupted
tangent.  

Here's an illustration of the dBASE IV function FIELD() which uses the
field structure as shown in the box below.


Structure for database: C:\DBASE\SAMPLE1.DBF
Number of data records:       5
Date of last update   : 09/05/90
  Field Name      Type            Width   Dec   Index
1  NAME     Character     30      Y 
2  ADDRESS    Character     30      N
3  CITY     Character     20      N
4  STATE    Character    2      N
5  ZIP      Character      5      N
            __
** Total **         88

.? FIELD(2)
  ADDRESS

The UDF FieldNum does the direct opposite.  In the case where an invalid or
incorrectly spelled fieldname is entered as a parameter, FieldNum() returns
0.

.? FieldNum("Address")
2
.? FieldNum("Addrexx")
0

Function: FieldNum()

FUNCTION FieldNum
    PARAMETER FldName
    mExact = SET("EXACT")
    SET EXACT ON
    xf = 1
    DO WHILE UPPER(FldName) <> FIELD(xf) .AND. xf <= FIELDS()
       xf = xf + 1
    ENDDO
    SET EXACT &mExact
RETURN IIF(LEN(TRIM(FIELD(xf))) = 0, 0, xf)

While dBASE IV offers a number of functions and commands that give you
information about current active files, one it lacks is the ability to
display the total number of fields in a structure list.  UDFs are real
handy for filling in those little gaps.

Function: Fields()

FUNCTION FIELDS
    IF LEN(TRIM(DBF())) = 0
       RETURN 0
    ENDIF

    xs = 1
    DO WHILE LEN(TRIM(FIELD(xs))) <> 0
       xs = xs + 1
    ENDDO
RETURN xs - 1

This next function will increment Numeric (Float & Fixed), Date, and
Character fields. The character fields can only be incremented if there are
numbers exclusively in that field (it will NOT increment AAAAAA  see below
for modifications). Under Edit options for a field you wish to increment,
enter Incr("<field name>") into the Default value prompt.  Optionally, you
can assign a value of .F. to the Permit edit if option to disallow
modifications to that field.  Only one item may be incremented in the
format screen at a time.

Using the database structure, Sample2.dbf shown below, following are some
examples:

Structure for database: C:\DBASE\SAMPLE2.DBF
Number of data records:       0
Date of last update   : 09/05/90
Field  Field Name  Type       Width    Dec    Index
    1  IDNUM       Character      6               Y
    2  NAME        Character     30               Y
    3  COUNTER     Numeric        3      0        N
    4  PCT         Float          9      2        N
    5  LASTUPDATE  Date           8               N
    6  NOTES       Memo          10               N

Assume today is 12/2/90, if the database were empty and you APPENDed a
BLANK record:

.? Incr("IDNUM")
000001
.? Incr("COUNTER")
1
.? Incr("LASTUPDATE")
12/02/90

If several records were added and the last one (in the active index, in
this case, IDNUM) was the record which appeared as follows, Incr() would
have incremental effects on each of the valid fields.

IDNUM    NAME    COUNTER  PCT  LASTUPDATE  NOTES
001234   SMITH        17  1.21   08/31/90  memo

.? Incr("IDNUM")
001235
.? Incr("COUNTER")
18
.? Incr("LASTUPDATE")
09/01/90

The character field incrementing is set up to pad leading zeros.  If you
wanted a specific code with letters and/or special characters (such as
ABA-00001), just modify the code so that LEN(&FldName) is adjusted to take
into account the number of non-padded spaces ( 4 for ABA-) and add this to
mFldname (mFldName = "ABA-" + RIGHT(...   Len(&FldName) - 4)...).  The
calculation for the CASE condition [TYPE(FldName) = "C"] is quite involved
for a single line of code, so here it is broken down into smaller
components (using the database above for this example, where the last
record for the IDNUM field = "001234") :
.? Incr("IDNUM")

&FldName = IDNUM                  && Parameter passed.
LEN(&FldName) = LEN(IDNUM) = 6    && From structure.
REPLICATE("0", 6)                 &&'000000'
VAL(IDNUM) + 1 = 1234 + 1         && 1235
LTRIM(STR(1235, 6, 0))            && '1235'
RIGHT('000000' + '1235', 6) = 
RIGHT('0000001235', 6)            && '001235'

001235

If you wanted to increment by n instead of by 1, simply change the code
everywhere there is a 1 to n, or add a parameter that passes the increment
value (IncrAmt) and replace 1 with this second parameter in this function.

Function: Incr()

FUNCTION Incr
    PARAMETER FldName
    mDele=SET("DELETED")
    SET DELETED ON
    * If you want to increment so there are no gaps, SET DELETED ON.
    * Otherwise SET DELETED OFF, which will get the next number
    * whether or not the record has been deleted.

    IF .NOT. BOF()
      GO BOTTOM
      DO CASE
        CASE TYPE(FldName) = "C"
          mFldName = RIGHT(REPLICATE("0", LEN(&FldName)) + ;
          LTRIM(STR(VAL(&FldName) + 1, LEN(&FldName), 0)), ;
          LEN(&FldName))
        CASE TYPE(FldName) $ "FND"
          mFldName = &FldName + 1
      ENDCASE
      ELSE
      DO CASE
        CASE TYPE(FldName) = "C"
          mFldName = RIGHT(REPLICATE("0", LEN(&FldName)) + ;
          LTRIM(STR(VAL(&FldName) + 1, LEN(&FldName), 0)), ;
          LEN(&FldName))
        CASE TYPE(FldName) = "F"
          mFldName = FLOAT(1)
        CASE TYPE(FldName) = "N"
          mFldName = 1
        CASE TYPE(FldName) = "D"
          mFldName = DATE()
      ENDCASE
    ENDIF
    SET DELETED &mdele
RETURN mFldName

Have you ever had your file organized by an index and needed to know what
sequential position a particular record held.  For example, consider a
database with 100 records indexed on Lastname conditionally FOR Lastname =
"Smith".  Suppose this results in a list as follows:

RECORD    FNAME LNAME
23    Adam  Smith
12    Bob Smith
97    John  Smith

If you were to SEEK Bob Smith and want to know what record position you are
currently at, RecPos() will return 2, the logical position of the record in
the index list as opposed to RECNO() which would return the physical record
number location of 12; simple enough concept and so is the function.

Function: RecPos()

FUNCTION RecPos
    mRec = RECNO()
    GO TOP
    COUNT TO mPos WHILE RECNO() <> mRec
RETURN mPos + 1

Well, this was a short journey through UDFs this time.  (MacroMan's a busy
guy y'know).  Stay well and have a prosperous 1991 (which, incidentally, is
the actual FIRST year of the last decade of the twentieth century, so Happy
New Decade). 

