Using margin data from one table to calculate prices in another table - help!
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!!