VA FileMan V. 22 Key and Index Tutorial
[<-- Previous Lesson]   Lesson 4 Quiz Button   [Next Lesson -->]

Part 1 - Regular Indexes


Lesson 4. Transforms on Subscripts

In this lesson you will learn about the three different transforms you can define on cross-reference values, and how they can help make your indexes more usable.


Three Types of Transforms for Cross-Reference Values

These are the three types of transforms you can perform in VA FileMan on cross-reference values used as subscripts in an index:

1. Transform for Storage: You can use Transform for Storage to transform the internal value of the field before it is stored as a subscript in the index. In the exercises in this lesson, you will create a New-style Regular "D" index on the SSN field and use the Transform for Storage to append a space to the end of the SSN before it's stored in the index. This will make all SSNs noncanonic, so that they will all sort in the ASCII collation sequence ("011223333 " sorts before "111223333 ").
2. Transform for Lookup: When VA FileMan uses an index in a lookup, it first looks up the value exactly as it was input. If it can't find the lookup value as it was originally input in the index, it performs some basic transformations on the value, as shown below:

Graphic: bullet Converts the lookup value to all uppercase,
Graphic: bullet Truncates long input to the maximum subscript length,
Graphic: bullet Performs comma-piecing, or
Converts values to internal form for the following fields: date, set of codes, pointer, and variable pointers, it converts values to internal form.
Graphic: bullet

Once this conversion has taken place, VA FileMan tries the lookup again - but this time, looking for the transformed value.

With New-style indexes, you can define your own transform that VA FileMan will perform in addition to its usual transforms. In Exercise 4.2 of this lesson, you will use the Transform for Lookup in your "D" index to strip out any hyphens from the SSN entered by the user. For example, if the user enters "123-45-6789" VA FileMan would strip the hyphens and also look for 123456789 in the index.
3. Transform for Display: If a subscript in the index has a Transform for Storage, you would probably also want to define a Transform for Display that takes the value as it is stored in the index and converts it back into a form that can be displayed to the user. In Exercise 4.2 of this lesson, you will use the Transform For Display property to take the space out of the SSN. (This is not really necessary in our case, since the extra space displayed at the end of the SSN wouldn't confuse the user. However, we'll strip it anyway for illustration purposes.)

The Transform properties are located in the "Field-Type Cross Reference Value" pop-up window, shown in the next screen capture.

Number: nn            EDIT AN INDEX              Page 2 of 2

CROSS-REFERENCE VALUES: Order... Subscr Type Length Field or Computed Expression -------- ------ ---- ------ ----------------------------

Field-Type Cross Reference Value
  Order Number: nn             Subscript Number: nn
         Field: nn                         File: nnn
    Field Name: SSN

Maximum Length:                       Collation: forwards
 Lookup Prompt:
Transform for Storage:
Transform for Lookup:
Transform for Display:

COMMAND:                    Press <>PF1>H for help    Insert


Exercise 4.1.  Create a New-Style Regular "D" Index on the SSN Field

To illustrate how and why you might use these transforms, you will create another New-style index, this time using the SSN field as the cross-reference value.

Step 1. Create a New-style Regular "D" index on the SSN field. Here's an abbreviated procedure for doing this:
1. Select UTILITY FUNCTIONS and CROSS-REFERENCE A FIELD OR FILE on the VA FileMan menu.
2. Select NEW for type of cross-reference.
3. Select the ZZINDIVIDUAL file.
4. Choose C to create a new index.
5. Enter REGULAR for type of index.
6. Enter LOOKUP & SORTING for how the index should be used.
7. Enter D for the index name.
8. On page 1 of the ScreenMan form, at the "Short Description:" field enter A simple index on the SSN field.
9. On page 2, in the "Order..." column type 1 and press Enter.
10. For "Cross-Reference Values Type of Value," enter FIELD.
11. In the pop-up window, for Field, enter SSN.
12. Press <PF1>E twice and press Enter until you return to programmer mode.

Note: Refer to Lesson 1, "Exercise 1.1. Create Your First Compound Index" for more information on creating a New-style Regular index on a field. However, don't forget to use these values.
The resulting Set and Kill Logic should look like this:
  Set Logic: S ^DIZ(662nnn,"D",$E(X,1,30),DA)=""
 Kill Logic: K ^DIZ(662nnn,"D",$E(X,1,30),DA)
 Whole Kill: K ^DIZ(662nnn,"D")
Step 2. This step and the next will illustrate two problems with the "D" index that you just created. The first is that the SSNs aren't sorted the way that you would expect. The second is that SSNs entered with hyphens are rejected.

From programmer mode, set up the input variables using the "D" index and perform an IX^DIC call. Afterward, enter two question marks (??) at the "Select ZZINDIVIDUAL SSN:" prompt to see a list of records in your test file as they are currently sorted in the "D" index.
>S DIC="^DIZ(662nnn,",DIC(0)="QEAZ",D="D"
>D IX^DIC

Select ZZINDIVIDUAL SSN: ??
 Choose from:
  111223333
  234567890
  333221111
  345678901
  444996666
  543891234
  555443333
  678765432
  765432123
  777889999
  888776666
  987654321
  012345678

  LAKE,MARIGOLD
  RIVERS,HOLLY
  CURRENT,ROSEMARY
  FROST,HAZEL
  SNOW,BASIL
  RIPPLE,SAFFRON
  GEISER,JASMINE
  PUDDLES,CHERVIL
  WELLS,PERIWINKLE
  BROOKS,SAGE
  WATERS,HERB
  TIDE,DILL
  RAINEY,GINGER

Note: The record with the SSN of 012345678 appears at the end of the list. This is because in M, all canonic numbers sort before strings of characters that are noncanonic. A canonic number is a number that contains no nonsignificant leading zeros, no plus signs (+) in the case of positive numbers, and no insignificant trailing zeros to the right of the decimal point in the case of real numbers. The SSNs 111223333 through 987654321 are canonic numbers and are ordered numerically. All of them precede 012345678 which is noncanonic.

In exercise 4.2, you will define a Transform for Storage for the SSN field to have VA FileMan store the SSNs as non-canonic strings. This will make the SSN of 012345678 appear at the beginning of the list.

Step 3. At the "Select ZZINDIVIDUAL SSN:" prompt, enter 111-22-3333:
Select ZZINDIVIDUAL SSN: 111-22-3333 <Enter> ??

Note: Since the SSNs are stored in the "D" index without hyphens, VA FileMan is unable to find the SSN in this format: 111-22-3333.

In exercise 4.2, you will define a Transform for Lookup that will allow you to look up an SSN, even if you enter it with hyphens.

End of Exercise 4.1.



Exercise 4.2.  Modify the "D" Index to Include Transforms on the SSN Subscript

In this exercise you will modify the "D" index created in Exercise 4.1 so that:

1. The list of SSNs displayed on ?? is sorted as you would expect, regardless of whether the SSN is canonic or noncanonic.
2. Input of SSNs with hyphens (-) is accepted.

To do this you will use the three Transform properties of cross-reference values:

1. The Transform for Storage to force each SSN stored in the index to be noncanonic by appending a space at the end.
2. The Transform for Lookup to remove hyphens (-) from the lookup value.
3. The Transform for Display to remove the space at the end of the SSN.


Step 1. Edit the "D" index, and on page 2 of the ScreenMan form, press Enter in the "Order…" column for Order number 1 to select the SSN cross-reference value.


Step 2. In the pop-up window, Tab to the "Transform for Storage" and enter the following M code:
S X=X_" "

(This concatenates a space at the end of each SSN.)

Step 3. At the "Transform for Lookup", enter the following M code:
S X=$TR(X,"-")

(This removes all hyphens from any SSN entered by the user.)

Step 4. At the "Transform for Display", enter the following M code:
S:$E(X,$L(X))=" " X=$E(X,1,$L(X)-1)

(This removes the space, previously concatenated in Step 2, at the end when displaying the SSN.)

Step 5. Press <PF1>E to close the pop-up window. Press <PF1>E again to save changes and exit the form. Answer YES to the following two questions:
  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!


Note: If you haven't installed Patch DI*22.0*58, you will not see this prompt and the index will be built automatically. This patch was released October 18, 2000.

Step 6. From programmer mode, set up the input variables and perform an IX^DIC call using the "D" index. Afterward, enter two question marks (??) at the "Select ZZINDIVIDUAL SSN:" prompt.
>S DIC="^DIZ(662nnn,",DIC(0)="QEAZ",D="D"
>D IX^DIC

Select ZZINDIVIDUAL SSN: ??
 Choose from:
  012345678
  111223333
  234567890
  333221111
  345678901
  444996666
  543891234
  555443333
  678765432
  765432123
  777889999
  888776666
  987654321

  RAINEY,GINGER
  LAKE,MARIGOLD
  RIVERS,HOLLY
  CURRENT,ROSEMARY
  FROST,HAZEL
  SNOW,BASIL
  RIPPLE,SAFFRON
  GEISER,JASMINE
  PUDDLES,CHERVIL
  WELLS,PERIWINKLE
  BROOKS,SAGE
  WATERS,HERB
  TIDE,DILL
Note: This time, in contrast to Exercise 4.1, SSN "012345678" is displayed first.

Step 7. At the "Select ZZINDIVIDUAL:" prompt, enter 111-22-3333:
Select ZZINDIVIDUAL: 111-22-3333 <Enter> 111223333  LAKE,MARIGOLD
Note: In contrast to Exercise 4.1, FileMan was able to find the entry you were looking for, even though you entered the SSN with hyphens, a form not found in the index.

End of Exercise 4.2.



Lesson 4 Quiz Button 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