6 Replies Latest reply on Apr 19, 2016 10:49 AM by reth6720

    Autofill from another record based on field data

    ChristopherKessler

      Title

      Autofill from another record based on field data

      Post

           I have two tables in my database, one with People information that includes a gender field, and another table for Married couples that has a unique ID for each married couple, along with fields for the Male and Female in this marriage. I have these databases related by this unique ID, but what I would like is to simply enter the Marriage ID in these databases, and then with this link have the database look up the appropriate gender to place in the respective "Male" and "Female" fields. Is this possible to do? I cannot find a way to have FileMaker perform this, either as a search, or as a lookup routine.

        • 1. Re: Autofill from another record based on field data
          philmodjunk

               My best guess is that you have these relationships:

               People-----Couples-----People 2

               Couples::_fkMaleID = People::__pkPeopleID
               Couples::_fkFemaleID = People 2::__pkPeopleID

               Is that what you have?

               But in what table would you enter such a "Marriage ID"? Would this be a third table different from the above 2?

               And if that is the case, is the data you need to appear data found in the Couples table or People?

          • 2. Re: Autofill from another record based on field data
            ChristopherKessler

                 The tables currently are the following:

                 People

                                                                                                                                                                                                                                                                                                                                                                                                 
                                Marriage ID                     Person                     Gender
                                1                     Bob                     Male
                                1                     Mary                     Female
                                2                     Joe                     Male
                                2                     Melissa                     Female

                 Marriages

                                                                                                                                                                                                                                                     
                                Marriage ID (entered value)                     Male (auto-lookup)                     Female (auto-lookup)
                                1                     Bob                     Mary
                                2                     Joe                     Melissa

                 The goal here is to have the auto-lookup values be entered from the People table, when I enter the Marriage ID in this table. I currently have the tables related by dragging "Marriage ID" from one to the "Marriage ID" of the other, so Marriage ID = Marriage ID for that relationship. I do not have a second instance of the People table related as you have in that scheme (I'm not sure why this would be needed, but I'm clearly no expert here).

            • 3. Re: Autofill from another record based on field data
              philmodjunk

                   I don't quite see how that can work for you unless there are details about your design not yet shared.

                   The problem that I see is that you don't have a Marriage ID that you can specify in People until you first create a record in Marriages in order to enter a value for Marriage ID into the Marriage ID field of the two people records for the spouses. And at that point there is nothing more to look up.

                   It would make much more sense, to me at least, if you set up a portal to People on your Marriages layout and used the portal to create or select your two People records for the spouses.

                   And there's another issue: You don't want to match records like this by name. Names are not unique and vulnerable to data entry errors. I strongly recommend that you link records by ID numbers rather than names.

              • 4. Re: Autofill from another record based on field data
                ChristopherKessler

                     How do you match by ID number? In the relationship graph you can only drag a field name from one table to a name on the other table.

                • 5. Re: Autofill from another record based on field data
                  philmodjunk

                       The field that you drag would be the field with the auto-entered ID number. You'd use an auto-entered serial number field in one table, such as Marriage ID in the Marriages table and drag from it to a corresponding Marriage ID number in the People table.

                       Linking People records by ID would work the same way. One field is the ID number field for uniquely identifying each People record and that auto-enters a serial number: __pkPeopleID and you'd link that to a number field, _fkPeopleID in a related table by your "dragging" in Manage | Database | Relationships.

                       For an examples of this and an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

                  • 6. Re: Autofill from another record based on field data
                    reth6720

                    Christopher, I might suggest a slightly alternative design for you.  I'm hoping you haven't already loaded the data.  I realize also that we are almost two years later and it might be a moot point but I just stumbled across this and thought I would offer my thoughts on the off-chance that you are in fact still looking.

                     

                    Based on what you are telling me, I think you have 2 options.

                     

                    OPTION 1 - males and females on different source lists

                    A table of males with Name and Marriage ID

                    A table of females with Name and Marriage ID

                    A table of couples with Male Name, Female Name, and Marriage ID

                    Link the Marriage ID in the Couples table to Marriage ID in the males table

                    Link the Marriage ID in the Couples table to Marriage ID in the females table

                    Go back to the Couples table and turn the Male Name Field into a calculation that = Males:Name

                    Turn the Female Name Field into a calculation that = Females:Name

                    As you enter the marriage ID on the Couples table, each of the males and females should auto-populate

                     

                    OPTION 2 - Males and females on the same source list (you can't use table view, you need to view it on a list or form layout)

                    A table of people, as you have with Name, Marriage ID, and gender

                    A Table of Marriages with just the Marriage ID

                    Link the Marriage ID in the Marriage table with the Marriage ID in the People table

                    Create a new form or list layout generated from the Marriage table.  In this layout, make sure to include:

                    Marriage ID               Portal object (2 lines), that retrieves all records from the people table that match the Marriage ID (in your case, should only be 2, so a 2-line portal is sufficient.

                     

                    I hope that helps, or at the worst is no longer relevant.  If you already have data in the people table and want to re-format it to organize it by couple, I would highly suggest the second option, but both will have the same effect.  It depends on whether you require both the males and females to be on the same list, or if separating them out is an option (which creates a cleaner relationship model if you want to do more automation with the data down the line, but is an extensive re-working if you already have them all in one list).