In order for this site to work correctly, and for us to improve the site, we need to store a small file (called a cookie) on your computer.
By continuing to use this website, you agree to our cookies and privacy policy.
  
Home page Home page Home page Home page
Pixel
Pixel Header R1 C1 Pixel
Pixel Header R2 C1 Pixel
Pixel Header R3 C1 Pixel
Pixel

QTIPS - Manipulating XREF formulae.

Perry Howell at the Inland Revenue recently encountered problems when trying to set a large stop list for a cross reference index on a text column. Even though the index itself was defined to be case insensitive, words were only stopped if they were entered into the text in UPPER case. This fairly fundamental flaw will not be addressed in 3.03, so a short term work-round was required. (Ideally RevTI would provide two separate flags for case sensitivity, so that even in a case sensitive cross reference the stop list could still be tagged as case insensitive).

A simple solution is to create a symbolic which takes the text and converts it into upper case, and then index the symbolic instead. However, similar requirements encountered over the years have involved the elimination of numeric "words" and the exclusion of all words of less than four letters. Unless you have a Scrabble dictionary and a long, long time to enter the stop list, there is a more general technique which addresses all these requirements and many more besides.

Caveat : This technique involves editing the formula generated for the Column_XREF dictionary item. If the index is ever removed and reinstalled, any changes made to the formula will be lost so it is a good idea to keep a backup copy safe.

The standard formula generated by the system for a cross reference is of the form


0001    CALL XREF({COLUMN}, HexDelimiters, StopList, StopMode)

(see REVMEDIA passim for a full discussion of these parameters). Pre processing can be applied to the data before the call is made to avoid the case sensitivity problem. In addition, any subsequent processing can be applied to the multi-valued @ANS which is returned by the XREF call, allowing words to be excluded according to any pre-programmed general rule. The source code example given below shows the formula for a column called TEXT_XREF which implements all of the rules mentioned above.


0001    * Make stop list case-insensitive.
0002    Temp = {TEXT}
0003    Convert @Lower.Case To @Upper.Case In Temp
0004    CALL XREF(Temp,\202E2C\,"COMPUTER":@VM:"PRINTER","2")
0005  
0006    * Now perform any additional filtering, resetting @ANS.
0007    NewList = ''
0008    Col = 0
0009    Loop
0010      Remove Word From @Ans At Col Setting Flag
0011    While Word
0012      Begin Case
0013       Case Word Matches "0N"    ;Null
0014       Case Len(Word) LT 4       ;Null
0015       Case 1                    ;NewList:= Word:@Vm
0016      End Case
0017    Repeat
0018    NewList[-1,1] = ''
0019    Transfer NewList To @Ans

(Volume 4, Issue 8, Pages 13,14)
Pixel
Pixel Footer R1 C1 Pixel
Pixel
Pixel
Pixel