1 2 Previous Next 28 Replies Latest reply on Jan 25, 2010 12:29 PM by comment_1

    Partial success with table relationship

    filmcutter

      Title

      Partial success with table relationship

      Post

      Hi all.  This is my first post.  After using Filemaker Pro over the years for various small tasks as an (assistant editor) in managing data on film-based TV shows and movies, I have expanded my ambitions.  I am setting up a database using FMPro 8 on a Mac running OS 10.5.5.  Now that production is over, I have imported data on all the footage;  this includes all the information on film rolls, and reference numbers for film footage and frame counts.  Since the film was transferred to videotape, the information for tape and timecode for each take was also imported.  This amounts to my first table, which I've called "Footage".  As the editor finishes reels on this movie, I can make EDLs, which will include information referencing tape and timecode.  What I want to do is import EDLs into a second table, called "EDL", and then use that information to produce data referencing the original "Footage" table.  The information I create will be the film footage counts.  In both tables I have created fields that convert timecode into an overall frame count for "in" and "out" points which mark the actual edits.  This data, along with tape name, allows me to create a relationship on three criteria between the "Footage" and "EDL" tables:  Tape = Tape, Start Frames ≤ Source In Frames, and End Frames ≥ Source Out Frames.  

      In the "Footage" layout I have created a portal that allows me to see which takes are used, and the timecodes marking the edits.  But several takes are used more than once and when that happens the repeating field simply repeats the calculation of the first instance the shot is used, so my first question; is it possible to create a repeating field to do what I want, which is to show separate calculations for each time a shot is used?

      Another, bigger obstacle is that I also want "Footage" data to show up in the "EDL" table but I cannot get data from any "Footage" field to show up in "EDL".  Interestingly, conducting a "find" using a "Footage" field will return accurate results, so the relationship works up to a point.  What do I need to understand about the relationship graph to fully utilize matches between my two tables?

       

      Thanks in advance. 

       

      Scott 

        • 1. Re: Partial success with table relationship
          philmodjunk
            

          Here's where I got lost: "...when that happens the repeating field simply repeats..." That's the first mention of a repeating field in your post. What repeating field is this, what's its calculation and what is it's purpose?

           

          This sounds like one of those cases where it's very difficult to describe everything in words alone. If you can post a screen shot of your relationships and your layout, it might help.

           

          Here's a link describing step by step how to post a picture to your forum messages:

          Tutorial-How to post a picture so that it shows up in your message

          • 2. Re: Partial success with table relationship
            filmcutter
              

            Photobucket

             

             

            Sorry to be late following up, but here is an image, which may clarify things. 

            Footage table; information about the take is at the top.

            The portal below shows information from the EDL table. This one take is used six times. Notice that while the "Source In" is different for each edited event that the "Source In Film Ftg" remains the same--the calculation is correct for the first "Event" where the "Source In" is 08:06:24:16. As the "Source In" changes with subsequent events the "Source In Film Ftg" should change as well. This calculation is made by subtracting "Start" frames (from the Footage table) from "Source In" frames (from the EDL table) then taking that number and converting it into film footage.  

             

            I hope this makes more sense now.  An comments would be appreciated.

             

            Thanks,

             

            Scott 

            • 3. Re: Partial success with table relationship
              comment_1
                 First, you should check if you're getting the same results in the EDL table itself (where hopefully the calculation field is). If so, please explain how the relationship between Footage and EDL is defined.
              • 4. Re: Partial success with table relationship
                filmcutter
                  

                I do not have the calculation field in the EDL table.  At the moment I cannot get any information from the "Footage" table to appear in the EDL table.  As I noted in my initial post, a "Find" conducted in the EDL table will return records related to "Footage", but I cannot get information to appear.  This must be pointing to the problem with the relationship, but I can't figure it out.  Details of the relationship between the two tables are also in my first post. 

                 

                Hope this helps,

                 

                Scott 

                • 5. Re: Partial success with table relationship
                  comment_1
                    

                  filmcutter wrote:

                  I do not have the calculation field in the EDL table.


                  In which table is it then? It seems like it should be in the EDL table - but I am not sure about your relationships.

                   

                   


                  filmcutter wrote:

                  Details of the relationship between the two tables are also in my first post.


                  I am afraid I don't see them. I believe it should be:

                   

                  Footage::FootageID = EDL::FootageID

                   

                  i.e. one footage has many EDL lines, but you didn't say one way or another.

                   



                  • 6. Re: Partial success with table relationship
                    filmcutter
                      

                    You wrote, "In which table is it then? It seems like it should be in the EDL table - but I am not sure about your relationships."

                    I have been creating my film footage calculation fields in my "Footage" table.  I could put them in my "EDL" table but at the moment I can't get any data from my "Footage" fields to appear in "EDL".  Below is a picture of the relationship graph and below that a picture showing the criteria I used.    

                     

                    You wrote, "i.e. one footage has many EDL lines, but you didn't say one way or another."  It's true that one "Footage" record (which is one take) can include many records from "EDL".   Is it this "one to many relationship" that prohibits data from "Footage" fields from appearing in EDL?

                     
                    • 7. Re: Partial success with table relationship
                      comment_1
                         First, a calculation that is supposed to return a result specific to an EDL line must reside in the EDL table - otherwise it will return a single result for all its child lines (as shown in your earlier pic).

                      There's no reason why such calculation couldn't use data from the parent record in Footage - provided the relationship works correctly.


                      Now, I cannot really make heads or tails of your graph: I see you have auto-enters on both sides, but I don't see what is being entered. In any case, I don't think anything should be auto-entered on the child (i.e. the EDL) side.

                      I suggest you define a FootageID field (type Number) in both tables. In the Footage table only, set the field to auto-enter a serial number. Then change the relationship to use only these two fields as matchfields (as shown in my previous message).



                      • 8. Re: Partial success with table relationship
                        filmcutter
                          

                        "I suggest you define a FootageID field (type Number) in both tables. In the Footage table only, set the field to auto-enter a serial number. Then change the relationship to use only these two fields as matchfields (as shown in my previous message)."

                         

                        I can define a FootageID field for both tables as you suggest, but I don't understand what happens for that field in the EDL table;  how is a match created, or where does data for the match come from?  I'm sure there is something simple here that I'm just not getting.

                         

                        Thanks,

                         

                        Scott 

                        • 9. Re: Partial success with table relationship
                          comment_1
                             A match can be created in several ways, but I suppose in this case you need to select the FootageID for each event you add to the EDL.
                          • 10. Re: Partial success with table relationship
                            filmcutter
                              

                            I don't believe the Footage ID will work;  my EDL table is created in another application (Avid) as the editor cuts the footage so I don't have the option of adding the Footage ID to the data that is exported from the Avid for use in FileMaker.  

                            For the purposes of matching data in the EDL to data in my Footage table I am trying to use the relationship of tape and timecode because each take used in the EDL can be uniquely matched when;

                             

                            Footage : Tape =  EDL: Tape &

                            Footage : Dailies Start Frames ≤ EDL : Source Start Frames &

                            Footage:  Dailies End Frames ≥ Source Out Frames 

                             

                            Logically speaking this should work and this relationship does allow data from the EDL table to appear in Footage but I cannot get data from Footage to appear in EDL and my problem is that I don't know FileMaker well enough to understand how to create a data base that produces the calculations I want.  Specifically, I want to use the timecode in and out times from EDL to calculate film footage based on data that I have in Footage.

                            Perhaps what I'm looking for isn't possible with two separate tables?

                             

                            Scott 

                             

                            • 11. Re: Partial success with table relationship
                              comment_1
                                

                              filmcutter wrote:

                              I don't have the option of adding the Footage ID to the data that is exported from the Avid for use in FileMaker.


                              Yes, you do - turn the auto-enter option on when importing, and Filemaker will assign each imported record a unique serial ID.

                               

                              You cannot have a one-to-many relationship without having a unique identifier in the "one" table. In some cases, a unique value can be calculated from two or more fields - but such calculation cannot depend on values in the "many" table; that just doesn't make sense.


                              • 12. Re: Partial success with table relationship
                                filmcutter
                                  

                                comment wrote: 
                                You cannot have a one-to-many relationship without having a unique identifier in the "one" table. 
                                I understand the need for a unique identifier in the "one" table and how to create a "Footage ID" field set to auto entry.  What is necessary for records in the "many" table to find the proper match?
                                 
                                Scott 

                                 

                                • 13. Re: Partial success with table relationship
                                  comment_1
                                     I am not sure what you mean by "find the proper match". A child record needs to have the parent's serial ID value stored in its own field (this is known as the foreign key). This value needs to be either selected by the user (assign a child to a parent) or entered automatically if child records are created through a portal on parent layout.
                                  • 14. Re: Partial success with table relationship
                                    filmcutter
                                      

                                    comment wrote:
                                     A child record needs to have the parent's serial ID value stored in its own field (this is known as the foreign key). This value needs to be either selected by the user (assign a child to a parent) or entered automatically if child records are created through a portal on parent layout.

                                    The foreign key is what I've missing all along.  My problem now is that I don't think it's practical to select the value;  my Footage table has almost 3500 records and my EDL table might have 2000.  The second approach won't work for me because I'm not creating records in the EDL table, I'm importing them.  
                                    If I use auto enter to create a number for a Footage ID field is it practical to think of writing a script that would work in the EDL table to find the parent?  Is there a better way?
                                     
                                    Thanks,
                                     
                                    Scott 

                                     

                                    1 2 Previous Next