2 Replies Latest reply on Oct 3, 2012 1:22 PM by user20843

    Case function alternatives

    user20843

      Title

      Case function alternatives

      Post

           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

        • 1. Re: Case function alternatives
          philmodjunk

               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);
                       ....)

               As far as I know there is no effective limit to the number of Boolean expressions you can use in Case, but this does look like something better off implemented with a table look up instead.

               Consider the following related table used to to replace the above case function:

               ProductID           Expression (Field of type text)
               1362                  Quotes::Total Weeks
               1119                  Quotes::Core_m*Quotes::Percent Livingstone * 2
               1642                  Round ( Quotes::TotalWeeks * (( Quotes::Water Depth_m + 6 ) / 1.5 ) + 0.499999999 ; 0 )

               Then you can add a text field, Expression to your original table that looks up the Expression text and then this calculation will replace your Case function:

               Evaluate ( Expression )

               (You can also forgo the looked up value field and just use: Evaluate ( RelatedTable::Expression ) , but then subsequent changes to expressions in this table will change the computed value in existing quotes--which is probably not what you would want here.)

          • 2. Re: Case function alternatives
            user20843

                 Thanks that formula is the piece I was missing!