1 2 Previous Next 17 Replies Latest reply on Jul 9, 2014 10:44 PM by laser

    How to display data from another record within a related record

    laser

      Hi-

       

      I'm still new to Filemaker and have run into an issue I am hoping I can get help with.

       

      Here are the details:

       

      I have a single table with 100 records that I imported.

       

      Each of the records is identified by a unique alpha-numeric code (X1, X2, X3 etc.).

       

      Each record also contains the following data (fields): name, height, weight and the number of vegetables eaten on a specific date.

       

      So for each record, I have one layout that displays all the above data including the unique code (X1, X2, X3 etc).

       

      Here is where I am running into problems.

       

      Some of the individual records are related. Specifically, there are children and parents within my 100 records.

       

      I am now trying to find a simple way to add specific details about a parent in the single layout that is already displaying all the child's data,

       

      As I build this database, I want to be able to manually put in the code number for the parent in the layout displaying the child's data and then have Filemaker report back the parents's name, height and weight within the child's layout.

       

      If there is a "best practices" way of doing this or simply a way that works, I'd be interested in learning both.

       

      Thank you very much. And I hope this is clear.

       

      I look forward to your input.

        • 1. Re: How to display data from another record within a related record
          mark_b

          Hi Laser,

           

          This sounds similar to a personnel file where bosses are in the same file (table) as their employees and if you are on an employee record, you want to see their boss info, or the other way around - show a list of employees for each boss.  This type of relationship is called a "self join", where you create a link between parent and child records.  To do this, the child record needs to store the identifer (key) of the parent record.  Since you imported all of your records into a "flat" file, there needs to be a "foreign key" field in the child record that points to the parent's record.  Once that is in place, you can create a relationship using TableName::ID = TableName::ParentID.  Then you could use this relationship to show Parent info in a portal within the Child's record.  I hope that made sense.  It all depends on setting up that foreign key.

           

          Cheers, Mark

          1 of 1 people found this helpful
          • 2. Re: How to display data from another record within a related record
            erolst

            laser wrote:

            Some of the individual records are related. Specifically, there are children and parents within my 100 records.

             

            In that case, add a foreign key field for the parent ID; this lets you create a self-join as a one-to-many (aka parent/children) relationship.

             

            Display the fields from the (only) related record aka parent on the display to show and/or edit the parent's data from the context of a child.

             

            You can use the relationship in the reverse direction to display and, if desired, edit them. Note that in this direction you'll need a portal (since a parent can have multiple children).

            • 3. Re: How to display data from another record within a related record
              laser

              Hi Mark-

               

              Thank you for the reply and your example is spot on. I did read about the foreign key and making relationships an dportals but was not clear how to actually execute it for my scenario.

               

              Would it be possible for you to list the steps I need to perform to carry our what you have suggested? And I would like you to know that I am not being lazy- I have tried to read through the Filemaker documentation, but I am still a bit confused and want to truly understand what I am doing.

               

              I do have an understanding of how to make the realtionships, but need help making the foreign key and perhaps selecting the right fields to display in the portal.

               

              Thank you very much.

              • 4. Re: How to display data from another record within a related record
                laser

                Hi erolst-

                 

                Both you and Mark have answered my question in the the same way. I am now seeking clarity on how to make the foreign key and the relationships I need to estalish.

                 

                Feel free to add clarity to the discussion, as I would like to learn the best I can.

                 

                Thank you very much.  Also is there a way to mark boh your answers as correct?

                • 5. Re: How to display data from another record within a related record
                  mark_b

                  Hi Laser,

                   

                  The first thing you need to do is create unique record id.  This is best accomplishted by creating a field (usually called ID or _pkID) that is an AutoEnter Serial number.  So when a record is created, the system automaticaly assigns a value (next value) to that field and increments the next value. To initialize this field, you can select all records, then use the "Records | Replace Field Contents" feature and select "Replace with Serial Number".  Then you need to create a foreign key field (sometimes called _fkID).  This field must be entered by you (or a script) as the children records are identified for each parent.  When that is done, you can then create your relationship linking ID to _fkID.  I hope that helps.

                   

                  Cheers, Mark

                  1 of 1 people found this helpful
                  • 6. Re: How to display data from another record within a related record
                    laser

                    Hi Mark-

                     

                    Thank you. I think my initial confusion came from the fact that I already what I thought was a unique ID, my alpha-numeric code that I mentioned in the first post. I am having some difficulty understanding why I should have to creat another, FileMaker generated, unique ID.

                     

                    I will read up a bit and try out what you have kindly suggested. If (or when ) I run into problems, I will post back.

                     

                    Thank you for setting me on the right path.

                     

                    Best,

                    • 7. Re: How to display data from another record within a related record
                      erolst

                      laser wrote:

                      Thank you. I think my initial confusion came from the fact that I already what I thought was a unique ID, my alpha-numeric code that I mentioned in the first post. I am having some difficulty understanding why I should have to creat another, FileMaker generated, unique ID.

                       

                      Read up on the discussion of natural vs. artificial keys, and why it is preferable to not use any kind of business data (if that what X1, X2 etc. is) as your internal keys.

                       

                      As to the relationships: that's pretty much FileMaker basics. Even though you're creating a relationship between two occurrences (TOs) of the same table, the internal handling is the same as creating a relationship between two tables, since tables are always represented and accessed via TOs.

                       

                      So all you need to do is create the foreign key field, plus a new TO of your table – say, YourTable_children, and create a relationship between an already existing TO (e.g. the one your main/editing layout is based on) and this new TO by

                       

                      YourTable_mainTO::parentID = YourTable_parent::uniqueID

                       

                      which means that on a layout based on YourTable_mainTO, you can simply display fields from the (only) related parent record on the layout.

                       

                      If you want to list all children of a parent in a portal, you can use the same relationship, but need to switch to a layout based on YourTable_parent* – or create another TO, say, YourTable_children, and relate it via

                       

                      YourTable_mainTO::uniqueID = YourTable_children::parentID.

                       

                      (*Which has to do with the arguably single most critical concept of FileMaker – “context”.)

                       

                      All that's left is to find a way to easily populate the parentID with the unique ID of the correct parent – e.g. by using a value list with the ID and name/data of all records, or a conditional value list hollding all eligible parents (as filtered by some criterion).

                      • 8. Re: How to display data from another record within a related record
                        mark_b

                        Hi Laser,

                         

                        I decided to have some fun and I created a dummy database that contains a bunch of records for people.  I set up a method of identifying parents from a child record and a couple of portals to show either the parents or children from a person's record.  Have fun.  Another thought about primary (unique) keys.  There are lots of examples where "data" can be used as a unique key, such as social security nyumber, employee ID, etc.  However, in a database i is always a good practice to have the system generate the key.  I like simple numbers, but other people use the UUID feature.  The key can (and maybe should) be hidden from the user.  But that way you are guaranteed that someone didn't enter the "key" twice.

                         

                        Cheers, Mark

                        • 9. Re: How to display data from another record within a related record
                          laser

                          Thank you erolst-

                           

                          Between this answer and Mark's I'm on my way to a real understanding of what I need to do.

                           

                          Thank you for taking the time to help. It is appreciated.

                          • 10. Re: How to display data from another record within a related record
                            laser

                            Hi Mark-

                             

                            Thank you for taking the time to do this. Very helpful. I'll play around with it and apply what I learn to my database.

                             

                            Will my already assigned unique identifier (X1, X2, X3) be linked to the new database generated identifier that I must create? Because, these identifiers we have been using (X1, X2) hold meaning for our group.

                             

                            Thank you.

                            • 11. Re: How to display data from another record within a related record
                              mark_b

                              Hi Laser,

                               

                              You can certainly keep the previous identifyer, but I still would set up the auto enter serial number and base my relationships on that.  Once you have set up the auto enter serial number field, be sure to uncheck the Prohibit Modification option so you can populate the field from the Replace discussed earlier.  Then go back and check the Prohibit Modification.  That is one bonus of having the system create your keys.  You can't (or a user can't) modify an existing key and break a bunch of relationships.  After you do the Replace, I would remove that field from any layouts (except one for your eyes only).  The user can still think that everything is tied to the X1... values, but by using the auto enter, it is much safer.

                               

                              Cheers, Mark

                               

                              ps: I don't remember where or when I read/saw/heard this but I recall that someone stongly suggested that key fields not have any meaning.  The new UUID function is a perfect example and serial numbers are similar - although there is the potential to get confused with serial numbers between different tables because each table may have the same serial number, whereas the UUID is pretty much never going to be duplicated in any table.  Also, you can make your serial number start with an "X", and then have the system automatically increment the integer portion, but that might mess up any meaning the previous values had.  I like the plain serial number for the simple reason that the UUID is quite long.

                              • 12. Re: How to display data from another record within a related record

                                laser wrote:

                                 

                                Each record also contains the following data (fields): name, height, weight and the number of vegetables eaten on a specific date.

                                I am now trying to find a simple way to add specific details about a parent in the single layout that is already displaying all the child's data,

                                 

                                If there is a "best practices" way of doing this or simply a way that works, I'd be interested in learning both.

                                 

                                Hi Laser,

                                 

                                You have a one-to-many relational need and that means two DIFFERENT tables.  One Person can have many days of vegetable selections.

                                 

                                First table holds the unique ID, name, height, weight, maybe called PEOPLE

                                Second table holds Vegetable and EatenDate, maybe called DIET.

                                 

                                One would think that it would be simple to have a field for FatherID and a field for MotherID within the Person record.  And that would work like this:

                                 

                                For relationships between people, each record in the PEOPLE table should also have a FatherID and a MotherID, which is the PersonID.  A value list called People, which holds all PersonIDs can be attached for selecting the parents.   Or you can further define conditional value lists which filter your pop-ups by gender i.e., Male People and Female People so you won't accidentally assign a female to the FatherID (for example).

                                 

                                However, in the world of human relationships, there can be many fathers and many mothers because of divorce, death, and polygamy etc.  So if you wish to allow for multiple relationships, you will need a join table to resolve many-to-many situation.  The join table would hold one record as:

                                 

                                PersonID

                                RelationID (the person ID of the related party being identified)

                                Relationship:  Father, Mother, Sister, Brother etc.

                                 

                                The People table would be attach to both sides of this join table (create another occurrence of the People table) so all family members can be viewed in a portal on a Person layout.  It can get complex.  But if you use a join table for the 'relationships', you won't ever hit a dead-end.  If you use the FatherID and MotherID fields in the PEOPLE table, you will lose tracking a person's original father when you change it to their current 'father.'

                                 

                                These are, of course, business decisions you will need to make ahead of time.  If you wish, I could provide a sample file (no navigation, just structure) but I wouldn't have the time for a few days yet.  You can search for 'join tables' and probably take it on yourself.  Well, you asked for 'best practices', LOL, so I hope this has helped.

                                 

                                Kind regards,

                                LaRetta

                                 

                                ADDED - if you would like a sample file, please provide your FM version.  :-)

                                 

                                Message was edited by: LaRetta

                                • 13. Re: How to display data from another record within a related record
                                  laser

                                  Hi LaRetta-

                                   

                                  Thank you for the response. If you have the time, a sample file would be great to see the structure. The issues you bring up will be things I will have to consider once I get beyond the basics.

                                   

                                  I am running FM Pro12.

                                   

                                  Thank you very much.

                                  • 14. Re: How to display data from another record within a related record
                                    laser

                                    Hi Mark-

                                     

                                    I was able to play around a bit this weekend with your example file. I have copied your structure into my database and it seems to be working with one small issue that I can't seem to resolve.

                                     

                                    I would like use our unique codes (X1, X2) to identify the parent of the child. So when we work in the Child data layout, there would be Parent field and in that field we would put (X1, X2). And based on this, the database would display, in a portal, the parents data.

                                     

                                    The way I am understanding your sample file and they way I have it working is that I have to put in the unquie database dervied ID to get the portal to display the parents data.

                                     

                                    My understanding was that this database derived unique identification should remain hidden from the users. But it seems I need to input this unique database dervied ID in the Child data layout in order to display the parent data (height, weight etc.) in a portal.

                                     

                                    Is there a way around this, specifically to use our identifier (X1, X2) to get the parent data to display in the portal rather than using the database derived unique identifier? Or is this just bad starting design or a lack of understaqnding on my part?

                                     

                                    Thank you.

                                    1 2 Previous Next