3 Replies Latest reply on Feb 13, 2009 3:16 PM by jenifben

    Need some basic help with setting up database relationships

    jenifben

      Title

      Need some basic help with setting up database relationships

      Post

      Hi,

       

      This is probably a very basic question (my skills with Filemaker are quite basic) so hopefully there is an easy answer.  I am setting up Filemaker for our small business and am a little stuck with making the realtionships work the way I want them to.  I hope this is the forum for people like me.  My apologies if not.

       

      To quickly summarise my needs, I have 3 tables;

       

      Customer Details

      Customer Vehicle Details

      Job Details 

       

      I have matched fields between Customer Details and Vehicle details (Customer ID)

      I have matched fields between Vehicle Details and Job Details (Vehicle ID)

      I want to also match the Customer ID field between Customer Details and Job Details but this creates a cycle which I understand is not allowed.  Filemaker automatically creates a new table when I try to do this but I don't then know how to use it!  How do I get customer details to relate to Job details?   

       

      Does this even make sense?  

       

      Any help greatly appreciated.

       

      Thanks

       

       

        • 1. Re: Need some basic help with setting up database relationships
          Sorbsbuster
            

          Welcome to the forum, jenifben,

           

          In a layout from the Customer Details Table just add a field from the 'Job Details' table and you will see that Filemaker is happy to 'see' all the connections all the way along the chain:

          Customer Details -> Vehicle Details -> Job Details.

           

           Use a portal if you want to see several records from the Job Details table.

           

          You can directly relate the Customer Details Table to the Job Details (if you don't want to go through the Vehicle Details table).  Do as you are doing, dragging the CustomerID fields to match in both tables.  Filemaker will create a second 'Occurance' of the Job Details Table, because when you link to it from the Customer Details Table, Filemaker has to know whether you mean to use the first relationship to find related records, or the second.  You tell it which you mean by choosing the 'Table Occurance Name' from the top of the field listing in setting a 'Calculated Field', for example.

           

          However, if you are only going to use this new relationship to 'tunnel' the CustomerID all the way through, there may not be any advantage (unless, for example, some Jobs do not involve any Vehicle.)

           

          See if this starts you going down the right lines, then post again if you want more details.

           

          HTH

          Alan.

          • 2. Re: Need some basic help with setting up database relationships
            swj
              

            Jenifben,

            Did you get your database working the way you wish?

            • 3. Re: Need some basic help with setting up database relationships
              jenifben
                

              Thanks Sorbsbuster, that got me going along the right track and I think I almost have this database thing sorted.

               

              Cheers