How to make an auto-incrementing index 
Author Message
 How to make an auto-incrementing index

For something like customer records, how do we make a field
automatically increment to keep track of customer id's?  That way we can
use the field to index on.

--
Chris Chen



Wed, 18 Mar 1998 03:00:00 GMT  
 How to make an auto-incrementing index


Quote:
> For something like customer records, how do we make a field
> automatically increment to keep track of customer id's?  That way we can
> use the field to index on.

> --
> Chris Chen


if i understand you correctly try setting order to index(customer serial no)
go bottom
m.ref = ref+ 1
append blank
etc etc

RICH  <*********** Relax Be happy **************>


                             *********************
                             * BAMBER BRIDGE     *
                             * PRESTON           *
                             * LANCS             *
                             * Tel 01772 316632  *
                             *********************



Thu, 19 Mar 1998 03:00:00 GMT  
 How to make an auto-incrementing index

BP>ncrementing index
BP>Newsgroups: comp.databases.xbase.fox
BP>Path:
BP>news.crso.com!news1.fonorola.net!fonorola!inline!wire!nic.wat.hookup.
BP>net
noc.tor.hookup.net!hookup!usenet.eel.ufl.edu!news.mathworks.com!uhog
BP>For something like customer records, how do we make a field
BP>automatically increment to keep track of customer id's?  That way we
BP>can use the field to index on.

BP>--
BP>Chris Chen

I use a separate table 'Custid' with one field 'CustId' that contains
the last number used.  When I add a customer the number is incremented
and the new value is stored and used as the new customer's id.

use custid
m.custId=custid+1
replace custid with m.custid

For a mutli user system lock the record during the incrementing
procedure.




Wed, 25 Mar 1998 03:00:00 GMT  
 How to make an auto-incrementing index

Quote:
>   BP>For something like customer records, how do we make a field
>   BP>automatically increment to keep track of customer id's?  That way we
>   BP>can use the field to index on.

>   BP>--
>   BP>Chris Chen

>   I use a separate table 'Custid' with one field 'CustId' that contains
>   the last number used.  When I add a customer the number is incremented
>   and the new value is stored and used as the new customer's id.

>   use custid
>   m.custId=custid+1
>   replace custid with m.custid

        Why do you waste time to open/close additional file ?
    USE customer
    CALCULATE MAX(custid) to k
    k=k+1

        SQL's SELECT instead of CALCULATE can be used.

        T.J.



Sun, 29 Mar 1998 03:00:00 GMT  
 How to make an auto-incrementing index

Quote:
> >   replace custid with m.custid
>         Why do you waste time to open/close additional file ?
>     USE customer
>     CALCULATE MAX(custid) to k
>     k=k+1
>         SQL's SELECT instead of CALCULATE can be used.
>         T.J.

Theres potential trouble there in that sometimes customers can be
deleted for various reasons, (close of account) etc. giving a wrong
last number.


Tue, 31 Mar 1998 03:00:00 GMT  
 How to make an auto-incrementing index
J.T.    
        I strongly recommend the use of an "SYSTEM.DBF" type file to record
the last use ID for three reasons:
        1>  If you delete users, you run the potential of reusing a number.
        2>  If you index gets "OFF", then you run the potential of duplicate
numbers.
        3>  Your method will not work in with networks because one user could
incriment the customer ID and other could request the next ID before
the first user has saved their new ID to the customer file.  

Karl S.
Boulder Software

Quote:

>>   BP>For something like customer records, how do we make a field
>>   BP>automatically increment to keep track of customer id's?  That way we
>>   BP>can use the field to index on.

>>   BP>--
>>   BP>Chris Chen

>>   I use a separate table 'Custid' with one field 'CustId' that contains
>>   the last number used.  When I add a customer the number is incremented
>>   and the new value is stored and used as the new customer's id.

>>   use custid
>>   m.custId=custid+1
>>   replace custid with m.custid
>        Why do you waste time to open/close additional file ?
>    USE customer
>    CALCULATE MAX(custid) to k
>    k=k+1
>        SQL's SELECT instead of CALCULATE can be used.
>        T.J.



Tue, 31 Mar 1998 03:00:00 GMT  
 How to make an auto-incrementing index

Quote:

>I have run into the same problem. J.T. is correct. My application must
>generate a unique customer number with 50 users accessing the database. I use
>the method of a single field database with a single field customerno. When a
>user needs to generate a customer number I take customerno and add 1, but to
>ensure uniqueness I have this nested into a do while seek(m.customerno) so
>that it will continue to generate customer numbers untill it is one that is
>not found on an existing record.
>It's not an easy Problem!!

You can avoid the whole SEEK(...) logic by placing a Record Lock on
the single field database *before* obtaining the next number, update
the number, then release the lock.  Only one user can get a record
lock at a time.
-j

Joe Holland
Apogee Information Systems, Inc
5 Mount Royal Ave
Marlboro, MA  01752
Voice:  508/481-1400
FAX:    508/481-3343



Mon, 13 Apr 1998 03:00:00 GMT  
 How to make an auto-incrementing index

Quote:

>I have run into the same problem. J.T. is correct. My application must
>generate a unique customer number with 50 users accessing the database. I use
>the method of a single field database with a single field customerno. When a
>user needs to generate a customer number I take customerno and add 1, but to
>ensure uniqueness I have this nested into a do while seek(m.customerno) so
>that it will continue to generate customer numbers untill it is one that is
>not found on an existing record.
>It's not an easy Problem!!

No, its not.  My solution was to come up with a routine that would
pretty much get a unique number each time.  I use the customers zip
(required field) + DTOS of the current date + the current time (to
1000th of a sec) as the ID.  This gives a rather long ID, but it is SO
unlikely that it would EVER give a duplicate ID, that I consider it to
be just about foolproof.

**************************************************************
* Cy Welch                              All views are my own *




* http://www.calweb.com/~cwelch/    Web Page                 *
**************************************************************



Mon, 13 Apr 1998 03:00:00 GMT  
 How to make an auto-incrementing index
Just a Thought.  I would approch it from a little different angle.  

1.  Create a field in a system database that contains the current customer
number and a local var to the AR that defines is the AssignCustNo function
is currently in use.

2.  Then when you need a customer number:
      A.  Check if the active flag is InActive
      B.  Set Active Flag to Active
      C.  Increment the number and write it back
      D.  Set the Active Flag to Inactive

This would require a user to wait for the previous user to finish before
they could get a new customer number but the whole process should only
take a very short time.  It would probably be best if it were just a
function call.  It would also make a nice little object for VFP.

Hope this helps some where:-)



Wed, 15 Apr 1998 03:00:00 GMT  
 
 [ 10 post ] 

 Relevant Pages 

1. Auto Increment Field

2. Auto Increment

3. Auto-incrementing a primary key field

4. Auto Increment Field

5. Auto-Incrementing

6. Auto Incrementing Primary Key

7. Auto Increment Field

8. Auto-Incremented field

9. Auto-increment Field in Foxpro

10. auto-incrementing a primary key

11. auto increment a field

12. VFP5 and "auto increment"

 

 
Powered by phpBB® Forum Software