7 Replies Latest reply on Sep 3, 2009 10:28 AM by ninja

    Relational DB / Multiple Layouts

    ChrisB

      Title

      Relational DB / Multiple Layouts

      Post

      I am attempting to create a video / post production logging system.

       

      Table 1 will have an ID number, and multiple fields with project information.

      Table 2 will have an identical ID number as Table 1 and will have multiple fields with tapes that are being used for the project in Table 1

      Table 3 will once again have an identical ID number and will have multiple fields with hours that have been spent on the project.

       

      Tables 2 & 3 will always have multiple entrys for 1 project.

       

      It seems as though FileMaker is bunching everything together, for example if I am on layout 1 which has the project information, it shows 3 records. If I click on a button that takes me to add another tape to the project the record number changes to 4 and so on. If I delete all of the records on "Tape" Layout then it also deletes all of the records for the project.

       

      Please advise on exactly what I am doing incorrectly.

       

      Thanks!

       

       

        • 1. Re: Relational DB / Multiple Layouts
          ninja
            

          Howdy ChrisB,

           

          Welcome to the forum.

           

          If layout1 shows 3 records, then you click to add a tape and you end up with 4 records...I suspect that layout1 is based on table2.  Could you post which tables your layouts are based on?

           

          Your project layout should be based on table 1, possibly with a portal from Table2 to show tapes, and possibly even a portal from Table3 showing hours (depending on how you want to structure your user interface).  From the tidbit of info you've given, it looks like you're working from a layout based on table2 when you thought it was based on Table1.

           

          More info is always good.

          • 2. Re: Relational DB / Multiple Layouts
            ChrisB
              

            Ninja, that was the problem! Thank you!

             

            What is the best way to get the ID number from layout 1 to carry over to layout 2 (Tapes) and layout 3 (Project Hours)?

             

            Thank you for the help!

            • 3. Re: Relational DB / Multiple Layouts
              ninja
                

              Howdy ChrisB,

               

              Understand up front that I'm winging it with the little detail you've given re: user interface.  But it seems like your structure is intended to relate the three tables via ID#.  This being the case, you don't want to "carry over" the data in ID fields, you want each table to have the data, and relate the table's records when the ID#'s match.

               

              Assuming this is correct, go to File>Manage/Define database, go to the relationships tab, and drag the ID# field from table1 to the ID field of table two to form the relationship.  Do the same from table1 to table3.  You should see links form between the table graphically.

               

              Assuming further on your usage intent, double click on each relationship and ensure that records can be created in table2 and table3 via the relationship.  You would likely NOT want records created via the relationship in table1 (assumption is that you won't have tapes or billable hours without a project initiated first).  Record creation via relationship is found in the check boxes at the bottom of the "relationship" window that popped up when you double clicked the relationship.

               

              If you now have a layout based on table1, with a portal showing records from table2, and you enter tape data into the portal line, it will create a record in table2 with an ID# that matches the ID# from table1.  Same goes for a portal from table3 on that same layout.

               

              You'll probably want to create basic layouts based on tables 2&3 to verify that this is functioning as you would want.

               

              Get this far, then let me know what the next step is toward your end goal.  If my assumptions are incorrect, please elaborate.

              • 4. Re: Relational DB / Multiple Layouts
                ChrisB
                  

                Project Details:

                 

                Layout 1:

                Table has an ID field with a number being assigned internally.

                Layout has text fields that are associated with the Project Table.

                Has buttons to move to previous record (Project) and next record (Project)

                button for a new project (Stays on layout 1 just creates another record in table 1, users enters project data and clicks on another button that commits the record)

                button to discard changes (layout 1)

                button for tape log (this button changes to layout 2, which is the tape log screen)

                button for project hours (this button changes to layout 3, which is the project hours screen)

                 

                Layout 2:

                Tape Log Information

                < and > buttons to navigate tape logs

                New Tape Button (creates a new record in the tape DB)

                Save Button (Commits Record)

                another table that also has an ID field

                 

                Layout 3:

                Project Hours Information

                Record Button (Commits record)

                Discard Button (Discards New Record)

                Return to project Button (returns to layout 1)

                once again another table, with an ID field.

                 

                I have completed the link as you said and it does not appear to be holding the ID from layout 1's Record

                Is this because I am not using a portal to enter the data as you said?

                 

                Can I just set a variable from layout 1's current record and then pull the variable into layouts 2 and 3? if so how can I do it?

                 

                I hope this is a little more of what you needed to know.

                 

                Thanks for all of the help!

                 

                 

                 

                • 5. Re: Relational DB / Multiple Layouts
                  ninja
                    

                  Chris B wrote:

                   

                  I have completed the link as you said and it does not appear to be holding the ID from layout 1's Record

                  Is this because I am not using a portal to enter the data as you said?

                   


                  Yes.  If the record for a tape is not created from the point of view of the corresponding project, FMP doesn't know what ID# to give it.  But don't worry, a portal is one way but not the only way.

                   


                  Chris B wrote:

                  Can I just set a variable from layout 1's current record and then pull the variable into layouts 2 and 3? if so how can I do it?

                   


                   

                  Yes, sort of, but let me word it differently.  If you wanted to be on the "Tape Layout" (based on Table2) and simply hit the "NewTape" button, we need to do something to tell the record which project (Table1) this new tape should be associated with.

                   

                  If you'll enter tapes from the tape layout (rather than through a portal), you could have a dropdown list of "Projects" that the user would select from.  This dropdown should be based on a value list (read up on these in the help files).  The value list would be put onto the field Table2::ID#, it would be based on the feild Table1::ID#, but would show information only from a second field...the field Table1:: ProjectName.

                  In this way, you'll see a list of recognizable project names, select the name, and have the ID# from the project be put into the ID# of the tape.  This method would not need a portal to achieve the ID# linkage.

                  The value list concept is pretty strightforward and fairly well described in the help screens.  try it out and see if it doesn't make sense on its own.  If not, post back with what's hanging up on you.

                   



                  • 6. Re: Relational DB / Multiple Layouts
                    ChrisB
                      

                    Thanks for the help Ninja! I ended up figuring out that I can copy and paste a field in a script and take care of the ID number issue.

                     

                    Thank you!

                     

                     

                    • 7. Re: Relational DB / Multiple Layouts
                      ninja
                        

                      Sounds great ChrisB,

                       

                      As you keep on, keep ruggedness in mind.  If you're on the Tape table, and click "new tape"...where are you copying from?  If the active record on the Project table is not the same project as the tape...will you get the correct ID#?

                       

                      Troubleshoot your solution and make sure of these type of things.

                       

                      Glad to hear you're past this hurdle!

                       

                      Enjoy the day!