Create unique ID's in VFP

Question

I need to create unique record id's in VFP based on mailing information: zip5, address, lastname, firstname. Once created, relational tables will be loaded in SQL server 7 with the unique ID's. Any suggestinos?

Answer 1

You can use a GUID: GUID entry at FoxPro Wiki.

And here are some examples.

Easiest one using WSH...

   * VFP 7+
   oGUID = CreateObject("scriptlet.typelib") 
   cGUID = Strextract(oGUID.GUID, "{", "}" )

   * Other VFP
   oGUID = CreateObject("scriptlet.typelib")
   cGUID = substr( oGUID.GUID, 2, 36 )

Answer 2

VFP does have support for unique IDs - in that it has Primary Indexes (which can be based on multiple fields - but make sure the key length is fixed, so if your VFP table uses varchars you will need to pad the fields) and a table can also have Candidate Indexes (where the indexed field(s) must be unique, just like a primary key, but you can have multiple candidate indexes per table).

Either of these will enforce uniqueness in your fields, but generating a primary key based on zip5, address, lastname and firstname will be inefficient. The suggestion of GUIDs will work nicely, or if you have VFP8 or later you can use an Autoinc column, which is analogous to an Identity column in SQL Server.

Incidentally, unique indexes are only used for backward compatibility..

Answer 3

I created my own function for that purpose to return a unique identifier. When I have a new record I just scan the database and have the function replace the unique identifier field (I call mine UID) in the database with a new uid if the record does not have one.

FUNCTION UIDgenerator()
   LOCAL c_UID
   LOCAL c_dump

   c_UID = STRTRAN(SYS(2015),"_","") + [-]

   c_dump = STRTRAN(SYS(2015),"_","")
   c_dump = STRTRAN(SYS(2015),"_","")
   c_dump = STRTRAN(SYS(2015),"_","")

   c_UID = c_UID + STRTRAN(SYS(2015),"_","")


   RETURN c_UID

   endfunction() &&UIDGenerator

You don't have to do c_dump 3 times, but I wanted the identifiers to be a little more further apart.