| CATEGORY |
FUNCTION NAME |
HELP TEXT |
EXAMPLE |
DEFAULTPROTOTYPE |
| Date & Time |
.Date & Time |
Standard date and time functions. |
NULL |
NULL |
| Information |
.Information |
Functions that evaluate a supplied expression or cell reference. |
NULL |
NULL |
| Logical |
.Logical |
Logic functions that return True or False based on a stated condition. |
NULL |
NULL |
| Lookup & Reference |
.Lookup & Reference |
Functions that lookup or reference values in cells or ranges of cells. |
NULL |
NULL |
| Math |
.Math |
Standard mathematical functions that manipulate or return numeric values. |
NULL |
NULL |
| Parse |
.Parse |
Functions that parse and return text values. |
NULL |
NULL |
| Regex |
.Regex |
Regular Expression functions. |
NULL |
NULL |
| Statistical |
.Statistical |
Functions that return statistical information about a list of values or range of cells. |
NULL |
NULL |
| Step Control |
.Step Control |
Functions that allow formula results to control target step flow. |
NULL |
NULL |
| System Functions |
.System Functions |
Functions that return system variables. |
NULL |
NULL |
| Text |
.Text |
Functions that manipulate or return text values. |
NULL |
NULL |
| User Defined Formula |
.User Defined Formula |
All user-defined or compound functions. |
NULL |
NULL |
| Math |
ABS |
Returns the absolute value of a number |
ABS( NumericSourceField ) |
ABS( %s ) |
| Lookup & Reference |
ALIASLIST |
Returns a list of aliases for a string from a lookup table. Use with the IN operator for lookup links. |
ALIASLIST( TextSourceField, "connection", "table", "field" ) |
ALIASLIST( %s, "", "", "" ) |
| Logical |
AND |
Returns True if all arguments are true; returns False if at least one argument is false. |
AND( logical_list ) |
AND( %s ) |
| Text |
ASC |
In DBCS (Far-East) systems, this function returns a copy of text in which the double-byte characters are converted to single-byte characters, if possible |
ASC( SourceField ) |
ASC( %s ) |
| Statistical |
AVERAGE |
Returns the average of the supplied numbers. The result of AVERAGE is also known as the arithmetic mean. |
AVERAGE( number_list ) |
AVERAGE( %s ) |
| Text |
CDATA |
Returns the supplied text as a XML CDATA node. |
CDATA( text ) |
CDATA( %s ) |
| Math |
CEILING |
Rounds a number up to the nearest multiple of a specified significance. |
CEILING( NumericSourceField, significance ) |
CEILING( %s, 2 ) |
| Text |
CHAR |
Returns a character that corresponds to the supplied ASCII code. |
CHAR( NumericSourceField ) |
CHAR( %s ) |
| Lookup & Reference |
CHOOSE |
Returns a value from a list of numbers based on the index number supplied. |
CHOOSE( index, item_list ) |
CHOOSE( , %s ) |
| Text |
CLEAN |
Removes all nonprintable characters from the supplied text. |
CLEAN( TextSourceField ) |
CLEAN( %s ) |
| Text |
CODE |
Returns a numeric code representing the first character of the supplied string. |
CODE( NumericSourceField ) |
CODE( %s ) |
| System Functions |
COMPUTERNAME |
Returns the computer name |
COMPUTERNAME( ) |
NULL |
| Text |
CONCATENATE |
Joins several text strings into one string. |
CONCATENATE( SourceField | "text1", SourceField | "text2", ...) |
CONCATENATE( %s, "" ) |
| Date & Time |
CONVERTDATETIME |
Converts the specified DateTime from one time zone to another, using the specified FromUTCOffset and ToUTCOffsets, which are the standard UTC (GMT) offsets, ex: -5 for Eastern time. Also accounts for Daylight Savings using the specified locations. |
CONVERTDATETIME( DateTime, FromUTCOffset, "US" | "EU" | "AU" | "", ToUTCOffset, "US" | "EU" | "AU" | "" ) |
CONVERTDATETIME( %s, -5, "US", -5, "US" ) |
| Statistical |
COUNT |
Only numerates numbers or numerical values such as logical values, dates or test representations of dates. Empty cells, logical values, text and error values are ignored. Could be used to validate that all source fields are numbers. |
COUNT( SourceField ) |
COUNT( %s ) |
| Statistical |
COUNTA |
Returns the number of cells that contain data. Null values are counted, but empty cells are ignored. Could be used to validate that all source fields at least include null. |
COUNTA( SourceField ) |
COUNTA( %s ) |
| Date & Time |
DATE |
Returns the serial number of the supplied date. |
DATE( year, month, day ) |
DATE( %s ) |
| Date & Time |
DATE2JULIANDATEJDE |
Returns the JD Edwards-formatted Julian date for the supplied Gregorian/common date. |
DATE2JULIANDATEJDE(date) |
DATE2JULIANDATEJDE( %s ) |
| Date & Time |
DATEADD |
Returns a date to which a specified time interval has been added |
DATEADD( Interval, Number, StartDate ) |
DATEADD( "m", 1, %s ) |
| Date & Time |
DATEDIFF |
Returns the number of time intervals between two specified dates |
DATEDIFF( Interval, Date1, Date2 ) |
DATEDIFF( "d", %s, ) |
| Date & Time |
DATEPART |
Returns an number containing the specified part of a given date |
DATEPART( Interval, Date ) |
DATEPART( "d", %s ) |
| Date & Time |
DATETIME2JULIANDATE |
Returns the Julian datetime for the supplied Gregorian/common datetime. |
DATETIME2JULIANDATE(datetime) |
DATETIME2JULIANDATE( %s ) |
| Date & Time |
DATEVALUE |
Returns the serial number of a date supplied as a text string. |
DATEVALUE( SourceField ) |
DATEVALUE( %s ) |
| Date & Time |
DAY |
Returns the day of the month that corresponds to the date represented by the supplied number. |
DAY( serial_number ) |
DAY( %s ) |
| Date & Time |
DAYOFYEAR |
Returns the day of year for the supplied date. |
DAYOFYEAR(date) |
DAYOFYEAR( %s ) |
| Date & Time |
DAYS360 |
Returns the number of days between two dates based on a 360-day year (twelve 30-day months) |
DAYS360( StartDate, EndDate, [Method] ) |
DAYS360( %s, ? ) |
| Text |
DBCS |
Returns a copy of text in which the single-byte characters (if any) have been converted to double-byte characters |
DBCS( SourceField ) |
DBCS( %s ) |
| Lookup & Reference |
DBLOOKUP |
Looks up and substitutes one field for another. Useful to substitute full values for codes or vice versa. The second parameter specifies the name of the database connection to use. |
DBLOOKUP( TextSourceField, "connection", "table", "lookup_field", "substitution_field" ) |
DBLOOKUP( %s, "", "", "", "" ) |
| Lookup & Reference |
DBLOOKUP2 |
Similar to the DBLOOKUP function, except that it accepts two lookup values and fields. |
DBLOOKUP2( SourceValue1, SourceValue2, "connection", "table", "lookup_field1", "lookup_field2", "substitution_field" ) |
DBLOOKUP2( %s, , "", "", "", "", "" ) |
| Lookup & Reference |
DBLOOKUP2NOCACHE |
Similar to the DBLOOKUPNOCACHE function, except that it accepts two lookup values and fields. |
DBLOOKUP2NOCACHE( SourceValue1, SourceValue2, "connection", "table", "lookup_field1", "lookup_field2", "substitution_field" ) |
DBLOOKUP2NOCACHE( %s, , "", "", "", "", "" ) |
| Lookup & Reference |
DBLOOKUPCACHED |
Similar to DBLOOKUP except that the values are cached. The data is read once (on the first row) and not refreshed (for subsequent rows) . This can improve performance for queries whose data doesn't change. |
DBLOOKUPCached( TextSourceField, "connection", "table", "lookup_field", "substitution_field" ) |
DBLOOKUPCached( %s, "", "", "", "" ) |
| Lookup & Reference |
DBLOOKUPDEFAULT |
Similar to DBLOOKUP function, except that it accepts an additional default_value that is returned if no matching record is found. |
DBLOOKUPDEFAULT( TextSourceField, "connection", "table", "lookup_field", "substitution_field", "default_value" ) |
DBLOOKUPDEFAULT( %s, "", "", "", "", "" ) |
| Lookup & Reference |
DBLOOKUPDEFAULT2 |
Similar to the DBLOOKUPDEFAULT function, except that it accepts two lookup values and fields. |
DBLOOKUPDEFAULT2( SourceValue1, SourceValue2, "connection", "table", "lookup_field1", "lookup_field2", "substitution_field", "default_value" ) |
DBLOOKUPDEFAULT2( %s, , "", "", "", "", "", "" ) |
| Lookup & Reference |
DBLOOKUPLIST |
Similar to the DBLOOKUP function, except that it returns all matching values as a comma delimited string. This function works only with databases or adapters that support SQL queries. |
DBLOOKUPLIST( SourceValue, "connection", "table", "lookup_field", "substitution_field" ) |
DBLOOKUPLIST( %s, "", "", "", "" ) |
| Lookup & Reference |
DBLOOKUPLIST2 |
Similar to the DBLOOKUPLIST function, except that it accepts two lookup values and fields. This function works only with databases or adapters that support SQL queries. |
DBLOOKUPLIST2( SourceValue1, SourceValue2, "connection", "table", "lookup_field1", "lookup_field2", "substitution_field" ) |
DBLOOKUPLIST2( %s, , "", "", "", "", "" ) |
| Lookup & Reference |
DBLOOKUPNOCACHE |
Similar to DBLOOKUP except that the values are never cached. The data is read on the execution of each step, even if the input parameters have not changed. This function should only be used if the value to be looked up may change during execution. |
DBLOOKUPNOCACHE( TextSourceField, "connection", "table", "lookup_field", "substitution_field" ) |
DBLOOKUPNOCACHE( %s, "", "", "", "" ) |
| System Functions |
DBNAME |
Given a database connection name returns the database name. |
DBNAME( "connection" ) |
%s DBNAME( "" ) |
| Math |
DEC2HEX |
Converts a decimal number to hexadecimal |
DEC2HEX( SourceField ) |
DEC2HEX( %s ) |
| Math |
DEC2OCT |
Converts a decimal number to octal |
DEC2OCT( SourceField ) |
DEC2OCT( %s ) |
| Text |
DOLLAR |
Returns the specified number as text, using currency format and the supplied precision. (A value representing the number of decimal places to the right of the decimal point. Omitting this argument assumes two decimal places). |
DOLLAR( number [, precision] ) |
DOLLAR( %s ) |
| Date & Time |
EDATE |
Returns the date that is the indicated number of months before or after a specified date (the start_date) |
EDATE( StartDate, Months ) |
EDATE( %s, ) |
| Step Control |
ENDJOBFAILED |
Returns #ENDJOBFAILED! which causes the job to stop with a fatal error. |
ENDJOBFAILED( ) |
NULL |
| Step Control |
ENDJOBFAILEDMSG |
Returns #ENDJOBFAILEDMSG!( error message ) which causes the job to stop with a fatal error. The supplied error message is then set in the ExecutionLog error message. The message may contain up to 255 characters. |
ENDJOBFAILEDMSG( "error message" ) |
ENDJOBFAILEDMSG( "" ) |
| Step Control |
ENDJOBRETRY |
Returns #ENDJOBRETRY! which causes the job to stop with a retry result. |
ENDJOBRETRY( ) |
NULL |
| Step Control |
ENDJOBRETRYMSG |
Returns #ENDJOBRETRYMSG!( error message ) which causes the job to stop with a retry result. The supplied error message is then set in the ExecutionLog error message. The message may contain up to 255 characters. |
ENDJOBRETRYMSG( "error message" ) |
ENDJOBRETRYMSG( "" ) |
| Step Control |
ENDJOBSUCCESSCOMMIT |
Returns #ENDJOBSUCCESSCOMMIT! causing the job to stop with a successful result. The function causes previous steps to be committed if transactions are enabled. See also ENDJOBSUCCESSROLLBACK. |
ENDJOBSUCCESSCOMMIT( ) |
NULL |
| Step Control |
ENDJOBSUCCESSROLLBACK |
Returns #ENDJOBSUCCESS! causing the job to stop with a successful result. The function causes previous steps to rollback (if not yet committed) if transactions are enabled. See also ENDJOBSUCCESSCOMMIT. |
ENDJOBSUCCESSROLLBACK( ) |
NULL |
| Date & Time |
EOMONTH |
Returns the date for the last day of the month that is the indicated number of months before or after start_date |
EOMONTH( StartDate, Months) |
EOMONTH( %s, ) |
| Information |
ERROR.TYPE |
Returns a number corresponding to an error |
ERROR.TYPE( SourceField ) |
ERROR.TYPE( %s ) |
| Text |
EXACT |
Compares two expressions for identical, case-sensitive matches. True is returned if the expressions are identical; False is returned if they are not. |
EXACT( SourceField | "expression1", SourceField | "expression2" ) |
EXACT( %s, "" ) |
| Step Control |
FAILROW |
Returns #FAILROW! which causes the current source row to fail with an error. |
FAILROW( ) |
NULL |
| Step Control |
FAILROWMSG |
Returns #FAILROWMSG!( error message ) which causes the current source row to fail with an error. The supplied error message is then set in the TransactionErrors log. The message may contain up to 255 characters. |
FAILROWMSG( "error message" ) |
FAILROWMSG( "" ) |
| Logical |
FALSE |
Returns the logical value False. This function always requires the trailing parentheses. |
FALSE( ) |
NULL |
| Lookup & Reference |
FILELOOKUP |
Looks up and substitutes one field for another. Useful to substitute full values for codes or vice versa. The SECTION parameter indicates the section name (in square brackets) within FILENAME where the TEXT key can be found. |
FILELOOKUP( TextSourceField, "filename", "section" ) |
FILELOOKUP( %s, "", "" ) |
| Text |
FIND |
Searches for a string of text within another text string and returns the character position at which the search string first occurs. |
FIND( "search_text", SourceField [, start_position] ) |
FIND( "", %s ) |
| Lookup & Reference |
FIRSTNAMEMATCH |
Returns a list of first names that are a likely match for the given name. Use with the IN operator for lookup links. Uses the FIRSTNAMES table in the Internal database. |
FIRSTNAMEMATCH( TextSourceField ) |
FIRSTNAMEMATCH( %s ) |
| Text |
FIXED |
Rounds a number to the supplied precision, formats the number in decimal format, and returns the result as text. |
FIXED( number [, precision] [, no_commas] ) |
FIXED( %s ) |
| Lookup & Reference |
FKEYSUBST |
Looks up and substitutes one foreign key value for another. Required when transferring data between two databases that use different fields as foreign keys. |
FKEYSUBST( TextSourceField, "connection", "table", "lookup_field", "substitution_field" ) |
FKEYSUBST( %s, "", "", "", "" ) |
| Math |
FLOOR |
Rounds a number down to the nearest multiple of a specified significance. |
FLOOR( NumericSourceField, significance ) |
FLOOR( %s, 2 ) |
| Text |
FORMAT |
Returns a string formatted according to instructions contained in a format expression |
FORMAT( SourceField, Format ) |
FORMAT( %s, "#" ) |
| System Functions |
GETLASTRUNDATE |
Returns the most recent date and time (local) that the specified DTS was executed. |
GETLASTRUNDATE( dtsFilename ) |
%s GETLASTRUNDATE( "" ) |
| System Functions |
GETLASTRUNDATEUTC |
Returns the most recent date and time (in UTC/GMT) that the specified DTS was executed. |
GETLASTRUNDATEUTC( dtsFilename ) |
%s GETLASTRUNDATEUTC( "" ) |
| System Functions |
GETNAMEDVALUE |
Retrieves a value previously stored by SETNAMEDVALUE or SETNAMEDVALUEIF. |
GETNAMEDVALUE( Name ) |
GETNAMEDVALUE( "name" ) |
| System Functions |
GETROWERROR |
Returns any error information that occurred while processing a row. |
GETROWERROR() |
GETROWERROR() |
| Step Control |
GOTOSTEP |
Returns #GOTOn! which causes a jump to the specified step number (without executing the current step). |
GOTOSTEP( StepNumber ) |
%s GOTOSTEP( 2 ) |
| System Functions |
GUID |
Returns a globally unique identifier (GUID) in a 36-character string |
GUID( ) |
NULL |
| Math |
HEX2DEC |
Converts a hexadecimal number to decimal |
HEX2DEC( SourceField ) |
HEX2DEC( %s ) |
| Math |
HEX2OCT |
Converts a hexadecimal number to octal |
HEX2OCT( SourceField ) |
HEX2OCT( %s ) |
| Date & Time |
HOUR |
Returns the hour component of the specified time in 24-hour format. |
HOUR( serial_number ) |
HOUR( %s ) |
| Text |
HTML2TEXT |
Returns the text that is contained within HTML. |
HTML2TEXT( HTML ) |
HTML2TEXT( %s ) |
| Logical |
IF |
Tests the condition and returns the specified value. |
IF( condition, "true_value", "false_value" ) |
IF( %s, "", "" ) |
| Math |
INT |
Rounds the supplied number down to the nearest integer. |
INT( NumericSourceField ) |
INT( %s ) |
| Information |
ISBLANK |
Determines if the specified cell is blank. |
ISBLANK( SourceField ) |
ISBLANK( %s ) |
| Information |
ISDATE |
Returns a Boolean value indicating whether an expression can be converted to a date |
ISDATE( SourceField ) |
ISDATE( %s ) |
| Information |
ISEMAILADDRESS |
Determines if the specified expression is a valid email address |
ISEMAILADDRESS( expression ) |
ISEMAILADDRESS( %s ) |
| Information |
ISEMPTY |
Returns TRUE if the value is zero length or all spaces |
ISEMPTY( SourceField ) |
ISEMPTY( %s ) |
| Information |
ISERROR |
Determines if the specified expression returns an error value. |
ISERROR( expression ) |
ISERROR( %s ) |
| Information |
ISLOGICAL |
Determines if the specified expression returns a logical value. |
ISLOGICAL( expression ) |
ISLOGICAL( %s ) |
| Information |
ISNA |
Determines if the specified expression returns the value not available error. |
ISNA( expression ) |
ISNA( %s ) |
| Information |
ISNONTEXT |
Determines if the specified expression is not text. |
ISNONTEXT( expression ) |
ISNONTEXT( %s ) |
| Information |
ISNUMBER |
Determines if the specified expression is a number. |
ISNUMBER( expression ) |
ISNUMBER( %s ) |
| Lookup & Reference |
ISPICKLISTVALUE |
Determines if a value is valid for a specified picklist field. The picklist field is indicated by specifying the database connection name, table name, and field name. The parent value is only used if the picklist field has a parent. |
ISPICKLISTVALUE(SourceValue, "connection", "table", "picklist_field", ParentValue ) |
ISPICKLISTVALUE(%s, "", "", "", "") |
| Information |
ISTEXT |
Determines if the specified expression is text. |
ISTEXT( expression ) |
ISTEXT( %s ) |
| Date & Time |
JULIANDATE2DATETIME |
Returns the Gregorian/common datetime for the supplied Julian datetime. |
JULIANDATE2DATETIME(julian_date) |
JULIANDATE2DATETIME( %s ) |
| Date & Time |
JULIANDATEJDE2DATE |
Returns the Gregorian/common date for the supplied JD Edwards-formatted Julian date. |
JULIANDATEJDE2DATE(julian_date_jde) |
JULIANDATEJDE2DATE( %s ) |
| Parse |
LEFT |
Returns the leftmost characters from the specified text string. |
LEFT( SourceField [, num_chars] ) |
LEFT( %s ) |
| Text |
LEN |
Returns the number of characters in the supplied text string. |
LEN( SourceField ) |
LEN( %s ) |
| Date & Time |
LOCAL2UTC |
Converts the specified DateTime from the local timezone to UTC time, using the current windows Date & Time settings. |
LOCAL2UTC( DateTime ) |
LOCAL2UTC( %s ) |
| Text |
LOWER |
Changes the characters in the specified string to lowercase characters. Numeric characters in the string are not changed |
LOWER( SourceField ) |
LOWER( %s ) |
| Statistical |
MAX |
Returns the largest value in the specified list of numbers. |
MAX( number_list ) |
MAX( %s ) |
| Parse |
MID |
Returns the specified number of characters from a text string, beginning with the specified starting position. |
MID( SourceField, start_position, num_chars ) |
MID( %s, , ) |
| Statistical |
MIN |
Returns the smallest value in the specified list of numbers. |
MIN( number_list ) |
MIN( %s ) |
| Date & Time |
MINUTE |
Returns the minute that corresponds to the supplied date. |
MINUTE( serial_number ) |
MINUTE( %s ) |
| Math |
MOD |
Returns the remainder after dividing a number by a specified divisor. |
MOD( NumericSourceField, divisor ) |
MOD( %s, ) |
| Date & Time |
MONTH |
Returns the month that corresponds to the supplied date. |
MONTH( serial_number ) |
MONTH( %s ) |
| Date & Time |
MONTHNAME |
Returns a string indicating the specified month |
MONTHNAME( Month, Abbreviate ) |
MONTHNAME( %s, 0 ) |
| Information |
N |
Tests the supplied value and returns the value if it is a number. |
N( SourceField ) |
N( %s ) |
| Information |
NA |
Returns the error value #N/A, which represents "not available" |
NA( ) |
NULL |
| System Functions |
NEXTITEMNUMBER |
Returns the next sequential number if the supplied string value does not change, otherwise resets to 1 |
NEXTITEMNUMBER( SourceField ) |
NEXTITEMNUMBER( %s ) |
| Step Control |
NEXTROW |
Returns #NEXTROW! which causes execution to continue, without error, on the next source row. |
NEXTROW( ) |
NULL |
| System Functions |
NEXTTRANSNUMBER |
Returns the next sequential number every time the supplied string value changes |
NEXTTRANSNUMBER( SourceField ) |
NEXTTRANSNUMBER( %s ) |
| System Functions |
NEXTTRANSNUMBERN |
Returns the next sequential number every time the supplied string value changes - can handle more than one value at a time |
NEXTTRANSNUMBERN( Index, SourceField ) |
NEXTTRANSNUMBERN( 1, %s ) |
| Logical |
NOT |
Returns a logical value that is the opposite of its value. |
NOT( logical ) |
NOT( %s ) |
| Date & Time |
NOW |
Returns the current date and time as a serial number. |
NOW( ) |
NULL |
| Math |
OCT2DEC |
Converts an octal number to decimal |
OCT2DEC( SourceField ) |
OCT2DEC( %s ) |
| Math |
OCT2HEX |
Converts an octal number to hexadecimal |
OCT2HEX( SourceField ) |
OCT2HEX( %s ) |
| Logical |
OR |
Returns True if at least one of a series of logical arguments is true. |
OR( logical_list ) |
OR( %s ) |
| System Functions |
OSPATHTOUNC |
Returns the UNC version of the given path if available or the given path unchanged if not |
OSPATHTOUNC( path ) |
OSPATHTOUNC( %s ) |
| Text |
PAD |
Returns a string padded on the left or right with spaces |
PAD( SourceField, "L" | "R", Width ) |
PAD( %s, "L", 1 ) |
| Parse |
PARSE |
Parses any string into individual words or tokens. |
PARSE( TextSourceField, token_number, "delimiters" ) |
PARSE( %s, 1, "," ) |
| Parse |
PARSEADDR |
Decomposes an address line into the individual components: city, state, and zip code. |
PARSEADDR( TextSourceField, "C" | "S" | "Z" ) |
PARSEADDR( %s, "S" ) |
| Parse |
PARSENAME |
Decomposes a person’s name into the individual components: prefix, first, middle, last, suffix, or professional degrees. |
PARSENAME( TextSourceField, "P" | "F" | "M" | "L" | "S" | "D" ) |
PARSENAME( %s, "F" ) |
| Math |
PRODUCT |
Multiplies a list of numbers and returns the result. |
PRODUCT( number_list ) |
PRODUCT( %s ) |
| Text |
PROPER |
Returns the specified string in proper-case format. |
PROPER( SourceField ) |
PROPER( %s ) |
| Math |
RAND |
Returns a number selected randomly from a uniform distribution greater than or equal to 0 and less than 1. |
RAND( ) |
NULL |
| Regex |
REGEXMATCH |
Returns whether or not the regex found a match. |
REGEXMATCH( input, pattern ) |
REGEXMATCH( %s, "" ) |
| Regex |
REGEXREPLACE |
Returns a new string that is identical to the input string, except that a replacement string takes the place of each matched string. |
REGEXREPLACE( input, pattern, replacement ) |
REGEXREPLACE( %s, "", "" ) |
| Text |
REPLACE |
Replaces part of a text string with another text string. |
REPLACE( TextSourceField, start_position, num_chars, "repl_text" ) |
REPLACE( %s, , , ) |
| Text |
REPT |
Repeats a text string the specified number of times. |
REPT( SourceField, number ) |
REPT( %s, ) |
| Parse |
RIGHT |
Returns the rightmost characters from a given string |
RIGHT( SourceField [, num_chars] ) |
RIGHT( %s ) |
| Math |
ROUND |
Rounds the given number to the supplied number of decimal places. |
ROUND( NumericSourceField, precision ) |
ROUND( %s, 2 ) |
| Math |
ROUNDDOWN |
Rounds a number down. |
ROUNDDOWN( NumericSourceField, numberOfDigits ) |
ROUNDDOWN( %s, 2 ) |
| Math |
ROUNDUP |
Rounds the given number up to the supplied number of decimal places. |
ROUNDUP( NumericSourceField, numberOfDigits ) |
ROUNDUP( %s, 2 ) |
| System Functions |
ROWNUM |
Returns the row number from the source. |
ROWNUM( ) |
NULL |
| Text |
SEARCH |
Locates the position of the first character of a specified text string within another text string. |
SEARCH( "search_text", SourceField [, start_position] ) |
SEARCH( , %s ) |
| Date & Time |
SECOND |
Returns the second that corresponds to the supplied date. |
SECOND( serial_number ) |
SECOND( %s ) |
| Logical |
SELECTCASE |
Returns the value from the case string that is associated with the expression value, or a default value if the expression value is not found. Supported operators: =, <, >, <=, >=, Else. The default return value is #NULL! if Else is not provided. |
SELECTCASE( expression, "=X~1 | >Y~2 | <=Z~3 | Else~0" ) |
SELECTCASE( %s, "" ) |
| System Functions |
SEQNUM |
Returns the next sequential number. The first number returned is the initial value that is passed into this function. After that the count is incremented by one and returned. |
SEQNUM( initial_value_number ) |
%s SEQNUM( 1 ) |
| System Functions |
SEQNUMN |
Like SEQNUM, returns the next sequential number, but also provides a 1 based index so that multiple numbers from the sequence may be used on each source row. |
SEQNUMN( initial_value, index ) |
%s SEQNUMN( 1 , 1 ) |
| System Functions |
SETNAMEDVALUE |
Stores a value by name. The value will persist across source rows and can be retrieved using GETNAMEDVALUE. When invoked, SETNAMEDVALUE also returns the Value just set. |
SETNAMEDVALUE( Name, Value ) |
SETNAMEDVALUE( "name", %s ) |
| System Functions |
SETNAMEDVALUEIF |
Similar to the SETNAMEDVALUE function, but this function only saves the value if the passed in condition is true. |
SETNAMEDVALUEIF( Name, Condition, Value ) |
SETNAMEDVALUEIF( "name", TRUE, %s ) |
| Math |
SIGN |
Determines the sign of the specified number. |
SIGN( NumericSourceField ) |
SIGN( %s ) |
| Information |
SIZE |
Returns the size of the specified field |
SIZE( "connection", "table", "field" ) |
%s SIZE( "", "", "" ) |
| Step Control |
SKIPSTEP |
Returns #SKIPSTEP! which causes the current step to be skipped. |
SKIPSTEP( ) |
NULL |
| Text |
SOUNDEX |
Returns a code or number that represents the sound of a word. Use to populate a field that can be used to match on later. |
SOUNDEX( TextSourceField, "connection" ) |
SOUNDEX( %s, "" ) |
| Parse |
SPLITFILENAME |
Returns the requested part of the given filename - [P]ath, [N]ame or [E]xtension |
SPLITFILENAME( "P" | "N" | "E", Filename ) |
SPLITFILENAME("N", %s ) |
| Lookup & Reference |
SQLQUERY |
Executes a SQL query and returns the values in the first column of the first row. Could be used as a select count. A WHERE clause may use %s to reference the Source Field value. i.e. Select .....Where name = ' %s' |
SQLQUERY( TextSourceField, "connection", "sqlStatement" ) |
SQLQUERY( %s, "", "select " ) |
| Lookup & Reference |
SQLQUERY2 |
Similar to the SQLQUERY function, except that two values may be passed in. The first value replaces any %1, and the second value replaces any %2. |
SQLQUERY2( SourceValue1, SourceValue2, "connection", "sqlStatement" ) |
SQLQUERY2( %s, , "", "select " ) |
| Lookup & Reference |
SQLQUERYDEFAULT |
Similar to SQLQUERY function, except that it accepts an additional default_value that is returned if no matching record is found. |
SQLQUERYDEFAULT( TextSourceField, "connection", "sqlStatement", "default_value" ) |
SQLQUERYDEFAULT( %s, "", "select ", "" ) |
| Lookup & Reference |
SQLQUERYDEFAULT2 |
Similar to the SQLQUERYDEFAULT function, except that two values may be passed in. The first value replaces any %1, and the second value replaces any %2. |
SQLQUERYDEFAULT2( SourceValue1, SourceValue2, "connection", "sqlStatement", "default_value" ) |
SQLQUERYDEFAULT2( %s, , "", "select ", "" ) |
| Parse |
STRIP |
Returns only the requested type of characters from a non-Unicode text string: alpha, numeric, punctuation, whitespace, other/unknown |
STRIP( SourceField, "A" | "N" | "P" | "W" | "O" ) |
STRIP( %s, "N" ) |
| Parse |
STRIPCOMPANY |
Removes all punctuation, any trailing words in the following list: Inc, Incorporated, Corp, Corporation, Co, Company, Ltd, etc. |
STRIPCOMPANY( TextSourceField ) |
STRIPCOMPANY( %s ) |
| Parse |
STRIPCOMPANYSUFFIX |
Removes all trailing punctuation and words in the following list: Inc, Incorporated, Corp, Corporation, Co, Company, Ltd, etc. |
STRIPCOMPANYSUFFIX( TextSourceField ) |
STRIPCOMPANYSUFFIX( %s ) |
| Parse |
STRIPEX |
Returns only the requested type of characters from a Unicode text string: alpha, numeric, punctuation, whitespace, other/unknown |
STRIPEX( SourceField, "A" | "N" | "P" | "W" | "O" ) |
STRIPEX( %s, "N" ) |
| Text |
SUBSTITUTE |
Replaces a specified part of a text string with another text string. |
SUBSTITUTE( SourceField, "old_text", "new_text" [, instance] ) |
SUBSTITUTE( %s, , ) |
| Math |
SUM |
Returns the sum of the supplied numbers. |
SUM( NumericSourceField ) |
SUM( %s ) |
| Math |
SUMROWS |
Sums a set of values across multiple source rows. Uses a variable/label name to allow multiple values to be summed at the same time. Set Reset to TRUE to reset sum to 0. Returns the current sum. |
SUMROWS ( Name, Value, Reset ) |
SUMROWS ( "name", %s, FALSE ) |
| Text |
T |
Tests the supplied value and returns the value if it is text. |
T( SourceField ) |
T( %s ) |
| Text |
TEXT |
Returns the given number as text, using the specified formatting. |
TEXT( NumericSourceField, "formatString" ) |
TEXT( %s, ) |
| Date & Time |
TIME |
Returns a serial number for the supplied time. |
TIME( hour, minute, second ) |
TIME( %s ) |
| Date & Time |
TIMEVALUE |
Returns a serial number for the supplied text representation of time. |
TIMEVALUE( SourceField ) |
TIMEVALUE( %s ) |
| Date & Time |
TODAY |
Returns the current date as a serial number. |
TODAY( ) |
NULL |
| Text |
TRIM |
Removes all spaces from text except single spaces between words. |
TRIM( TextSourceField ) |
TRIM( %s ) |
| Text |
TRIMX |
Removes leading and trailing space, line, paragraph separators and tab characters. |
TRIMX() |
TRIMX() |
| Logical |
TRUE |
Returns the logical value True. This function always requires the trailing parentheses. |
TRUE( ) |
NULL |
| Math |
TRUNC |
Truncates the given number to an integer. |
TRUNC( NumericSourceField [, precision] ) |
TRUNC( %s, 2 ) |
| Information |
TYPE |
Returns the argument type of the given expression. |
TYPE ( expression ) |
TYPE ( %s ) |
| Text |
UPPER |
Changes the characters in the specified string to uppercase characters. |
UPPER( TextSourceField ) |
UPPER( %s ) |
| System Functions |
USERNAME |
Given a database connection name returns the user name. |
USERNAME( "connection" ) |
%s USERNAME( "" ) |
| Date & Time |
UTC2LOCAL |
Converts the specified DateTime from UTC time to local time, using the current windows Date & Time settings. |
UTC2LOCAL( DateTime ) |
UTC2LOCAL( %s ) |
| Text |
VALUE |
Returns the specified text as a number. |
VALUE( TextSourceField ) |
VALUE( %s ) |
| Lookup & Reference |
VALUEMAPLOOKUPA |
Looks up and substitutes one value for another. The MapLabel parameter controls which group of values to use from the VALUEMAP table in the Scribe Internal database. Looks up the value in APP_B_VALUE and returns APP_A_VALUE. |
VALUEMAPLOOKUPA( "MapLabel", SourceValue, "DefaultValue" ) |
VALUEMAPLOOKUPA( "", %s, "#NULL!" ) |
| Lookup & Reference |
VALUEMAPLOOKUPB |
Looks up and substitutes one value for another. The MapLabel parameter controls which group of values to use from the VALUEMAP table in the Scribe Internal database. Looks up the value in APP_A_VALUE and returns APP_B_VALUE. |
VALUEMAPLOOKUPB( "MapLabel", SourceValue, "DefaultValue" ) |
VALUEMAPLOOKUPB( "", %s, "#NULL!" ) |
| Date & Time |
WEEKDAY |
Returns the day of the week that corresponds to the supplied date. |
WEEKDAY( serial_number ) |
WEEKDAY( %s ) |
| Date & Time |
WEEKDAYNAME |
Returns a string indicating the specified day of the week. |
WEEKDAYNAME( Weekday, Abbreviate, FirstDay ) |
WEEKDAYNAME( %s, 0, 0 ) |
| Lookup & Reference |
XLSLOOKUP |
Searches the first column of xlsFile for a value and returns the contents of a cell in that table that corresponds to the location of the search value. |
XLSLOOKUP( xlsFile, search_value, search_range, search_col ) |
XLSLOOKUP( "", %s, "", "" ) |
| Lookup & Reference |
XMLLOOKUP |
Returns the value referenced by XPath from the XML file listed in Filename |
XMLLOOKUP( Filename, XPath ) |
XMLLOOKUP( "", %s ) |
| Lookup & Reference |
XMLXPATH |
Returns the value referenced by XPath from the XML source connection; the behavior is undefined if a source connection is not used. |
XMLXPATH( XPath, "connection" ) |
XMLXPATH( %s, "" ) |
| Lookup & Reference |
XMLXREF |
Returns the value referenced by Node and LookupValue from a specially defined cross reference document |
XMLXREF( Filename, Node, LookupValue ) |
XMLXREF( "", "", %s ) |
| Lookup & Reference |
XREFLOOKUP |
Returns the value for the Target Key related to the supplied Source Key in a key cross reference table. Returns an error if multiple matches are found. |
XREFLOOKUP( SourceKeyValue, "source/target connection", "table", "SourceKeyField", "TargetKeyField" ) |
XREFLOOKUP( %s,"", "", "", "" ) |
| Lookup & Reference |
XREFLOOKUPINTERNAL |
Similar to the XREFLOOKUP function, but designed for use only with the KEYCROSSREFERENCE table in the Scribe Internal database. Returns an error if multiple matches are found. |
XREFLOOKUPINTERNAL( LabelValue, SourceKeyValue ) |
XREFLOOKUPINTERNAL( "Label", %s ) |
| Lookup & Reference |
XREFLOOKUPLOCK_AKEY |
Similar to the XREFLOOKUP function, but designed for use only with the KEYCROSSREFERENCETWOWAY table in the Scribe Internal database. Locks the xref record to prevent duplicate keys. Returns the "App A Key" matching the supplied "App B Key". |
XREFLOOKUPLOCK_AKEY( LabelValue, AppBKeyValue ) |
XREFLOOKUPLock_AKEY( "Label", %s ) |
| Lookup & Reference |
XREFLOOKUPLOCK_BKEY |
Similar to the XREFLOOKUP function, but designed for use only with the KEYCROSSREFERENCETWOWAY table in the Scribe Internal database. Locks the xref record to prevent duplicate keys. Returns the "App B Key" matching the supplied "App A Key". |
XREFLOOKUPLOCK_BKEY( LabelValue, AppAKeyValue ) |
XREFLOOKUPLock_BKEY( "Label", %s ) |
| Lookup & Reference |
XREFLOOKUPNOLOCK_AKEY |
Identical to the XREFLOOKUPLOCK_AKEY function, without locking the xref record so that multiple messages will not conflict with each other when using message queues. Returns the "App A Key" matching the supplied "App B Key". |
XREFLOOKUPNOLOCK_AKEY( LabelValue, AppBKeyValue ) |
XREFLOOKUPNOLOCK_AKEY( "Label", %s ) |
| Lookup & Reference |
XREFLOOKUPNOLOCK_BKEY |
Identical to the XREFLOOKUPLOCK_BKEY function, without locking the xref record so that multiple messages will not conflict with each other when using message queues. Returns the "App B Key" matching the supplied "App A Key". |
XREFLOOKUPNOLOCK_BKEY( LabelValue, AppAKeyValue ) |
XREFLOOKUPNOLOCK_BKEY( "Label", %s ) |
| Date & Time |
YEAR |
Returns the year that corresponds to the supplied date. |
YEAR( serial_number ) |
YEAR( %s ) |
