I’m using an ‘allow creation’ relationship to create related phone, email, and address records for contacts, using a technique I believe is sometimes called ‘magic key’ — where a global field in the contact record relates to the ID field in the child.
So, for example, the creation relationship for an address record would look like this:
Contact::id = Address::id_contact
Contact::CREATION_KEY = Address::id
CREATION_KEY is a global field. At the start of the creation process, the global field is empty. I set a value in the child record, thus creating the child, and that causes the global field to be set to the ID of the newly-created record.
It works fine.
I use the same CREATION_KEY field in multiple creation relationships — to create address records, phone records, and email records. The phone and email records are based on a single table, distinguished by a ‘type’ field that is either ‘P’ (phone) or ‘E’ (email). In total, the field is used in five separate relationships.
I just noticed that FileMaker has been duplicating my CREATION_KEY field. The contacts table now contains CREATION_KEY, CREATION_KEY_1, CREATION_KEY_2. and CREATION_KEY_3. They’ve appeared as I defined additional creation relationships.
I’ve checked using BaseElements, and none of the added versions of the field are being used in any relationships.
I’m a bit concerned that FileMaker is trying to tell me that I’m doing something wrong.
Can anyone give me any insight as to what might cause this to happen?
Two additional notes:
First, about the structure of my relationship graph:
I generally use a strict Anchor-Bouy structure, but the relationship graph is a bit complex and, because Contacts appear in multiple places, I’ve connected my base Contacts TOG to two other base TOGs.
The most recent two copies of the CREATION_KEY field (#2 and #3) appeared when I was creating a duplicate Contacts TOG (to avoid a circular relationship). However, that’s not the case with #1.
Second, here's an experiment I tried:
I deleted the extra CREATION_KEY fields and saved the change. I then deleted the most recent creation relationships (in my second Contacts TOG), and saved the changes again. I then recreated those creation relationships. This time, no additional copies of the CREATION_KEY field appeared.