SEQVALUE.CC problem

[G. P. Howell]

A routine that I wrote and maintain for a local Sheriff's office
requires sequential numbers be assigned to each document to be served
as it comes into the office. I had tried several ideas to avoid
duplicating paper numbers before finding Romain's SEQVALUE.CC code,
and thought that it was the answer to my problem.

I had to make a slight change to allow some of these papers to carry
the same paper number -- they do multiple services in the case of
Juvenile and Domestic Relations Court summons, and insist that all
must have the same number. In order to do that, I set a flag on the
form and when the SEQVALUE.CC routine runs (now called SEQMULTI.CC
with credit to Romain) after it assigns a number, it checks to see if
the same number is to be used multiple times, and continues until the
next paper requiring a new number is added.

This routine runs on a 15 computer multi user network, with a Windows
2000 file server, where the data files reside. The workstations run
Windows 98 SE.

It worked well when I made the change and installed the new version.
However, I began getting reports that it was again duplicating
numbers, but not all the time.

I am now getting reports that duplicate numbers are generated almost
daily when the two secretaries are working together to enter all the
new papers that have arrived. I suspect Windows and its buffer system
to be the root of the problem, as I have implemented everything I can
think of in my program to rapidly move the new paper number to the
database.

Any ideas on avoiding this problem, other than having only one person
at a time adding new papers? That isn't an option.

Thanks!

Geep Howell

The modified code follows:

* seqmulti.cc
* sub class of Romain Strieff's SEQVALUE.CC
* uses the FORM.MMULTI variable in BIGBOOK to determine whether
* the serial number of a paper should be incremented
* uses the form value FORM.HOWMANY to determine when the routine runs
as written or substitutes the same paper number

// BIGBOOK is the name of my program, from the Big Book that they used
// to handwrite all this information into

* modified by g p howell
* 3/14/03



*---------------------------------------------------------------------
*-- SEQVALUE.CC.: A SEQUENTIAL value Custom Class Entryfield.
*-- Programmer..: Romain Strieff (CIS: 71333,2147)
*-- Date........: 02/05/1996 Original
*-- ............: 03/08/1997 removed FIELDS bug
*-- Notes.......: Custom entryfield that will fill automatically
*-- ............: with sequential values when issuing
Form.Saverecord()
*-- ............: in the form it is placed. Used for InVoice/Order N#
*-- ............: etc where the sequence of the numbers must be
*-- ............: guaranteed.
*-- Written for.: Visual dBASE 5.5a for Windows
*-- Calls.......: None
*-- Based on....: AUTOINC.CC from Ken Mayer (CIS: 71333,1030)
*-- Remarks.....: Your form must use Form.BeginAppend() and
*-- ............: Form.SaveRecord() for the CC to work.
*-- Comment.....: This CC overrides the Forms SAVERECORD() method.

*----------------------------------------------------------------------



* CLASS SeqValue(F,N) Of Entryfield(F,N) Custom
* classname modified to add multiple number capability
CLASS SeqMulti(F,N) of Entryfield(F,N) Custom
Protect Onopen,When,Speedtip,enabled
This.Speedtip = "SeqMulti Custom Control -- You Can't Edit
Me!"
This.When = {;Return .F.}
This.Onopen = Class::SeqMultiOnopen
this.enabled =.f.

Procedure Incrementit

*this control MUST have a datalink
If Empty(This.Datalink)
?? Chr(7)
Msgbox("There is no datalink for this control!","ERROR",16)
Return .F.
Endif && Empty(This.Datalink)

*check if it already has a value
If Type("this.value")="N" .And. This.Value > 0
Return .F. && Don't do anything!
Endif && Type("this.value")="N" .And. This.Value
If Type("this.value")="C" .And. .Not. Empty(This.Value)
Return .F. && Don't do anything!
Endif && Type("this.value")="C" .And. .Not. Empty


*-- if table doesn't exist, create it:
*-- check if you don't have any _old_ dbf with the same name!!!
cAlias = Alias()
If .Not. File("SeqValue.DBF")
Select Select() && Next Work Area
*structure has been changed so don't use any old version
dbf!
Create Table "SeqValue.DBF" (Incfield Char (50),Incvalue;
Numeric(15,0))
Use SeqValue Excl &&Use Exclusive To Create Index Tag
sFields=Set("FIELDS")
set fields off

*create index tag, so that we can use one record for each
*field that needs an incrementing value
Index On Upper(Incfield) Tag Incfield
*close newly created dbf and return to the saved workarea
Use
set fields &sFields.
Select (Calias)
Endif && .Not. File("SeqMulti.DBF")


*****************************************************************
* making another change here
* on the FIRST instance of the multivalue paper we need a
* number
* but ONLY on the first one.
* use the form.howmany variable to determine which paper this
* is
****************************************************************

if form.howmany > 1 .AND. form.mmulti = .t.
* it has gone through the first save process and stored a
unique paper number
* to the database
* now we need to assign the SAME paper number to the next
one until
* the multiple paper routine runs its course

* don't use the database value
* use the same value as prior papers -- multiple paper
this.value = form.papernum
form.saverecord()
return .f.

else && do the normal routine

*-- open SeqValue.dbf, so we can increment it, then
*-- close it as soon as possible:
*create exact search expression so that we don't have to
*bother with SET EXACT
cSeek=Left(Upper(This.Datalink)+Space(50),50)

Select Select() && Next Work Area
Use SeqValue Order Incfield && Open The Table
*wait loop for multiple users the same time
Do While .Not. Flock() && Try To Flock It, If Impossible
Enddo &&Another User Was Faster
*We lock the _file_ instead of the record (RLOCK()) to avoid
*problems when 2 users would try to add a new record to
SEQMulti
*the same time for the same table field when used the first
time.
sFields=Set("FIELDS")
? sFields
set fields off

*search record with this datalink
If Seek(cSeek) &&Ok Record Exist
*increment value
Replace SeqValue->Incvalue With SeqValue->Incvalue+1 &&
Increment
Else
*it does not exist, so add a new record
Append Blank
*save datalink to this record and reset the counter
*Here's where the problem could happen if we used record
lock
*instead on file lock, 2 users might add a record the the
same
*table-field
Replace SeqValue->Incvalue With 1,Incfield With
This.Datalink
Endif && Seek(Cseek) &&Ok Record Exists

Do Case
Case Type("this.value")="N" &&It's a numeric field
*assign numeric value unchanged
This.Value = SeqValue->Incvalue
Case Type("this.value")="C" &&It's a char field
*construct the right picture clause for this field
*@L means leading zeroes
Cpicture ="@L "+Replicate("9",Len(This.Value))
*transform to chr type and lenght
This.Value= Transform(SeqValue->Incvalue,Cpicture)
Otherwise
*the user set the datalink to an illegal type field
?? Chr(7)
Msgbox("SeqMulti.CC only handles N and C datatypes!")
Endcase

* set up the form.papernum value
form.papernum = this.value

set fields &sFields.
Use
Select (cAlias)
*so we just updated the value, now save it with the rest of
*the changes that were done.
form.SaveRecord()

Return .F.

ENDIF

Procedure SeqMultionopen
*set the focus to every SeqMultirement control automatically
each
*time a navigating occurs in the form

If Type("form.SeqMulti_already_installed")="U"
*do this only once for the first SeqMultirement
*control on the form.
Form.SeqMulti_already_installed=.T.

*If the form does not have an overriden Saverecord

If(Empty(Form.SaveRecord))

Form.SaveRecord=Class::Checkauto
Else
*otherwise execute both codes
Form.AutoSaveRecord=Form.SaveRecord
*save reference to new property and

Form.SaveRecord={;class::checkauto();form.AutoSaveRecord()}

Endif && (Empty(Form.Onnavigate))
Endif && Type("form.SeqMulti_already_installed")="

Procedure Checkauto
*set focus to each SeqMulti object on the form to check
*if it has a value

*save current control to a variable
Oscontrol=Form.Activecontrol
*loop through all controls and set focus to all the
*SeqMulti controls
Ocontrol=Form.First
Do

If "SEQMULTI" = upper(Ocontrol.ClassName)
Ocontrol.incrementit()
Endif && "SeqMulti" $ Ocontrol.Name
Ocontrol=Ocontrol.Before
Until Ocontrol.Name==Form.First.Name
*set focus to the control that had it before this loop
oScontrol.Setfocus()

ENDCLASS

[Roland Wingerter]

Post by G. P. Howell
A routine that I wrote and maintain for a local Sheriff's office
requires sequential numbers be assigned to each document to be served
as it comes into the office. I had tried several ideas to avoid
duplicating paper numbers before finding Romain's SEQVALUE.CC code,
and thought that it was the answer to my problem.
I had to make a slight change to allow some of these papers to carry
the same paper number -- they do multiple services in the case of
Juvenile and Domestic Relations Court summons, and insist that all
must have the same number.

------
Have you considered keeping the seqValue unique (as it should be) and adding
a field paper_num. Depending on the circumstances, the paper_num would be
used to assign the seqValue from the current record or a duplicated value
that is "inherited" from an existing record's seqValue.

This way each record would have a unique seqValue but those belonging
together would share the same paper_num.

Roland

[Romain Strieff [dBVIPS]]

Post by Roland Wingerter
Have you considered keeping the seqValue unique (as it should be) and adding
a field paper_num. Depending on the circumstances, the paper_num would be
used to assign the seqValue from the current record or a duplicated value
that is "inherited" from an existing record's seqValue.

I second that.

[G. P. Howell]

Hadn't thought about that (obviously) but it does seem like a better
way to skin the cat. The seqvalue does remain unique, even with the
changes that I have made, but the paper number is duplicated within
the SEQMULTI routine rather than the body of the form. It would be
much cleaner to keep the duplicate number code within the form, and
leave the CC as written. Good idea, thanks.

That being said, I still can't explain the dupe numbers. The
SEQVALUE.CC function, my version or Romain's, gets and increments the
number at the time the record is saved, so it would seem that even
with two fast typists at work filling out the forms that no dupes
should occur. Yet they do. The only answer that I can come up with
is Windows keeping the information in a buffer too long before writing
it back to the SEQVALUE table. If that is a reasonable assumption, is
there a cure? If it isn't, can you point me in a different direction?
Again, my thanks for your advice.

Post by Romain Strieff [dBVIPS]

Post by Roland Wingerter
Have you considered keeping the seqValue unique (as it should be) and adding
a field paper_num. Depending on the circumstances, the paper_num would be
used to assign the seqValue from the current record or a duplicated value
that is "inherited" from an existing record's seqValue.

I second that.

[Romain Strieff [dBVIPS]]

Post by G. P. Howell
That being said, I still can't explain the dupe numbers. The
SEQVALUE.CC function, my version or Romain's, gets and increments the
number at the time the record is saved, so it would seem that even
with two fast typists at work filling out the forms that no dupes
should occur. Yet they do.

Check if all involved copies have LOCALSHARE set to true.

[G. P. Howell]

I've done that, and all are set to TRUE. I am going to spend a day
with them and watch what is happening to see if something they are
doing is causing the problem, other than being speed demons. There's
also something in one of the find routines that is causing crashes
that I can't duplicate at home, and I need to get a handle on that
too. As nearly as I can tell, the crashes are not happening while
papers are being put on the system, only when they are searching for
one by a particular date, so I don't think the two problems are
related.

Thanks again for your time and expertise. I'll see what I find after
observing, perhaps something will be obvious.

geep

Post by Romain Strieff [dBVIPS]

Post by G. P. Howell
That being said, I still can't explain the dupe numbers. The
SEQVALUE.CC function, my version or Romain's, gets and increments the
number at the time the record is saved, so it would seem that even
with two fast typists at work filling out the forms that no dupes
should occur. Yet they do.

Check if all involved copies have LOCALSHARE set to true.

[Romain Strieff [dBVIPS]]

Post by G. P. Howell
I've done that, and all are set to TRUE. I am going to spend a day
with them and watch what is happening to see if something they are
doing is causing the problem, other than being speed demons

....

Post by G. P. Howell
Thanks again for your time and expertise. I'll see what I find after
observing, perhaps something will be obvious.

Perhas somebody is swithing the machine off/ripping the plug without
properly closing the app and windows?