Created 2 files that had 1 field (text, indexed). Related these 2 files on their 1 field. Each file has 1 record. File 1 had the data in its 1 field of 111222333444555666771

The 2nd file had data in its 1 field of 111222333444555666772

Notice that the last character is different, yet FileMaker believes that these records are related. After testing, FileMaker only checks the first 20 characters of a relationship. This problem is also true for checking for duplicates as well.

Is anyone aware of this? Is this documented anywhere?

Filemaker only indexes the first 20 characters.

you have 21. Therefore the relationship is only based on what is indexed.

Hence why it thinks that they are related. Is there any reason you are using a 21 letter field for a relationship.

Thanks for the reply.

Yes, there is a reason for a having a relationship on a field that has data greater than 20 characters. In the environment where I use FileMaker, we have the need for relationships that use concatenations. Because of this <21 character indexing limitation, this severely limits our options.

I often use a 'return' separated list to form the subject of the relationship. I have numerous instance where there are lots more than 20 chars in the field - but none where there are 20+ chars before a 'return'

Looks like FMP indexes the first 20 chars before a return !

Ah Mark,

Yes. A multikey. I use these as well to group multiple contacts together in a group. Then you can go show any related records where the id exists in the multikey.

Also compound keys where you combine multiple fields for a portal filter.

yes, these are usually greater than 20 characters, just seperated by a return for each instance.

An example might be

Name & ":" Activity Type & "|" & Date & " " &

"ALL" & ":" & Activity Type & "|" & Date & " " &

"ALL:ALL|" & Date" & " "

"

Filemaker will index only 20 characters per "word," so you can insert a space after the 20th character (or some other convenient place) and get the relationship to work.

Eg:

11122233344455566677 1

11122233344455566677 2

20 characters per "word", delimited by spaces. 60 characters per "line", delimited by paragraph returns.

Kurt has it correct. Please note that should any line of a multikey exceed 60 characters that line AND all subsequent lines will fail.

Old Advance Man

quote:

---

Originally posted by Old Advance Man:

[QB]Please note that should any line of a multikey exceed 60 characters that line AND all subsequent lines will fail./QB]

---

What OAM stated is VERY important. Most people mistakenly think that all characters after 60 on a line will simply be ignored, but the reality is that indexing will actually STOP at that point.

This is one of the biggest causes of confusion in multi-line keys.

I did some testing on that as well because I use a field made of concatenated values separated by a period (that is way longer than 20 char) in a relationship....

My tests went as expected : The indexing only considered the first 20 char (in one word).

Than why does my relationship work so well ? I thought that maybe the period was considered proper word separator and therefore the indexing was considering the first 60 char of the line.

So I did more testing :

I know that : 123456789012345678901 will relate to 123456789012345678902

Will 1.2.3.4.5.6.7.8.9.0.1.2.3.4.5.6.7.8.9.0.1 relate to 1.2.3.4.5.6.7.8.9.0.1.2.3.4.5.6.7.8.9.0.2 ?

Yes it did !

If I replace the periods by spaces, it doesn't relate as expected.

So the period IS NOT a word separator !

THAN HOW COME :

Liechtenstein.36.Business.Multiple.180.30 doesn't relate to Liechtenstein.36.Business.Multiple.180.60 ?

(these are exactly the values from my real db)

-The difference is past 20 char

-The string doesn't contain any spaces

-The period is NOT a word separator

I MUST be missing the something big here, I've had a hard day and haven't had lunch yet, so be kind in your replies

Thank you all

FileMaker employs some kind of algorithm to deterine what is a "word", while "lines" are delimited by one of the various return characters, breaks, line feeds, etc.

Most likely this algorithm considers a "." within a set of Integers simply part of the word, while the same character within a set of letters a seperator.

The " " is about the only thing that is universially regarded as seperating "words".

The morale of this story is to use spaces to seperate each block of up to 20 characters in you compound keys.

Aren't you helpful today ! (M.Captkurt has been solving all my problems today !)

It makes a lot of sense ! After all a period can be used with digits to form a number which should definitely be considered a single word. The flaw was in my test !

I won't separate my compound key with spaces because it would get confusing since the first element of the compound key is a country name and certain country names contain spaces (United Kingdom...). Using the period is the next best thing and it makes it all work ! I tested my solution with long, single word countries and multiple word countries and it seems to be working well. In any case the relationship (actually part of 5 relationships of the same type) is not mission-critical, it is only used to populate conditional value lists.

Once again .... Thank you very much for your help, you just got yourself a five star rating.