9 Replies Latest reply on Jul 30, 2015 4:31 PM by philmodjunk

    Cost problem for Product database



      Cost problem for Product database


      I'm creating a new product database but I have encountered a problem. The problem that I am facing is a cost of the product because most of our products may have multiple costs due to different venders (manufacturers). 

      In this case, what is my best option to solve this? For example, when I create a PO to my vender, the items could lookup the cost automatically by themselves for each specific vender. 

      Please help~~

        • 1. Re: Cost problem for Product database

          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:


          • 2. Re: Cost problem for Product database

            Hi Phil, 

            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? 

            • 3. Re: Cost problem for Product database

              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

              • 4. Re: Cost problem for Product database

                Hi Phil, 

                Thanks~~ I will do it and see how it works!!

                You are a life saver~

                • 5. Re: Cost problem for Product database

                  Hi Phil, 

                  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....

                  • 6. Re: Cost problem for Product database

                    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.

                    • 7. Re: Cost problem for Product database

                      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? 



                      • 8. Re: Cost problem for Product database

                        Hi Phil, 

                        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!!!!

                        • 9. Re: Cost problem for Product database

                          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.