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 | ||
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 | 1 for X>0, 0 for X=0, -1 for X<0 | |
SIN | X | Sine | |
SINH | X | Hyperbolic Sine | |
SQR | X | Square of X = X * X | |
SQRT | X | Square Root of X | |
TAN | X | Tangent | |
TANH | X | Hyperbolic Tangent |
Boolean Functions
Function | Parameters | Returns… |
IsNumber | S | True if the String S is a valid number, otherwise returns False |
IsAssigned | VariableName | True if the variable name is assigned to the product, otherwise returns false |
IsAssigned | Object Reference | True if the object reference can be resolved, otherwise returns False (e.g. if referencing Company.ShippingAddress.Country but no customer is assigned to the order, the object reference cannot be resolved |
String Functions
Function | Parameters | Returns… |
CharCount | S | Number of characters in a string (same as Len) |
Left | S, Len | Left Len characters from S. If S is smaller than Len, the entire string is returned |
Len | S | Number of characters in a string (same as CharCount) |
LineCount | S | Number of lines in a string (ignores empty lines) |
Lower | S | S converted to lower case |
LowerCaseCount | S | Number of lower case lines in a string |
Mid | S, Start [, Len] | Substring from S starting at position Start for Len characters. If Len is omitted, the entire rest of the string is returned |
Pos | S, T [, Pos] | Position of substring T in string S, starting at character Pos (or the first character if not specified) |
PunctuationCount | S | Number of non-letter and non-number in a string |
Replace | S, OldPattern, NewPattern [, ReplaceAll [, IgnoreCase]] | Replaces occurrences of OldPattern in S with NewPattern. If ReplaceAll is False or not supplied, only the first occurrence is replaced. Is IgnoreCase is TRUE or not supplied, the case is ignored. |
Right | S, Len | Right Len characters from S. If S is smaller than Len, the entire string is returned |
ToNumber | S [, Default] | Converts the String S to a Number. If S is not a valid number, the value Default is returned instead. If no default value is given, an error is generated if S is not a valid number. |
ToMoney | X | Converts a number to a string formatted to match the local currency settings specified in Windows |
ToString | X, Y | Converts the number X to a String, displaying Y decimal places |
Trim | S | String S stripped of any leading or trailing white space |
Upper | S | S converted to upper case |
UpperCaseCount | S | Number of upper case letter and numbers in a string |
WordCount | S | Number of words in a string (separated by spaces or line breaks) |
DateTime Functions
Since Microsoft has changed the date and time format used in the new .NET architecture, it is strongly recommended that all date and time manipulation be accomplished using CFL functions and not numeric operators.
Function | Parameters | Returns… |
DateTime | None | Returns the current DateTime |
DateTime | S or X | String S converted into a DateTime (standard CFL formatting) or the number X converted into a DateTime |
DateTime | Y, M, D | Creates a DateTime from the Year (Y), Month (M), and Day (D) supplied |
DayOfMonth | D | Day of the Month from the DateTime D |
DayOfWeek | D | Day of the Week (as a number 0-7) from the DateTime D |
DecDay | X [ , I ] | Value of DateTime X decremented by I Days. If “I” is omitted, defaults to 1 |
DecHour | X [ , I ] | Value of DateTime X decremented by I Hours. If “I” is omitted, defaults to 1 |
DecMinute | X [ , I ] | Value of DateTime X decremented by I Minutes. If “I” is omitted, defaults to 1 |
DecYear | X [ , I ] | Value of DateTime X decremented by I Years. If “I” is omitted, defaults to 1 |
DisplayDate | D | Date of D as a string |
DisplayDateTime | D | Date and time of D as a string |
DisplayDay | D | Day of the week as a string |
DisplayLongDate | D | Date of D formatted as a string using the regional settings for Long Date |
DisplayLongDateTime | D | Date and time of D formatted as a string using the regional settings for Long Date |
DisplayLongTime | D | Time of D formatted as a string using the regional settings for Long Date |
DisplayTime | D | Time of D as a string |
IncDay | X [ , I ] | Value of DateTime X incremented by I Days. If “I” is omitted, defaults to 1 |
IncHour | X [ , I ] | Value of DateTime X incremented by I Hours. If “I” is omitted, defaults to 1 |
IncMinute | X [ , I ] | Value of DateTime X incremented by I Minutes. If “I” is omitted, defaults to 1 |
IncYear | X [ , I ] | Value of DateTime X incremented by I Years. If “I” is omitted, defaults to 1 |
MonthName | X | Name of the Xth month in the year (January, February, …) |
MonthOfYear | D | Month of the Year (as a number 0-12) from the DateTime D |
Today | None | Current date and time. |
WeekdayName | X | Name of the Xth day of the week (Sunday, Monday, …) |
Year | D | Year from the DateTime D |
String and HTML Formatting Functions
String Formatting functions are advanced tools to assist in the creation of strings for product layouts. HTML Formatting functions are designed to simplify the generation of HTML code by wrapping a string value with the appropriate HTML tags.
Function | Parameters | Returns… |
HTMLBold | S | Makes text bold |
HTMLBullet | None | HTML character for a bullet |
HTMLBulletList | S1, S2, … | An unordered (bullet) list of unlimited length made up of the strings passed. If any strings are empty, they are omitted |
Char | X | Returns the ASCII character of X |
DisplayModifierValue | S | Returns the value of modifier named S |
DisplayVariableUnits | V | Returns the unit of variable V |
DisplayVariableValue | V | Returns the value of variable V with units |
DisplayVariableValue | ? | Rounds the returned value of variable with units |
DisplayVariableValueNoUnits | V | Returns the value of variable V without units |
DisplayVariableValueNoUnits | ? | Rounds the returned value of variable without units |
HTMLListItem | S | Returns a string in either a bullet or number list |
HTMLNumberList | S1, S2 … | Creates a numbered list of unlimited length |
HTMLReturn | None | Returns an HTML line break (return). |
TabChar | None | ASCII tab character |
HTMLFont | S (Font [size], [color]) | Sets the font face, size and color of a string |
HTMLWrapLines | S | Returns a string with multiple lines formatted for HTML (with HTML line breaks) |
OpenBrowser | URL, OpenInternal | Opens a web browser with the specified URL. The second parameter is optional and indicates if the browser should be opened in Control or externally using the default Windows browser. The default value of the second parameter if not specified is True (internal). |
SQL Functions
SQL functions are advanced tools to assist in retrieving values directly from the database. SQL functions are ran asynchronously in the background during calculations. When a SQL function is called, the TemporaryValue parameter value is immediately returned and SelectSQL statement is handed to a background thread. Once the SelectSQL statement has finished executing, the line item is recalculated using the value of the first column of the first row of records returned from the SQL statement. The value is stored in the line item and a flag is set to prevent the SQL statement from executing again.
* SQL Functions will be available in versions 04.50.1103.1801 and later.
Function | Parameters | Returns… |
ReturnSQLStringValue | SelectSQL, TemporaryValue | The TemporaryValue the first time the function is called. Once the SelectSQL has finished executing, the value of the first column in the first row of records returned from the SelectSQL is returned. Note: This function is deprecated in versions 6.1 and later. |
ReturnSQLNumberValue | SelectSQL, TemporaryValue | The TemporaryValue the first time the function is called. Once the SelectSQL has finished executing, the value of the first column in the first row of records returned from the SelectSQL is returned. Note: This function is deprecated in versions 6.1 and later. |
ReturnSQLStringValueWithWait | SelectSQL, TemporaryValue | Once the SelectSQL has finished executing, the value of the first column in the first row of records is returned. If there are no records found, the TemporaryValue is returned. |
ReturnSQLNumberValueWithWait | SelectSQL, TemporaryValue | Once the SelectSQL has finished executing, the value of the first column in the first row of records is returned. If there are no records found, the TemporaryValue is returned. |
ExecSQL | SQLCommand | Executes a SQL command without a return value. Allows execution of Insert and Update commands. |
LoadFromURL | URL, RaiseErrorOnFailure=True, TimeOutInMS=1000 | Returns the result of the URL endpoint. Triggers external events, though it could be used to load data from an API. |
Example of a Select SQL statement:
SELECT AccountNumber FROM Account WHERE CompanyName = TransHeader.CustomerName
Example of a Update SQL statement:
UPDATE Account SET AccountNumber = 0 WHERE CompanyName = TransHeader.CustomerName
Variable Property Functions
Variable Property functions are used to read and write additional properties on a variables.
Function | Parameters | Returns… |
SetVariableProperty | VariableName, PropertyName, Value | No value. This function sets the variable property PropertyName on variable VariableName to Value. No value is returned. |
GetVariablePropertyValue | VariableName, PropertyName, DefaultValue | The numeric or boolean variable property named PropertyName on variable VariableName is returned if it exists, otherwise the DefaultValue is returned. |
GetVariablePropertyText | VariableName, PropertyName, DefaultValue | The text variable property named PropertyName on variable VariableName is returned if it exists, otherwise the DefaultValue is returned. |
Utility Functions
Function | Parameters | Description |
CMD | Command, WorkingDir, RunElevated | CMD(Command: string, WorkingDir: string, RunElevated: boolean); This function runs a Command Shell. The Command parameter contains the CMD to run. The WorkingDir parameter contains the folder the Command Shell starts in. If not specified, this defaults to the folder Control was launched from. The RunElevated parameter is used to determine if the process is run with administrative privileges. |
RunMacro | MacroName, ID, ClassTypeID | RunMacro( MacroName : string, ID : int, ClassTypeID : int ); This function executes a particular macro. The MacroName parameter is the text name of the Macro to be executed. The ID is the ID of the record instance passed into the Macro. The ClassTypeID is the ClassTypeID of the record instance passed into the Macro. |
Layout Specific Functions
CFL has several functions are not general purpose (as those above) but specific to layout of pages and signs on a roll or sheet. Though the calculation can be (and has been) done using the general formulas, these occur so often that CFL has special functions to manage them.
These are complex functions that perform many calculations and set many variables.
Shipping Functions
Function | Parameters | Returns… |
ShippingCostEstimated | ||
ShippingCostActual | ||
ShippingCount | ||
ShippingTrackingNumbers | ||
ShippingItems[Index].ActualCost | ||
ShippingItems[Index].CarrierName | ||
ShippingItems[Index].CarrierService | ||
ShippingItems[Index].ContactName | ||
ShippingItems[Index].Account | ||
ShippingItems[Index].AccountName | ||
ShippingItems[Index].EmailAddress | ||
ShippingItems[Index].EstimatedCost | ||
ShippingItems[Index].IsShipped | ||
ShippingItems[Index].InsuredValue | ||
ShippingItems[Index].Notes | ||
ShippingItems[Index].NumberofPackages | ||
ShippingItems[Index].PackageSize | ||
ShippingItems[Index].PackageWeight | ||
ShippingItems[Index].ShippedDate | ||
ShippingItems[Index].ShippedDueDate | ||
ShippingItems[Index].ShippedDueDateOV | ||
ShippingItems[Index].ShipLineItemsXML | ||
ShippingItems[Index].ShipmentNumber | ||
ShippingItems[Index].ShipToCompany | ||
ShippingItems[Index].ShipToContact | ||
ShippingItems[Index].ShipToPhoneText | ||
ShippingItems[Index].ShipToEmailAddress | ||
ShippingItems[Index].ShipToAddressText | ||
ShippingItems[Index].Summary | ||
ShippingItems[Index].TrackingNumber |
Obsolete Functions
Deprecated functions are those that are currently supported for backwards compatibility, but may not be supported in the future. Users should avoid using these obsolete functions and should convert any formulas using these functions. Deprecated functions are normally removed from the CFL after one or two major releases, so using them is risky.
Function | Parameters | Convert From |
CHS | X | chs(arg) = -arg change sign |
Concat | S, T, … | Concatenates (combines) two or more strings |
CUBE | X | X * X * X |
FromCentimeters | X | Centimeters to Internal Units |
FromCuCentimeters | X | CuCentimeters to Internal Units |
FromCuFeet | X | CuFeet to Internal Units |
FromCuInches | X | CuInches to Internal Units |
FromCuMeters | X | CuMeters to Internal Units |
FromCups | X | Cups to Internal Units |
FromCuYards | X | CuYards to Internal Units |
FromDays | X | Days to Internal Units |
FromFeet | X | Feet to Internal Units |
FromFluidOunces | X | FluidOunces to Internal Units |
FromGallons | X | Gallons to Internal Units |
FromGrams | X | Grams to Internal Units |
FromHours | X | Hours to Internal Units |
FromInches | X | Inches to Internal Units |
FromKilograms | X | Kilograms to Internal Units |
FromLiters | X | Liters to Internal Units |
FromMeters | X | Meters to Internal Units |
FromMilligrams | X | Milligrams to Internal Units |
FromMilliliters | X | Milliliters to Internal Units |
FromMillimeters | X | Millimeters to Internal Units |
FromMinutes | X | Minutes to Internal Units |
FromOunces | X | Ounces to Internal Units |
FromPints | X | Pints to Internal Units |
FromPounds | X | Pounds to Internal Units |
FromQuarts | X | Quarts to Internal Units |
FromSeconds | X | Seconds to Internal Units |
FromSqCentimeters | X | SqCentimeters to Internal Units |
FromSqFeet | X | SqFeet to Internal Units |
FromSqInches | X | SqInches to Internal Units |
FromSqMeters | X | SqMeters to Internal Units |
FromSqMillimeters | X | SqMillimeters to Internal Units |
FromSqYards | X | SqYards to Internal Units |
FromTons | X | Tons to Internal Units |
FromYards | X | Yards to Internal Units |
IIF | X, Y, Z | IIF(X, Y, Z if X<> 0 then return Y else return Z |
InCentimeters | X | Internal Units to Centimeters |
InCuCentimeters | X | Internal Units to CuCentimeters |
InCuFeet | X | Internal Units to CuFeet |
InCuInches | X | Internal Units to CuInches |
InCuMeters | X | Internal Units to CuMeters |
InCuMillimeters | X | Internal Units to CuMillimeters |
InCups | X | Internal Units to Cups |
InCuYards | X | Internal Units to CuYards |
InDays | X | Internal Units to Days |
InFeet | X | Internal Units to Feet |
InFluidOunces | X | Internal Units to FluidOunces |
InGallons | X | Internal Units to Gallons |
InGrams | X | Internal Units to Grams |
InHours | X | Internal Units to Hours |
InInches | X | Internal Units to Inches |
InKilograms | X | Internal Units to Kilograms |
InLiters | X | Internal Units to Liters |
InMeters | X | Internal Units to Meters |
InMilligrams | X | Internal Units to Milligrams |
InMillimeters | X | Internal Units to Millimeters |
InMinutes | X | Internal Units to Minutes |
InOunces | X | Internal Units to Ounces |
InPints | X | Internal Units to Pints |
InPounds | X | Internal Units to Pounds |
InQuarts | X | Internal Units to Quarts |
InSeconds | X | Internal Units to Seconds |
InSqCentimeters | X | Internal Units to SqCentimeters |
InSqFeet | X | Internal Units to SqFeet |
InSqInches | X | Internal Units to SqInches |
InSqMeters | X | Internal Units to SqMeters |
InSqMillimeters | X | Internal Units to SqMillimeters |
InSqYards | X | Internal Units to SqYards |
InTons | X | Internal Units to Tons |
InYards | X | Internal Units to Yards |
OverallMinimum | None | Renamed to SubtotalMinimum |
POWER | X, Y | Raise X to a power of Y (X must be greater than 0) |
PRODUCT | X, Y, .. | Product of arguments. UNLIMITED arguments |
SUM | X, Y, .. | Sum of all arguments. UNLIMITED arguments |
TableValue | VariableName or “VariableName” | Returns the value of the Lookup Table of the Variable specified by the Parameter. Note: Just using VariableName returns the same value |
VariableValue | VariableName or “VariableName” | Returns the value of the variable. This is the same as just using the variable name |