How perform AND (& other complex finds) with relationships?

cpbatwes · December 19, 2001

I've seen LiveOak and others suggest that the fastest way to find records in databases with many records is through relationships rather than finds. I'd love to clear up the confusion, and get answers to these questions:

1) How does Filemaker work such that relationships are faster than finds (I thought they both worked from the same index). Knowing this will help me understand the ins and outs of all this. Would be much appreciated!

2) How do I do an AND find with relationships, e.g., I'm looking for all LASTNAME=smith, FIRSTNAME=john, and STATE=NY? As far as I can tell, "go to related" only brings me to a set of records for ONE relationship. Once I do "go to related" again, doesn't it reset the found set rather than narrow it down?

3) I like how filemaker allows me to enter criteria in any field - what is the easiest way, using relationships, to mimic this ability (almost the same question as 2 above).

Thank you very much!!!

LiveOak · December 19, 2001

I don't know the technical details of how indices and relationships are implemented in FM, but they are built at a low level and are probably the most optimized code in FM. Maybe droid can comment a little on this topic.

The key to using relationships for finds is building compound keys and sometimes multi-keys. For instance, if you wish to simulate a find on First Name Last Name and State, you might build a compound key with a calculation:

Key (calculation, text, indexed, ASCII) =

FirstName & "-" & LastName & "-" & State

Which might look like:

John-Smith-NY

The reason I use separators is to prevent unintended matches (I can't think of an easy example of where the concatenated first and last names can be separated in two ways, but it will happen). The indexing is set to ASCII, otherwise the "-" is ignored.

So using a compound key, everything you want to find on must be included in the key. This creates some limitations as FM only indexes words of up to 20 characters and only 60 characters maximum on one line. You must also create some way to handle the case of finding on just one criteria (say only first name). This is usually done with a multi-key. For each entry in your people file, you might build a seven line text multi-key. A "Go to Related Record" will match if any line in the multi-key matches. To allow a related find for only one of the three criteria, I would have to build a multi-key:

For "John", "Smith", "NY" this might look like

John-Smith-NY

all-Smith-NY

John-all-NY

John-Smith-all

all-Smith-all

John-all-all

all-all-all

If you wanted to do a "find" on everyone with the first name "John", you would set the left side key to "John-all-all" and perform a "Go to Related Record(show only related records).

Clearly, if you have 50 fields you wish to find on, this isn't going to work. There are some ways to cheat the length limits. If one field has only ten static values, you can assign each a number (0-9) and use only one character is your possible 60. For multi-keys, you are also limited to 64K characters in the text key fields.

Another example, to find on a range of dates, your left side key has to contain all dates in the range. For example, to find from 12/5/2001 to 12/10/2001, your key would look like:

12/5/2001

12/6/2001

12/7/2001

12/8/2001

12/9/2001

12/10/2001

I hope a least some of this is understandable.

-bd

cpbatwes · December 19, 2001

THANK YOU!

IT will take me a few hours to digest what you just wrote. I think what you're getting at is some of the most useful and interesting stuff in filemaker. I hope you write more on this topic.

thanks.