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

VFP (like prior versions of FoxPro) has no real built-in support for unique IDs. (Even unique indexes don't help, as all that means is that a duplicate row won't be added to the index; it still goes into the database, though, just not the index.)

My suggestion would be not to create them yourself. Let SQL Server create one when the data is imported using an IDENTITY column. You can insert a row of the parent (main) data, and then append the child data associated with that parent data. That handles everything for you consistently, and you don't have to worry about any conflicts if you use SCOPE_IDENTITY() for the child table inserts.

Answer 2

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 3

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..