Case function alternatives
I have three tables Quotes, QuoteLineItems and Products (basic invoice setup). The quotes table contains information used to calculate the qty of products in the QuoteLineItems table. I am currently using a Case function to calculate the qty but seem to have hit the limit of tests I can put in one case statement.
Here is an example
Quotes contains the following fields: WaterDepth_m, Core_m, Total Weeks, percent Livingstone
QuoteLineItems contains the following fields: ProductID, qty_calc
Where qty_calc is a case function:
Case(ProductID=1362; Quotes::Total Weeks; ProductID=1119; (Quotes::Core_m*Quotes::Percent Livingstone)*2; ProductID=1642; Round(Quotes::Total Weeks*((Quotes::Water Depth_m+6)/1.5)+.499999999;0);....) . . . This function goes on an on with different Quotes::fields and factors for each product.
I have about 50 products in my QuoteLineItems template quote that need to have a qty calculated based on the parameters listed in the Quotes table. I seem to have hit a limit in the number of tests I can put in this one case statement. There has to be a more elegant solution than case statements that would allow me to calculate the qty to quote our customers. Can this be done with database relationships, look-ups.... QUESTION: What alternatives are there to using a case function or getting around the limit to the number of tests that can be in a case function?