FileMaker Pro Table Relationships Issue

Good day everyone. I was recently tasked with reviewing a FileMaker Pro 12 DB. In looking some changes that needed to be made I ran into an issue because I just remember enough about how this particular issue would be handled.

I have one layout which is used to enter in client data and there is a unique ID for the client which does link to the second layout which is a client survey. The issue is that the survey has the unique ID, first name and last name that were suppose to be linked back to the client data layout (and they are) but instead of having the auto-populate, they began entering them by hand. Now they are looking at having the unique ID auto-populate but I can't seem to figure out how to link that back to the client data layout? Any ideas?

Let me know if you need further clarification in any of these areas as I know this is a little confusing.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Just tested the effect you mention on a very simple client FileMaker 12 database and there might be a remedy.

In my client's case ID is followed by Surname and Forename in the master table and these fields are mirrored in the structure of an appropriately related sub-table showing as a portal about contacts with these same fields as CId, CSurname and CForeme shown as related to the master table followed by other unrelated fields as required (C for this case the master table has portals to four different sub-tables on the same screen and the initial letter of each table name is placed in front of the rest of the related field names in the hope that we will remember which one we are dealing with).

If you go into the sub-table and remove the automatically echoed Surname and Forename from some of the records and later put them back again the portal sub-table will not show them unless you have keyed each of them exactly as they should be (so no spelling mistakes) but, correctly spelt or otherwise, they will still be there in the sub-table. The same thing would  apply if you key additional records directly into the sub-table without careful matching to the required relationships

But if you have restored every previous record as it should be (as far as the contents of related fields are concerned) and you then proceed with the appropriately linked sub-table both the previously corrected records and the new ones will be  displayed in the portal with the related fields in the new records filled from the auto-links to the master table.

You might want to test it as described above first!
Will LovingPresidentCommented:
You have several issues so I'm going to lay out what I think you should do.

The best way to handle IDs is to have a unique user ID for each client that is completely independent of the Client's name or any other personal data. This would a Number field - perhaps called "ClientID" or "zk_ClientID" to indicate that it's the key field. Use the Auto-Enter Serial Number option and set it to Prohibit User Modification.

Add this field to the Client Layout, choose "Show All Records" and then use the "Replace Field Contents…" function to populate the ClientID serial number for all existing records. Also click the "Update Next Serial" option in the Replace dialog so that when a new Client record is added it automatically get's the next number.

Then, to update your existing Survey records to have the new ClientID, add a ClientID Number field to the Survey table (do not include any auto-enter options, it's just a number field). Put the new field on the Survey layout and then, using your existing relationship between Clients and Survey, click in the field and use the "Replace…" function to pull in the ClientID value from the Client records.

Once you have all the Client and Survey records populated with the new ClientID value, change the relationship to use that instead of the old ID.

Finally, and I think this gets to your original question, you need a mechanism to to auto-populate NEW Surveys with the Client ID rather than hand entering it. The simplest way to do this would be to have a script that creates a New Survey and links it to the client record. It might look something like this:

Set Variable [ $ClientID ; Client::ClientID ]
Go to Layout [ Survey ]
New Record/Request
Set Field [ Survey::ClientID ; $ClientID ]
Commit Records
Will Loving advised me how to keep track of related fields in his repose to an earlier query of my own as I was starting on the FileMaker learning curve.

It is good to know he is there.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

mig1980Author Commented:
Thank you all for your input. A lot of great information. To answer Will Loving's comments, there is a unique ID (ClientID) that is the primary key for the client data regardless of the first name and last name or any other information. This key is however manually entered as the project has a sequence they follow for entering these.

Would this ID serve that purpose or would you suggest a new ID field be created with the parameters mentioned?
Will LovingPresidentCommented:
I would always recommend that the a unique record ID exist that is auto-entered and that that be used as the key to relate one record to another. It's fine to have a secondary identifier that is manually entered as long as there is a mechanism to ensure that it's unique (some organizations or businesses have an established Account Code format, e.g. "SmithJack1234".

The issue is that anytime you have a record key being manually entered you significantly increase the likelihood of improper entry, duplicate keys, blank keys, etc. Go ahead and have a manually entered Client Identifier if you wish, but make sure there is a unique, auto-entered serial number for each and every Client record (and every other table as well) that the user cannot touch and that is used to relate each record to records in other tables. You can, in addition, have something like the Account Code mentioned above that is manually entered, but don't use it as the key to relate to other records.

Using manually entered keys is simply asking for trouble and sooner or later you'll need to change it and do cleanup on your data to fix issues that have arisen from that structure. I've seen it time and time again. Just use auto-entered serial numbers as the key field within the relationship graph and you'll avoid the issue. The end user never has to see the serial number but having it be immutable will avoid all kinds of trouble.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mig1980Author Commented:
Thank you all for this input. It took some time but we have successfully made the transitions needed.
Will LovingPresidentCommented:
Glad to help. You'll be avoiding trouble and headaches down the road by using Unique User ID (UUID) rather than manually entered ones.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
FileMaker Pro

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.