3 Replies Latest reply on Feb 14, 2011 10:45 AM by philmodjunk

    Using margin data from one table to calculate prices in another table - help!

    icanfly_1999

      Title

      Using margin data from one table to calculate prices in another table - help!

      Post

      Hi all.... Fairly new to FMP so I'm sure this is probably a pretty basic question.  Thanks in advance for any guidance you can provide!

      I'm creating a database for my family's small retail operation.  One component of this operation is a retail flooring store and right now I'm trying to create the infrastructure for listing, pricing, and cataloging the myriad different types of flooring/carpet that we offer for sale.

      Right now, I have 2 different tables in this part of the database.  I have a table named FlooringParentProducts and FlooringProducts.  These 2 tables are related via the SerialParent #.  The Parent Table includes the overall group name of a bunch of individual types of flooring/carpet which are listed individually in the FlooringProducts table.

      What I'm trying to do with this is create a parent group that includes margin percentages (along with other physical attributes of the whole group) that can then be used in a related individual record in the Flooring Product table to calculate an individual item's price.  I'm trying to avoid entering margin data in each individual record in the Flooring Product table and instead trying to enter it only once in a record in the Parent Product table that may have 100 related carpet types in the Product table.

      In the Product Table, I do have a lookup field that allows me to select which Parent group the carpet belongs to.  I first tried to create calculation fields that then multiply the margin stated in the Parent table by the item's cost in the Parent table and had no luck.  I also tried creating a lookup field in the Product Table that would look up the margin in the related record in the Parent Product Table with the idea that I would then multiply that by the cost to derive the selling price. 

      I greatly appreciate any advice that anyone has to make this work.  I'm also not completely loyal to the structure I've tried to create and if anyone has another way to accomplish the same things, I'm all ears.  Thanks!!

        • 1. Re: Using margin data from one table to calculate prices in another table - help!
          philmodjunk

          I first tried to create calculation fields that then multiply the margin stated in the Parent table by the item's cost in the Parent table and had no luck.  I also tried creating a lookup field in the Product Table that would look up the margin in the related record in the Parent Product Table with the idea that I would then multiply that by the cost to derive the selling price. 

          Both of those approaches should work for you, though the results differ in very key ways--especially for an invoicing system like this. The fact that they didn't work for you indicates that something in how you are defining and managing the relationships that link a FlooringProduct record to a FlooringParentProducts record likely is not set up correctly.

          Keep this thought in mind with any follow up posts you make:

          Your first approach, is a "dynamic" link to your Parent table. Any change you make to the margin in a Parent record automatically affects the results computed in all related FlooringProduct records. The second approach is a "static" link where changes to a Parent record will not automatically update values in the flooring products table unless you specifically take steps to make the update happen.

          • 2. Re: Using margin data from one table to calculate prices in another table - help!
            icanfly_1999

            Hey Phil... Thanks for the quick response!

            I have both tables joined by the SerialParent#.  The crows feet of the join points to the flooring product table.  If I open the Edit Relationship dialog, I currently have the Allow creation of records in this table via this relationship selected for no other reason than I have it selected in another part of my database that deals with actual invoicing and it made that operation work.  Below is some of the fields that I have in each table:

            FlooringParentProduct Table           FlooringProduct Table
            SERIALParent#  --------------------  SERIALParent#
            MARGINLevel1                              PRICEcalcLevel1
            MARGINLevel2                              PRICEcalcLevel2
            MARGINLevel3                              PRICEcalcLevel3
            MARGINTextNameLevel1                PRICETextNameLevel1
            MARGINTextNameLevel2                PRICETextNameLevel2
            MARGINTextNameLevel3                PRICETextNameLevel3
                                                              PRICEOurCost
                                                              LOOKUPParentFlooringItem


            The idea is that you can create a Parent Product (let's say BIG CARPET VENDOR - RESIDENTIAL CARPET) and create individual (child) carpets in the FlooringProduct table that belong to the Parent Product (via selecting the appropriate FlooringParentProduct with the LOOKUPParentFlooringItem popup menu field).  I have 3 levels of Margin because we generally markup an individual product differently based on the quantity the customer needs (the more the customer buys, the less the margin).  So, MarginLevel1 may be based on the customer buying less than 50 sq yds and MarginLevel2 may be based on the customer buying 50 to 100 sq yds. So, lets say I wanted MarginLevel1 to be 1.5 (now that I think about it, it's actually a MARKUP... I'll have to change my nomenclature later), I would want PriceCalcLevel1 to multiply PRICEOurCost by the MARGINLEVEL1 in the associated ParentProduct record to derive our selling price. 

            The MARGINTextName fields are there so I can name these different Price Levels.  For instance, Level 1 for carpet may be based on Less than 50 sq yds being purchased, but in another parent record, we may be dealing with hardwood flooring parent group where we would want the price breaks to be predicated on how much square footage the customer will buy.  I want to be able to name these pricing levels and carry them over to the PRICETextName fields in the FlooringProduct table with merge fields and subsequently carry them over to the labels that I will be printing to price our flooring display boards. 

            In reference to your post, I do want this to have a "dynamic" link so we can periodically tweak our markups (or the MARGINTextName fields for that matter) and have the new prices automatically generate in the FlooringProduct table.  We have learned that keeping pricing updated on many hundreds of samples from dozens of different vendors is extremely time consuming and I'm trying to come up with a way of doing it much more efficiently and accurately.  Up to this point, I've been using Excel worksheets (essentially one worksheet per FlooringParent item) with markups listed that will then automatically calculate each individual item's price.  Perhaps I'm still thinking in the "Excel World" and not thinking appropriately for the "FMP world"! 

            Thanks Phil for all of your help.  I've learned countless things from your past posts and want to truly thank you for all the great expertise and patience you bring to this forum!

            • 3. Re: Using margin data from one table to calculate prices in another table - help!
              philmodjunk

              Ok, Starting point is Manage | Database | Relationships where both FlooringParentProduct and FlooringProductTable have table occurrence "boxes" linked by a relationship line:

              FlooringParentProduct::SerialParent# = FlooringProductTable::SerialParent#

              If so, then you can define PRICEcalcLevel1 as a calculation field (NOT a number field with an auto-entered calculation):

              FlooringParentProduct::MARGINLevel1 * PRICEOurCost

              The Table occurrence name you use to the left of :: must be exactly the same name as the name of the "box" for this table in your graph where you define the above relationship.

              PRICETextNameLevel1, PRICETextNameLevel2, and PRICETextNameLevel3 are redundant and you can remove them from your table. In every situation where you want to use them, use the matching fields from FloorintParentProduct instead.