====== QTIPS - Manipulating XREF formulae.=====

^Published By^Date^Version^Knowledge Level^Keywords^
|Sprezzatura Ltd|01 JAN 1993|3.01+|EXPERT|XREF, FORMULA, STOP LIST|

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

<code>
 CALL XREF({COLUMN}, HexDelimiters, StopList, StopMode)
</code>

(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.

<code>
  * 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
</code>

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