7 Replies Latest reply on Feb 22, 2010 9:09 AM by philmodjunk

    Relating Two Databases Using FMPro 9.0



      Relating Two Databases Using FMPro 9.0


      I have two separate databases, and want to relate such fields as FirstName, LastName, etc. from one database to the other so I will not have to retype such information.  I can't find a way to do it even using Serial Number and Lookup unsuccessfully!  Please advise.  Thank You !!

        • 1. Re: Relating Two Databases Using FMPro 9.0

          I don't have filemaker 9, but the steps in filemaker 10 should be exactly the same.

          1. Figure out which file is the "parent". In which file is the data entered first?
          2. Define an auto-entered serial number field, PersonID in the correct table in that file. (The table that stores the information you want to reference from the other file.)
          3. Open the other, "child", file and go to Manage | Databases | Fields
          4. Define a matching number field in one of your tables, PersonID
          5. Click the Relationships tab.
          6. Add a new relationship Table occurrence by clicking the bottom left button
          7. Select "Add Filemaker Data Source from the drop down in the dialog that pops up.
          8. Find your Parent file; select it; and click Open.
          9. You'll now see a list of all tables defined in the Parent file. Select the table where you defined PersonID as an auto-entered serial number.
          10. Link the two table occurrences that have PersonID to each other.
          11. Now you can define looked up values or simply use the field tool to add the name fields from this new table occurrence to a layout based on the table occurrence you just linked it to.
          • 2. Re: Relating Two Databases Using FMPro 9.0



            Thanks for the quick and detailed guide-response.  Unfortunately, I followed the steps - I think correctly - but did not get the information from the "parent" database to the "child" database.  I did link the Serial Numbers from both and even added "Lookup" tags to the various "fields" I wanted to "copy over" such as FirstName, LastName, etc.  It must be "me" as the operator.  The odd thing is that I was able to do this using FM Pro 5.5 - it was easy then.  Any other words of wisdom?


            Thank You !!

            Jose' Boix

            • 3. Re: Relating Two Databases Using FMPro 9.0

              The records in the related tables must have matching serial numbers. You'll have to come up with a method for assigning matching serial numbers to the related tables. For example, you can set up your serial number fiield for the child table as a 2 column drop down list of serial numbers and names from the parent table. To link the records, you select an ID from this list and the matching related data should appear in the fields or be looked up by the looked up value fields.

              • 4. Re: Relating Two Databases Using FMPro 9.0

                I really appreciate Phil trying to resolve my Relational Database query; I have tried - the best I can to use the guides given totally unsuccessfully.  I went back to my old FileMaker Pro 5.5 files and was able to "relate" my earlier developed two databases perfectly and easily.  Looks like the same type steps but no graphs on v5.5.  I just can't see how v9.0 has to be that much more complex to do the same "action."


                I am using the Serial Number type field just like v5.5, looks lie the fields have the same ":: linked/related" markings but the information does not "copy" from the "host/parent" databases to the "receiver/child" database.


                Perhaps my FileMaker Pro 9.0 is corrupted?


                One other issue that has developed is the inability to have a "Date Field" show a plain 4-Digit Year for Date; I have tried all the various forms and the only way is changing the "field" to "text" or "number."  I can have the Date field show "dd/mm/yyyy" or such other combinations but not just "yyyy."


                Frustrating !!

                • 5. Re: Relating Two Databases Using FMPro 9.0

                  Still having difficulties "relating" one FM Pro 9.0 database to another using the Serial Number approach to link records.  I confirmed that the same databases I have as FileMaker Pro 5.5 "relate" just fine, so I opened copies of these two files with FileMaker Pro 9.0 and again the "relations were lost" somehow and the two databases failed to link.


                  I am thinking that FileMaker Pro 9.0 has a "flaw" in this regard; also regarding the ability to use a "Date" format to show only a 4-Digit Year "yyyy."


                  Is there any way to have FileMaker checked this out?  Or am I forced to get v10?  Could it be that somehow my FileMaker 9.0 has become corrupted?



                  • 6. Re: Relating Two Databases Using FMPro 9.0

                    I don't have FM 9, just 10, so I'm not sure if this is new in 10, but:


                    Year(“5/5/2010”) returns 2010



                    • 7. Re: Relating Two Databases Using FMPro 9.0



                      I take weekends off from the forum, so just saw your posts.


                      Filemaker 5.5 matches values just a little different from Filemaker 9 and other newer versions. Is there a text field that's part of your relationships?


                      In filemaker 5.5, Apple* Apple. and Apple will all match. In newer versions such as Filemaker 9, they will not. Spaces will also be ignored in Filemaker 5.5 but not in 9.


                      If that's not a factor, please describe your matching fields in detail and maybe we can spot the issue.