4.1   What do I need index values for?
 
 Most operations are based not on the value typed by hand, but on an
 indexed value that was derived from the actually typed value.
 Rapid find commands, comparisons, lookups and calculations all are
 triggered on the index value.
 The index of a field can be displayed by cmd-I. It is a list of all
 indexed values, sorted in alphabetical order.
 The index can be used to select and paste previously entered values
 rapidly.
 Index also works on check boxes and radio buttons - as cut, copy and
 paste do.
 Indexing works different on various field formats.
 Indexing is not done immediately. At least after selecting the next
 record, the index should get updated properly.
 Due to conversions, bugs or system crashes, the index may get corrupted.
 In this case find and lookup won't work properly. Importing in a clone
 of the database will force the construction of a new index.
 Calculations take the indexed value for comparisons. Such "A" = "a" is
 true. If you want to use an exact comparsion, you may use the Exact
 function. Exact("å", "a") then will return false.
   
4.2   Lookups
 
 Lookups are based on the indexed values of all records. It will copy a
 value from the first, matching record that it finds.
 For FMP2.1 lookups are based on matching words:
 Example:
 Lookup File "main", Current File "main"
 Copy the contents of "Field1" into the field "Lookup_Field"
 when the value in "Field2" matches a new entry in "Field2"
 This will cause a self lookup, pasting a matching value.
 Text Field2 contains "A B" (record 1) and "B A" (record 2). Both fields
 are indexed as "A " and "B ". Creating a new record "A B" will copy
 Field1 from Record "A B" to the Lookup_Field. Creating a new record "B
 A" will copy from Record "A B", too, since the triggers "A " and "B "
 are the same.
 A lookup on "A" or "B" may match an existing record as well, since all
 words of the actual record ar matched. This may even work on incomplete
 words.
 If you want to copy from exact matching values, use the non-separating
 delimiter option-space. "B<option-space>A" gets indexed as it is. Words
 only get indexed up to 20 characters!
 Lookups will be used only after you entered new data in the trigger
 field (Field1 in example).
 A relookup on all selected records can be forced by selecting the lookup
 trigger field and choosing Relookup from the menu Edit.
 Lookups don't work based on multiple trigger fields. Concatenating
 multiple fields by a calculation and triggering on the calculation is
 possible.
 Relookups on calculations don't work by selecting the calculation field,
 but by selecting any of the fields it is based on.
 For FMP3 lookups are based on exactly matching lines. They use the same
 mechanism as used for relations - check the matching section.
   
4.3   Text formats and Index basics
 
 For an introduction, see the manual "working with Information in
 Records" (Section 4), "Using the Index" (4-9):
 - "Each word in a text field is indexed separately so you can later find
 the records that contain any word you've typed"
 - "Punctuation and special characters aren't indexed"
 - the first character of a word is converted to Upper Case
 - all other characters will get lower case
 - an additional blank character gets appended at the end of the Word
 Example: "this wordTest" is indexed as "This ", "Wordtest "
 The ordinary range for characters is [a..z], [A..Z]. The numbers [0..9]
 get treated as usual. The non-separating blank "option-space" is treated
 like an ordinary character, thus preventing the separation of words.
 Some unusual behaviors I found are (undocumented - don't rely on them):
 - Indexed words are limited to the first 20 characters! Since find
 requests and lookups are based on the index, take care of this
 limitation!
 - Text following an underscore won't get indexed for FMP2.1! "Text_A"
 and "Text_B" both get indexed as "Text ". Don't use the underscore as
 non-separating delimiter!
 - Some special 8-bit characters get translated! Most of the accented
 characters are translated to plain characters. Some special characters
 get translated to two character combinations, e.g. the German Umlaute
 ae, oe, ue and ss. The ligatures fi and fl get lost for FMP2.1!
 - Between characters &, -, / and ' are handled properly, otherwise
 ignored.
 - Between numbers ,, . and : are handled properly, otherwise ignored.
 - Other characters get ignored. All ignored characters work like a
 space, for index display, calculation, find and word delimiter.
 This behavior belongs to text fields. The text limit is 64k, with any
 number of lines. For other field types the behavior may be different, as
 described below.
 FMPro 3 takes an almost identical mechanism for words, but builds its
 index on lines with a maximum of 60 characters instead.
   
4.3.1  Character translation for indexed values
 
 Some of the following 8-bit characters might get lost while mailing.
 Messages posted to newsgroups will be converted to the ISO 8859-1
 (Latin) character set (by YA-NewsWatcher 2.0.29 or better). Messages
 mailed will get translated as Quoted Printable (MIME) (by Eudora 1.5.3
 or better). Please don't complain about messed up characters in this
 subsection.
 ====== Original and Converted Character for Index (US-Version) ======
   Ä  Å  Ç  É  Ñ  Ö  Ü  á  à  â  ä  ã  å  ç  é  è  ê  ë
   A  A  C  E  N  Oe Ue a  a  a  ae a  a  c  e  e  e  e
   í  ì  î  ï  ñ  ó  ò  ô  ö  õ  ú  ù  û  ü
   i  i  i  i  n  o  o  o  oe o  u  u  u  ue
   ß
   ss
   Æ  Ø
   Ae O
   ª  º
   a  o
   æ  ø
   oe o
   À  Ã  Õ  ‘  ¦
   A  A  O  Oe oe
   ' (upper apostrophe = ANSI 213 = 0xD5)
   '
   Þ  þ ligatures fi and fl get lost
 ======================================================================
 Modifications for FMPro 3.0:
 ====== Original and Converted Character for Index (US-Version) ======
   ' '
   ' '
   Þ  þ
   fi fl
   ======================================================================
   
4.4   Printing index fields
 
 The Index itself is not printable. Workarounds are:
 * export all text, then sort and remove the duplicates.
 "It's easy to do on an UNIX mainframe (sort -u flatfile) but takes some
 time [...] It will not return the real index but the contents of a
 field." (from Patrick Delges <pdelges@SEGI.ULG.AC.BE>)
 * export all text, open a text editor with spelling capabilities and
 create a dictionary with all words,
 * export all text, convert all blanks to Carriage Returns, thus creating
 a list of all words, import, use strategy to remove all duplicates,
 * to print the index of a particular field, create a new layout,
 selecting the columnar report option. Select the field that you wish to
 print the index of.
 Double click on the body part and make it a sub summary when sorted on
 the field in question. Now sort the database on that field and print
 from this layout.
 (from tscott@metz.une.edu.au (Timothy Scott))
 * If you want to export just the index of the field, create a new field
 of type summary. Sort the database on the field you want to export.
 Select the field you want to export and deselect the others. Now select
 your summary field just by clicking on the field name. You now get an
 extra option "Summarise By..." - click on this button, tick to the left
 hand side of the field name, click on OK and then untick the new field
 (if you want to) that FMPro wishes to export.
 (from tscott@metz.une.edu.au (Timothy Scott))
 * Consider Print2Pict to get the contents of a print as readable text
 file.
 * A final solution to extract a table of all field contents by Excel
 Did you ever try to create an index for all fields of a FMP3 database?
 Here's a quick and dirty method that I just developped by using excel
 macros (German release 4.0)
 steps required:
 export all from fmp, e.g. tab delimited
 open for Excel, option e.g. text tab delimited
 create a macro table
 make the proper macros,
 then let it go.
 The two macros required are, as pasted from Excel,
 Extract (a)
 =AUSWÄHLEN("S1")
 =ZELLEN.EINFÜGEN(1)
 =AUSWÄHLEN("Z1S1")
 =FORMEL("=Z1S2")
 =AUSWÄHLEN("Z2S1")
 =FORMEL("=WENN(GLÄTTEN(ZS(1)) = GLÄTTEN(Z(-1)S2);"""";GLÄTTEN(ZS(1)))")
 =KOPIEREN()
 =AUSWÄHLEN("Z1S2")
 =ENDE.AUSWÄHLEN(4)
 =AUSWÄHLEN("ZS(-1)")
 =AUSWÄHLEN(POSTEXT(AKTIVE.ZELLE())&":Z3S1")
 =EINFÜGEN()
 Dateiname=DATEI.ZUORDNEN(1)
 =KOPIEREN("s2")
 =NEU(1)
 =EINFÜGEN()
 Dateineu=DATEI.ZUORDNEN(1)
 =GEHEZU(Z1S2)
 =RÜCKSPRUNG()
 (hm, pasting from Excel as international macro doesn't work. Maybe
 someone does request the excel macro file itself and translate it
 properly. Here's a dictionary: select = auswählen, insert =
 zellen.einfügen, formula = formel, copy = kopieren, reftext = postext,
 cell = zelle, active = aktive, paste = einfügen, new = neu, get.document
 = datei.zuordnen. goto = gehezu, return = rücksprung, trim = glätten,
 row = Zeile, r = z, column = Spalte, c = S, activate = aktivieren, sort
 = ordnen, edit = bearbeiten, delete = löschen, if = wenn,
 datei.berechnen = calculate.document, paste.special = inhalte.einfügen)
 This macro creates a first column that computes its value as "" if it is
 the same as the previous one, otherwise the value itself, both related
 to the next, second column. IT REQUIRES THAT THE FIRST COLUMN OF THE
 TABLE HAS NO EMPTY CELLS AND AN ENTRY FOR EVERY RECORD.
 The second macro takes an unprocessed column, moves it to the second
 column, sorts, computes the values and appends them to a new table. You
 must place it as the second column of your macro table, otherwise the
 goto commands will fail.
 SortAndFilter (b)
 =AKTIVIEREN(Dateiname)
 =AUSWÄHLEN("S3")
 =KOPIEREN()
 =AUSWÄHLEN("S2")
 =EINFÜGEN()
 =ORDNEN(1;"Z1S2";1)
 =AUSWÄHLEN("S3")
 =BEARBEITEN.LÖSCHEN(1)
 =WENN(Z1S2="";RÜCKSPRUNG();)
 =DATEI.BERECHNEN()
 =AUSWÄHLEN("S1")
 =KOPIEREN()
 =AKTIVIEREN(Dateineu)
 =AUSWÄHLEN("Z1S(1)")
 =INHALTE.EINFÜGEN(3;1;WAHR;FALSCH)
 =ORDNEN(1;AKTIVE.ZELLE();2)
 =GEHEZU(Z2S2)
 =RÜCKSPRUNG()
 As you might see, this macro shall stop on the first, blank column. You
 may force a new execution of the second script. If you assigned a proper
 name and opt-cmd abbreviation (in my case it is Name "SortAndFilter" for
 Z1S2, cmd-opt-b) the simple cmd-opt-b keystroke will let it run up to
 the next blank column.
 Now, maybe someone else can help me on the "" problem - unfortunately
 this is not empty, but sorts in front of any other value. How may I
 force it to be empty? Assigning NV() is no good, skip blank lines on
 paste.special with results only does not work, and the German docs are
 incredibly poor.
   
4.5   Number format
 
 Entries may be up to 255 characters in only one line. Most characters
 are accepted, but only numbers will be recognized. Thus you can enter
 currency symbols, decimal points, percent symbols etc.
 The valid numbers are 0..9
 Leading "-" is recognized as minus
 , (comma) and . (dot) may be used as delimiters. The symbols are treated
 differently in international versions! In Germany "," is a decimal
 delimiter, "." marks thousands, in US it's just vice versa. FMPro seems
 to recognize the system settings.
 Yes and True are translated to 1
 No and False are translated to 0
 All other characters are just ignored, except blank and Š (="...") that
 work as separators.
 No additional blank is appended to the index value. Only 122 characters
 will get indexed.
 Examples:
 "1.2 DM" is "1.2" (US) or "12" (German)
 "-3"     is "-3"
 "4-5"    is "45"
 "6Š7"    is "6" and "7"
 "8 Pounds 9 Pence" is "8" and "9"
 Some less known fact is, that the internal representation will use 15
 digits only! Thus the TextToNum(of a 16 digit credit card number) will
 get rounded to 15 digits. The random function only produces 15 digit
 values.
 Counting serial numbers from
 999 999 999 999 999 will continue with
 999 999 999 999 1000 thus taking the leading part as text, increasing
 only the last part before the delimiter. This is the same usage as
 serial numbers with a leading string.
 The maximum possible serial number is "2147483647". Counting from there
 on FMP may produce "-2147483648", "--2147483647", "---2147483648".
 2147483648 equals 2^31. The possible integer range of 32 bit is
 -2147483647 ... 2147483647.
 As indicated elsewhere, 2147483647 ist the last possible number for a
 goto last record script.
 One may assume that the internal representation of numbers is either as
 32 bit integers (within the range -2^31 ... +2^31) or as 64 bit floating
 number with 54 bits mantissa (2^54 = 1.8 * 10^16, artificial limit at
 10^16 - 1), one bit sign and maximum 9 bits exponent for a 64 bits
 representation. A typical size of 32 bit CPUs for the data type floating
 double (64 bits) is 2.2 * 10^-308 to 1.7 * 10^308 (plus and minus, 15
 digits precission)
   
4.6   Date format
 
 Entries may be only one line long.
 The date format depends on the system settings. Possibilities are
 day/month/year, month/day/year and year/month/date.
 For further explanations the German format DD.MM.YYYY is used.
 Input may be any valid one to four digit number, from x.x.x to
 xx.xx.xxxx. The valid range is from 01.01.0001 to 31.12.3000.
 Any single non-numerical character may be used as delimiter.
 If the year is omitted, the actual year will be used. Taking only two
 digit years should take the actual century.
 Examples:
 "1.1.1"   is "01.01.0001"
 "29.2.94" is "29.02.94"
 "1-4+04"  is "01.04.04"
 "1-4"     is "01.04.95"
 Some more words on the actual date format: The date is stored similar to
 a numerical value. Calculations on dates will work like those on
 numerical values. Conversions are done automatically but may work
 different than you might expect! Take care and it will be very powerful.
 The date with the number 1 is "01.01.0001" although our calendar didn't
 exist at that time, but only since the Gregorian calendar was introduced
 today      728 460 is "16.06.1995"
 maximum  1 095 727 is "31.12.3000"
 next higher values might be displayed as "?" and won't get indexed, but
 e.g. 1 000 000 000 is "04.01.*0,1" as date
   
4.7   Time format
 
 Although various output formats are possible, the input is limited to
 numerical values and the colon as delimiter.
 AM/PM is available for 12-hour formats.
 Additional time information is allowed, depending on your system
 settings. Example: while typing, it was "15:48:23 Uhr", indexed as
 "15:48:23"
 Input format is hours/minutes/seconds Input may be from "x:xx" to
 "xx:xx:xx" in the valid range The valid range is between -32767..32767
 hours.
 Similar to the dates, the internal representation can be handled as
 number in seconds from time "00:00:00" (see above).
 The time with the number
 0 is "00:00:00"
 -60 is "-00:01:00"
 Numbers outside the valid range may be converted properly.
 Tip: If you needed the input format minutes/seconds, just use "HH:MM"
 instead! Most of the time this is fine, e.g. for recording the length of
 CD tracks. If you need the real time for another calculation, convert it
 by a division /60.
   
4.8   Hidden fields
 
 In order to paste values to a certain field, e.g. while using scripts,
 find dialogs etc., the field must be available on the choosen layout.
 If the field shall be available but not be displayed you may
 - assign a field size of 1 by 1 point to the field:
 select layout mode, display and edit size (recommended)
 - assign an invisible font color to the text:
 choose background color as text font
 - hide the field behind another field:
 just move another field on top of it. Fields are staggered by order of
 creation but may be moved by Arrange forward/backward
 - exclude the field from the tab order:
 edit the tab order, exlude it or move it to a late tab order
 (recommended as additional step)
 FMP3 offers a new command "Set Field" that works both on visible and
 excluded fields. Set Field also works on fields in the header or footer
 of a list layout, while those fields are not editable otherwise.
 Warning: Set Field may only enter data of the proper type, such as a
 real date to a date field. If you want to find e.g. the records within
 the last thirty days, by a find string including a ">" symbol, you have
 to use a paste command instead, such as
   Paste Result["> "&Status(CurrentDate)-30]
   
4.9   Sorted lists
 
 FMP3 offers pop up lists based on two fields. In general they get
 alphabetized by their second field. If you don't want this behavior,
 turn off indexing for the second field or, if you have to have it
 indexed, make an unindexed calculation field to show the value. (from
 Stefan Schutt <stefan@MOUSEUP.PP.FI>)