Jump to content

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


This topic is 7497 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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!!!

Link to comment
Share on other sites

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:


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








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:







I hope a least some of this is understandable.


Link to comment
Share on other sites

This topic is 7497 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...

Important Information

By using this site, you agree to our Terms of Use.