FileMaker FUNdamentals: Going All The Way On The Third Date
by Geoff Wells <geoff@filemakermagazine.com>
RATING: Intermediate
PLATFORM: Macintosh & Windows
VERSION: FileMaker 4/5
BONUS FILE: CALENDAR.FP5 & NOTES.FP5
We ended last month's column with a demonstration of the difference in display speed between stored and unstored calculations. <http://www.filemakermagazine.com/m/a.read/issue.55/article.4/>
Calendar{Date} is a field we have used in the past two months' examples to control the display of both a mini and a full-page calendar. In Issue 54, the mini calendar example, Calendar{Date}is a global date field that works very well for both single user and networked solutions. Each user on a network has their own local copy of Calendar{Date} and so they see the correct grid graphic for the month they select. There are very few calculations involved so the display updates immediately even on slow machines.
In order to display a full-page calendar, we used 37 calculations to figure out the date of each cell in the calendar grid. We also used these calculations as the primary keys in relationships to display any notes associated with the date. We demonstrated that by making Calendar{Date} a regular date field instead of a global, we could speed up the display quite considerably. The problem with this is, of course, in a network solution each person would see the same display.
The answer is to give each user their own copy of Calendar{Date}. The calendar display does not need to be in a separate file, it can easily be made part of the user file. When I design a secure network solution, I use a file called "Users.FP5" which stores the names and passwords of all those authorized to use the system. I also use it to store user preferences and any other items specific to each user.
A login script is used which stores the serial number of the current user in a local global. To use the full-page calendar display in such a situation you would go to the related user record (currentuser::userserial) and set the related Calendar{Date}field.
Used in this way your calendar can easily display not only notes for a particular date but also notes for a particular user on a particular date. The secret is "compound relational keys". This is somewhat similar to the technique I demonstrated in issue 52 when we used a multikey relationship to control a portal display. In the previous case we built a foreign key using terms separated by carriage returns. This resulted in a match if the primary key contained any of the terms in the multikey.
For this month's technique we concatenate the terms together to build key strings that represent the combination of factors we want to display. In our current example the foreign key of the notes database would be a concatenation of the user serial number and the date.
The most important thing to remember when using this technique is the way in which FileMaker indexes a text field.
1) The total number of characters indexed per field is 60.
2) Only the first 20 characters of each word in a field are indexed.
An example will make it clearer what is happening. If the text field contains
Abcdefghijklmnopqrstuvwxyz abcdefghijklmnopqrstuvwxyz abcdefghijklmnopqrstuvwxyz
The index will contain:
Abcdefghijklmnopqrst abcdefghijklmnopqrst abcdefghijklmnopqr
Note that the first two words end at "t" but the last word ends with "r" which is the 60th letter in this string.
Of course, your terms do not have to be 20 characters long.
1234567890 1234567890 1234567890 1234567890 1234567890 1234567890
will index as:
1234567890 1234567890 1234567890 1234567890 1234567890 12345
The spaces between the terms count as part of the 60 character limit but you must included a space (or other none indexable character) between each term if you want a key that is more than 20 characters long.
For our user/date example, we will start with last month's bonus files, Calendar.FP5 and Notes.FP5. First make sure that Calendar{Date} is defined as a regular date field and not a global and that Calendar{BlankDays}, Calendar{Col1Row1} and Calendar{DaysInMonth} have indexing turned on.
Now, we add a record serial number field and a user key. The serial number field is a standard auto increment serial number field but the user key uses the following calculation to pad a text field to the maximum 20 characters for a word.
UserSerialNumber
Number Serial Number with Current Value: "1" Increment: "1" Prevent data that is automatically entered from being changed.
UserRelKey
Calculation (Text) = Right("ABCDEFGHIJKLMNOPQRST" & UserSerialNumber, 20)
Author's Note:
Last month, we used the same set of fields for primary keys as we used for the date number display. In this example our primary key fields are going to be a concatenation of the user key and the grid date. Define each of the 37 fields using the formula:
Key{Sun1} Calculation (Text) Indexed
= UserRelKey & " " & DateToText(Calendar{DateSun1})
to
Key{Mon6} Calculation (Text) Indexed
= UserRelKey & " " & DateToText(Calendar{DateMon6})
We also need to create a new primary key for the CurrentNote relationship so that each user will see their own current notes.
Key{CalendarDate} Calculation (Text) Indexed = UserRelKey & " " & DateToText(Calendar{Date})
You will need a matching field in the Notes.FP5 file that will be used as the foreign key. Go to the "Define Fields" dialog in Notes.FP5 and add the text field "Key{UserDate}".
In the Calendar.FP5 file change each of the relationships from Sun1 to Mon6 to use the new "Key" fields. In each case the foreign key in Notes.FP5 is "Key{UserDate}". Do the same for "CurrentKey".
I have set up this month's bonus file to demonstrate how three different users, Larry, Curly and Moe, will see a different set of notes for their activities each month. In actual use you would use the user key that you stored with a login script to go to the related record in the User.FP5 file and display the calendar layout.
Compound Keys and MultiKeys
Using a compound key to find related records is much like using a scripted find, only faster.
Think of a compound key as an "AND" relationship and a multikey as an "OR" relationship. In other words if you concatenate the terms then all the terms in the primary and foreign keys must be the same otherwise the resulting strings will not match. With a multikey only one term in a list on either side of the relationship has to be the same for a match to exist.
Of course, there is nothing to stop you having compound mutikeys on both sides of the relationship and dynamically altering their contents. The only stipulation, of course, is that the foreign key must be indexed.
Ranges Plug-in
If you have the new Ranges plug-in from Troi you can do some really interesting things with compound multikeys <http://www.troi.com>. For example, you could store a time component with your calendar notes and then retrieve notes for a specific time range by using a primary multikey created by the plug-in.
In a products file you could use this technique to list all the items of a particular product type that were manufactured this week, or just the blue ones. Simply by changing the primary key.
Educators can quickly produce student reports by manipulating the terms in the primary multikey to display any subset of records with matching terms in the foreign key. All the students taught by Miss Tingle or just the ones that graduated, for example.
Some Quick Gotcha's
One thing to look out for when constructing compound keys are records that have missing data in fields you use for keys. Use auto-enter data like "Unknown" or "Undefined" to make sure the field isn't blank. You can then use this term in the primary key to display these records.
Another thing that has tripped me up from time to time is mismatched data types. Make sure all the terms for your key field are specifically cast as text.
Happy FileMaking!
If you have any ideas for topics you want me to cover in future articles, I would love to hear from you.
Geoff Wells is the author of the new shareware program MIXOLOGY available from <http://fmfiles.com/newfiles>. Search the database of close to 1500 drink recipes by name, liquor or mix. Geoff is a Senior Editor at ISO FileMaker Magazine. Email him at <geoff@filemakermagazine.com>.