# Differences

This shows you the differences between two versions of the page.

Both sides previous revision Previous revision | |||

cfl_ch_3-formulas_and_functions [2019/07/15 19:38] admin [SQL Functions] |
cfl_ch_3-formulas_and_functions [2019/07/15 20:48] (current) scotts |
||
---|---|---|---|

Line 264: | Line 264: | ||

==== Numeric Functions ==== | ==== Numeric Functions ==== | ||

- | |**Function** |**Parameters** |**Description** | | + | |**Function** |**Parameters** |**Description** | | |

- | |ABS|X|Absolute value of X| | + | |ABS|X|Absolute value of X| | |

- | |ACOS|X|Arccosine| | + | |ACOS|X|Arccosine| | |

- | |ACOSH|X|Hyperbolic Arccosine| | + | |ACOSH|X|Hyperbolic Arccosine| | |

- | |ASIN|X|Arcsine| | + | |ASIN|X|Arcsine| | |

- | |ASINH|X|Hyperbolic Arcsine| | + | |ASINH|X|Hyperbolic Arcsine| | |

- | |ATAN|X|Arctangent| | + | |ATAN|X|Arctangent| | |

- | |ATANH|X|Hyperbolic Arctangent| | + | |ATANH|X|Hyperbolic Arctangent| | |

- | |BINOM|N, K|Binomial Coefficient BINOM(N, K)=N!/(K!*(N-K)!)| | + | |BINOM|N, K|Binomial Coefficient BINOM(N, K)=N!/(K!*(N-K)!)| | |

- | |COS|X|Cosine| | + | |COS|X|Cosine| | |

- | |COSH|X|Hyperbolic Cosine| | + | |COSH|X|Hyperbolic Cosine| | |

- | |COTAN|X|Cotangent| | + | |COTAN|X|Cotangent| | |

|EXP|X|e\^X. Same as POWER( LN2, X )| | |EXP|X|e\^X. Same as POWER( LN2, X )| | ||

- | |FACT|X|Factorial Function X!=X*(X-1)*(X-2)*...*1| | + | |FACT|X|Factorial Function X!=X*(X-1)*(X-2)*…*1| | |

- | |FRAC|X|Extract Fractional Part| | + | |FRAC|X|Extract Fractional Part| | |

- | |INT|X|Extract Integer Part| | + | |INT|X|Extract Integer Part| | |

- | |LG|X|Base 10 Logarithm| | + | |LG|X|Base 10 Logarithm| | |

- | |LN|X|Natural Logarithm| | + | |LN|X|Natural Logarithm| | |

- | |LN2| |Natural log constant| | + | |LN2| |Natural log constant| | |

- | |MAX|X, Y, ..|Maximum value of arguments. UNLIMITED arguments| | + | |MAX|X, Y, ..|Maximum value of arguments. UNLIMITED arguments| | |

- | |MIN|X, Y, ..|Minimum value of arguments. UNLIMITED arguments| | + | |MIN|X, Y, ..|Minimum value of arguments. UNLIMITED arguments| | |

- | |PI| |PI number (3.14159... – constant)| | + | |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.| | + | |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 )| | + | |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| | + | |SIGN|X|1 for X>0, 0 for X=0, -1 for X<0| | |

- | |SIN|X|Sine| | + | |SIN|X|Sine| | |

- | |SINH|X|Hyperbolic Sine| | + | |SINH|X|Hyperbolic Sine| | |

- | |SQR|X|Square of X = X * X| | + | |SQR|X|Square of X = X * X| | |

- | |SQRT|X|Square Root of X| | + | |SQRT|X|Square Root of X| | |

- | |TAN|X|Tangent| | + | |TAN|X|Tangent| | |

- | |TANH|X|Hyperbolic Tangent| | + | |TANH|X|Hyperbolic Tangent| | |

==== Boolean Functions ==== | ==== Boolean Functions ==== | ||

Line 326: | Line 326: | ||

==== DateTime Functions ==== | ==== 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. \\ | + | |

+ | 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…** | | |**Function** |**Parameters** |**Returns…** | | ||

Line 356: | Line 357: | ||

==== String and HTML Formatting Functions ==== | ==== 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. | 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. | ||

Line 379: | Line 381: | ||

==== SQL Functions ==== | ==== 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 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// a//vailable in versions 04.50.1103.1801 and later.// |

- | \\ | + | |

- | //* SQL Functions will be// a//vailable in versions 04.50.1103.1801 and later.// | + | |

|**Function** |**Parameters** |**Returns…** | | |**Function** |**Parameters** |**Returns…** | | ||

Line 389: | Line 389: | ||

|**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.| | |**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.| | |**ExecSQL** |SQLCommand|Executes a SQL command without a return value. Allows execution of Insert and Update commands.| | ||

- | |**LoadFromURL** |URL, RaiseErrorOnFailure(Optional), TimeOutInMS(Optional)|Used 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 )| | ||

- | \\ | + | \\ 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** |

- | 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 ==== | ||

- | Variable Property functions are used to read and write additional properties on a variables.\\ | + | |

+ | Variable Property functions are used to read and write additional properties on a variables. | ||

|**Function** |**Parameters** |**Returns…** | | |**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.| | |**SetVariableProperty** |VariableName, \\ PropertyName, \\ Value|No value. This function sets the variable property PropertyName on variable VariableName to Value. No value is returned.| | ||

Line 409: | Line 406: | ||

|**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.| | |**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.| | |**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.| | ||

+ | |**LoadFromURL** |URL, RaiseErrorOnFailure(Optional), TimeOutInMS(Optional)|Used 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 )| | ||

+ | | | | | | ||

- | ==== Layout Specific Functions ==== | + | ==== 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.\\ | + | 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. |

- | These are complex functions that perform many calculations and set many variables. | + | |

- | * See [[CFL Function - CalculateLayoutOnRoll|]] | + | * See [[:cfl_function_-_calculatelayoutonroll|CFL Function - CalculateLayoutOnRoll]] |

- | * See [[CFL Function - CalculateLayoutOnSheet|]] | + | * See [[:cfl_function_-_calculatelayoutonsheet|CFL Function - CalculateLayoutOnSheet]] |

+ | <code> | ||

\\ | \\ | ||

- | |||

- | |||

===== Shipping Functions ===== | ===== Shipping Functions ===== | ||

+ | </code> | ||

|**Function** |**Parameters** |**Returns…** | | |**Function** |**Parameters** |**Returns…** | | ||

Line 456: | Line 454: | ||

===== Obsolete Functions ===== | ===== 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. | 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. | ||