11 Replies Latest reply on Sep 26, 2014 8:14 AM by philmodjunk

    Related Record problem

    juanZhang

      Title

      Related Record problem

      Post

      created a database with 5 tables "Demographic", "Employment", "Education" , "Curriculum" and "Mentor". Created  ID and layout for all 5 tables. Went to the manage database and did the relationship thing and linked all the ID together.  When I click on the button employment or Education, it jumped from ID 2 to ID 3. How can I click and have it stay on the same record with the same ID? Help!

      fmpro1.jpg

        • 1. Re: Related Record problem
          philmodjunk

          did the relationship thing and linked all the ID together.

          That's a bit vague don't you think? There's a lot of possible ways that you might have done that. Care to upload a screen shot of your relationships and table occurrences in Manage | Database | Relationships?

          When I click on the button employment or Education, it jumped from ID 2 to ID 3.

          And what buttons options and/or scripts did you set up to be performed when those buttons are clicked?

          And is this the ID for a "Demographic", "Employment", "Education" , "Curriculum" or "Mentor" record?

          Caulkins Consulting, Home of Adventures In FileMaking

          • 2. Re: Related Record problem
            juanZhang
            /files/e30a232287/fmpro3.jpg 1238x598
            • 3. Re: Related Record problem
              juanZhang
              /files/7080ee4453/fmpro4.jpg 786x612
              • 4. Re: Related Record problem
                juanZhang
                /files/246e91fedd/fmpro5.jpg 1061x623
                • 5. Re: Related Record problem
                  juanZhang

                  I just started to learn filemaker pro, so it will be a learning process for me, thanks Phil !

                  • 6. Re: Related Record problem
                    juanZhang

                    I just started to learn Filemaker pro, so it will be a learning process for me, thanks Phil !

                    • 7. Re: Related Record problem
                      philmodjunk

                      You'll need to learn the difference between a primary key and a foreign key. ID in Demographics is a primary key. It appears that you have defined it as an auto-entered serial number field.

                      But ID in the other three tables, is set up as a foreign key and each table is missing a Primary Key.

                      A primary key uniquely identifies each record in the table where you define it. So in Demographics, ID should, from what I can see be unique for every record in Demographics. A foreign key stores the value of a related record's Primary key. Multiple records in the same table might store the same value in a foreign key field as they might all be linked to the same record in that related table.

                      So when you have a record in Demographics with an ID of 1, any record in Employment with an ID of 1 will be linked to that same record in Demographics. That means one record in Demographics to possibly many records in Employment. That's what we mean by a "one to many" relationship. But what of the relationship between Employment and education? Do you want many records in education linked to one record in Employment? if so your relationship should match a primary key in Employment to a foreign key in Education. Or do you want ID to identify the related Demographics record, one record in demographics to many records in Education? IF so, Education should link directly to Demographics, not to Employment. So either way, you need to change your relationships.

                      And your looked up value settings are not going to work to copy over a primary key from a related table. That's because you need to have the linking ID in place before you can look up values from a related table. It's like calling the phone company to string wire from the street to your house from a land line phone inside your house. You can't call the phone company because you don't have the line and once you have the line, there's no need to call and ask. wink

                      You'll need to use other methods to copy over the primary key ID from a related (Parent) record to a new record in a child table.

                      Caulkins Consulting, Home of Adventures In FileMaking

                      • 8. Re: Related Record problem
                        juanZhang

                        so I need two IDs for Demographics? one for Primary which create automatically when I enter information, and another ID for other table to link to?  For Education, I need two IDs as well, one primary and one to link to Demographics?

                        Basically I need Employment, Education, , employment for John Doe. because it's too much to fit in one screen, that's why I want have them in different layout, and when I am in John Doe's Employment information, after I click education, I don't want go to Mary's Education information instead of John's.

                         

                         

                        • 9. Re: Related Record problem
                          philmodjunk

                          No, the other table would link to ID. But when that other table links to other tables, the other table needs another ID field

                           For Education, I need two IDs as well, one primary and one to link to Demographics?

                          Generally the case. One identifies each record in Education, one identifies the parent record in Demographics.

                          Basically I need Employment, Education, , employment for John Doe.

                          You don't need to add tables just because you are going to put the data on a different layouts. You can use New Layout to create multiple layouts that all refer to the same table but just have different fields from the same table. The related tables are something you would use to better organize your data. You might, for example, have 3 Education records in Educucation for John Doe--each for a different school that John Doe attends. Jane Doe PHD, on the other hand might have more than 3 education records...

                          It sounds like you need these relationships:

                          Education>------Demographics---------<Employment
                                                              |
                                                              ^
                                                      Curriculum

                          Demographics::__pkDemographicID = Employment::_fkDemographicID
                          Demographics::__pkDemographicID = Education::_fkDemographicID
                          Demographics::__pkDemographicID = Curriculum::_fkDemographicID

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

                          • 10. Re: Related Record problem
                            juanZhang

                            Thanks Phil, sorry, been crazily busy last 10 days. I am back to the database now. Looks pretty complicated since only one unique id needed and other tables are sharing this unique id, and i need to figure out a way that the user doesn't go to other table first which will mess up the unique ID. Maybe the best way is to start from begin again  and combine them into one table??

                            • 11. Re: Related Record problem
                              philmodjunk

                              To repeat from my earlier posts. It depends on what you need to do with the data. A single table is a valid option to consider for this information, but if you find you have to define multiple fields to store what is essentially multiple instances of the same type of information for the same person, then you really are better off with a related table. As I demonstrated in the last example, it's possible that a given person my have one record in demographics and multiple records in education.

                              i need to figure out a way that the user doesn't go to other table first which will mess up the unique ID.

                              I don't see what that would be a problem. NONE of these ID fields should be directly editable by the user. The original primary key is created from an auto-enter option that enters the data when the record is created. The foreign keys then get their values from the parent record's primary key at the time that they are created.