VA FileMan Version 22.0 Key and Index Tutorial: Part 3, Lesson 10

Your browser or user agent does not support scripting. The JavaScript contained on this web page provides hyperlinks to display pop-up windows for additional topical information. The information contained in each window is provided in the associated HTML Title tag attribute. For user agents, your device should be able to read this content. For browsers, you should be able to display this information by simply moving your mouse across the hyperlink. Please contact the IIS Webmasters (see bottom of this web page for contact information) if you cannot read/access this information.

VA FileMan V. 22 Key and Index Tutorial
[<-- Previous Lesson]

[Next Lesson -->]

Part 3 - Keys

Lesson 10.

Print Key Definition and View Uniqueness Index

In Lesson 9, you created a primary key on the ZZINDIVIDUAL file, with key fields NAME (#.01) and SSN (#.02). In this lesson, you will print the definition of the key and look at the Uniqueness Index automatically created. You will also explore different methods for defining the fields in the key.

The "Keys Only" Data Dictionary Listing

The standard data dictionary listing in Version 22 has been modified to display the keys defined on a file or subfile. In addition, a new Keys Only type of listing has been created to display only the keys defined on a file.

Exercise 10.1. Display the Key You Created in Lesson 9

In this exercise, you will use the Keys Only data dictionary listing to take a look at the key (Key A) you defined in Lesson 9. You will also look at the Uniqueness Index VA FileMan automatically created to support the key.

Step 1.

Use VA FileMan's List File Attributes option to select the "Keys Only" listing format:

Select OPTION: DATA DICTIONARY UTILITIES
Select DATA DICTIONARY UTILITY OPTION: LIST FILE ATTRIBUTES
START WITH WHAT FILE: ZZINDIVIDUAL
     GO TO WHAT FILE: ZZINDIVIDUAL// <Enter>
     Select SUB-FILE: <Enter>
Select LISTING FORMAT: STANDARD// ??

   Choose from:
   1            STANDARD
   2            BRIEF
   3            CUSTOM-TAILORED
   4            MODIFIED STANDARD
   5            TEMPLATES ONLY
   6            GLOBAL MAP
   7            CONDENSED
   8            INDEXES ONLY
   9            KEYS ONLY

Select LISTING FORMAT: STANDARD// KEYS ONLY
DEVICE: ;80;999  <Enter>

The output is a report of all the keys defined on the ZZINDIVIDUAL test file (there is only one):

FILE #662nnn ------------ PRIMARY KEY: A (#nn) Uniqueness Index: E (#nnn) File, Field: 1) NAME (662nnn,.01) 2) SSN (662nnn,.02)
KEY LIST -- FILE #662nnn			09/26/00 PAGE 1

Step 2.

Do another data dictionary listing, but this time select the INDEXES ONLY format to view the definition of the "E" Uniqueness Index.

Select DATA DICTIONARY UTILITY OPTION NAME: LIST FILE ATTRIBUTES
  START WITH WHAT FILE: ZZINDIVIDUAL// <Enter>
      GO TO WHAT FILE: ZZINDIVIDUAL// <Enter>
      Select SUB-FILE: <Enter>
Select LISTING FORMAT NAME: STANDARD// INDEXES ONLY
DEVICE: ;80;999

INDEX AND CROSS-REFERENCE LIST -- FILE #662nnn   09/26/00 PAGE 1
----------------------------------------------------------------

File #662nnn

...

  E (#nnn)    RECORD    REGULAR    IR    LOOKUP & SORTING
       Unique for:  Key A (#nn), File #662nnn
      Short Descr:  Uniqueness Index for Key 'A' of File #662nnn
        Set Logic:  S ^DIZ(662nnn,"E",X(1),X(2),DA)=""
       Kill Logic:  K ^DIZ(662nnn,"E",X(1),X(2),DA)
       Whole Kill:  K ^DIZ(662nnn,"E")
             X(1):  NAME  (662nnn,.01)  (Subscr 1)
             X(2):  SSN  (662nnn,.02)  (Subscr 2)

...

Note:

The report includes the definition of the "E" index. The "Unique for" line indicates that this Regular compound index is the Uniqueness Index for Key A. VA FileMan created this Uniqueness Index automatically when you created the key.

Step 3.

Now, from programmer mode, use ^%G to look at the data in the "E" index, the Uniqueness Index for Key A.

>D ^%G

Global ^DIZ(662nnn,"E"
        DIZ(662nnn,"E"
^DIZ(662nnn,"E","BROOKS,SAGE",777889999,6) =
^DIZ(662nnn,"E","CURRENT,ROSEMARY",333221111,1) =
^DIZ(662nnn,"E","FROST,HAZEL",345678901,7) =
^DIZ(662nnn,"E","GEISER,JASMINE",555443333,2) =
^DIZ(662nnn,"E","LAKE,MARIGOLD",111223333,4) =
^DIZ(662nnn,"E","MODIFIED,ENTRY",123456789,14) =
^DIZ(662nnn,"E","RAINEY,GINGER","012345678",9) =
^DIZ(662nnn,"E","PUDDLES,CHERVIL",678765432,11) =
^DIZ(662nnn,"E","RIPPLE,SAFFRON",543891234,8) =
^DIZ(662nnn,"E","RIVERS,HOLLY",234567890,13) =
^DIZ(662nnn,"E","SNOW,BASIL",444996666,12) =
^DIZ(662nnn,"E","TIDE,DILL",987654321,5) =
^DIZ(662nnn,"E","WATERS,HERB",888776666,3) =
^DIZ(662nnn,"E","WELLS,PERIWINKLE",765432123,10) =

End of Exercise 10.1.

Defining the Fields in a Key

The Key Definition option gives you two ways to define fields in a key:

1.	You can enter the fields in the key in the KEY FIELDS section on the ScreenMan form, and let VA FileMan create or modify the Uniqueness Index for you. This is how you will usually define a key, and is the method you used to create Key A in Lesson 9.
2.	You can select a New-style Regular index as the Uniqueness Index for the key, and when prompted, instruct VA FileMan to make all the fields in the index key fields. The Uniqueness Index you select, however, must meet the following conditions:
		It must be a Regular, New-style cross-reference.

		It must be used for lookup and sorting; that is, it cannot have a name that starts with the letter "A".

		It cannot have any Set or Kill Conditions.

		It must consist of only field-type cross-reference values, all of which are used as subscripts; that is, it can contain no computed values.

		No subscripts can have transforms.

In the next three exercises you will see how the above two methods work in defining the key fields in Key A.

Exercise 10.2. Add DOB Field (#.03) to Primary Key A

In this exercise, you will add the DOB field to Primary Key A and have VA FileMan automatically modify the "E" Uniqueness Index to include the DOB field.

Step 1.

Select VA FileMan's KEY DEFINITION option, and edit Key A.

Select OPTION NAME: UTILITY FUNCTIONS
Select UTILITY OPTION NAME: KEY DEFINITION

MODIFY WHAT FILE: ZZINDIVIDUAL// <Enter>
Select Subfile: <Enter>

Keys defined on file #662nnn:

  A  PRIMARY KEY    Uniqueness Index: E
         Field(s):  1) NAME (#.01)
                    2) SSN (#.02)

Choose V (Verify)/E (Edit)/D (Delete)/C (Create): EDIT

Which Key do you wish to edit? A// <Enter>

Step 2.

You are now presented with a one-page ScreenMan form where you can edit the properties of the key.

Number: nn EDIT A KEY Page 1 of 1
  File: 662nnn         Name: A             Priority: PRIMARY
KEY FIELDS:

==========
  Field          Seq No.  File            Field Name
  -----          -------  ----            ----------
  .01              1      662nnn          NAME
  .02              2      662nnn          SSN
  Uniqueness Index: 662nnn               E
                Uniqueness Index for Key 'A' of File #662nnn

   Index Details...

COMMAND:                    Press <>PF1>H for help    Insert
Note: The Uniqueness Index field on the form shows you some of the information about the Uniqueness Index used by Key A; namely, the file on which the index resides, the name of the index, and the short description of the index.

Step 3.

Navigate to "Index Details…" and press Enter. A pop-up window displays more detailed information about the Uniqueness Index.

       File: 662nnn                    Root File: 662nnn
 Index Name: E                         Root Type: INDEX FILE

 Short Description: Uniqueness Index for Key 'A' of File #662nnn
  Description (wp):    (empty)

 Order...  Subscr  Length  [File,Field] Field Name
 --------  ------  ------  -----------------------
   1        1              [662nnn,.01] NAME
   2        2              [662nnn,.02] SSN
Set Logic: S ^DIZ(662nnn,"E",X(1),X(2),DA)=""
Kill Logic: K ^DIZ(662nnn,"E",X(1),X(2),DA)
Note: On this pop-up window you can edit only some of the properties of the Uniqueness Index - the Index Name, the Short Description, the Description (wp), and the subscript Length.

Step 4.

Press <PF1>C to close the pop-up window. You won't make any changes to the Uniqueness Index here.

Step 5.

Navigate to the third row in the "Field" column of the KEY FIELDS section. Enter DOB (or .03) to add the DOB field as a third field primary Key A. In the "Seq No." column for the DOB field, enter 3.

Number: nn EDIT A KEY Page 1 of 1
  File: 662nnn         Name: A             Priority: PRIMARY
KEY FIELDS:

==========
  Field          Seq No.  File            Field Name
  -----          -------  ----            ----------
  .01              1      662nnn          NAME
  .02              2      662nnn          SSN
  DOB <RET>        3 <RET>
  Uniqueness Index: 662nnn               E
                Uniqueness Index for Key 'A' of File #662nnn

   Index Details...

COMMAND:                    Press <>PF1>H for help    Insert

Step 6.

Press <PF1>E to exit the form and save changes.

Step 7.

At this point, the Uniqueness Index "E" contains only the two fields NAME and SSN. VA FileMan notices the discrepancy between this and the fact that you specified the three fields NAME, SSN, and DOB in the "KEY FIELDS" section of the form. So, VA FileMan asks you how you want to resolve this discrepancy.

The Key fields and the fields in the Uniqueness Index don't
match.

     Select one of the following:

        1   Re-Edit the Key
        2   Make Key match Uniqueness Index (also selected on
            up-arrow)
        3   Make Uniqueness Index match Key

Enter response: 3 <Enter> Make Uniqueness Index match Key

Modifying Uniqueness Index ... DONE!

Select option number 3, "Make Uniqueness Index match Key." This will make VA FileMan modify the definition of the Uniqueness Index so that it contains the three fields listed in the KEY FIELDS section of the form.

Step 8.

If prompted to delete the old and rebuild the new index, answer YES to both questions. Press Enter again to continue.

Do you want to delete the old index now? YES// <Enter>
  Removing old index ...  DONE!

Do you want to build the index now? YES// <Enter>
  Building new index ...  DONE!

Press RETURN to continue: <Enter>

Step 9.

Next, you are asked whether you want to check the integrity of the key. Since you are going to modify this key again in the next exercise, answer NO to this question.

Do want to check the integrity of this key now? NO

End of Exercise 10.2

Exercise 10.3. Make the "C" Index the Uniqueness Index for Key A

In this exercise, you will select the "C" index created in Lesson 1 as the Uniqueness Index for Key A. Then you will have VA FileMan automatically redefine the key so that the fields in that index (DOB and SSN) are the key fields.

Step 1.

Select VA FileMan's KEY DEFINITION option, and edit Key A.

Select OPTION NAME: UTILITY FUNCTIONS
Select UTILITY OPTION NAME: KEY DEFINITION

MODIFY WHAT FILE: ZZINDIVIDUAL// <Enter>
Select Subfile: <Enter>

Keys defined on file #662nnn:

  A  PRIMARY KEY    Uniqueness Index: E
         Field(s):  1) NAME (#.01)
                    2) SSN (#.02)
                    3) DOB (#.03)

Choose V (Verify)/E (Edit)/D (Delete)/C (Create): EDIT

Which Key do you wish to edit? A// <Enter>

Step 2.

On the ScreenMan form, navigate to "Index Details…" and press Enter. Notice that as a result of Exercise 10.2, VA FileMan added the DOB field to the "E" Uniqueness Index. Press <PF1>C to close the pop-up window.

Step 3.

Now, navigate to "Uniqueness Index" and enter C to select as the Uniqueness Index for Key A the "C" index you created in Lesson 1 of this tutorial. The "C" index is a compound index with two fields, DOB and SSN.

Number: nn EDIT A KEY Page 1 of 1
  File: 662nnn         Name: A             Priority: PRIMARY
KEY FIELDS:

==========
  Field          Seq No.  File            Field Name
  -----          -------  ----            ----------
  .01              1      662nnn          NAME
  .02              2      662nnn          SSN
  .03              3      662nnn          DOB
  Uniqueness Index: C <Enter>
                     This is a Regular index on the DOB and SSN fields.

   Index Details...

COMMAND:                    Press <>PF1>H for help    Insert
Note: Notice that entering "C" as the "Uniqueness Index:" property displayed a description of the index. If you'll remember, this is the Short Description you entered for the "C" index in Lesson 1 of this tutorial.

Step 4.

Press <PF1>E to exit the form and save changes.

Step 5.

Since you changed the Uniqueness Index for Key A from "E" to "C", VA FileMan asks if you no longer need the "E" index and whether you want to delete it. Answer NO. In a later step, you will again make index "E" the Uniqueness Index for Key A.

Do you want to delete the 'E' Uniqueness Index (#nnn) on File
#662nnn previously used by Key 'A' of File #662nnn? NO

Step 6.

Now VA FileMan recognizes that the fields listed in the KEY FIELDS section of the ScreenMan form (NAME, SSN, and DOB) don't match the fields defined in the "C" index (DOB and SSN), and asks you how you want to resolve this discrepancy. This time, select option 2, "Make Key match Uniqueness Index."

The Key fields and the fields in the Uniqueness Index don't
match.

     Select one of the following:

        1   Re-Edit the Key
        2   Make Key match Uniqueness Index (also selected on
            up-arrow)
        3   Make Uniqueness Index match Key

Enter response: 2 Make Key match Uniqueness Index (also selected on
            up-arrow)

  Modifying Uniqueness Index ...  DONE!
Press RETURN to continue: <Enter>

Note: Selecting option 2 makes the KEY FIELDS match the fields in the "C" Uniqueness Index, in this case, the DOB and SSN fields.

Step 7.

Answer NO when asked if you want to check the integrity of the key.

Do want to check the integrity of this key now? NO

Keys defined on file #662nnn:

  A  PRIMARY KEY    Uniqueness Index: C
         Field(s):  1) DOB (#.03)
                    2) SSN (#.02)
Note: The Uniqueness Index is now "C", and the two key fields are DOB and SSN.

End of Exercise 10.3

Exercise 10.4. Restore Key A To Its Original Definition

In this exercise you will restore the definition of Key A so that it contains the fields NAME and SSN, and uses "E" as its Uniqueness Index.

Step 1.

Select the VA FileMan's KEY DEFINITION option, and edit Key A.

Select OPTION NAME: UTILITY FUNCTIONS
Select UTILITY OPTION NAME: KEY DEFINITION

MODIFY WHAT FILE: ZZINDIVIDUAL// <Enter>
Select Subfile: <Enter>

Keys defined on file #662nnn:

  A  PRIMARY KEY    Uniqueness Index: C
         Field(s):  1) DOB (#.03)
                    2) SSN (#.02)

Choose V (Verify)/E (Edit)/D (Delete)/C (Create): EDIT

Which Key do you wish to edit? A// <Enter>

Step 2.

On the ScreenMan form, navigate to the second row in the "Field" column of the "KEY FIELDS" section and change the .03 to .01.

Step 3.

Navigate to "Uniqueness Index" and enter E. The screen should look like this:

Number: nn EDIT A KEY Page 1 of 1
  File: 662nnn         Name: A             Priority: PRIMARY
KEY FIELDS:

==========
  Field          Seq No.  File            Field Name
  -----          -------  ----            ----------
  .02              2      662nnn          SSN
  .01              1      662nnn          NAME
  Uniqueness Index: 662nnn               E
                Uniqueness Index for Key 'A' of File #662nnn

   Index Details...

COMMAND:                    Press <>PF1>H for help    Insert

Step 4.

Press <PF1>E to exit the form and save changes.

Step 5.

Answer NO when asked if you want to delete the "C" index.

Do you want to delete the 'C' Uniqueness Index (#nnn) on File
#662nnn previously used by Key 'A' of File #662nnn? NO

Step 6.

Again the KEY FIELDS (NAME and SSN) don't match the fields in the "E" Uniqueness Index (NAME, SSN, and DOB). Select option 3 to have FileMan modify the "E" index.

The Key fields and the fields in the Uniqueness Index don't match.

     Select one of the following:

        1   Re-Edit the Key
        2   Make Key match Uniqueness Index (also selected on
            up-arrow)
        3   Make Uniqueness Index match Key

Enter response: 3 Make Uniqueness Index match Key

  Modifying Uniqueness Index ...  DONE!

Step 7.

Answer YES if asked whether you want to delete the old and build the new "E" index. Press Enter to continue.

Do you want to delete the old index now? YES// <Enter>
  Removing old index ...  DONE!

Do you want to build the index now? YES// <Enter>
  Building new index ...  DONE!

Press RETURN to continue: <Enter>

Step 8.

Answer YES to check key integrity. There should be no problems.

Do want to check the integrity of this key now? YES

Checking key integrity ...  NO PROBLEMS

Press RETURN to continue: <Enter>

Keys defined on file #662nnn:
    A PRIMARY KEY Uniqueness Index: E
       Field(s): 1) NAME (#.01)
                 2) SSN (#.02)

End of Exercise 10.4

Select this link to test yourself on what you've learned in this lesson.

[<-- Previous Lesson] [Intro] [1] [2] [3] [4] [5] [6] [7] [8] [9] 10 [11] [Next Lesson -->]

Reviewed/Updated: March 20, 2007