Jennifer, the NEXT function will increment the autonumber column for
you. Another user will not get the same number. Here are 5 commands that
I ran against an Invoices table that demonstrate what happens. Notice
that the last posted invoice number is 52341.
-- For reference, I get the last invoice number on file.
R>sele max(invno) from invoices
max (invno
----------
52341
-- Then I get the next invoice number. As you can see, it is one more
than the last.
R>set var vInvNo = (NEXT(Invoices,InvNo))
R>show var vinvno
52342
-- If I do the NEXT() again, I get a number that is one higher, as the
system autonumber function has incremented for the last NEXT()
R>set var vInvNo = (NEXT(Invoices,InvNo))
R>show var vinvno
52343
To use this in a temp entry table, you would get the NEXT invoice
number, and use that in the PK place in the temp table. By using NEXT to
get the number, you have assigned it to the user. No one else can get
the same number, because in their temp table, the NEXT function will get
the number after the one you got. Here is one way of implementing this,
to give you an idea or two.
SET VAR vInvNo INTEGER = (NEXT(Invoices,InvNo))
CREATE TEMP TABLE InvEntry (InvNo INTE, <rest of the columns here>)
INSERT INTO InvEntry (InvNo) values (.vInvNo)
EDIT USING InvEntry
-- when you are done, there should be two buttons on the form, [Post]
and [Cancel]
IF vButton = "Post" THEN
INSERT INTO Invoices (col list here) +
SELECT InvNo, <and the rest> +
FROM InvEntry NONUM
ENDIF
-- NONUM turns off autonumbering while loading, thereby allowing loading
of a specific value for autonumber columns. The default is NUM.
I hope this helps you.
Albert
Roberts, Jennifer wrote:
Hello,
I am working with version 7.6, build 7.6.8.31102 – and I am wanting to
change the way the users currently add/edit information in this
multi-user database. I would like to have the users working in
temporary tables which would then update the permanent tables. Could
someone kindly give some advice on how they have handled primary keys
in this situation. For example, the user wants to enter a new
client. The client table has a primary key that is an auto-number.
Is it better to grab the next number from the permanent table and
reserve it or generate the key in the temporary table and then check
to see if it used? Or is there another way that I hadn’t thought of
(most likely J).
Thank you in advance and Happy Holidays!
Jennifer Roberts
Business Applications Specialist
Ohio Masonic Home
Phone: 937.525.3080
Fax: 937.505.4009
Ext: 5788
This electronic message contains information
from the Ohio Masonic Home,
its operating subsidaiaries or affiliates.
The information may be
confidential, privileged or otherwise
protected from disclosure.
The information is intended to be received
solely by [email protected],
for use as designated in the message. If you are not
the designated recipient, you are instructed not to review,
disclose, copy, distribute orotherwise use of
this transmission
or its contents. If you have received this
transmission in error, please notify the
Ohio Masonic Home immediately
at [email protected]; in addition,
please delete your record of
transmission. Sending, receiving, downloading, displaying,
printing material that is harassing, fraudulent, offensive,
intimidating, defamatory, or otherwise
unlawful or inappropriate is strictly prohibited by The Ohio Masonic Home.
