Key Incorrectly Relating?

[LaRetta]

Hi Everyone!

I have related two dBs on a Match.Key.

Main dB HistData (114,200 records) with cService.key (text, unstored) as:

DateToText(ActivityDate) & ClientID & StaffID & ProcedureID & NumToText(Amount)

Child dB Service (4,843 records) with cHist.Key (text, stored) as:

DateToText(DateOfService) & ClientID & StaffID & ProcedureID & NumToText(LineTotal)

My problem is that the Key is 'relating' them even when it shouldn't. Both date fields are formated as date, both dollar fields are formatted as number and the ClientID, StaffID & ProcedureID are formatted as text in both dBs!

When I place both Keys on a table in HistData, they display on the same record as:

HistData: 7/10/2002C1000144H1000073SP10003063.75

Service: 7/10/2002C1000144H1000073SP10002163.75

The 63.75 is the amount. Notice that the ProcedureID is different (SP100030 & SP100021)! The cService.Key should be blank! Does anyone know what is going on?

LaRetta

[SteveB]

You have more than 20 chars in the key. Remember FM only indexes the first 20.

[LaRetta]

Hi Steve

Oh, I had forgotten that! Thanks so much!

LaRetta

[CobaltSky]

Well actually, FileMaker indexes the first 60 characters per line. But it will only index individual words up to twenty characters.

Therefore you might consider including spaces between the components of your concatenated keys so that each 'segment' is below 20 characters in length.

FileMaker performs its key matching on word order as well as word content, so 'XXX YYY' will *not* match to 'YYY XXX' - which is the spurious concern that leads some folks to making up long key strings without spaces.

Besides, without spaces, it is quite hard-going to read or transcribe the keys. A few spaces will not only make them work correctly in FileMaker, but will make them a lot easier to look at...

[LaRetta]

Hi Ray

This is very good to know! I'm using these Match Keys to identify relationships between unrelated dBs (from a data migration). My keys need to be long to match up the unique records. I've been trying to modify my keys to keep them to 20 characters, but it just doesn't give me enough information to guarantee unique matches.

Oh, but now I'll be able to pull it off. Thank you!

LaRetta