AnsweredAssumed Answered

Case function alternatives

Question asked by user20843 on Oct 3, 2012
Latest reply on Oct 3, 2012 by user20843


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?  

     Thank you