Multiple wholesale costs table and auto enter calculation question.
Hello, I will do my best to explain clearly what I am trying to accomplish. I struggle with words when trying to describe the situations and problem encountered when looking for a solution.
My company sells art prints. We have loose prints (no frame) and framed prints (whose wholesale prices are determined by the wholesale price from our framer plus a 20% markup).
I have two tables that I use to enter this information. A "FRAMER WHOLESALE PRICES" table, and our "COMPANY WHOLESALE PRICES" table.
In the "FRAMER WHOLESALE PRICES" table, i have fields for "Size", "Paper", "Frame Type", and "Cost". The cost entered is what we are charged by our framer.
In "COMPANY WHOLESALE PRICES" , these are the prices that we sell to the public at. This table has the fields "Size", Paper Type", "Frame Type", and "Cost". The prints with no frame have a set price (based on sq. in. of paper and ink). The Framed prints, however should be a calculated total that grabs the related record from the "FRAMER WHOLESALE PRICES" table and adds the 20% markup to it. If the framer were to change his wholesale prices, and I change that cost in the "FRAMER WHOLESALE PRICES", I need the costs in the "COMPANY WHOLESALE PRICES" table to update to reflect those changes. So in essence it is an overview of ALL our total wholesale prices.
I'm trying to figure out how to have the "COMPANY WHOLESALE PRICES::Cost" field accomplish this. Since I need to manually enter some numbers, but have it perform a calculation if it's a framed print, I'm assuming that it needs to be an Auto-Enter Calculation. But if I change the "FRAMER WHOLESALE COST::Cost" field, then the cost field in the "COMPANY WHOLESALE PRICES::Cost" field won't update reliably because "Replace existing data" will have to be turned off for me to be able to manually enter some prices.
Am I correct in my thinking? I would really appreciate some insight in how to get this Cost field to behave as needed.