4 Replies Latest reply on May 10, 2013 9:17 AM by JasonO'Berry

# Multiple wholesale costs table and auto enter calculation question.

### Title

Multiple wholesale costs table and auto enter calculation question.

### Post

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.

Thank you..

• ###### 1. Re: Multiple wholesale costs table and auto enter calculation question.

I think your problem lies in the fact that you are using two different tables. I don't understand why.

I would think these two prices need to exist in the same table.
In that table a field for the "Framer Price" and a calculation field that adds 20% and gives you a Wholesale price.

Any changes made to the "Framer Price" are immediatly added to the "Wholesale Price".

Make sure that when you sell an artwork you do a lookup of the current wholesale price into a new record in an other table. Otherwise price changes will be reflected in sales records that have already passed. And that's never a good idea.

• ###### 2. Re: Multiple wholesale costs table and auto enter calculation question.

Yes I think you're right. I guess it will have to be a conditional calcualtion that looks to see if it is a loose print or framed print, and if it's framed add 20%. Does that sound correct to you?

• ###### 3. Re: Multiple wholesale costs table and auto enter calculation question.

https://dl.dropboxusercontent.com/u/18099008/Demo_Files_FMP12/Paintings.fmp12

I'm thinking you might be best of by having one table that contains the info about the artwork. And a related table for all the different versions or formats you can have of that painting. Look at the above file for an example.

It also looks at the Frame Type. If there is "No Frame" it just takes the cost. Otherwise it adds the markup that's in the Markup field.

It's set to 20% automatically but it can be changed per item.

• ###### 4. Re: Multiple wholesale costs table and auto enter calculation question.

Thanks for your help DaSaint. I appreciate you taking the time to make the example file for me. You've helped me out alot!!