11 Replies Latest reply on Feb 2, 2015 4:06 AM by erolst

    Connecting Tables best practice?

    frankst

      Hi,

       

      I have looked at the Lynda training for Filemaker Pro 13 which really helped me allot (new to Filemaker Pro)

      But I like to link multiple tables in the Relationships, what would be the best practice here? In the Lynda training it only goes over 3 tables with a 4th one to connect the 3.

       

      I have the following tables:

      • Customers
      • Employees
      • Work Order
      • Task

      So I like to have the relationship where you create a new Work Order for a Customer, in that Work Order should be able to create a Task where you can assign a Employee to it.

       

      I guess my next following question would be they interchange as in you can view all the Tasks a Employee is assigned to, view all the Work Order of a Customer and back and forth.

       

      Thank you

        • 1. Re: Connecting Tables best practice?
          coherentkris

          research the concept of join tables to get your data right and remember FileMaker is context sensitive.. layouts are based on TOs which can leverage relationships to show data.

          You have identified 4 entities and now you have to to figure out how they relate to each other..

          Can a customer have more than one work order? Can a work order have many tasks? Can a task be associated with more than one work order? How do employees fit into the scheme? This will help you to set up the basic relationships that will determine what can be "seen" from any given context. Then you extend your relationship graph to accomplish your goals

          • 2. Re: Connecting Tables best practice?
            frankst

            Hello,

             

            yes a customer can have multiple work orders and a work order can have multiple tasks.

            The employee will be assigned to a task so a employee can have multiple tasks.

             

            Here is the relationship I have at the moment. But when I add the connection from __pkWorkOrderID to the Tasks _fkWorkOrderID it wants to add a Relationship... Name of Occurrence Tasks 2...

             

            Screen Shot 2015-01-29 at 09.23.48.png

            • 3. Re: Connecting Tables best practice?
              erolst

              frankst wrote:

              when I add the connection from __pkWorkOrderID to the Tasks _fkWorkOrderID it wants to add a Relationship... Name of Occurrence Tasks 2...

              Are you sure about that?

               

              FM doesn't allow circular relationships, because that would result in ambiguous paths.


              In the RG shown, OTOH, the TO Tasks isn't related to any other TO yet, so there cannot be a conflict – and you should be able to relate the existing Tasks TO to the WorkOrders TO, without Filemaker forcing you to create and use another one.

              • 4. Re: Connecting Tables best practice?
                frankst

                Sorry not sure what you mean with OTOH and TO?

                 

                So how would  I have the relationships with the Customers, Work Order, Tasks and Employees?

                I followed the instruction from the Lynda training DVD it was mentioned you can't or shouldn't have multiple relations between two tables, reason why there is the WorkOrdersLine table, right?

                 

                I  will create video in a few show how filemaker pro is reacting.

                • 5. Re: Connecting Tables best practice?
                  erolst

                  TO = table occurrence

                  OTOH = on the other hand

                   

                  You can't have multiple relations between the same table occurrences – but the concept of table occurrences serves exactly the purpose of being able to connect the same tables in multiple (presumably different) ways, or in the same way from different contexts …

                   

                  The WorkOrders table is what allows you implement a many-to-many relationship (by connecting a one-to-many with a many-to-one); but this has nothing to do with the concept of table occurrences.

                   

                  Anyway, see here a mockup of your setup

                   

                  re.png

                   

                  and notice how you need two TOs of the Employees table, because it must be referenced from two different contexts (an Employee can be a “parent” in two different join tables).

                   

                  This is what you will have to do in your setup as well; you couldn't connect, say, Employees_forWorkOrderLines to Tasks, because then the path to Employees_forWorkOrderLines from another TO would be ambiguous – which route to choose?

                   

                  But the Tasks table (which we were talking about) is only needed once; and the single, as-of-yet unconnected Tasks TO shown in your screenshot could be related to any other TO without causing a conflict.

                  • 6. Re: Connecting Tables best practice?
                    coherentkris

                    Internalizing the distinction between tables and expressions of tables as occurences on the relationship graph is vital knowledge for making effective use of FileMaker. The graph is not an entity relationship diagram but can be thought of as a graphical query engine.

                    • 7. Re: Connecting Tables best practice?
                      frankst

                      Thanks for the info!

                       

                      So I will create two Employees tables but use the same field "__pk_employeeID" from Employees_forWorkOrderLines in Employees_forTasks?

                      • 8. Re: Connecting Tables best practice?
                        frankst

                        ...by using a Specify Calculation?

                        Or how do the Employee ID relate?

                        • 9. Re: Connecting Tables best practice?
                          erolst

                          frankst wrote:

                           

                          Thanks for the info!

                           

                          So I will create two Employees tables but use the same field "__pk_employeeID" from Employees_forWorkOrderLines in Employees_forTasks?

                           

                          You don't create another Employees table; you create another table occurrence of the (one and only) Employees table; and yes, you use the same __pk_employeeID field.

                           

                          Heed coherentkris's advice and learn about tables and table occurrences, and how they (together with layouts) provide the current context you're always in when working with FileMaker (and which determines what data are accessible).

                          • 10. Re: Connecting Tables best practice?
                            frankst

                            I assumed in your sample that you created two tables seeing Employees_forTasks and Employess_forWorkOrderLines.

                            When using the __pk_employeeID for both tables how do the related to each other.

                             

                            Any suggestions on resources for tables?

                             

                            Thanks

                            • 11. Re: Connecting Tables best practice?
                              erolst

                              frankst wrote:

                              When using the __pk_employeeID for both tables how do the related to each other.

                              There is only one Employees table; and, as shown in the screenshot, you connect its table occurrences to other table occurrences by matching the primary key from Employees with a Employee foreign key in each of the other table occurrences.

                              frankst wrote:

                              Any suggestions on resources for tables?

                              No idea what exactly you mean by that.

                               

                              As coherentkris said: The distinction between tables and their occurrences in the Relationship Graph is a basic/vital concept in FM – so vital that it is discussed in virtually every documentation / third party training resource you come across (and that addresses novice users/developers, I should probably add).