3.1   Think relational
 
 FileMaker Pro 3.0 is a further improvement of the simple, flat database
 concept of a table.
 It supports "relational capabilities". What's this? If you knew FMPro
 2.1, it's a kind of editable, bidirectional lookup.
 A relation is a kind of static connection when a certain field of a
 record of a database matches its counterpart somewhere else. Based on
 this relation you may get the contents of other related fields to this
 matching record.
 The basic relation is from one field to another one, a one-to-one
 relation. Example: a relation from a customer database to get related
 data from an address database, based on the matching customer id.
 You may have multiple records that share the same information - this is
 a many-to-one relation. Here the concept of relations demonstrates its
 major power: you change the information only once, and immediately it
 becomes available for all other records that hold a relation to this
 information. Example: A customer ordered multiple times. His address is
 still the same.
 Some definitions on database concepts (by demey@hgins.uia.ac.be (Hendrik
 Demey)):
 * A relationship: a "virtual" link between 2 different databases (based
 on a link between 2 *identical* fields in these 2 databases), allowing
 information from database 1 (the parent) to be displayed in database 2
 (the child). The relationship is based on an exact match between two
 fields in the parent and child database. The trigger field is also
 called a "primary key". When a child database looks at information from
 its parent, it will display this "primary key". Some databases are
 constructed to display information from several "parents", in that case
 all the "primary keys" are called "foreign keys", because they uniquely
 identify information in other databases.
 * A related field: Information in a related field exists only in another
 (the parent) database, and not in the database in which this field is
 being looked at (the child). Related fields are represented by their
 name being prerended by a double colon (::related_field_name). If You
 try to export all the info of a certain record to a tab delimited file,
 starting from a database with one or more related fields, based on one
 or more relationships, the info from the related field is not exported
 to this text file, as it does not exist in this specific child database,
 but only in its related parent.
 Comment: you may include related records for export. This will include
 EVERY matching record! Example:
 Field 1, Related::Field 2 will export as
 Key1,"The first related field"
 ,"Another related field, matching to Key1, too"
 * A portal is a new kind of lay-out tool in FM3, by which You create a
 "window" inside the child database enabling You to view the related
 fields from a parent database. In a one to many relationship (e.g. a
 database of a school, with the parent a list of all pupils, the class
 they attend and their home address; the child is a database of all
 classes. Selecting a specific class will give a view of all pupil
 records attending this specific class), this portal will present a list
 view of all related records, based on a specific relationship. A
 specific portal can only be based on *only one* relationship. In a
 datase, several relationships can be created to different (or the same,
 even the same child) files, therefore, more than one different portals
 can be created. If specifically enabled, it is possible to create new
 records with new information (e.g. a new pupil entering a certain class)
 in a portal, that will be posted into the parent database. Again, if
 specifically enabled, records viewed through a portal can be deleted
 from their parent, while being looked at from the child database.
 * A lookup physically copies information from the parent database, again
 based on a specific relationships (this is different in FM3 from earlies
 versions!). Exporting a tab delimited text file from a record contains
 also the info carried over from a related file through a lookup field.
 Information in a lookup field is static. Once copied over from the
 parent database, the information inside this lookupfield stays
 unchanged, even if the content of the parent field in the parent databse
 is changed in the meantime. Only if specifically asked by a relookup
 command, or by changing the information in the trigger field (the one on
 which the relationship of this lookup is based) will a new relookup be
 performed by FM3 and the content of the lookup field be renewed/matched
 to the parent. Existing contents will get overwritten.
 * Pros and cons of related fields/portals versus lookup fields:
 - Information in a portal or related fields is dynamicaly updated, each
 time the information in the parent database is changed. Lookup fields
 are static.
 - Related databases are smaller than databases based on lookup fields.
 - Related databases present consistent information.
 - Lookups present static information.
 Take a set of invoices and the tax amount (a percentage) based on a
 related field. If You change this tax level, all invoices (included
 older ones) will represent this new value, and all invoices (including
 the older ones) will be recalculated. With a tax based on a lookup
 field, the information in the older invoices remains unchanged, and
 therefore correct. Only newly created invoices will be based on the new
 tax level. (Insofar a relookup order is not being issue!!!)
 - In a lookup field, an exact match is not necesssary; they are more
 flexible regarding the match field to copy next higher or lower matching
 values. In a relationship an exact match is an absolute necessity in
 order to obtain related records. The dialog box of the lookup definition
 contains 4 radio buttons, enabling the user to define an exact match,
 copy an item lower or higher than, or put up a dialog box with a
 message.
 * Types of relationships:
 - One-to-one: only one record corresponds to a certain trigger.
 Information based on a one-to-one relation can easily be entered in the
 same database, in one record.
 - One-to-many: one record in the parent corresponds with multiple
 records in the child (or vice versa). This kind of information is
 represented on the "layout" of an FM3 database by a related field (for a
 many-to-one) or a portal (for a one-to-many).
 - Many-to-many: several one-to-many relationships exist at the same
 moment, and between the same groups.
   
3.1.1  Many to many relations (m:n)
 
 There are multiple problems on defining many-to-many / m:n relations. FMP's solution is
 not to support them directly. However, you may use them by mapping a m:n relation
 manually down to m:1 and 1:n. For that purpose you may have to create a separate file.
 Example: There's a nice learning example with multiple teachers and pupils. A teacher may
 have different pupils, a pupil several teachers.
 Teacher database              M:N database        Pupil database
 Key, Teacher Name    Teacher key, Pupil key       Key, Name
 T1,  AAA                      T1, P1              P1,  ZZZ
 T2,  BBB                      T1, P2              P2,  ZZX
 T3,  CCC                      T2, P2              P3,  ZZY
 Now you may get every pupil taught by teacher AAA, or any teacher of pupil ZZX.
 Workaround:
 Relations within FileMaker Pro are based on indexed lines. A line is composed of all
 characters up to the end of line (in fact it's an end of paragraph, entered by <return> or the
 full, last line) with a maximum of 60 characters; from the 61st character and up is not
 used. Only text fields should hold return symbols. You may use them e.g. for a one-to-may
 key or a many-to-one key, and thus as well for a many-to-many key.
 Example:
 The teacher database may hold the record and fields
 Teacher: T1, Pupils: "P1<return>P2"
 This will relate to both pupils ZZZ (P1) and ZZX (P2)
 Within the Pupil database you may use
 Pupil: P1, Teachers: "T1<return>T2"
 Now you may find from P1 all other pupils of his class of teacher T1.
   
3.2   What are portals?
 
 Q: What are portals?
 A: Imagine them as a window to look through at datas within another
 database.
 Actually, there are some limitations:
 * In a portal, records are displayed in their order of creation only.
 * Deleting portals should be done very carefully.
 If you delete a record, and permit deletion of related records, you may
 remove data that is still used for other records.
 If you do not use deletions properly, you may delete by mistake actual
 or related records.
 A related record may contain more data than what you see within the
 portal.
 But here the Pros:
 They are fast and flexible!
 You may use them to navigate around quickly by the "Go to related
 record" script command.
 Additional note on sliding portals: From FMP 3.0v3 on fields must be
 perfectly enclosed to slide properly.
   
3.2.1  Sorting Portal Records
 
 Portals have no option to sort, but show the records in order of
 creation.
 There are various workarounds:
 - For complete databases this will force a permanent, resorted order:
 * sort the database in question in correct order,
 * create a clone,
 * import to the clone
 - For small sets of records
 1) Flag the records in the portal
 2) Sort the records in the many file (contains the portal records)
 3) Duplicate the sorted records (in creation order the duplicates are
 already sorted)
 4) Delete the flagged records
 5) View the records in the one file through the portal
 (reported by Kirk Bowman <bowman@ONRAMP.NET>)
 Example from Ted Shapiro, NY:
 ----------
 By the way, here's a not too horrible method for sorting the contents of
 a portal, aka the SORTAL (tm):
 - Many students have registered for one class.
 - Each class has a unique ID#.
 - In the CLASSES file there is a portal to view students in the related
 file ROSTERS.
 In CLASSES -- script: SORTAL Part 1
   Freeze Window
   Copy Class ID#
   Perform script (External: ROSTERS "SORTAL Part 2")
   Refresh Window
 In ROSTERS -- script: SORTAL Part 2
   Perform Script (Find & Flag Class ID)
   Perform Script (Sort StudentLastName)
   Perform Script (DupeLoop)
   Perform Script (Find Flagged & Delete)
 script: Find & Flag Class ID
   Enter Find Mode
   Paste (Select, "Class ID")
   Perform Find
   Replace (No Dialog, "Flag", "1")
 script: Sort StudentLastName
   Sort (Restore, no dialog)
 script: DupeLoop
   Go to Record/Request (First)
   Loop
    Duplicate Record/Request
    Clear (Select, "Flag")
    Go to Record/Request (Exit after last, Next)
   End Loop
 script: Find Flagged & Delete
   Perform Find (Restore) --- this is find Flag=1
   Delete All Records (No Dialog)
 ----
 Another solution is to go via a separate database. Example: database 1
 holds field A as trigger for portal B. A script finds all matching
 values in database 3 and sorts them properly. Then the script lets
 import all relevant portal data to a special database 2 that only holds
 e.g. the portal data. The portal then gets all data from this portal
 database 2 to database 1.
   
3.3   Bug & Wish List
 
 Send suggestions and bug reports to www.claris.com on their suggestion
 page.
 http://www2.claris.com/forms/suggestion.html "Suggestions & Bug reports"
 "James Fortier" <jim40er@halcyon.com> maintains a list that may be
 available on
 http://www.ecxs.com/filemaker/ftp/FixesFeaturesFolder.sea.hqx
   
3.4   Relational Examples
 
 This section will describe unusual solutions for relations to
 demonstrate the capabilities. Please forward your examples for
 inclusion!
   
3.4.1  Self Joining list
 
 In case you want to see a portal of records that match a valid search
 criterion only:
 "Now here's something else that's way cool about Self-joins.  If you
 want to generate aggregate data on only a subset of your database, you
 can do so in the following manner:
 1) Create a text field called, say, FoundSet.
 2) Create a calculation field called, say, FoundSetIndex as a
 concatenation of YourKeyValue (such as Customer ID, Client ID, or
 whatever) & FoundSet.  Make the result text.
 3) Create a self-relation on FoundSetIndex, i.e., have the same file
 point back to itself.
 4) Define your aggregate function to be dependent on the related
 (self-joined) field (this is important).
 5) Find the records you want to work with
 6) Go to the FoundSet field and replace the value in your current found
 set with something like an "F".  This makes only the current found set
 records create the self-join.
 7) Stand back and view in amazement the power of FMP3.
 PS Don't forget to clear the value in the FoundSet field before you exit
 the script."
 (from Jim Burd <burdman@cl.k12.md.us>)
 <value sensitive lists under construction, currently about 8
 alternatives to verify and classify>
   
3.5   Globals
 
 Globals are helpful im many ways.
 They may hold variables, such as
 - a predefined constant value that you use throughout your database
 This may be e.g. a user-defined delimiter for phone numbers, a certain
 key word, a signature, logo, warning etc.
 - a variable value, such as the layout number, a buffer for some
 calculation, replace, set field, copy or paste script step etc.
 Globals are not as global as you might expect. When a database is shared
 between multiple users, every user may modify the global value on his
 own, although he may not be able to save this modified value.
 Be warned that when you clone a database, the value of a global field
 will be removed as well!
 Global fields may be repeating fields. Thus you may use e.g. a
 calculation or script to extract one of multiple choices for variables
 or even pictures.
 Global fields may not get indexed. This may look obvious, but results in
 some drawbacks. A major one is visible for relations: They only may be
 built on indexed values. But as soon as a calculation includes an
 unindexed value, such as another unstored calculation or global field,
 it may not get indexed any more.
 BTW you get a warning when you build a relation on a global field
 itself, but it still is possible. A suitable example is e.g. a dummy
 relation (global field one to related field one, both contain the value
 "1") to a shared database in order to use the same - maybe global -
 information within different databases.
 Any global field within another database may be accessed by any
 relation, regardless how nonsensical or matching this relationship is.
 "did you know you can still modify globals:
  - even if the file is "locked" in the finder
  - even if the file is on a locked disk/volume
  - even if the access privileges are set to disallow edits
 The only way to prevent mods are to set the access privileges for that
 field to be read-only.
 Interestingly, the first two attempts above allow changes which don't
 get saved to disk, but the third does get saved - which could be a
 "gotcha" if you assume otherwise."
 (by Eric Scheid <ironclad@planet.net.au>)