Relating not working on imported records

[bdalton]

I have imported records from an older Filemaker database into our current Contacts database. The current DB has separate tables for clients (companies) and contacts (individuals) and these tables are related via the client name field. For some reason none of the imported records are recognizing the relationship. This DB is a modified version of one of the templates that comes with FMPro. There is a field named zk_F_clientID.t that is used to calculate a value used in the relationship (I'm not quite sure how it works) but it appears to not be working on the imported records. Thanks in advance for any help you can provide

[Fitch]

Relating on the name field is a Bad Idea. But is it actually doing that, or is it using the mysterious "zk_F_clientID.t" field? What exactly is on each side of the relationship? And what is the calculation of the "zk" field?

[Fenton]

From my interpretation of their naming convention, I would suspect that "zk_F_clientID.t" is not a calculation field. It's:

z = just to sort it to the bottom

k = key

F = foreign key

clientID = the value of the clientID (duh :-)

.t = it's text

So, the two questions are:

Does it have the value of the Client's ID? If not, why not?

Is the relationship really on the name (not good, as Fitch says)? Or is it really on the ClientID? (good, but needs the value passed to the child table).

Seeing how it is a FileMaker template one hesitates to say it must be using the ClientID,* but, since it has the ID field, so named, it's likely using it. So who created the "name" relationship, FileMaker or bdalton?

*FileMaker often uses the name in a "similars" self-relationship; but that is not the same thing, more like fluff.

[bdalton]

OK, I think your comments have rooted out the problem. More than likely the imported records are not creating the "invisible" clientID that is needed to complete the relationship. I am attaching a copy of the DB with one old record and one new (non-working) record. Can someone please explain how the clientID calculation works (ie what's the purpose) and why relating the name fields is a bad approach. I am a complete rookie at the programming side of FileMaker.

I also just found a Contact DB I like better from SeedCode but it is even more complex in its relationship setup and importing from my existing DB will not work without a lot of rework after the fact.

OfficeRolodex.zip

[Fitch]

The "zk_F_clientID.t" field is as Fenton described it: the key field that establishes the relationship between the client and the contact.

It has an auto-enter option that says:

"Do I contain the client name? If so, replace me with the client ID."

I could see using that as a one-time fix for imported data, but after that it should be turned off. I don't see any other purpose for it.

I would suggest that you re-import your contact data. Be sure to match up the fields so that the CLIENT ID is imported into the "zk_F_clientID.t" field.

When you import it will ask if you want to perform auto-enter options. Say NO.

Your ID numbers in this database are being created with a timestamp/random number calculation, so you don't have to worry about them getting out of sync when you import (i.e. you don't have to set the next serial number in the auto-enter field options).

---

I don't understand your database. How are you supposed to create a new contact from a client? I don't see a button or anything to do it. It seems like the design is incomplete.

---

Why is relating based on name a bad Bad BAD idea? Well, what if two companies have the same name? What if someone types a name in incorrectly? When you correct it, the relationship will break. Or a name changes, so when you change it... breaks the relationship.

In general you should NEVER use a user-modifiable field to establish relationships.