FileMaker Pro Table Relationships Issue

Posted on 2014-08-28
Last Modified: 2015-01-22
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.
Question by:mig1980

    Assisted Solution

    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!
    LVL 24

    Assisted Solution

    by:Will Loving
    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

    Expert Comment

    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.

    Author Comment

    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?
    LVL 24

    Accepted Solution

    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.

    Author Comment

    Thank you all for this input. It took some time but we have successfully made the transitions needed.
    LVL 24

    Expert Comment

    by:Will Loving
    Glad to help. You'll be avoiding trouble and headaches down the road by using Unique User ID (UUID) rather than manually entered ones.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Having just upgraded from Filemaker 11 to Filemaker 12 over the weekend, we thought we would add some tips for others making the same move.  In general, our installation went without incident. Please note that this is not a replacement for Chapter 5…
    As a Mac user and former AppleCare AHA & Senior Advisor, I'm constantly bombarded with questions about Macs and if they need Antivirus. This short article is my response to those questions.
    Users will learn how to set proper sequence settings, scale images, paste attributes, add transitions, fades, and music. Open up Final Cut Pro 7 and Create a new Project: Set the Sequence Settings. a) Click File > Easy Setup > Format > Apple ProRe…
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now