You may need both a pricing(cost) table and a products table or you might use one table for each.
But what you will want is one record for each product, but also for each vendor.
So if you purchase Product A from Vendor 1 and Vendor 2, you have two records for Product A, one for the vendor 1 price and one for the vendor 2 price. A relationship would then match by vendor and by product ID in order to look up the correct price.
Whether you use a table for pricing and a table for products will depend on how much additional data you need to store about the product. If you have additional data that you need store on each product in your inventory that is the same no matter the vendor from which it was purchased, then you need both a pricing and a products table with this type of relationship:
Sorry, but I only catch half part of your instruction. My original idea of a relationship (maybe wrong) is shown below:
Vender ------< PO -------< POLineitems >------ Product -----< Cost
If my idea is correct, how should I build a relationship that match vender and product ID to look up the correct price?
Or if my idea is wrong and follow your instruction, how should the PO, Product ID, and vender's relationship should be so it will look up the correct cost?
I wouldn't use your data model for this. I'd use what I recommended, but with vendor linked to PO.
The Relationship details linking lineItems to cost would look like this:
POLineItems::_fkVendorID = Cost::_fkVendorID AND
POLineItems::_fkProductID = Cost::_fkProductID
The key trick is to copy the value of PO::_fkVendorID to Costs::_fkVendorID.
There are several approaches that can do this from an auto-enter option on the field or by setting up this relationship from PO to LineItems:
PO::__pkInvoiceID = POLineItems::_fkPOID AND
PO::_fkVendorID = POLineITems::_fkVendorID
Thanks~~ I will do it and see how it works!!
You are a life saver~
Attached image is the relationship diagram that I made while following your instruction #1 & #2 (and you also mentioned that vender could link to PO), so that is what I did.
Could you please help me to verify if what I did is correct? Honestly, I am kinda confused....
Pretty much correct. But the assumption here ia that you would use a layout based on PO with a portal to POLineItems. And that layout could benefit from enabling "allow creation of records via this relationship" for POLineItems in the left hand Edit Relationship dialog shown in your screen shot. The reason for using VendorID as an added match field in the PO to POLineitems relationship si so that when you create a new record in the portal, the VendorID is automatically copied into the lineItem record so that you can match to the correct Price record when you select a product for that line item.
So next step now is that I need to create some records on my cost table. For example, for product A, I will have two records with different cost for vender 1 and vender 2. Thus, when I create a PO with POLineItem portal, it will automatically match the cost for that product, am I right?
What I have learned about Relationship in FMP, we should avoid many to many & one to one. But the way you instructed me about the relationship between POLineItem and Cost are many to many. (see attached image). Is it ok?
Just a curiosity!!!!
Actually, at DevCon, one to one relationships were a recommended method for getting faster performance by producing "narrow" tables.
Never really agreed with the advice that you should avoid these so called "many to many" relationships*. There are just too many exceptions to that rule and this is one of them. I'd change this only if I found it was resulting in an unacceptable delay when selecting products i the POLineItems portal. Technically, the combined values of _fkProductID and _fkVendorID form a one to many relationship from Cost to POLineItems, it just doesn't look that way and FileMaker has no way to know that--thus showing "crows feet" where in a true ER diagram, you'd really have a single connector here. In fact, as a safety measure, you might add a text field to Cost with this auto-enter calculation:
_fkProductID & "|" & _fkVendorID
with the "do not replace existing value..." check box cleared.
You could then add a unique values validation on this text field to enforce data integrity in the Cost table. But it won't change what you see in the relationships graph.
*"So called" because a relationship between two table occurrences that shows "crows feet" on both ends of the line isn't a true many to many relationship. That requires either an intermediary Join table or a multi-value key.