How to Add Fields to a Table Programmatically

• Microsoft FoxPro for Macintosh, versions 2.5b, 2.5c
• Microsoft FoxPro for Windows, versions 2.5, 2.5a, 2.5b, 2.6
• Microsoft FoxPro for MS-DOS, versions 2.5, 2.5a, 2.5b, 2.6

SUMMARY

This article describes how to add fields to a database through a program.

MORE INFORMATION

   ****************************************************************
   ** Program: ADDFIELD.PRG
   ** Purpose: Add fields to a table
   **
   ** Parameters:
   **  1) numfield = The number of fields to add to the table
   **
   ** NOTE: All new fields are of type character and width 25.
   **
   ** Example of Usage:
   **   DO addfield WITH 1
   ****************************************************************

   PARAMETER numfield

   * Verify that a valid parameter has been passed.
   * Store 1 to the number of fields to add if the parameter
   * is not numeric.
   IF TYPE('numfield')<>'N'
      numfield=1
   ELSE
      * End the program if number passed is not a positive whole number.
      IF numfield < 1 OR INT(numfield)<>numfield
         WAIT WINDOW "Not a valid number of fields to add"
         RETURN
      ENDIF
   ENDIF

   * Environment settings
   msafe=SET('SAFETY')
   SET SAFETY OFF
   mtalk=SET('TALK')
   SET TALK OFF

   * Prompt for a table to open if one is not open in the
   * current work area.
   IF !USED()
      dbf_file = GETFILE('DBF', 'Select a Database')
      IF EMPTY(dbf_file)
         WAIT WINDOW "No table open in the current work area" NOWAIT
         RETURN
      ENDIF
      USE (dbf_file)
   ENDIF

   * Create area containing structure of database and
   * adjust length to add or remove fields.
   dbf_alias=ALIAS()
   =AFIELDS(mystruc)
   newlen=FCOUNT()+numfield
   DIMENSION mystruc(newlen,4)

   * Add new field names, types, and widths to end of array.
   IF newlen>FCOUNT()
      newname="NEWFIELD"
      mcount=0
      FOR x=numfield TO 1 STEP -1
         DO WHILE ASCAN(mystruc,newname)<>0
            mcount=mcount+1
            newname="NEWFIELD"+ALLTRIM(STR(mcount))
         ENDDO
         mystruc(newlen-x+1,1)=newname
         mystruc(newlen-x+1,2)="C"
         mystruc(newlen-x+1,3)=25
         mystruc(newlen-x+1,4)=0
      ENDFOR
   ENDIF

   * Create array containing index tags and expressions.
   SELECT (dbf_alias)
   mcount=0
   DO WHILE !EMPTY(TAG(mcount+1))
      mcount=mcount+1
      DIMENSION mindex(mcount,2)
      mindex(mcount,1)=TAG(mcount)
      mindex(mcount,2)=SYS(14,mcount)
   ENDDO
   morder=SET('ORDER')

   * Create a cursor file from updated array and
   * add data from table.
   CREATE CURSOR temp FROM ARRAY mystruc
   APPEND FROM (dbf_alias)

   * Re-create database with new structure.
   SELECT temp
   COPY STRUCTURE EXTENDED TO temp2
   SELECT (dbf_alias)
   CREATE (DBF(dbf_alias)) FROM temp2

   * Re-create indexes, if there were any.
   IF TYPE('mindex')<>'U'
      FOR mcount2=1 TO mcount
         mexpr=mindex(mcount2,2)
         mtag=mindex(mcount2,1)
         INDEX ON &mexpr TAG &mtag
      ENDFOR
      SET ORDER TO &morder
   ENDIF

   * Restore the data and browse.
   APPEND FROM DBF('temp')
   BROWSE

   * Clean up.
   CLOSE ALL
   DELETE FILE temp2.dbf
   SET SAFETY &msafe
   SET TALK &mtalk