This page describes our approach to using the Part Price in a selection list so that any changes or updates to the part's price are reflected in any ListPriceValue or ListCostValue calculations made for the row in the list for which the part is used. This discussion assumes a working knowledge of how lists work. For detailed discussion of Cyrious lists see pricing_ch_06-selection_lists.

We wanted to use the fixed cost value for a part as the ListPriceValue in a selection list, replacing manual $$ entries in the list. This straight lookup is simple but we also need to convert the value from a square foot cost to an “equivalent” sheet cost.

Every selection_list|Selection Lists row in Control have columns labeled Price F(x) and Cost F(x). These are called the List Price Value and the List Cost Value. This technique relies on the following aspects of the List Price Value:

  • In CFL, you can use the functions ListPriceValue(“Variable”) and ListCostValue(“Variable”) to access the value of these values.
  • The ListPriceValue can be entered as a number or a formula. We will use the formula version in this example. (You can also have it look up the price/cost directly from the linked parts by choosing the appropriate selection in the “Price Lookup” and “Cost Lookup” columns.)

This example uses concepts which assume a reasonable familiarity with the Parts setup and list setups. Before using these concepts be sure that the part in question is not being priced or costed in other formulas which are using the numerous costing options for a part, or you will create erroneous results for other products.

  1. For this example, we will assume a 4 x 8 ft sheet of PVC for which our costing formulas are based on square inch pricing and our actual purchasing is based on sheets. In the consumption tab we set the Unit Type to Each and the Stocking Unit to Sheet. We set the rounding to 0 and the Custom Rounding to the smallest possible unit. We use 1/10000.
    • Note: Don't choose custom rounding and enter “0”. This means to round to no places which will not give you what you want!
  2. For the consumption formula we include two types. The Default is Each, Sheet, and the Consumption formula is 1. We use the New Item tab to add a second formula based on Area. Clicking the New Item tab we select Area as the Unit Type and Sq Feet as the Unit. For the “One Sq Foot equals” formula we enter 1/(4*8). And for the default consumption formula we enter Quantity * Area in SqFeet.
  3. Next, we go to the Part Cost tab and make sure that we have a fixed price entered and it is not greyed out.
  4. Next we go to the Selection List where we want to use this part price. If not already there, we insert the pricing_ch_04-parts from the Insert Column selection choices. That will cause several columns to be added to your list. The first is the part, and when you click on the little box to the right, it will give you the browser tree to drill down and find the Part you want to add. Select that Part and it will be added to your list row. Next you need to tell it what the consumption unit is. Here we enter a 1. The next column is the Consumption Unit you wish to use. For this example we created a part with two Unit types - Sheet and square Inches. Your choice here depends on what your ListPrice value expects. If it expects a full sheet price, select sheet. Note that this would not work for other sheet sizes as Cyrious would deliver the price for a 5 x10 and your list price is assuming that it is 4 x 8. For that you would have to include an adjustment formula in your list price calculation. We solve that problem by retrieving the Square Foor price and multiplying it by 32 sq feet. (Because of Cyrious rounding formulas which we have not found a way to bypass, we find that Square Feet is the smallest unit we can use which returns a price close to the actual price.)
  5. Now go to your List Price column labelled Price(fx). You have several formulas you can enter to retrieve your part. If you have chosen the Default unit, in this case Sheets, the PartBasedPrice code will return the correct number. If you are using an alternate unit, we have found that retrieving the cost from one of the cfl_ch_6-property_references – the PartByVariableName.PartName – gets the result we are seeking. The best way to get that is to click on the Rename button in the Part Setup and copy the name there. The variable we use for the list with the parts is SubstrateThickness. The code to retrieve that information is PartByVariableName(SubstrateThickness“,”Part Name“).EstimatedCost. Note that the variable name and the Part Name in this code are in quotation marks. You won't get an error if you don't use quotation marks, but it won't return the price. Finally, we don't want the program to crash or throw out a lot of errors if the part isn't found, so we use a CFL Code ISASSIGNED() to trap that situation. With this addition, the formula will return 0 if the part is not found. Finally, to make sure that we are creating a price for a 4 x 8 Sheet, regardless of the Part Sheet Size, we multiply the square foot price by 32. The resultant code for 1.16th inch Acrylic is:

code format="vb"

IF ISASSIGNED(PartByVariableName(“SubstrateThickness”)) THEN

PartByVariableName("SubstrateThickness", "0.06 Piedmont CLEAR 48x96") * 32

ELSE

ENDIF

6. If you have set all the variables in the Part and list as described above, this formula will now return the price for the equivalent of a 4 x 8 sheet, regardless of the sheet size.

Contributor: Steve Gillispie, Acorn Sign Graphics Date: 07/15/2009 Version: Control 4.3_

You could leave a comment if you were logged in.