14 Replies Latest reply on Jun 26, 2014 4:09 PM by ChadAdams

    Relationship problem


      My girlfriend doesn't seem to understand me.


      That aside, I also have a relationship problem in a Filemaker script I am trying to build.


      I have a table and in it is a unique value. I have another table and in it are exactly two records that have a value that matchs the unique value form the first.


      Each of these two records in the second table have their own unique value that I need to get back into the first table.




      Table1:name = abcd (there is only one record with this value)

      Table2:name = abcd & ID:1234

      Table2:name = abcd & ID:5678


      Now, those two records in Table2 are for two different kinds of things - let's call them 720 & 1080. So, further:


      Table1:name = abcd (there is only one record with this value)

      Table2:name = abcd & ID=1234 & Type=720

      Table2:name = abcd & ID=5678 & Type=1080


      So, to make sure I can tell the difference between them for export to the other table, I define a field that is empty unless it is the right type. I can do this because each record also has a field telling me which type it is, so:


      Table1:name = abcd (there is only one record with this value)

      Table2:name = abcd & ID=1234 & Type=720 & TypeID720 = 1234 & TypeID1080 = ""

      Table2:name = abcd & ID=5678 & Type=1080 & TypeID720 = "" & TypeID1080 = 5678


      Finally, to get both of those records to relate to table 1 so I can get both IDs and know which type the ID is I have:


      Table:name = abcd & TypeID720 = 1234 & TypeID1080 = 5678

      Table2:name = abcd & ID=1234 & Type=720 & TypeID720 = 1234 & TypeID1080 = ""

      Table2:name = abcd & ID=5678 & Type=1080 & TypeID720 = "" & TypeID1080 = 5678


      The problem is, while all the calculation fields work as expected in Table2, the type fields in Table1 only populate for whichever type record comes first in Table2, so if the first record in Table2 is a 720 record, I get:


      Table:name = abcd & TypeID720 = 1234 & TypeID1080 = ""

      Table2:name = abcd & ID=1234 & Type=720 & TypeID720 = 1234 & TypeID1080 = ""

      Table2:name = abcd & ID=5678 & Type=1080 & TypeID720 = "" & TypeID1080 = 5678


      And if the 1080 record comes first I get:


      Table:name = abcd & TypeID720 = "" & TypeID1080 = 5678

      Table2::name = abcd & ID=5678 & Type=1080 & TypeID720 = "" & TypeID1080 = 5678

      Table2:name = abcd & ID=1234 & Type=720 & TypeID720 = 1234 & TypeID1080 = ""


      So, basically, the Table1 single record relationship to two records in Table2 doesn't seem to work. Any advice?





        • 1. Re: Relationship problem

          I don't follow.


          Table2:name = abcd & ID=1234 & Type=720


          What is "Table2.name" is that a field name?  Do you really have the "Table2." prefix in the name?


          What is "abcd & ID=1234 & Type=720", is that the actual calculation in the field definition?

          • 2. Re: Relationship problem



            Might be time for a new girlfriend?


            Anyway regarding FileMaker,  normally we relate fields from one table to the next.  If the value in the field matches then you have access to the data in the second table from the first.  So in your case you have two tables and three fields.


            Table1::name   ( table1 field called name )

            Table2::TypeID  ( table2 field TypeID )

            Table2::Type      (Table2 field Type )


            If you have the relationship

            Table1::name = Table2::TypeID  if the value in both fields is abcd then you can create a layout based on Table1 and put the field Table2::TypeID or Table2::Type on the layout and see  value in the field.  If you change the value of Table2::TypeID and committ the record you won't see or be able to set the values for the fields in the corresponding record in Table2.  It will no longer match.


            If the value in Table1::name is abcd and the value in Table2::TypeID is abc3 you won't be able to access or set the values in either field.


            You can create a second Table occurence for table2.  Call it table2A and create the relationsip between table1::name = table2A::type.  If the values in both fields match you will be able to see the fields in table2 through the lense of table occurence table2A and change or modify the values in the second table.


            This is a very basic/beginner type question.   FileMaker has released the FileMaker Training series Basics.  You might want to download it and read through it.  I think it will be a big help.  Here is a link



            Hope things improve with your girlfriend.


            • 3. Re: Relationship problem

              Hi Dan,


              I can't tell if your examples are just providing us with sample data, or if they are the actual calculation fields you are dealing with.


              So, I could be way off here but it appears to me you are trying to create a key field by concatenating together different values from each table.  First of all if that is right, then your calcs are probably not giving you the key you want because they are giving boolean results for some of the tests you have in place.  For example:


              Table2:name = abcd & ID=1234 & Type=720 & TypeID720 = 1234 & TypeID1080 = ""


              If we assume ID = 1234 and Type = 720 and TypeID720 = 1234 and TypeID1080 is blank, then this Calc will evaluate to be "abcd1111" (the unique value "abcd" and the true result for each of the 4 tests.  Basically I hope I'm wrong on my assumption that you are trying to make key fields, because that approach would be all wrong and not work well (no offense).


              My understanding is that the goal is to return a specific ID from table 2 for both data types (720 & 1080) and you need to do this for the table 2 records that match the abdc value in Table 1.


              If that is correct, consider these two options:


              1. Create a multipredicate relationship based on the abcd value and constant type field (calculated to be 720).  This relationship will allow you to pull back the ID of the child record that you want.  Create another multipredicate relationship for abcd and type 1080.
              2. Utilize the ExecuteSQL function to query for the data you want in the child table without having to setup any relationships.


              Please confirm or reject the assumptions above to help clarify the issue.





              • 4. Re: Relationship problem

                Sorry, the tables are tables.  Each table has a field called "name."  In Table1, name is a unique value in each record.  In Table2, there will be exactly 2 records that have the same value in the name field as that one single record in Table1.


                Basically, I have a name for a video file in Table 1.  This is a sibgle record containing information about that video like "Shoot Date," or "Camera Lens."  For each video file, there are two copies - a 720p copy and a 1080p copy


                Though both copies have the same name, they each have a unique ID.  Table2 has a record for each actual copy of the video file.  One for the 720p copy and one for the 1080p copy.  I need to get the unique IDs for both the 720p copy and the 1080p copy of the file into the record for the related record in Table1.


                Sorry for my notation, but let me try again, here.  This is what I get when the first record in Table2 happens to be the 1080 copy of the file:


                Table::name = "abcd", Table1::TypeID720 = "", Table1::TypeID1080 = "5678"

                Table2::name = "abcd", Table2::ID="5678", Table2::Type="1080", Table2::TypeID720 = "", Table2::TypeID1080 = "5678"

                Table2::name = "abcd", Table2::ID="1234", Table2::Type="720", Table::TypeID720 = "1234", Table2::TypeID1080 = ""


                Some things to note: The name fields in both tables and the ID fields in Table2 are text fields and I have no control over the documents that these values come from.  All of the fields that begin with "Type" are calculations.  The Type fields in Table 2 are based on information in Table2.  The Type fields in Table1 are just:


                Table1::TypeID720 = Table2::TypeID720

                Table1::TypeID1080 = Table2::TypeID1080


                ...based on the relationship:


                Table1::name = Table2::name


                But again, when a single record in, "Table1::name" equales two records in, "Table2::name", I only get one of the TypeIDs filed in Table one as noted above.  Whichever comes first in Table2.  If the record for the 1080p file comes first, the Table1::TypeID1080 field will populate and vice versa.

                • 5. Re: Relationship problem


                  You can use the command  list[Table2::TypeID] and get back all matching values.  The command can be used in field definition or in a script with set field or set variable.



                  • 6. Re: Relationship problem

                    Right, and I actually do that for things like shot descriptions because that information will be the same for both the 720p and 1080p files.  While that works fine and I do get both IDs when I do that, it dosn't tell me which ID is for which copy.  There are certain operations I need to perform where I need to know which ID belongs to which file.

                    • 7. Re: Relationship problem

                      Sorry, I'll try to be more explative.


                      So, first, everything works in Table2.  I get exactly the values I want and here's how I get them.


                      I get an import that tells me a file name (there will be two separate recoards for each name) a UUID ( this will be unique to each record) and another field with a ton of metadata that is also unique.  I have a field "size" that has a calculation that searches the metadata field to find out whether the record in questionh is for the 720p or 1080p copy.  The value that results is either 1080 or 720.  By ay of information, the calculation is:


                      Let ( [ height = Position ( pix_metadata ; "<height>" ; 1 ; 1 ) + 8 ;

                      end = Position (pix_metadata ; "</" ; height ; 1 ) - height ] ;

                      Middle ( pix_metadata ; height ; end ) )


                      I have two fields 720_id and 1080_id.  Their calculations are:


                      Table2::1080_id = If ( size = 1080 ; UUID )

                      Table2::720_id = If ( size = 720 ; UUID )


                      Based on the above, I correctly get a an ID in the appropriate field only if the record is for a video file of that size.  So, basically, everything in Table2 works as expected.  If the record is for a 1080 file then I get a UUID in the 1080_id field and the 720_id field is blank and vice versa.


                      What doesn't work is when I try to get those IDs from the two separate records into a single record in Table1.  To better describe the relationship, I have




                      relates to:





                      As I said, the fields in Table2 populate exactly as expected.  However, the two calculations in Table1 do not.  Based the above relationship, in a single record, the calculations:


                      Table1::1080_id = Table2::1080_id

                      Table1::720_id = Table2::720_id


                      ...only yeild a result for the first record imported.  So if the first record imported is a 1080 file, I will get an ID for the 1080 file in Table1, but not the 720 file and vice versa even though the corosponding fields in the related records are populated.


                      Interestingly, in the same Table1, this calculation does work:


                      Table1::UUIDs = List(Table2::UUID)


                      In the same record, this will give me both the ID for the 720 file and the 1080 file even though the calculations above will only work for one or the other.

                      • 8. Re: Relationship problem

                        Actually, the relationship is much more simple.  It's just:


                        Table1:name = Table2:name


                        In Table 1, name is unique to each record.  In Table 2, name will have exactly two records with the same value in that field.


                        What I am trying to get is other, unique data out of both matches in Table 2 into table 1 while knowing which record it came from.


                        Based on the relationship I have, this works:


                        Table1::UUID = List(Table2::UUID)


                        That gives me the UUIDs from both of the matching records in Table 2.


                        However, through some other calculations in Table2, I can tell which UUID is for which copy of the file - namely the 1080p copy or the 710p copy.  So what I want to make work is:


                        Table1::UUID_720 = Table2::UUID_720

                        Table1::UUID_1080 = Table2::UUID_1080


                        While in the same record the first calculation will populate the IDs of both matching records in Table2, the second scenario will only give me one or the other - basically, whichever was imported first.

                        • 9. Re: Relationship problem

                          Wow.  So my question has changed.  Can anybody tell me why, in the above scenario, this works:


                          Table1::UUID_720 = Substitute ( List ( table2::UUID_720 ) ; "¶" ; " " )

                          Table2::UUID_1080 = Substitute ( List ( table2::UUID_1080 ) ; "¶" ; " " )


                          But this doesn't:


                          Table1::UUID_720 = Table2::UUID_720

                          Table1::UUID_1080 = Table2::UUID_1080


                          Thr first populates both fields in every record from Table1.  The later only populates one or the other.

                          • 10. Re: Relationship problem

                            When you use the list command you are getting all of the available values.  You subsitute command removes the seperator so the field has both values separated by a space.


                            The second version only gets the first available value because it isn't using the list command.

                            • 11. Re: Relationship problem

                              Like other respondents, I am somewhat bamboozled by your complicated description of what you are trying to do—and you appear to have bamboozled yourself as well. Forgive me if I am misjudging you, but it appears to me that one thing you don't seem to have is a proper join path between the two tables. Put simply, you don't appear to be creating a conventional join, thus:


                              1.  Each record in any table should have its own unique ID—but you don't try to join the unique ID in one table to the unique ID in another

                              2.  If you wish to link records in table 2 to records in table 1, then table 2 must contain a separate field into which you enter the table 1 ID of the record each relates to—I see no evidence of the existence of this simple, standard join path


                              The attached demo file shows the above in practice.

                              • 12. Re: Relationship problem

                                No, the relationship is simple and works.  Table1 has a name field.  Table2 has a name field.  The relationship is Table1::name = Table2::name.  If I was only dealing with a single size file as I usually am this works fine because the tables have a 1 to 1 relationship so I just do:


                                Table1::UUID = Table2::UUID


                                ...and the UUID populates.


                                However, this show is using both 720p files and 1080p files so Table2 has an indvidual records for each single shot - two Table2 records (1080p and 720p) for each Table1 record.  This works fine for Table1 as expected:


                                Table1::UUID = List(Table2::UUID)


                                That will give me the UUIDs from both of the related records in Table2, so, basically, the relationship is fine.  The problem is that calculation doesn't tell me which UUID is for the 720 file and which is for the 1080 file.  However, when, through some calculations in Table2, I figure out which ID belongs to which size file I could only import the ID from one record or the other until I did what I pointed out above.  Now it works and the relationship is the same.  I'm just not clear exactly why it works.

                                • 13. Re: Relationship problem

                                  Okay, bingo.

                                  • 14. Re: Relationship problem



                                    I think the basis of the issues you are seeing is because when you use a related field in a calculation (or show one on a layout), you only get the first record from that relationship. 


                                    In order to pull the data you want through a relationship you'll need two relationships.  One that only shows the 720 child records for the abcd record, and a different one that shows the 1080 child records for abcd record.  In my earlier post that's what I meant by a multipredicate relationship.


                                    Or you can change each of your calc fields to use an ExecuteSQL statement to pull the correct data (which will not rely on a relationship at all).  Something like this:


                                    ExecuteSQL ( "SELECT theIDField FROM table2 WHERE theChildID=? and type=720" ; "" ; "" ; Table1::theParentID )


                                    And another one for the 1080 field.  You might have to single quote the 720 to get that to work...I forget.


                                    The substitute functions you list below are using the list function to pull over all the the id values from each child record, then using the substitute function to remove empty rows.  Technically that would work if you only ever have two child records.  But if there is a chance that you might have more than two records, you'd end up with two (or more) id values all strung together in your calc.