Hi Shah,

Have a look at the "Primary Keys" article on VFP MVP Craig Berntson's
website: http://www.craigberntson.com/articles.htm.

--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy.winegarden (AT) mvps (DOT) org, www.cindywinegarden.com

"ShahJagat" <shahjagat (AT) aol (DOT) com> wrote

ShahJagat wrote:

Here's my method (I use a distributed system so plain number sequence
would get tough to prevent replication):

Have you always dreaded making record IDs? Random sequences aren't
good, and counters may pose their own problems also, and if things go
too fast the in-system counter from Fox may not increment either.

I have just been revising my 12 character record ID (a universal format
I use for distributed database work*), and have added an encoded date and
time stamp as well as a counter along with a file ID and user ID
character so not only does it uniquely id the record it tells me what
file it is for, what station made it, and the date and time, in 11
characters (actually down to 9 or even 8)!

This is from foxbase, but it most likely can be used unmodified in foxpro

Variables used:
ZNODE, a one character id of the workstation
XNUM a public variable that is a counter, it is retrieved when the
database starts up and re-stored when you exit the database system.

This is a UDF, called such as:

REPLACE RID WITH UID("B") && "A" being the database ID for our
mailing list.*

Here is the UDF code, contained in uid.prg:

parameter v

** string together the passed 'file code', node character, and
compressed current date to our string

store
v+znode+chr(48+mod(year(date()),100))+chr(48+month (date()))+chr(48+day(date()))
to v

** Add in the time, also compress to three characters

store
v+chr(48+val(substr(time(),1,2)))+chr(48+val(subst r(time(),4,2)))+chr(48+val(substr(time(),7,2)))
to v

** finally the 5 digit to 3 char. counter just to guard against very
fast record creation.

store
v+chr(xnum/6084+48)+chr(mod(xnum,6084)/78+48)+chr(mod(xnum,78)+48) to v

** increment the utility counter variable**

store iif(xnum>99999,xnum-99999,xnum+1) to xnum

return v


How the coding works:
It takes a value from 0 to 78 and translates it into ASCII value
48-126 (I picked 48 as a start because 1)I wanted printable characters,
2)to avoid any problems having the macro ampersand symbol in the string
and 3)for low numbers 1-9 you can actually see 0-9.) It could be made
for a broader range, but your mileage may vary if you stretch the range
out too far.

Dates and times are always under 60 and for big values you can use base
78 or whatever to get the characters right.

Example:
AA2<5?La02H

A - File ID (ex: mailing list)**
A - What node created record
2 - ASCII value 50 - 48 = 2, Year #2 (2002)
< - ASCII value 60 - 48 = 12, 12th month.
5 - ASCII value 53 - 48 = 5, the 5th
? - ASCII value 63 - 48 = 15, 3 PM
L - ASCII value 76 - 48 = 28, 28 Min
a - ASCII value 97 - 48 = 49, 49 Sec
0 - ASCII value 48 - 48 = 0 counter highest
2 - ASCII value 104 - 48 = 2 counter hi
H - ASCII value 72 - 48 = 24 counter lo: 0*6084+2*78+24 = 180

** I did a literal translation of my old 5 digit utility counter, this
could be brought down to a single character without any ill effects...
unless you have a system that can generate over 78 RIDs within a second.

One of the essentials besides the date/time/counter is each workstation
must have a unique ID within the RID they generate, if isn't present in
the data network then you get into problems as the times could sync and
create duplicates. This example limits the number of files and
workstations to about 90 each (ASCII-control chars.) using 2 character
station IDs would increase the number. Also if you want to be able to
sort by date/order created (without having to have a separate timestamp
field, just move the file/station ID to the end and you get a solid
creation order too!

* by identifying the files in the RID you can have skinny record delete
logs, which contain the RID, 'date deleted' and 'date the record was
last modified' (which is important, to keep from deleting records still
in use by others..)

I refined my uid function since I wrote that, I realized I didn't need a
three byte counter:

parameter v
store
v+chr(48+mod(year(date()),100))+chr(48+month(date( )))+chr(48+day(date()))
to w
store
w+chr(48+val(substr(time(),1,2)))+chr(48+val(subst r(time(),4,2)))+chr(48+val(substr(time(),7,2)))
to w
store iif(xnum>74,1,xnum+1) to xnum
store w+chr(xnum+47)+znode to w
return w


Larry
--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
HRC Child Care Resources - a non-profit agency of HRC Inc.
Serving child care needs for over 20 years in Amador and Calaveras Counties
Calaveras: (209)754-1075 - Amador: (209)223-1624 - http://www.hrcccr.org/