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.
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.
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?
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.
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.
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.
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.
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?
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::__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.
I'd recommend "Adventures in FileMaking #3 - Many to Many Relationships" but I've barely started work on that Adventure File.
Weird, I created a new join table but it's still not letting me join the two together in the relationship graph
Same reason as before. You'll need additional table occurrences for each place where you set this up in your relationship graph.
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?
Heres my graph
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.