1 2 Previous Next 20 Replies Latest reply on Sep 19, 2014 10:50 AM by BryanN

    Relating Two Tables

    BryanN

      Title

      Relating Two Tables

      Post

       

      I'll try to lay out my basic relationships here:

             
      • Vendors and Customers are my highest level tables, all other tables are child tables from these      
                    
        • Work Orders, Purchase Orders and Quotes are the tables that are child to Vendors or Customers (never both)           
                           
          • I did create a relationship between Work Orders and Purchase orders for the purpose of deriving the Purchase Order # based upon the Work Order it is going towards
          •           
                    
        •      
             

      What I am trying to do is relate my Quotes to my Work Orders (so that after we receive an order from the customer, we can go back and connect the quote to the proper Work Order so we can reference it as need be.

      For the life of me, I don't understand why I can't make quotes a child to Work Orders; I'm sure there is some basic database logic that's preventing me from doing this but I can't figure out why.  Work Orders is a parent to many child tables. 

      Would making a separate table instance for wither Work Orders or Quotes be needed?  Scratching my head here.

      Thanks all!

        • 1. Re: Relating Two Tables
          philmodjunk

          Doesn't the Quote come before the Work Order? Won't that make the Work Order a child of the Quote rather than the other way around?

          And have you considered the possibility that the same record in the same table could be both a quote and a work order? That doesn't work for all business models, but one way to handle quotes and orders is to start the record as a "quote" and then change a status field from "quote" to order once it becomes an order.

          • 2. Re: Relating Two Tables
            BryanN

            Oh wow that was fast.  It doesn't really matter to me which way it is, just trying to make the relationship.  Unforutnately, it's not letting me do it, saying I need to create a new table occurrence.  If I do that, I'm concerned it could throw off all the other relationships.

            • 3. Re: Relating Two Tables
              philmodjunk

              That's an expected response any time linking in a table occurrence would create more than a single "path" from one table occurrence to another. Adding in an additional occurrence is the correct solution. But the correct additional occurrence might be an occurrence of Quotes as shown or it might be that you need an additional occurrence of the Table Occurrence which you were attempting to link to Quotes.

              I'd have to see all of your relationships to understand. (Generally, Parent records are the context from which child records are created via a relationship so Work Orders would appear to be a child of quotes.)

              To learn more about Table Occurrences, see: Tutorial: What are Table Occurrences?

              • 4. Re: Relating Two Tables
                BryanN

                Here's my relationship graph.  Keep in mind I run using a data separation model, so all of my self-add relationships are in the data file, including the relationship between Purchase Orders and Work Orders.  If I had a hierarchy, technically Work Orders and quotes would be on the same level as they are both child tables to Customers.

                Once we figure out which table needs a second occurrence, I plan on having a drop down selector field on my Quotes layout that allows me to choose which of the currently open Work Orders to relate to that quote. 

                • 5. Re: Relating Two Tables
                  philmodjunk

                  I don't understand your work flow. I would think that you would be selecting a Quote for your work order rather than a work order for your quote.

                  This is central to whether you add another occurrence of Quote or another occurrence of Work Orders

                  To do as you describe, you'd add another occurrence of Work Orders in order to link a quote to an existing Work Order, but if you selected a Quote on a Work Orders layout you'd add an occurrence of Quotes to your relationship graph.

                  • 6. Re: Relating Two Tables
                    BryanN

                    From a work flow perspective, we have to go back to our quote anyway to mark it won or lost, so ideally we'd enter it there.

                    • 7. Re: Relating Two Tables
                      philmodjunk

                      That would not alter my recommendations as you can "go back" to the quote with either option. Here's a more diagnostic way to look at this:

                      For a given quote, might it be linked to more than one work order?

                      For a given work order, might it be linked to more than one quote?

                      If you answer no to both questions, it doesn't matter which way you set it up as you have a one to one relationship.

                      If you answer yes only to the first question,

                      IF you answer yes only to the second question, you have a one work order to many quotes relationship. a Quote is the parent of Work Orders and you should link the primary key of the Quote to the foreign key of Work Orders and you need an added occurrence of Quotes .

                      If you answer yes to both questions, you have a many to many relationship and you need a join table.

                      • 8. Re: Relating Two Tables
                        BryanN

                        It's definitely a yes/yes so I think a join table is in order.  A quote could be used for multiple unique work orders and some work orders can require multiple quotes.

                        Any good article on how to do a join table?

                        • 9. Re: Relating Two Tables
                          philmodjunk

                          questions about join tables are so frequent, that I created a calculation field that writes the responses for me after I type in the table and field names from the user's original post:

                          Quotes-----<Quote_WO>-----WorkOrders

                          Quotes::__pkQuoteID = Quote_WO::_fkQuoteID
                          WorkOrders::__pkWorkOrderID = Quote_WO::_fkWorkOrderID

                          For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

                          You can place a portal to Quote_WO on the Quotes layout to list and select  WorkOrders records for each given Quotes record. Fields from WorkOrders can be included in the Portal to show additional info about each selected WorkOrders record and the _fkWorkOrderID field can be set up with a value list for selecting WorkOrders records by their ID field.

                          And here's a demo file on Many to Many relationships. It's in the older file format so FMP 12 and newer users will need to use Open from the FileMaker File menu to open the file and produce a copy that is converted to the newer file format.

                          https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7

                          I'd recommend "Adventures in FileMaking #3 - Many to Many Relationships" but I've barely started work on that Adventure File.

                          Caulkins Consulting, Home of Adventures In FileMaking

                          • 10. Re: Relating Two Tables
                            BryanN

                            Weird, I created a new join table but it's still not letting me join the two together in the relationship graph

                            • 11. Re: Relating Two Tables
                              philmodjunk

                              Same reason as before. You'll need additional table occurrences for each place where you set this up in your relationship graph.

                              • 12. Re: Relating Two Tables
                                BryanN

                                Maybe I'm missing something here.  Check my relationship graph.  I have 2 occurrences of the join table.  Since I'm entering it in via the quotes, the fk_workorderID field is based on join.quotes.workorders occurrence and is within a portal for that occurrence.  Won't let me enter in the data.  Maybe I'm missing something simple here?

                                • 13. Re: Relating Two Tables
                                  BryanN

                                  Heres my graph

                                  • 14. Re: Relating Two Tables
                                    BryanN

                                    I was dumb, forgot to check the box allowing it to create records int he join table via the portal. So I have that entering correctly, but if I bring a field from the Work Orders table into my quotes layout, it's still not giving correct data based on the relationship (it's showing the first of many related work orders, based on that customer. not the quotes-work orders relationship.

                                    1 2 Previous Next