Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Pete Sass
  Where is Pete Sass?
 Marathon, Ontario
 Canada
 Pete Sass
 To: Karben Selim Mejia
  Where is Karben Selim Mejia?
 San Pedro Sula
 Honduras
 Karben Selim Mejia
 Tags
Subject: RE: Unique reference number under multi-user wo...
Thread ID: 22134 Message ID: 22175 # Views: 30 # Ratings: 0
Version: Visual FoxPro 6 Category: General VFP Topics
Date: Friday, April 11, 2003 2:25:52 AM         
   


> > > I have an accounting application, where the Debit Note/Credit Note/Invoice numbers are taken from a table. When the user starts makeing the Debit Note a reference number from the the table lastno.dbf is alloted as Debit Note number and when he saves the Debit Note lasno.dbf field is incremented by 1. This works fine only when one user is working in the system. Now a situation has come where more than one users have to work simultaneously. There is a time gap of minimum 2 minutes for taking a new number and saving the documents - if the user gets a phone call in between, cannot say when he will save the document. During this time, if another user try to make a debit note he also makes a debit note with the same reference number.
> > >
> > > There are a lot of experienced foxite members who develop applications for multiuser environment. Could somebody explain how such situation is handled perfectly.
> > >
> > > Thanks for help.
> > >
> > > Benny
> >
> > What you could do is just assign the debit number "after" the user saves the data , in this manner , you will be sure that
> > redudancy will not happen.
> >
> > Foxpro Child
>
> IMHO this is the only way to do so, getting the number at the moment of saving the data, do not forget to lock the record when doing so though
>
> Karben Selim Mejia
>
> Life is a bad teacher, It puts you the test first, then teaches you the leasson.
> Honduras, Central America
> The very centre of the world



Hi from Pete,

Many ways to do this:
But the best way. Use a table that only has one record in it, your last number held in it.
In this case the table is called NEWID and the field that contains
the last invoice number is called NEWID

SELECT NEWID
SET REPROCESS TO 15
RLOCK('1','NEWID')
nNewid=NEWID.NEWID
xNewid=xNewid+1
REPLACE NEWID.NEWID WITH xnewid
=TABLEUPDATE()
UNLOCK IN NEWID

* --- Go on with your new record code.
SELECT INVOICE
APPEND BLANK
Thisform.txtInvoice_no.Value=xNewid

* --- After all entires are done and validations done
=TABLEUPDATE()


You can see in the simple code example above I select the table and ensure it
has SET REPROCESS 15 in the even in the first few attempts the table is being
used and locked by another user adding an invoice, this code will try 15 times
to lock the record. Since the rlock and tableupdate() take but a few milliseconds,
you should not run into any issues using this code even in a large multi-user enviroment.
In application with over 100 users, I SET REPROCESS 25.

As well, if you are coding an accounting system that requires unique ID's for
Invoices, PO's, Transactions; you can use 3 records in this table. Record
1 for Invoices, 2 for PO's, and record 3 for Transactions, etc.
In this case you may wish two fields in this table.
Field1 - Contains
====
Invoice
PO
Trans

Fields2
=====
Holding the last number

If this is the case have an index key on Field1 and lets say you are performing a new
PO entry.

So code like this:

SELECT NEWID
SEEK "PO"
nrec=RECNO()
nrec=STR(nrec,1,0)
SET REPROCESS TO 15
RLOCK(nrec,'NEWID')
nNewid=NEWID.NEWID
nNewid=nNewid+1
REPLACE NEWID.NEWID WITH nNewid
=TABLEUPDATE()
UNLOCK IN NEWID

* --- Go on with your PO code
SELECT PO
APPEND BLANK
Thisform.txtPO.Value=xNewid

* --- After all entires are done and validations done
=TABLEUPDATE()

You should also take a look at the examples that come with VFP 6.0, VFP 7.0, and VFP 8.0
that uses a function call in the database container to add a new number each time you add
a new record into the table. The mechanics if you look through the code is similar to the
basic code I am showing you above. "I want you to understand the basic mechanics !"

Hope this helps, but wanted to show you this code so you understand the basic mechanics.
THis ensures you have a unique number for sure. If you provide a =TABLEREVERT() then
the number incremented will be lost and never used again.

As a hint, never allow any user to edit, or change a unique field that ties Parent->Child records
together. So in the cases above; the Invoice Number, the PO Number, or Transaction Number will
be readonly in the textbox that displays the number.

Oh, by the way in your Main.prg setup program have MULTILOCKS ON as a basic system default.


Your question "What you could do is just assign the debit number "after" the user saves the data ,
in this manner , you will be sure that redudancy will not happen."
You could place this type of code as well in the Save button, but the user will not see the new number
until after the Save takes place. If this is not an issue then put it inside the Save button. Still use this
code, as it 100% ensures that a duplicate will not happen due to the record locking logic that takes
place.

Pete from the Great White North. (Only in Canada, ay.) Over and Out ...


COMPLETE THREAD

Unique reference number under multi-user working Posted by Benny Thomas @ 4/10/2003 9:05:26 AM
RE: Unique reference number under multi-user wo... Posted by Caij Gomez @ 4/10/2003 9:17:00 AM
RE: Unique reference number under multi-user wo... Posted by Sazabi Ho @ 4/10/2003 10:30:49 AM
RE: Unique reference number under multi-user wo... Posted by Karben Selim Mejia @ 4/10/2003 3:52:00 PM
RE: Unique reference number under multi-user wo... Posted by Pete Sass @ 4/11/2003 2:25:52 AM
Great! but how to handle "Cancel" Posted by Ellick Yeung @ 4/12/2003 5:59:29 AM
RE: Great! but how to handle "Cancel" Posted by Pete Sass @ 4/13/2003 5:48:11 AM
Thanks! Got it. Posted by Ellick Yeung @ 4/14/2003 9:53:02 AM