Category : Dbase (Clipper, FoxBase, etc) Languages Source Code
Archive   : TN9005.ZIP
Filename : INCRMENT.TXT

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

Incrementing the Easy Way
Michael P. Dean

Have you ever wanted to have a numeric field automatically increment
for each new record, only to find out that you would have to write a
program to accomplish the task? Well, you'll find it written for you
in this article. You will be shown two ways to increment a field
using format files. The first example requires that you create a
small User Defined Function (UDF), and then modify the .FMT file by
adding a few lines of code. The only disadvantage of doing it this
way is that if you ever change anything in your format file, your
changes are overwritten when the .FMT file is regenerated and you will
have to edit the file and type in the changes once again.

With the dBASE IV Developer's Edition we have the availability of the
Template Language which leads us to the alternative method. The
second example is a modified .COD file for a new form generator that
will automatically write all necessary code needed including the UDF!
By doing everything through a .GEN file, you don't have to worry about
re-writing any code just because you made a few changes to your
screen. You can also use this new .GEN file for regular format files
that do not have any auto incrementing fields. All you have to do is
rename the current FORM.GEN to something else (how about FORMOLD.GEN?)
and when you run the template file compiler, use the -o (output file)
option to output a new FORM.GEN. The compiler command would appear as
shown below:

DTC -i Autoincr.COD -o FORM.GEN

Alternatively, you can simply rename the file to FORM.GEN. It would
be advisable to make a backup copy of the FORM.GEN before any
modifications were made if you are using a modified version other than
what has come with your original diskettes. If you have any other
questions, refer to the Template Language book that comes with the
Developer's Edition.

For users who do not have the Developer's Edition, AutoIncr.GEN can be
obtained from the Ashton-Tate or CompuServe BBS. See back cover for
phone numbers and hours of access.

Things to Remember

A couple of points are worth mentioning before we get started.
Regardless of which method you choose to accomplish auto incrementing,
you must keep a few things in mind.

ù When using this form for auto incrementing, you must set an
index whose primary index key (controlling field) is the field
to be incremented.
ù In version 1.0, you can not have CARRY set to YES for any
other field in a screen form that uses auto-incrementing.
This will cause conflicts with the DEFAULT VALUE option used
in the UDF.
ù If you enter a new value into the field that contains the
default, this will throw the numbering sequence off. The
starting (default) value is determined by evaluating the last
sequence number in the index and incrementing it according to
the number you specify. Therefore, if you need to start out
at a specific number, add a record to the file without the
UDF/format file in use and insert the starting number.
ù Every time the UDF is modified and saved, do not forget to
compile it before activating the screen form or doing an
APPEND. An error message, "Illegal call to the compiler" may
result.

With these points in mind, hold on to your data, because here we go.
First, write the following program and compile it:

* Program ...: Fldincr.PRG
* Author ....: Michael P. Dean
* Date ......: Wed 11-29-1989
* Versions ..: dBASE IV Version 1.0
* Notes .....: UDF to automatically increment a numeric field.

FUNCTION Autoincr
PARAMETERS incr_value

fld_value = fld_value + incr_value
RETURN fld_value

* EOP: Fldincr.PRG

Then, make the following change to your .SCR file. Do this by typing
the following command from the dot prompt:

MODIFY SCREEN

From the Control Center, select the form to have this change and
choose the Modify layout option. Once you are on the Forms design
work surface, add a new field or highlight an existing numeric field
on whicht you want to perform auto incrementing. Under Edit options:
Default value, enter Autoincr(n) where n is the number to increment
by.

Inserting AutoIncr(4) under Default value will automatically
increment the field by 4. Decremnting by 2 would be expressed as
AutoIncr(-2).

Saving your form will generate an .FMT file. After this is complete,
make the following changes by typing from the dot prompt:
MODIFY COMMAND .FMT

Locate the line which appears below. Press the = key to position your
cursor above this line. Pressing Ctrl-N will open up blank lines for
you to include additional command and comment lines listed below.
You can use the Words:Locate option to search for the string "@ SAY
GETS". This should take you directly to the line referenced here.
Look for:

*Ä @ SAY GETS Processing.

Above this line, type in:

*Ä Store the current record number to a variable, then
* store the last number to the memory variable sys_value

sys_record = RECNO()
GO BOTTOM
sys_value = {FLD_FIELDNAME}

*Ä If at the beginning or end of file,
* do not attempt to go to the record
* stored in sys_record

IF sys_record <= RECCOUNT()
GO sys_record
ENDIF

Anything that is above the "@ SAY GETS...." line will get processed
only once. All remarks (lines beginning with an asterisk) are, of
course, optional. However, putting them in may be helpful to someone
else who may later need to modify the code, or remind you when you
need to look at the code at a later date.

When this is finished, select this form from the Control Center, or
type in SET FORMAT TO

from the dot prompt then append or
edit to your heart's content.

And now, for those of you who are ready for something more juicy and
want to venture into the Template Language, proceed with the following
steps! I'm only going to note those changes that are necessary since
the majority of the old FORM.COD is the same. The line numbers
requiring change are referenced and we will work from the bottom up so
as not to throw off the sequencing. Use the following format:

LINE #: (line number)
CURRENT: (references in the original FORM.COD)
CHANGE: (replacement code including remarks)

The line number references are to the original FORM.COD and not the
enhanced version you may have obtained from the Bulletin Board or
through Software Support. If you are modifying a later version, the
line references may not be applicable. Use the Locate option from the
Words menu to find the references specified below.

Changes to FORM.COD

LINE #: 581
CURRENT:

//ÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄ
define nul2zero(numbr)
// if number is nul and we are expecting a zero - convert the nul
to 0
if !numbr then numbr=0 endif
return numbr;
enddef
// EOP FORM.COD
}
CHANGE:

//ÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄ
define nul2zero(numbr)
// if number is nul and we are expecting a zero - convert the nul
to 0
if !numbr then numbr=0 endif
return numbr;
enddef
//ÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄ
//
// Create the UDF for auto incrementing
//
define udf create()
CREATE("Autoincr.PRG");
}
* Program ...: Autoincr.PRG
* Author ....: Michael P. Dean
* Date ......: Wed 11-29-1989 (Original Creation)
* Notes .....: UDF to automatically increment a numeric field.

FUNCTION Autoincr
PARAMETERS incr_value

fld_value = fld_value + incr_value
RETURN fld_value

* EOP: Fldincr.PRG

//
// Return to the format file and finish the code for it.
//

{ APPEND(fmt_name + ".FMT")
return;
enddef
// EOP FORM.COD
}

LINE #: 413
CURRENT:

RELEASE {if carry_flg then}lc_carry,{endif}lc_talk,lc_fields,lc_status

*Ä EOP: {filename(fmt_name)}FMT

CHANGE:

RELEASE {if carry_flg
then}lc_carry,{endif}lc_talk,lc_fields,lc_status{

if auto_flag then},sys_value,sys_record {endif}

*Ä EOP: {filename(fmt_name)}FMT

LINE #: 339
CURRENT:

WHEN {FLD_ED_COND} \
{ endif
if FLD_DEF_VAL then color_flg = 1;}
;
DEFAULT {FLD_DEF_VAL} \
{ endif
if FLD_HLP_MSG then color_flg = 1;}
;
MESSAGE \
{ if !AT("IIF", upper(FLD_HLP_MSG))}"{endif}{FLD_HLP_MSG}\
{ if !AT("IIF", upper(FLD_HLP_MSG))}"{endif} \

CHANGE:

WHEN {FLD_ED_COND} \
{ endif
if FLD_DEF_VAL then color_flg = 1;}
;
//
// If there is no incrementing field specified, set up a normal
// default setting, else, determine the amount to increment by, and
// insert the appropriate default setting.
//
{if auto_incr = 0
}
DEFAULT {FLD_DEF_VAL}
{else
if AT("++", FLD_DEF_VAL) > 0 then
incr amt = SUBSTR(FLD_DEF_VAL, AT("++", FLD_DEF_VAL) + 2,
LEN(RTRIM(FLD_DEF_VAL)) - 2);
}
DEFAULT Autoincr({incr_amt})\
{else
}
DEFAULT {FLD_DEF_VAL}
{endif
endif}
{ endif
if FLD_HLP_MSG then color_flg = 1;}
;
MESSAGE \
{ if !AT("IIF", upper(FLD_HLP_MSG))}"{endif}{FLD_HLP_MSG}\
{ if !AT("IIF", upper(FLD_HLP_MSG))}"{endif} \

LINE #: 168
CURRENT:
print(" ADDITIVE" + crlf);
endif
}

*Ä @ SAY GETS Processing.

CHANGE:

print(" ADDITIVE" + crlf);
endif
}
//
//ÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄ
// Process fields for build Auto Incrementing.
//ÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄ
// Start the loop to look into each default setting for every field
// on the form.
//
{foreach FLD_ELEMENT fld_plus

if AT("++", FLD_DEF_VAL) > 0 then //Look for the auto
incrementing marker.
++auto_incr; //Increment auto incr memory variable by 1

//
// If there is more than one field with the ++ in DEFAULT setting,
// display an error message and stop compilation, otherwise, check
// to see if the UDF program already exists, if it does, prompt the
// user and either erase the file, and create a new one, or go on,
// else, if the UDF doesn't exist, create it. Then, insert the
// necessary code into the .FMT file.
//
{foreach FLD_ELEMENT fld_plus

if AT("++", FLD_DEF_VAL) > 0 then //Look for the auto increment marker
++auto_incr; //Increment memory variable by 1

//
// If there is more than one field with the ++ in DEFAULT setting,
// display an error message and stop compilation, otherwise, check
// to see if the UDF program already exists, if it does, prompt the
// user and either erase the file, and create a new one, or go on,
// else, if the UDF doesn't exist, create it. Then, insert the
// necessary code into the .FMT file.
//
if auto_incr > 1 then
PAUSE(fld_incr)
FILEERASE(fmt_name + ".FMT");
CREATE(fmt_name + ".FMT");}
CLEAR

@ 3,5 SAY "This Format File was not generated because you specified"
@ 4,5 SAY "more than 1 Auto-incrementing Field. Press to exit."

{return
else
auto_flag = 1
if FILEEXIST("Autoincr.PRG") then
m_ans = UPPER(ASKUSER(udf_exist, "Y", 1))
if m_ans = "Y" then
FILEERASE("Autoincr.PRG")
UDF CREATE()
endif
else
UDF CREATE()
endif
}
*Ä Store the current record number to a variable, then
* store the last number to the memory variable sys_value

sys_record = RECNO()
GO BOTTOM
sys_value = {FLD_FIELDNAME}

*Ä If at the beginning or end of file, do not attempt to go to the
record
* stored in sys record

IF sys_record <= RECCOUNT()
GO sys_record
ENDIF

{ endif
endif
next fld_plus;
}

*Ä @ SAY GETS Processing. ÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄ

LINE #: 67
CURRENT:


page_cnt = 1
screen_size()

CHANGE:

page_cnt = 1
auto_incr = 0
auto_flag = 0
screen_size()

ù Miscellaneous Remarks, Do not add to .COD file. Store 0 to
auto incr and to auto flag (A zero in the Template Language can be
either a numeric or a logical. For a logical, 0 = FALSE, 1 = TRUE.)

LINE #: 53
CURRENT:
display, // Type of display screen in use
color; // Color returned from GETCOLOR() function

CHANGE:
display, // Type of display screen in use
auto_incr, // Count of total number of auto incrementing fields
incr_amt, // Number to increment by
auto_flag, // Logical field to determine if auto increment was used
m_ans, // Store answer to questions
color, // Color returned from GETCOLOR() function


ù Miscellaneous Remarks, Do not add to .COD file: initialize
the needed memory variables.

LINE #: 18
CURRENT:
enum wrong_class = "Can't use FORM.GEN on non-form objects. ",
form_empty = "Form design was empty. "

CHANGE:

enum wrong_class = "Can't use FORM.GEN on non-form objects. ",
form_empty = "Form design was empty. ",
fld incr = "You have specified to many increment fields, only 1
allowed." +
" Press any key",

udf_exist = "The AutoIncr.PRG already exists, Overwrite? (Y/N)"
ù Miscellaneous Remarks, Do not add to .COD file: these will be
the error messages used.
=========================== End of changes =========================

After these changes have been made, you will need to generate a .GEN
file from this .COD file. You do this by typing at the DOS prompt:
DTC -i Autoincr.COD

Then, copy the resultant .GEN file into your dBASE system directory.
The only remaining task is to modify your screen forms and, under EDIT
OPTIONS, DEFAULT VALUE, enter ++.

For example, typing in ++15 will increment by 15. Conversely, ++-2
would decrement the field by 2.

If, during compiling, you get the error message "You have specified
too many increment fields, only 1 allowed. Press any key to
continue.", the form will compile, and everything will appear to have
completed normally but, in fact, it has not. The .FMT file will be
incomplete, and the UDF will not be written. There is no way around
this. This process is built into MODIFY SCREEN which will
automatically compile when the generation has stopped, regardless of
whether or not an error was returned.

You can also use this form for your every day editing (without
worrying about setting an index), not just appending new records.
This is because a default value will only work during APPEND or INSERT
and not in EDIT, so it will not affect any data that is already
there.

In summary, you only need one screen form and very minimal
programming. If you have the Developer's Edition, you just have to
make the .COD file changes, recompile, then you can give the template
to whoever needs it. Soon, all your friends, neighbors and relatives
will be incrementing the easy way.