2 Replies Latest reply on Jan 27, 2011 6:48 PM by DavidJondreau

    Relationship Issues

    auger

      Title

      Relationship Issues

      Your post

      We recently converted about 75 related data base from Filemaker 6.0 to Filemaker 11. We had spent at least two years preparing for the conversion because of the change in the basic set. After doing a lot of clean up (mostly with Goto Related records) We have started to combine the external tables into one table. We are have problems with the relationships after combining the tables. The relationships no longer seem to work even if we redined the links.

      One of the lastest attempts to combine several tables into one has resulted in numerious problems. Currently I have imported about 14 tables with over 200 relationships and more scripts, fields and layouts than I can count. I am using a simplfied example below to show my problem. If ther is a limit to the number of elements in a table this may be my problem other wise I am stumped.

      Table "VL1" was imported into Table "EM1" linked in a one to one relationship based on a numeric value and the relationship is named VL_No. The numeric value in "EM1" is a required field and must be must be unique.  The field in "VL1" can be empty but must also be unique the "VL1" table. The next relationship connects table "EM1" to table "VL1" using a different field in both tables and is named EM_No.  The field in "VL1" cannot be be empty and must be unigue. The field in EM1 can be empty and must be unique. There is also a duplicate table of "VL1" with a relationsip set to "EM1" by a constant key and named VL1_X (a many to many) There is also a duplicate table of "EM1" with a relationship set "VM1" by a constant key and named EM_X (a many to many) These four relationships seem to working correctly.

      Now I have created another internal table named "VP1" and linked to "VL1" and named the relationship VP1_RNo (a many to one, many "VP1" records to one "VL1" record). There is a dupliciate of table "VP1" and it is linked to "VL1" by a constant key in "VL1" Named VP1_X (many to many).

      In a script I set a global field in the relationship VP1_X and goto a layout in the table "VP1" and create a new record using the value in the global field to set the key used in the relationship VP1_RNo. When I try to display a related value from the relationship "VP1_RNo" I am only seeing the fields from the table "VP1" not the fields in "VL1".

      I have deleted the VP1_RNo several times and added it back to the relationships graph and poited the linking key from table "VP1" to "VL1"' which shold when I am on a layout in table "VP1" display fields from table "VL1", but it displays field in table ""VP1"

      Any advise?

        • 1. Re: Relationship Issues
          philmodjunk

          Can't really plow through all the letters and detailed text to form a picture of what you have. You can click edit on your original post and upload a screen shot of Manage | Database | relationships ( or just a portion of the shot). That might make it easier to see what you have here.

          Please note that you can't "name" a relationship in FileMaker databases versions 7 and newer. Instead you name a "table occurrence" as needed to define relationships. While this is very similar to how we used to do it in the old days, it's not exactly the same thing. Also keep in mind that you now have objects called "data source tables"--the tables in the drop down in Manage | Database | Fields and "Table Occurrences"--the boxes in your relationship graph. If you double click a table occurrence, you'll see the data source table it refers to. I don't think this is a problem here, but your wording can be confusing here as I can't tell for sure when you've created a data source table, a relationship or a table occurrence.

          Two common relationship issues you can check: In FileMaker 6 and older, punctuation characters were ignored in text fields used to match records in a relationship. In recent FileMaker versions, these characters are not ignored. Thus minor text differences that didn't affect which fields matched in FileMaker 6 may keep the same records from matching in FileMaker 11. Also, make sure matching fields in your relationship are the same data type, text to text, date to date or number to number.

          • 2. Re: Relationship Issues
            DavidJondreau

            Two things come to mind.

            1) Make sure that the Go to Related Record[] script step is referring to the correct child table.

            2) I believe between 6 and 7, FileMaker changed how it handles a NULL value in relationships. Nulls to nulls no longer produce a valid relation.

            DJ