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

 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.

  * Make stop list case-insensitive.
  Temp = {TEXT}
  Convert @Lower.Case To @Upper.Case In Temp
  CALL XREF(Temp,\202E2C\,"COMPUTER":@VM:"PRINTER","2")

  * Now perform any additional filtering, resetting @ANS.
  NewList = ''
  Col = 0
  Loop
    Remove Word From @Ans At Col Setting Flag
  While Word
    Begin Case
     Case Word Matches "0N"    ;Null
     Case Len(Word) LT 4       ;Null
     Case 1                    ;NewList:= Word:@Vm
    End Case
  Repeat
  NewList[-1,1] = ''
  Transfer NewList To @Ans

(Volume 4, Issue 8, Pages 13,14)