40 Level Markup Table

I am straining to make sense of all these entities you are mentioning: "levels", "pieces", "items", "base cases", "product".

My best guess as to what you are describing is that you have a parent table and a child table and the price or cost associated with each child item needs to be marked up in a special way. 

I think what you are saying is that the cost or price of each child record has to summed up and that sum has to be put through some type of graduated function that has 40 breakpoints - like calculating a tax using 40 different income levels. In other words the markup depends on the total cost or price of the children. The output of this 40 level calculation yields a percentage or markup which has to be allocated to each of the items in the child table. Is that what you are trying to describe?

If this is so you need a summary field in the parent that give you a [total] and then a 40 breakpoint If() formula that yields a markup percentage. The child records lookup to the parent for the markup and multiply it by the child's price or cost.

Thanks for your help.  You have the correct understanding, however I was looking for a way to avoid a 40 level Case formula.  A Case formula solution does not allow for any changes that only affect future records without adjusting previously created records. 

I am also not understanding how a summary field in the parent records helps me if I am going to use a Case formula.  Why not just put the Case formula in the child record based upon the total for each child record?  I am looking for this markup % to end up on each child record to arrive at a sale price for each child.  The total cost and total sale will summarize on the parent record. 

I was thinking I would need a separate Markup table related to the parent but then I don't know how to select the correct record in this Markup table based upon a calculated number. 

I suggest having a single standard markup table with the two columns being [Order Size] and [Markup].  It will also have a number from 1-40 which you would set as your Key Field to represent the Markup level. 

On the parent record you would would have a formula calculate which of the 40 markups to use based on yes, a 40 line IF statement.

Then you would pull in the correct markup to apply into the Parent record and then look that up to the child records..

When the order has been invoiced, you would then trigger a native Action to copy the values from the markup lookup value into the frozen lookup value so that it would not change if you changed your discount structure in the future.  so the formula for the markup would actually be to use the frozen value if there is one, else use the lookup, and that is what would be passed down to the child records. 

I just wanted to try out this MathJax tool:



http://asciimath.org/

The marked up price pi in the child table is the cost ci times the value of the 40 breakpoint Markup function evaluated using the sum of all the costs ci.

A suggestion would be to have 2 tables: 

Table 1: Purchases
Table 2: Items

Set up 1 relationship: 
1 purchase to many Items

In the Items table, create a field called cost. 
In the relationship table, create a summary field called total cost. 
In the purchases table, create a formula-number field called markup with the following formula: 

If(([Total cost]/50)<1,0,
If(([Total cost]/50)>=1,[Total cost]/50))

the "[Total cost]/50))" section would be based on the what you'd prefer to increment by. 

for example:
If you'd prefer to have a markup of 50% for every 50 dollars, it would be ([Total cost]/50)*.5 

You'd use this instead: 

If(([Total cost]/50)<1,0,
If(([Total cost]/50)>=1,([Total cost]/50)*.5))

In the purchase table, create a formula-number field called final price with the following formula: 

[markup]+[Total cost]

Now, you'd be able to add multiple items to a purchase and apply a markup to the total amount when the total amount reaches a limit. 

Without the 40 lined case formula, you'd loose the ability to control each level individually. 

With the 40 option Case or If statements, it seems like I would still need to create 80 or 120 fields in either the child or the parent to pull in all the options to be able to test against.  Is there anyway around adding all these fields to parent of the Markup table?  Just for the record, the markup % do not increment in a set pattern at this point but this is something that I might try to change if there is no easy method to acquiring the markup % from a separate table.

Roger, the IF statement which calculate which One markup to pull in.  ie you are calculating the value for [Related Markup]

So you are not bringing in all 40 levels, just the correct level.

So, my if statement will basically be matching the markup table, ie if(total > 10, 3, if (total > 20, 4, etc.  where 3 is the correct record id # in the Markup table.  So I am basically building the formula to match the markup table.  I can alter the % markup on the Markup table without changing the formula but not the total, ie if I want to change the 10 above 11, I would need to change it in the Markup table and the formula to keep everything working properly.  Is this correct?

Yes you are calculation the record ID#, but my suggestion is to make a new numeric field called [Markup Level] and make that the Key field of the markup table.  The you will calculate the markup level with the 40 line IF statement.

As for this part of your question

I want to change the 10 above 11,

I did not understand that question.

Also pay attention to my other suggestion that you don't want historical pricing to be affected when you change the discount levels in your master Markup table..

When the order has been invoiced, you would then trigger a native Action to copy the values from the markup lookup value into the frozen lookup value so that it would not change if you changed your discount structure in the future.  so the formula for the markup would actually be to use the frozen value if there is one, else use the lookup, and that is what would be passed down to the child records. 

I appreciate all the help.  I ended up breaking my markup chart into 6 levels with a granulated scale for each level, ie min amount, max amount, qty of options within the level and the starting markup rate for that level.  I created a Markup Table with a child table for my Markup Details.  I then select the correct Markup record in my Estimate table and pull in some related Markup lookup fields into this Estimate record.  I pull some of these same fields into my Estimate Detail table and then have a relationship between my Estimate Detail table and my Markup Detail table to pull in the factors required to determine the correct markup for each record in my Estimate Detail table.  Everything seems to be working as expected.  This method allows for a different markup table to used in the future without affecting past records while not having to have 40-80 lookup fields within the Estimate or Estimated table.