Index Expression to Ignore a Word at the Beginning of a Field

If you have several company names in your database which begin with the word "The", here is a way to create an index which will skip that word when sorting the records.

From the Contact menu, select Contact Utilities, then Edit Indexes. Add a new index or modify the existing company name index, and enter the following expression:

IIF(UPPER(contact1.company)='THE',SUBSTR
(contact1.company,AT(' ',contact1.company)
+1,30), contact1.company)

Use the actual length of the field in place of the number 30.

This method can also be applied to other fields, and the expression can be expanded to operate using more than one word. For example, the following expression will ignore the prefixes "Mr." and "Ms." when sorting records:

IIF(UPPER(contact2.contact)='MR.',SUBSTR(contact2.contact,
AT(' ',contact2.contact)+1,30),IIF(UPPER(contact2.contact)=
'MS.',SUBSTR (contact2.contact,AT(' ',contact2.contact)+1,30),contact2.contact))

 

-- End of FAQ #514

FAQ ID #514

Product Related: TeleMagic 3.5x

Last Updated:
March 20, 2000