This is an old revision of the document!
The CFL Formula Builder is a powerful visual aide that helps complete and verify expressions. While typing a formula, the CFL checks the entry to assess the completeness, color-codes the information input, and offers suggestions as needed.
The default formatting scheme for the formula editor is:
| Category | Color | Attributes | |||
| Comments | Gray | Italics | |||
| Functions | Green | Bold | |||
| Global Constants | Blue | Bold | |||
| Global Variables | Blue | Bold | |||
| Keywords | Black | Bold | |||
| Number | Blue | Regular | |||
| Operators | Black | Bold | |||
| Quoted String | Blue | Regular | |||
| Temporary Variables | Green | Regular | |||
| Units | Maroon | Bold | |||
| Unknown Items | Green | Bold |
Formulas
Adding and multiplying are useful, but most users want to do more than basic math inside of CFL. To access the full power of CFL, one should make themselves familiar with the Language's built-in functions for complex math, manipulating strings, looking up information, and more.
The built-in formulas are divided into the following categories:
- Company Functions
- Contact Functions
- DateTime Function
- Math Functions
- Part and Cost Lookup
- Price Information Lookup
- SQL Functions
- String Functions
- Table Lookup
- Transaction Functions
- Variable Lookup
There is a last category, Obsolete Functions, which users should attempt to avoid. These deprecated functions are maintained for a limited period for backwards compatibility purposes, but are likely to be removed in subsequent versions of CFL.
Functions Associated with Pricing and Products
Cyrious has functions and keywords that return info about pricing and products.
Price Information Lookup
| Function | Parameters | Returns… | |||
| BaseMinimum | None | Minimum base price specified in product setup | |||
| BasePrice | None | Actual base price. If the base price is not overridden, this is the larger of the calculated price, the base minimum, and the per piece minimum times the quantity. If the user overrode this price manually, this value is returned | |||
| BuiltInDiscount | None | Built-in discounts included in the BasePrice. Usually used for Volume Discounts included in the purchase price | |||
| CalcBasePrice | None | Calculated BasePrice, which will equal the PreDiscountPrice less any BuiltInDiscount. This is the BasePrice used for the line item unless it is overridden | |||
| CalcModifierPrice | None | Calculated modifier price In modifier calculations | |||
| DefaultDiscount | None | Calculated default amount of the discount. Unlike the BuiltInDiscount, the DefaultDiscount is normally displayed for the customer to see | |||
| DiscountPrice | None | Actual discount amount for the product (excluding the BuiltInDiscount). If the discount has been overridden, this manually entered value is used. Otherwise, the DefaultDiscount is used | |||
| ModifierPrice | None | Sum of the actual prices for all modifiers in product calculations | |||
| PerPieceMinimum | None | Minimum base price per piece specified in the product setup | |||
| PreDiscountPrice | None | Calculated price of a line item before any built-in discounts | |||
| ProductErrors | Errors from product | ||||
| ProductWarnings | Warnings from product | ||||
| SubtotalMinimum | None | Minimum subtotal price specified in product setup | |||
| SubTotalPrice | None | Larger of the subtotal minimum price and the BasePrice + ModifierPrice – DiscountPrice adjusted for rounding | |||
| UnitVariable | None | String containing the name of the variable used for computing the unit pricing (usually Quantity) |
Error Checking
| Function | Parameters | Returns… | |||
| ProductWarnings | None | Evaluates and Returns the User Defined Warning Message associated with the product. If no user defined error message is present, an empty string is returned. Orders may be saved even with warnings. | |||
| ProductErrors | None | Evaluates and Returns the User Defined Error Message associated with the product. If no user defined error message is present, an empty string is returned. Orders can only be saved until no errors are returned |
Table Lookup
| Function | Parameters | Returns… | |||
| DiscountValue | “TableName” | Value of the DiscountTable “TableName”. The product's variables' values are automatically passed to the discount table for lookup. | |||
| LookupTableValue | “TableName” [ , Row1Value [,ColumnValue [,Row2 ]] | Value of the Lookup (Pricing) Table. The product's variables' values are automatically passed to the lookup table unless overridden by Row1, Column, and Row2. Row1, Column, and Row2 are each optional but must be used in order if specified. |
Variable Information Lookup
| Function | Parameters | Returns… | |||
| FamilyVariableTotal | VariableName | Total value (sum) of the variable in all line items in the same Pricing Family in the current variation | |||
| IsAssignedToParent | “VariableName” | True if the Parameter is assigned to the product, otherwise returns False. Note: Using this function without quotes around the variable name will not generate an error, however, the variable will be automatically added to the product | |||
| IsAssigned | “VariableName” | True if the Parameter is assigned to the product, otherwise returns False. Note: Using this function without quotes around the variable name will not generate an error, however, the variable will be automatically added to the product | |||
| ParentVariableValue | “VariableName”, DefaultValue | Value of the parents' variable name ParameterName if the Parameter is assigned to the parent product. If the product is not a child product or the parameter is not defined in the parent, the function returns the DefaultValue. Note: This function works without quotes around the variable name, however, the variable will be automatically added to the product | |||
| VariableTotal | VariableName | Total value (sum) of the variable in all line items in the current variation |
Variable Setting Functions
| Function | Parameters | Function… | |||
| SetVariables | JSON | This function sets the Variables with the values indicated in the JSON array. For example, SetVariables(' { “Quantity”:5, “Description”: “This is cool stuff!” } ') or SetVariables(' Quantity5') will set the quantity and description property accordingly. |
List Lookup
| Function | Parameters | Returns… | |||
| ListCostValue | VariableName | Value of the CostFx property for the selected value of the list item specified by “Parameter” | |||
| ListPriceValue | VariableName | Value of the PriceFx property for the selected value of the list item specified by “Parameter” | |||
| ListPropertyText | VariableName, “PropertyName” [, DefaultValue] | Same value as in the ListPropertyValue function as a string | |||
| ListPropertyValue | VariableName, “PropertyName” [, DefaultValue] | Value of the PropertyName property for the selected value of the list item specified by Parameter. If ParameterName does not contain a list or PropertyName is not found, the value of DefaultValue is returned |
Line Item Lookup
| Function | Parameters | Returns… | |||
| ActualActivityTotal | None | Actual total time of the associated scheduled product activity. If there is no corresponding activity or the activity is not complete, Zero is returned | |||
| BillableActivityTotal | None | Billable total time of the associated scheduled product activity. If there is no corresponding activity or the activity is not complete, Zero is returned | |||
| IsChildItem | None | True if this line item is a child item, False if this a top-level line item | |||
| WorkAssignmentEndDate | None | End date of the associated scheduled product activity. If there is no corresponding activity, the constant NoDate is returned | |||
| WorkAssignmentStartDate | None | Start date of the associated scheduled product activity. If there is no corresponding activity, the constant NoDate is returned |
Parts and Cost Lookup
| Function | Parameters | Returns sum of the (Estimated) costs of all parts that are of the type… | |||
| EquipmentCost | None | Equipment | |||
| FreightCost | None | Freight | |||
| LaborCost | None | Labor | |||
| MaterialCost | None | Material | |||
| OtherCost | None | Other | |||
| OutsourceCost | None | Outsource | |||
| OverheadCost | None | Overhead | |||
| PartBasedPrice | None | Determined by adding up the price specified in the part itself | |||
| TotalCost | None | All | |||
| Function | Parameters | Returns sum of the (Actual) costs of all parts that are of the type… | |||
| ActualEquipmentCost | None | Equipment | |||
| ActualFreightCost | None | Freight | |||
| ActualLaborCost | None | Labor | |||
| ActualMaterialCost | None | Material | |||
| ActualOtherCost | None | Other | |||
| ActualOutsourceCost | None | Outsource | |||
| ActualOverheadCost | None | Overhead | |||
| ActualTotalCost | None | All |
User Defined Field Lookup Functions
UDF Values for the current Transaction (Order or Estimate), Customer, Contact, and Product can be retrieved in any CFL function. The UDF Value for a Part may be retrieved through the Part Name or the name of the variable that linked to the part.
A default value should always be given. If the UDF is not defined or the associated information not present, and no default value is given, an error message will result. For example, if you use a Customer UDF but attempt to read the value before the customer is assigned to the order you will receive an error message.
To read the value of a UDF use the following functions:
| Function | Parameters | ||
| GetCompanyUDFValue | “UDF Name”[Company ID[, Default Value]] | ||
| GetProductUDFValue | “UDF Name”[, “Product Name”[, Default Value]] | ||
| GetPartUDFValue | “UDF Name”, “Part Name”[, Default Value] | ||
| CompanyUDF | “UDF Name”[, Default Value] | ||
| ContactUDF | “UDF Name”[, Default Value] | ||
| TransactionUDF | “UDF Name”[, Default Value] | ||
| ProductUDF | “UDF Name” [, Default Value] | ||
| PartUDFByVariableName | Variable name, “UDF Name” [, Default Value] | ||
| PartUDFByPartName | “Part Name”, “UDF Name” [, Default Value] |
Returns the value of the UDF name. The Default value is returned if the Company is not assigned or no UDF/Variable/Part with that name exists.
To set the value of a UDF, use the following procedure:
| Function | Parameters | ||
| SetCompanyUDF | “UDF Name”, New Value[, Company ID] | ||
| SetContactUDF | “UDF Name”, New Value | ||
| SetTransactionUDF | “UDF Name” , New Value | ||
| SetProductUDF | “UDF Name”, New Value[, “Product Name”] | ||
| SetPartUDF | “UDF Name”, New Value, “Part Name” |
To clear the value of a UDF, use the following procedure:
| Function | Parameters | ||
| ResetCompanyUDF | “UDF Name”[, Company ID] | ||
| ResetProductUDF | “UDF Name”[, “Product Name”] | ||
| ResetPartUDF | “UDF Name”, “Part Name” | ||
| ResetTransactionUDF | “UDF Name” |
To increment the value of a Number UDF, use the following procedure:
| Function | Parameters | ||
| IncrementTransactionUDF | “UDF Name”[, Amount] | ||
| IncrementCompanyUDF | “UDF Name”[, Amount] | ||
| IncrementContactUDF | “UDF Name”[, Amount] |
If the Amount isn't specified, the value is incremented by one (1).
To decrement the value of a Number UDF, use the following procedure:
| Function | Parameters | ||
| DecrementTransactionUDF | “UDF Name”[, Amount] | ||
| DecrementCompanyUDF | “UDF Name”[, Amount] | ||
| DecrementContactUDF | “UDF Name”[, Amount] |
If the Amount isn't specified, the value is decremented by one (1).
To check if a UDF is assigned:
| Function | Parameters | ||
| CompanyUDFIsAssigned | “UDF Name”[, Company ID] | ||
| ContactUDFIsAssigned | “UDF Name” | ||
| TransactionUDFIsAssigned | “UDF Name” | ||
| PartUDFIsAssigned | “UDF Name”, “Part Name” | ||
| ProductUDFIsAssigned | “UDF Name”[, “Product Name”] |
For Boolean values, returns true if the UDF exists and false otherwise. For Non-Boolean values, returns true if the UDF exists and is assigned, and false if it does not exist or is not assigned.
To check if a UDF is overridden:
| Function | Parameters | ||
| TransactionUDFIsOverridden | “UDF Name” |
Returns true if the UDF overridden and false if not overridden.
To retrieve the parent object of a UDF:
| Function | Parameters | ||
| CompanyUDFObject | “UDF Name”[, Company ID] | ||
| ContactUDFObject | “UDF Name” | ||
| TransactionUDFObject | “UDF Name” |
Returns the object assigned to the specified UDF Name. If the UDF does not exist, or no object is assigned to it, a “Null” value is returned.
To set the ID of a UDF's parent object:
| Function | Parameters | ||
| SetCompanyUDFObjectID | “UDF Name”, ID[, Company ID] | ||
| SetContactUDFObjectID | “UDF Name”, ID | ||
| SetTransactionUDFObjectID | “UDF Name”, ID | ||
| SetProdcutUDFObjectID | “UDF Name”, ID[, “Product Name”] | ||
| SetPartUDFObjectID | “UDF Name”, ID, “Part Name” |
Sets the value used by an object UDF to the ID specified. If the UDF does not exist a warning is logged. If the ID does not exist a message is displayed and the UDF not set. Passing an ID of zero (0) or negative one (-1) will clear the UDF.
Company Functions
With these functions, you can modify and save a Company record:
| Function | Parameters | Notes | |||
| CanEditCompany | [Company ID[, PlaceLock]] | Returns TRUE if the user has the rights to edit the Company and the Company is not being edited by another user. If a Company ID is not supplied, the order's Company will be checked. If TRUE is passed for PlaceLock, a lock will be placed on the Company. Preventing anyone else from locking it. | |||
| SaveCompanyChanges | [Company ID[, ReleaseLock]] | Will save any changes made to a Company. If Company ID is supplied, only that Company will be saved. Otherwise all companies with changes will be saved. If TRUE is passed for ReleaseLock or no value is supplied, the lock will be release on the Company. Allowing other users to lock it. | |||
| CancelCompanyChanges | [Company ID] | Will cancel any changes made to a Company. If Company ID is supplied, only that Company will be canceled. Otherwise all companies with changes will be canceled. The lock will be release on the Company. Allowing other users to lock it. | |||
| SetCompanyProperty | “Property Name”, Value[, Company ID] | Will set a property for a Company to the specified value. If Company ID is supplied, only that Company's property will be set. Otherwise the Order's Company's property will be set. If there isn't a lock placed on the Company, one will be placed. |
Other procedures that will automatically place a lock on a Company include:
- SetCompanyUDF
- SetCompanyUDFObjectID
- ResetCompanyUDF
- IncrementCompanyUDF
- DecrementCompanyUDF
Product Functions
With these functions, you can modify and save a Product record:
| Function | Parameters | Notes | |||
| CanEditProduct | [“Product Name”[, PlaceLock]] | Returns TRUE if the user has the rights to edit the Product and the Product is not being edited by another user. If a Product Name is not supplied, the order's Product will be checked. If TRUE is passed for PlaceLock, a lock will be placed on the Product. Preventing anyone else from locking it. | |||
| SaveProductChanges | [“Product Name”[, ReleaseLock]] | Will save any changes made to a Product. If Product Name is supplied, only that Product will be saved. Otherwise all companies with changes will be saved. If TRUE is passed for ReleaseLock or no value is supplied, the lock will be release on the Product. Allowing other users to lock it. | |||
| CancelProductChanges | [“Product Name”] | Will cancel any changes made to a Product. If Product Name is supplied, only that Product will be canceled. Otherwise all companies with changes will be canceled. The lock will be release on the Product. Allowing other users to lock it. | |||
| SetProductProperty | “Property Name”, Value[, “Product Name”] | Will set a property for a Product to the specified value. If Product Name is supplied, only that Product's property will be set. Otherwise the Item's Product's property will be set. If there isn't a lock placed on the Product, one will be placed. |
Other procedures that will automatically place a lock on a Product include:
- SetProductUDF
- SetProductUDFObjectID
- ResetProductUDF
Part Functions
With these functions, you can modify and save a Part record:
| Function | Parameters | Notes | |||
| CanEditPart | “Part Name”[, PlaceLock] | Returns TRUE if the user has the rights to edit the Part and the Part is not being edited by another user. If TRUE is passed for PlaceLock, a lock will be placed on the Part. Preventing anyone else from locking it. | |||
| SavePartChanges | [“Part Name”[, ReleaseLock]] | Will save any changes made to a Part. If Part Name is supplied, only that Part will be saved. Otherwise all companies with changes will be saved. If TRUE is passed for ReleaseLock or no value is supplied, the lock will be release on the Part. Allowing other users to lock it. | |||
| CancelPartChanges | [“Part Name”] | Will cancel any changes made to a Part. If Part Name is supplied, only that Part will be canceled. Otherwise all companies with changes will be canceled. The lock will be release on the Part. Allowing other users to lock it. | |||
| SetPartProperty | “Property Name”, Value, “Part Name” | Will set a property for a Part to the specified value. Otherwise the Item's Part's property will be set. If there isn't a lock placed on the Part, one will be placed. | |||
| GetInventoryItem | “Part Name”[, WarehouseName] | Returns the Inventory Item for the specified Part. If the WarehouseName is not specified, the Production Warehouse is returned. | |||
| ClonePart | “SourcePartName”, “NewPartName”[, UnitCost] | Clones the part with the named “SourcePartName” and names it “NewPartName”. If the part is tracking costs and a UnitCost is passed in, the new part's Unit Cost will be set to the UnitCost. |
Other procedures that will automatically place a lock on a Part include:
- SetPartUDF
- SetPartUDFObjectID
- ResetPartUDF
General Functions
Cyrious generates a number of functions to evaluate mathematical expressions, manipulate strings, work with dates and times, and format information for display.
Numeric Functions
| Function | Parameters | Description | ||||
| ABS | X | Absolute value of X | ||||
| ACOS | X | Arccosine | ||||
| ACOSH | X | Hyperbolic Arccosine | ||||
| ASIN | X | Arcsine | ||||
| ASINH | X | Hyperbolic Arcsine | ||||
| ATAN | X | Arctangent | ||||
| ATANH | X | Hyperbolic Arctangent | ||||
| BINOM | N, K | Binomial Coefficient BINOM(N, K)N!/(K!*(N-K)!) | ||||
| COS | X | Cosine | ||||
| COSH | X | Hyperbolic Cosine | ||||
| COTAN | X | Cotangent | ||||
| EXP | X | e\ | X. Same as POWER( LN2, X ) | |||
|---|---|---|---|---|---|---|
| FACT | X | Factorial Function X!X*(X-1)*(X-2)*…*1 | ||||
| FRAC | X | Extract Fractional Part | ||||
| INT | X | Extract Integer Part | ||||
| LG | X | Base 10 Logarithm | ||||
| LN | X | Natural Logarithm | ||||
| LN2 | Natural log constant | |||||
| LoadFromURL | URL, RaiseErrorOnFailure(Optional), TimeOutInMS(Optional) | Primary purpose is to trigger external events, though it could be used to load data from an API. S := LoadFromURL( string: URL, boolean: RaiseErrorOnFailure = true, int: TimeOutInMS = 1000 ) | ||||
| MAX | X, Y, .. | Maximum value of arguments. UNLIMITED arguments | ||||
| MIN | X, Y, .. | Minimum value of arguments. UNLIMITED arguments | ||||
| PI | PI number (3.14159… – constant) | |||||
| ROUND | X [, Y] | If Y is omitted, rounds argument to the nearest integer. If Y is present, round to Y places of precision. | ||||
| ROUNDUP | X | Rounds any fraction to the next higher integer number INT( X + 0.99999999 ) | ||||
| SIGN | X | |||||