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