Incrementing IDs--Revisited

The Solutions column of November 7, 1995, presented a FoxPro function to increment a numeric value stored as a character string. But if you're storing a unique value in a character string, why use only numbers? A 5-character string, in that case, yields only 99,999 unique numbers, but if you include the whole range of printable characters from ASCII 33 to ASCII 126, you can generate 945 (7,339,040,224) unique values.

That's enough unique values to generate a new one every second for over 200 years! And since they're all printable characters, you don't have to worry about side effects from viewing the contents in FoxPro's BROWSE windows. Figure 4 shows a function to increment IDs to the next unique value using printable ASCII characters.

    Mike Rothaus, Conshohocken, Pennsylvania

PC MAGAZINE: This is an interesting approach to generating unique IDs. The resulting values are not necessarily pleasing to look at, because they include characters like !, $, and ~, for instance, but they are unique and you can generate a lot of values in a lot less space than you'd need for strictly numeric characters. The values so generated can serve a useful purpose as primary key values in a FoxPro table, especially since FoxPro lacks any type of AutoIncrement counter type (even the newer and more capable Visual FoxPro lacks the AutoIncrement type).

You might be wondering why you'd place values that read like gibberish in your table as primary keys, but in truth the only requirement for a primary key is that it uniquely identify a row. It doesn't have to--and probably shouldn't--have any real meaning or "describe" the row. That's because it would then be more likely to be subject to change, and primary key values should never change. All the primary key needs to do is uniquely identify a row so that its primary key value can be placed in related rows in other tables.

I modified Mr. Rothaus's routine so that it returns a string of error characters when the pool of available IDs becomes exhausted. You can download the function (Idincr.prg) from PC Magazine Online.

--Sal Ricciardi

FIGURE: Use this function to increment a character ID to the next value using any of the printable ASCII characters.

    **************************************************************
    * FUNCTION Idincr( cId )
    * Increment a character string to the next unique ID using
    * the printable characters from chr(33) - chr(126).
    * Syntax:  cNewId = Idincr( cId )
    **************************************************************
    FUNCTION Idincr
    PARAMETER cId

    #DEFINE LoChar 33
    #DEFINE HiChar 126
    #DEFINE Errchar 27

    PRIVATE ReturnID, Cpos, CurDigit

    m.nIdlen = LEN(m.cID)   && Save the length of the id
    IF EMPTY(m.cID)
      * Return the beginning ID (just like starting at 1).
      m.ReturnID = REPLICATE( CHR(LoChar), m.nIdlen )
    ELSE
      * Start at the beginning, incrementing the first character.
      * If the first character is equal to HiChar, reset it to
      * LoChar and continue to the second character.  Continue
      * in this fashion until a character is not equal to HiChar (or
      * in the most unusual circumstance, every character in cID is
      * equal to HiChar and is to be replaced with LoChar, essentially
      * exhausting the pool of IDs, in which case we return a string
      * of Errchars.)
      m.ReturnID = m.cID
      m.Cpos = 1
      DO WHILE .T.
        m.CurDigit = ASC( SUBSTR(m.ReturnID,m.Cpos,1) )
        IF m.CurDigit < HiChar
          m.ReturnID = STUFF( m.ReturnID, m.Cpos, 1, CHR(m.CurDigit+1) )
          EXIT
        ELSE
          m.ReturnID = STUFF( m.ReturnID, m.Cpos, 1, CHR(LoChar) )
          m.Cpos = m.Cpos + 1
          IF m.Cpos > m.nIDlen
             m.ReturnID = REPLICATE( CHR(Errchar), m.nIDlen )
             EXIT
          ENDIF
        ENDIF
      ENDDO
    ENDIF
    RETURN m.ReturnID

    #UNDEF LoChar
    #UNDEF HiChar
    #UNDEF Errchar

Copyright (c) 1996 Ziff-Davis Publishing Company