Describes how to use the same table for different settings in the same product.

We want to use tables to return the letters per minute which can be cut, weeded, and transfer tape applied. When we provide the option for 4 different letter sizes in the same product. However, each table in Cyrious is created for specific variables, but we don't want to manage 4 separate tables that contain the same information.

The solution is to create a table (using one of the values, such as LetterHeight as the column variable) and the letters per minute are the row variables. We then override the LetterHeight variable and lookup the price for each size entered in the pricing form.

  • Tables we had created before Version 4.3 did not work with this function. (Not confirmed by Cyrious.)
  • You can not use Local Variables for the Table Row Number Expression.
  • You can use formulas with existing variables. For example, LookupTableValue(“VinylCutTimes”,CHARCOUNT(Text)) did return the correct value for the number of letters in the Text variable.
  • If you are passing a Letter as the column name instead of a number, it appears that in 6.0+ you need to concatenate ASCII values in the variable. This worked for columns named “P” or “PA”. It would not accept quoted letters - E.g. “T”, but did return the correct value for the column with Char(084). [added 11/28/2018]
  1. Create/Identify the varibles to capture the letter height and number of letters for each size.
    • Our Height variables are LetterSize1Height, LetterSize2Height, LettersSize3Height and LetterSize4Height. The Number of Letters are captured with the variables, LetterSize1_NoOfLtrs, etc.
  2. Create a Variable to Capture The Total Time.
    • For example, to capture the total weeding time we created a variable called VinylWeedCost.
  3. Use the LOOKUPTABLEVALUE function to access the tables values with the appropriate parameters.
    • For the VinylWeedCost variable we open the CFL Formula Builder. The function to access the correct row in a table from a CFL formula is LOOKUPTABLEVALUE(“TableName”,“VariableName”). With the Declare function we create a local variable to capture the weed rate for each letter size. That produces the letters per minute required to weed each letter size which we then multiply by the number of letters for a total time to weed that letter size. Using a User Constant for the weeding center hour rate (VinylHrRate in the example below) we calculate the labor cost of weeding those letters.
  4. Below is the code to return the weed cost for a product with up to 4 letter sizes.
Declare Size1WeedRate:=  LOOKUPTABLEVALUE( "WeedRate", LetterSize1Height  ) ;
Declare Size1WeedTime := If  Size1WeedRate > 0 THen  Size1WeedRate *  ( LetterSize1_NoOfLtrs ) Else 0 Endif;
Declare  Size2WeedRate :=  LOOKUPTABLEVALUE( "WeedRate",LetterSize2Height  ) ;
Declare  Size2WeedTime := If Size2WeedRate > 0 THEN Size2WeedRate * (LetterSize2_NoOfLetters)   Else 0 Endif ;
Declare  Size3WeedRate :=  LOOKUPTABLEVALUE( "WeedRate",LetterSize3Height ) ; 
Declare  Size3WeedTime :=  IF Size3WeedRate > 0 THEN Size3WeedRate * ( LetterSize3_NoOfLtrs) ELSE 0 ENDIF;
Declare  Size4WeedRate :=  LOOKUPTABLEVALUE( "WeedRate",LetterSize4Height) ;
Declare  Size4WeedTime := IF Size4WeedRate  > 0 THEN Size4WeedRate *  (LetterSize4_NoOfLtrs )  ELSE 0 ENDIF; 
//Calculate total weed time  in product for all sizes
Declare TotalWeedTime := Size1WeedTime + Size2WeedTime + Size3WeedTime + Size4WeedTime ;
//CAlculate price by multiplying total weed time by user constant vinyl hour rate
IF TotalWeedTime > 0 THEN ((TotalWeedTime )/60) * VinylHrRate ELSE 0 ENDIF;

Contributor: Steve Gillispie

Date: 7/25/2009

Version: Control 4.3

You could leave a comment if you were logged in.