How does multikey relationships work?

[El_Pablo]

Hi,

I've been doing FM for a few years, but never used calculated multikeys since they don't exist in SQL database (well if they do never used them).

By multikey I understand a calculated list from different field.

What is the theory between these kind of relationships?

Thank you

[TheTominator]

In a nutshell multikeys let you use two tables instead of the three that you'd need to use in SQL databases and other databases without multikeys.

Elaboration by example:

Say you have a table of people and a table of parties that people attend. A person can attend many different parties. A party has multiple people in attendance.

In an SQL database you'd use three tables:

Table "Person" with "Person_id" key field

Table "Party" with "Party_id" key field

Table "Attendance" that uses "Person_id" and "Party_id" to document in each record that a specific person has attended a specific party.

In a FileMaker multikey you can have a single text field in the Party table called "Person_ids" that contains a return-delimited list of all of the Person_id values that attended the party.

Despite the availability of this multikey feature, many people including myself choose to use the third "link" table since it provides much more flexibility in reporting and it is easier to keep the data integrity. If you set up the link table to have its records automatically deleted when either a Person or a Party is deleted, you don't have to worry about locating and removing each deleted ID from a multikey list.

Edited March 11, 2010 by Guest

[Fenton]

It is not that hard to understand the basic principle of multi-line keys (carriage return separated). It is harder to envision it happening in your head. Basically, ANY line of the multi-line key can match. It is an "Or" match.

It is similar to a "join" table; using a single-line key to a join table, which has another key field also. You go to the join based on your original single-line key. Using only the 2nd key field in the join table, to a 3rd table, would function like a multi-line; because it could have multiple values, and they would function the same as return-separated unique instances of that 2nd key.

Normally you would use a join table for this kind of functionality. But sometimes a multi-line calculation is called for. It is especially useful when built with the List (relationship::field) function, to assemble an unstored calculation for the originating side of a relationship.

In such a case duplicates values (lines) are a somewhat irrelevant to a relational key, though a lot would slow it down.

[El_Pablo]

Ok, can I use this kind of key for look alike records lookup purpose?

Let say a table with person names (first and last) with a self-join to check if there is another person with the same name?

[TheTominator]

Ok, can I use this kind of key for look alike records lookup purpose?

Let say a table with person names (first and last) with a self-join to check if there is another person with the same name?

If you are saying that the first AND last names much match I think you are describing using a "compound key" or using multiple keys to form a relationship.

If you are looking for records that have the first name OR the last name matching, then yes, a multikey relationship could be helpful. You'd use a calculation to list the first and last name onto separate lines and use this calculated field as the match key between the two table instances.

[El_Pablo]

Thanks! I always used multiple keys before knowing the usefulness of multikeys.

Ok here is another one :

Let say I have few records with a field named "aTextField" containing these values :

abcde

abcd

abc

ab

When a user start to type "a" in a field, it shows all the record in a portal. As he types in more characters, the portal shows less and less records. Do I use multikeys? If not, what should I use?

[TheTominator]

Let say I have few records with a field named "aTextField" containing these values :

abcde

abcd

abc

ab

When a user start to type "a" in a field, it shows all the record in a portal. As he types in more characters, the portal shows less and less records. Do I use multikeys? If not, what should I use?

Yes, that is another good example of the application of a multikey.

Usually the list of

abcde

abcd

abc

ab

would be the result of a calculation e.g.

Left(theName; 5) & "¶" &

Left(theName; 4) & "¶" &

Left(theName; 3) & "¶" &

Left(theName; 2)

[El_Pablo]

Hmmm... Is there a way to create a loop instead of creating a single line for all possibilities? I guess a custom function should do the job.

[Ocean West]

This function will be your new best friend http://www.fmfunctions.com/functions_display_record.php?functionId=118

[El_Pablo]

I'll give it a try next week, I have to present a new project tomorrow...

[El_Pablo]

Hi Guys,

I have a new question related to multikeys.

I want a portal to filter out automatically when the user set values to more and more precise fields.

Using a multikey expands the number of shown record instead of restraining the number records to more precise values.

I attach an example to show what I want to do.

The example is about a house inspection system. Real Estate records are already in a table, when the user must create a new inspection record and set values to the address to inspect, the portal should show more precise records as the user fills in more fields.

forum_multikey.zip