Cloud Inventory® Documentation

send feedback

❮ Previous Next ❯

Functions in formulas

Within app operations, formulas have a variety of uses, including assigning text values to a text box or field, setting date and time value, and assigning location information. But formulas may also be used to perform simple to complex evaluations and calculations. This is accomplished through formula functions. In this topic we discuss some of the capabilities of functions within the formula editor.

Functions are methods for editing or evaluating data elements within a formula expression. For example, functions can be used to trim leading and trailing spaces from a data field so that only the actual data is passed. You can also use the substring function to extract data within a string of data.

Functions can accept entire formulas (simple and complex) as input parameters. Special characters can also be used to create masks and for performing numeric calculations.

Available functions, descriptions, and use

Calc

Calc(value1,operator,value2)

Use: The function performs a mathematical calculation on two parameters based on the operator. It also edits dates and returns a numeric value. Calculation operators must be enclosed in quotation marks.

Char

Char(charNumber)

Use: Returns the Unicode character represented by a numeric value. Unicode numeric values are up to five digits in length. Leading zeros are not required. The highest value is 65535.

DateDiff

DateDiff(date1,date2)

Use: Returns the absolute value difference between two dates. Both parameters are date variables. This function returns a numeric value representing the difference between the number of days.

DayOfWeek

DayOfWeek(date)

Use: Returns the numeric value of the day of the week.

DecodeBase64

DecodeBase64(stringValue)

Use: Converts Base64 data into a text string.

Example: DecodeBase64("VGhpcyBpcyBhIHNhbXBsZQ==") returns This is a sample

EncodeBase64

EncodeBase64(stringValue)

Use: Converts a text string into Base64 data.

Example: EncodeBase64("This is a sample") returns VGhpcyBpcyBhIHNhbXBsZQ==

EncodeXML

EncodeXML(string)

Use: Replaces characters reserved in XML with their corresponding text equivalents for the symbols. This "cleans" the data of possible XML symbols to avoid conflicts. See specific replacements below.

' is replaced with '
" is replaced with "
& is replaced with &
< is replaced with <
> is replaced with >

FileExists

FileExists(stringValue)

Use: Returns a 1 (true) if the file exists on the device. If not, it returns 0 (false).

FmtTable

FmtTable(tableID)

Use: Returns row and field delimited data for entire table. Describes the structure and contents of a table as an input parameter to a function call or output. The delimiters are non-printable characters. The row delimiters are added only between rows.

Format

Format(value) Format(value,formatMask) Format(value,formatMask,invariant)

Use: Formats any type of data into displayable character data. This function supports the formatting of numbers, time, dates, and character formula elements. Thousandth separators (such as commas) can be used within a numeric mask. This function returns a string value.

FormatDateStr

FormatDateStr(sourceValue, sourceFormat, targetFormat, errorString)

Use: This function takes a string value and parses it as a date in the same manner as StrToDate. It then formats it back into a string in the same manner as Format. If the string fails to be parsed as a date, then the error string is returned instead.

GenerateGUID

GenerateGUID()

Use: This function will generate a 32 character globally unique identifier (GUID). This function will not accept parameters.

GetConfigValue

GetConfigValue (key,defaultValue)

Use: This function takes a configuration element as the parameter and return the corresponding value for the applicable environment. In the event the function cannot find the configuration element, the function will return the default value parameter as the function value. If configuration elements have been entered, the configuration element table located in the formula editor window can be used to quickly find the text ID for the formula. Double-click the specific configuration element row to insert the value.

IsAlpha

IsAlpha(stringValue)

Use: Returns a 1 (true) if a single character is an alphabetic character. If not, it returns 0 (false).

IfElse

IfElse(condition,trueValue,falseValue)

Use: Uses simple conditional logic to determine whether a condition is true or false, and then returns either the true value, or the false value.

IsDate

IsDate(dateString,formatString)

Use: Returns a 1 (true) if a string is a date. If not, it returns 0 (false).

IsDigit

IsDigit(stringValue)

Use: Returns a 1 (true) if a single character is a number. If not, it returns 0 (false).

IsNumber

IsNumber(stringValue)

Use: Returns a 1 (true) if a string contains all numeric value. If not, it returns 0 (false). The string must consist of a sensible number to return true. For example, it cannot contain two decimal points.

IsUserInOrgUnit

IsUserInOrgUnit(orgUnit)

Use: Returns a 1 (true) if the current user belongs to the specified organizational unit.

IsUserInRole

IsUserInRole(stringValue)

Use: Returns a 1 if the current user is in the specified role.

LastPositionOf

LastPositionOf(stringToSearch,stringToFind)

Use: Returns the 1-based position of the last occurrence of a string within another.

Length

Length(stringValue)

Use: Returns the count of characters in a string. This function returns a numeric value.

PositionOf

PositionOf(stringToSearch,stringToFind)

Use: Returns the 1-based position of a string within another.

RepositoryFile

RepositoryFile(file)

Use: Specifies a file or image residing in the Repository in Application Studio. The value in (file) is the ID of the file in the repository. Repository files can be selected for an element using the repository table located in the Formula Editor window. New files can be added using the Repository tab in Application Studio or using the Repository table's Upload New button in the Formula Editor window.

Replace

Replace(originalString,oldValueString,newValueString)

Use: Searches the specified source string for a string value you want to replace, and then replaces all instances of that string with a new string.

StrToDate

StrToDate(dateString,formatString) StrToDate(@_Date)

Use: Converts string data into a date value. The format mask describes the format of the date within the character formula. This function returns a date value.

SubStr

SubStr(stringValue,startPos,length) SubStr(stringValue,startPos)

Use: Returns any portion of a string. This function returns a string value. If length is omitted, everything after startPos is returned. In this following example, the substring function has three arguments.

The first argument is a formula or a data type representing the data contained in the substring.
The second argument is the starting position of the desired substring.
The third argument is the length of the resulting substring.

Example:

F 1 = " 5 " and F 2 = " 7 ",

SubStr("abc"+Trim(F_1+F_2)+"xyz",1,8) returns "abc57xyz"

Text

Text(stringID) Text(numberID)

Use: Displays user-defined and predefined Mobile Client text according to the referenced text ID number. User-defined text is defined for Mobile Client in Admin > Configuration > Localization. Text displayed will dynamically change based on user language settings. Once user-defined text is created, the Text table located on the Formula Editor window can be used to quickly find the text ID for the formula. Double-click the specific Text ID row to insert the text ID number. Searches can be done by text ID and text value. When typing in the search boxes, the search performs dynamic content matching.

ToLower

ToLower(stringValue)

Use: Converts the contents of a string to all lower-case letters.

ToUpper

ToUpper(stringValue)

Use: Converts the contents of a string to all upper-case letters.

Trim

Trim(stringValue) Trim(stringValue,charToRemove)

Use: Removes leading and trailing characters from a string. This function returns a string value.

Example:

T1.Quantity = _____100 T1.UOM = _____EA

Note:Underscores represent spaces.

"Qty:_"+Trim(T1.Quantity)+"_"+Trim(T1.UOM) returns 100 EA

TrimLeft

TrimLeft(stringValue) TrimLeft(stringValue,charToRemove)

Use: Removes all leading characters from a string. This function returns a string value.

TrimRight

TrimRight(stringValue) TrimRight(stringValue,charToRemove)

Use: Removes trailing right characters from a string. This function returns a string value.

Val

Val(stringValue) Val(stringValue,invariant)

Use: Converts a string into a numeric value. This function returns a numeric value.

Pre-defined formulas

Within the formula builder there are pre-defined formulas and options allowing the developer to use other system or app elements when building formulas. The following pre-defined formulas may be used to build your formula.

Text

Select predefined text strings to include in a formula.

Form elements

Select form elements to include in a formula.

Repository

Select Repository images or files to include in a formula.

Config elements

Select defined configuration elements to be used in the GetConfigValue function.

Function rules

A formula can be expressed as one or more arguments.
All arguments in a function can be separate formulas (simple and complex).
The size of embedded expressions is unlimited. However, the correct data types (character, numeric, or date) must be supplied to the function parameters.

Examples

The function below has only one argument.

"Qty: "+Trim(D1.R2TQTY)+" "+D1.R2UOM1

The following example is a formula with a function with three arguments. Notice how commas are used to separate the arguments.

SubStr(F_1,1,3)

A formula can have an unlimited number of nesting data elements and functions. The following example is a formula containing nesting elements (and three arguments).

SubStr("abc"+Trim(F_1+F_2)+"xyz",1,8)

In this formula:

The F_1 and F_2 fields are combined and then trimmed.
The literal "abc" is placed in front of the result, and the literal "xyz" is placed after the result.
The numbers 1 and 8 signify the starting position of the first value and the number of characters to be displayed in the result.

For example, F_1 = Branch/Plant 30 and F_2 = order number 99.

Before the substring is created, a portion of the result would be abc3099xyz.
After the substring is created, the result would be abc3099x.

The following example is a formula using the substring function:

SubStr("abc"+Trim(F_1+F_2)+"xyz",1,Val(F_1)+(V_1+V_2))

The third argument to the SubStr is the length of the substring. Therefore, the result passed to this parameter must be a numeric. If not, the formula will be invalid. The Val function converts the F_1 character data type to numeric.

Format

Formats any type of data into displayable character data. This function supports the formatting of numbers, time, dates, and character formula elements. Thousandth separators (such as commas) can be used within a numeric mask. This function returns a string value.

Format(value)

Format(value,formatMask)

Format(value,formatMask,invariant)

Format(value,formatMask,culture)

String Formatting

Specifier	Name	Description
C	Center	When used with a width specification, as in the following examples, the string is centered using spaces as the default character. Characters other than a space can be specified, as shown below. Examples: Format("M30","20C") yields " M30 " Format("M30","20C-") yields "----M30----*-" Format("M30","2C-+=#") yields "M3"
R	Right-justify	When used with a width specification, as in the following examples, the string is right-justified using spaces as the default character. Characters other than a space can be specified, as shown below. Examples: Format("M30","20R") yields " M30" Format("M30","20R-<") yields "-<-<-<-<-<-<-<-<-M30"
L	Left-justify	When used with a width specification, as in the following examples, the string is left-justified using spaces as the default character. Characters other than a space can be specified, as shown below. Format("M30","20L") yields "M30 " Format("M30","20L-") yields "M30---------------"
@	Right-justify with spaces substituted	The string is right-justified using spaces to replace the @ character. Examples: Format("M30","@@@@@@@@") yields " M30" Format("M30","!@@@@@@@@") yields "M30 "
!@	Left-justify with spaces substituted	The string is left-justified using spaces to replace the @ character. Examples: Format("M30","!@@@@@@@@@@") yields "M30 " Format("M30","!@@@@") yields "M30 "

Numeric Formatting No Mask

If value passed is not fractional, the display should be an integer and should not contain any thousandths separators, decimal point, or trailing zeros after the decimal point.
If the value passed is fractional, then the fraction is displayed but not the thousandths separator. Unless a format mask is supplied which contains the thousandths separator, the thousandths separator will not be included in the result.
Scrubbing the thousandths separator and the decimal point must take into account localization based on culture.

Value Passed to Format Function	Format Function Result
5	5
75	75
15000	15000
5.1	5.1
15000.45	15000.45

Numeric Formatting

Note:In Mobile Client for iOS, Mobile Client for Android, and Mobile Client for HTML5, if an unsupported mask is passed to the Format function, it returns the mask instead of the expected formatted value. Supported masks are as follows.

0 (zero placeholder)
# (Digit placeholder)
. (Decimal point)
C (Currency)

Format Specifier	Name	Description
C or c	Currency	The number is converted to a string that represents a currency amount. The conversion is controlled by the currency format information of the current localization settings. The precision specifier indicates the desired number of decimal places. If the precision specifier is omitted, the default currency precision given by the current localization settings is used. Examples: Format(1234.567,”C”) yields $1,234.57 Format(1234.567,”C3”) yields $1,234.567
E or e	Scientific (exponential)	The number is converted to a string of the form "-d.ddd…E+ddd" or "-d.ddd…e+ddd", where each 'd' indicates a digit (0-9). The string starts with a minus sign if the number is negative. One digit always precedes the decimal point. The precision specifier indicates the desired number of digits after the decimal point. If the precision specifier is omitted, a default of six digits after the decimal point is used. The case of the format specifier indicates whether to prefix the exponent with an 'E' or an 'e'. The exponent always consists of a plus or minus sign and a minimum of three digits. The exponent is padded with zeros to meet this minimum, if required. Examples: Format(12345.6789,”E”) yields ”1.234568E+004” Format(12345.6789,”E10”) yields ”1.2345678900E+004” Format(12345.6789,”e4”) yields ”1.2346e+004”
F or f	Fixed-point	The number is converted to a string of the form "-ddd.ddd…" where each 'd' indicates a digit (0-9). The string starts with a minus sign if the number is negative. The precision specifier indicates the desired number of decimal places. If the precision specifier is omitted, the default numeric precision is given by the current localization settings. Examples: Format(17843,”F”) yields 17843.00 Format(-29541,”F3”) yields -29541.000 Format(18934.1879,”F”) yields 18934.19 Format(18934.1879,”F0”) yields 18934 Format(-1898300.1987,”F1”) yields -1898300.2
G or g	General	The number is converted to the most compact of either fixed-point or scientific notation, depending on whether a precision specifier is present. If the precision specifier is omitted or zero, 15 is assumed and fixed-point notation is always used. If the precision specifier is specified, then fixed point notation is used if the exponent that would result from expressing the number in scientific notation is greater than -5 and less than the precision specifier; otherwise, scientific notation is used. The result contains a decimal point if required and trailing zeroes are omitted. If the precision specifier is present and the number of significant digits in the result exceeds the specified precision, then the excess trailing digits are removed by rounding. Examples: Format(12345.6789,"G") yields 12345.6789 Format(12345.6789,"G7") yields 12345.68 Format(.0000023,"G") yields 0.0000023 Format(.0000023,"G8") yields 2.3E-06 Format(123400000000000000,"G5") yields 1.234E+17 Format(123400000000000000,"G2") yields 1.2E+17
N or n	Number	The number is converted to a string of the form "-d,ddd,ddd.ddd…", where '-' indicates a negative number symbol if required, 'd' indicates a digit (0-9), ',' indicates a thousand separator between number groups, and '.' indicates a decimal point symbol. The actual negative number pattern, number group size, thousand separator, and decimal separator are specified by the current localization settings. The precision specifier indicates the desired number of decimal places. If the precision specifier is omitted, the default numeric precision is given by the current localization settings. Examples: Format(-12445.6789,"N") yields -12,445.68 Format(-12445.6789,"N1") yields -12,445.7 Format(123456789,"N1") yields 123,456,789.0
P or p	Percent	The number is converted to a string that represents a percent as defined by the current localization settings. The converted number is multiplied by 100 in order to be presented as a percentage. The precision specifier indicates the desired number of decimal places. If the precision specifier is omitted, the default numeric precision given by the current localization settings. Examples: Format(.2468013,"P") yields 24.68 % Format(.2468013,"P1") yields 24.7 %
0	Zero placeholder	If the value being formatted has a digit in the position where the '0' appears in the format string, then that digit is copied to the result string; otherwise, a '0' appears in the result string. The position of the left-most '0' before the decimal point and the right-most '0' after the decimal point determines the range of digits that are always present in the result string. The "00" specifier causes the value to be rounded to the nearest digit preceding the decimal, where rounding away from zero is always used. For example, formatting 34.5 with "00" would result in the value 35. Examples: Format(123,"00000") yields 00123 Format(1.2,"0.00") yields 1.20 Format(1.2,"00.00") yields 01.20 Format(.56,"0.0") yields 0.6
#	Digit placeholder	If the value being formatted has a digit in the position where the '#' appears in the format string, then that digit is copied to the result string. Otherwise, nothing is stored in that position in the result string. Note that this specifier never displays the '0' character if it is not a significant digit, even if '0' is the only digit in the string. It will display the '0' character if it is a significant digit in the number being displayed. The "##" format string causes the value to be rounded to the nearest digit preceding the decimal, where rounding away from zero is always used. For example, formatting 34.5 with "##" would result in the value 35. Examples: Format(0,"#") yields an empty string Format(1.2,"#.##") yields 1.2 Format(123,"#####") yields 123 Format(123456,"[##-##-##]") yields [12-34-56] Format(1234567890,"(###) ###-####") yields (123) 456-7890
.	Decimal point	The first '.' character in the format string determines the location of the decimal separator in the formatted value; any additional '.' characters are ignored. The actual character used as the decimal separator in the result string is determined by the current localization settings. Examples: Format(1.2,"00.00") yields 01.20 Format(1.2,"0.00") yields 1.20 Format(12345.67890,"000.000.000") yields 12345.678900 Format(86000,"0.###E+0") yields 8.6E+4
,	Thousand separator and number scaling	The ',' character serves as both a thousand separator specifier and a number scaling specifier. Thousand separator specifier: If one or more ',' characters is specified between two digit placeholders (0 or #) that format the integral digits of a number, a group separator character is inserted between each number group in the integral part of the output. The current localization settings determine the character used as the number group separator and the size of each number group. For example, if the string "#,#" and the invariant culture are used to format the number 1000, the output is 1,000. Number scaling specifier: If one or more ',' characters is specified immediately to the left of the explicit or implicit decimal point, the number to be formatted is divided by 1000 each time a number scaling specifier occurs. For example, if the string "0,," is used to format the number 100 million, the output is "100". Thousand separator and number scaling specifiers can be used in the same format string. For example, if the string "#,0,," and the invariant culture are used to format the number one billion, the output is 1,000. Examples for thousand separator: Format(1234567890,"#,#") yields 1,234,567,890 Examples for number scaling Format(1234567890,"#,,") yields 1235 Format(1234567890,"#,,,") yields 1 Examples for both: Format(1234567890,"#,##0,,") yields 1,235
%	Percentage placeholder	The presence of a '%' character in a format string causes a number to be multiplied by 100 before it is formatted. The appropriate symbol is inserted in the number itself at the location where the '%' appears in the format string. The percent character used is dependent on the current localization settings. Examples: Format(.00012,"#.##%") yields .01% Format(.086,"#0.##%") yields 8.6%
E0 E+0 E-0 e0 e+0 e-0	Scientific notation	If any of the strings "E", "E+", "E-", "e", "e+", or "e-" are present in the format string and are followed immediately by at least one '0' character, then the number is formatted using scientific notation with an 'E' or 'e' inserted between the number and the exponent. The number of '0' characters following the scientific notation indicator determines the minimum number of digits to output for the exponent. The "E+" and "e+" formats indicate that a sign character (plus or minus) should always precede the exponent. The "E", "E-", "e", or "e-" formats indicate that a sign character should only precede negative exponents. Examples: Format(86000,"0.###E+0") yields 8.6E+4 Format(86000,"0.###E+000") yields 8.6E+004 Format(86000,"0.###E-000") yields 8.6E004
“ABC”	Literal string	Characters enclosed in double quotes are copied to the result string, and do not affect formatting. Example: Format(86000,"0.###E-000 What?") yields 8.6E004 What?
;	Section separator	The ';' character is used to separate sections for positive, negative, and zero numbers in the format string. If there are two sections in the custom format string, the left-most section defines the formatting of positive and zero numbers, while the right-most section defines the formatting of negative numbers. If there are three sections, the left-most section defines the formatting of positive numbers, the middle section defines the formatting of negative numbers, and the right-most section defines the formatting of zero numbers. Examples: Format(1234,"##;(##);Zero") yields 1234 Format(-1234,"##;(##);Zero") yields (1234) Format(0,"##;(##);Zero") yields Zero

Time Formatting

Note:Without seconds, minutes may be fractional. Without minutes or seconds, hours may be fractional.

Specifier	Name	Description
H	Hours	Where Elapsed time - 4578 seconds Example: Format(@_Elapsed,"H") yields 1.271667
M	Minutes	Where Elapsed time - 4578 seconds Example: Format(@_Elapsed,"M") yields 76.3
HM	Hours, decimal minutes	Where Elapsed time - 4578 seconds Example: Format(@_Elapsed,"HM") yields 1:16.3
HMS	Hours, minutes, seconds	Where Elapsed time - 4578 seconds Example: Format(@_Elapsed,"HMS") yields 1:16:18
MS	Minutes, seconds	Where Elapsed time - 4578 seconds Example: Format(@_Elapsed,"MS") yields 76:18

Date Formatting

The following tables describe legacy, standard, and custom date formatting.

Legacy Date Format Strings

Specifier	Name	Description
/ - .	Separators	Where: V_1 = Date variable and 6/30/2009 Examples: Format(V_1,"M/D/Y4") yields 6/30/2009 Format(V_1,"M-D-Y4") yields 6-30-2009 Format(V_1,"M.D.Y4") yields 6.30.2009
M	1- or 2-digit month	Where: V_1 = Date variable and 6/30/2009 Example: Format(V_1,"Y-M-D") yields 9-6-30
MM	2-digit month, leading zero implied	Where: V_1 = Date variable and 6/30/2009 Example: Format(V_1,"MM.DD.Y2") yields 06.30.09
D	1- or 2-digit day	Where: V_1 = Date variable and 6/30/2009 Example: Format(V_1,"Y-M-D") yields 9-6-30
DD	2-digit day, leading zero implied	Where: V_1 = Date variable and 6/30/2009 Example: Format(V_1,"MM.DD.Y2") yields 06.30.09
Y or Y1	Year	The least significant digit of the year. The example assumes that V_date is set to July 8, 2009. Example: Format(V_date,"Y") yields 9
YY or Y2	2-digit year	Where: V_1 = Date variable and 6/30/2009 Example: Format(V_1,"MM.DD.Y2") yields 06.30.09
YYY or Y3	Year	The 3 least significant digits of the year. The example assumes that V_date is set to July 8, 2009. Example: Format(V_date,"YYY") yields 009
YYYY or Y4	4-digit year	Where: V_1 = Date variable and 6/30/2009 Example: Format(V_1,"M/D/Y4") yields 6/30/2009
J	Julian	The year and day of the year. The year portion is 1900 less than the actual year value. The example assumes that V_date is set to July 8, 2009. Example: Format(V_date,"J") yields 109189

Standard Date Format Strings

To use the following standard date formatting strings, begin the format string with a leading pound sign (#).

Specifier	Name	Description
d	Short date pattern	Represents a custom date and time format string defined by the current ShortDatePattern property. For example, the custom format string returned by the ShortDatePattern property of the invariant culture is "MM/dd/yyyy". The example assumes that V_date is set to July 8, 2009. Example: Format(V_date,"#d") yields 7/8/2009
D	Long date pattern	Represents a custom date and time format string defined by the current LongDatePattern property. For example, the custom format string for the invariant culture is "dddd, dd MMMM yyyy". The example assumes that V_date is set to July 8, 2009. Example: Format(V_date,"#D") yields Wednesday, July 08, 2009
M or m	Month day pattern	Represents a custom date and time format string defined by the current MonthDayPattern property. For example, the custom format string for the invariant culture is "MMMM dd". The example assumes that V_date is set to July 8, 2009. Example: Format(V_date,"#M") yields July 08
Y or y	Year month pattern	Represents a custom date and time format string defined by the current YearMonthPattern property. For example, the custom format string for the invariant culture is "yyyy MMMM". The example assumes that V_date is set to July 8, 2009. Example: Format(V_date,"#Y") yields July, 2009

Custom Date Format Strings

To use the following custom date formatting strings, begin the format string with a leading pound sign (#).

Specifier	Name	Description
d	Day of the month	Represents the day of the month as a number from 1 through 31. A single-digit day is formatted without a leading zero. Note that if there are no other characters in the format mask, this will be interpreted as the standard short date pattern. Override that behavior with the % character. The examples assume that V_date is set to July 8, 2009. Examples: Format(V_date,"#M/d") yields 7/8 Format(V_date,"#MMMM d") yields July 8 Format(V_date,"#d") yields 7/8/2009 Format(V_date,"#%d") yields 8
dd	Day of the month	Represents the day of the month as a number from 01 through 31. A single-digit day is formatted with a leading zero. The examples assume that V_date is set to July 8, 2009. Examples: Format(V_date,"#dd") yields 08 Format(V_date,"#MMMM dd") yields July 08
ddd	Abbreviated day of the week	Represents the abbreviated name of the day of the week. The examples assume that V_date is set to July 8, 2009. Examples: Format(V_date,"#ddd") yields Wed Format(V_date,"#ddd, MMMM d") yields Wed, July 8
dddd	Full day of the week	Represents the full name of the day of the week. The examples assume that V_date is set to July 8, 2009. Examples: Format(V_date,"#dddd") yields Wednesday Format(V_date,"#dddd, MMMM d") yields Wednesday, July 8
gg	Era	Represents the period or era, for example, A.D. The example assumes that V_date is set to July 8, 2009. Example: Format(V_date,"#MM/dd/yyyy gg") yields 07/08/2009 A.D.
M	Month	Represents the month as a number from 1 through 12. A single-digit month is formatted without a leading zero. Note that if there are no other characters in the format mask, this will be interpreted as the standard Month Day pattern. Override that behavior with the % character. The examples assume that V_date is set to July 8, 2009. Examples: Format(V_date,"#M d") yields 7 8 Format(V_date,"#M") yields July 08 Format(V_date,"#%M") yields 7
MM	Month	Represents the month as a number from 01 through 12. A single-digit month is formatted with a leading zero. The example assumes that V_date is set to July 8, 2009. Example: Format(V_date,"#MM") yields 07
MMM	Abbreviated month name	Represents the abbreviated name of the month. The example assumes that V_date is set to July 8, 2009. Example: Format(V_date,"#MMM") yields Jul
MMMM	Full month name	Represents the full name of the month. The example assumes that V_date is set to July 8, 2009. Example: Format(V_date,"#MMMM") yields July Format(V_date,"#MMMM d") yields July 8
y	Year	Represents the year as a one or two-digit number. If the year has more than two digits, only the two low-order digits appear in the result. If the first digit of a two-digit year begins with a zero (for example, 2009), the number is formatted without a leading zero. Note that if there are no other characters in the format mask, this will be interpreted as the standard Year Month pattern. Override that behavior with the % character. The examples assume that V_date is set to July 8, 2009. Examples: Format(V_date,"#MMMM y") yields July 9 Format(V_date,"#y") yields July, 2009 Format(V_date,"#%y") yields 9
yy	Year	Represents the year as a two-digit number. If the year has more than two digits, only the two low-order digits appear in the result. If the two-digit year has fewer than two significant digits, the number is padded with leading zeros to achieve two digits. The examples assume that V_date is set to July 8, 2009. Examples: Format(V_date,"#MMMM yy") yields July 09 Format(V_date,"#yy") yields 09
yyy	Year	Represents the year with a minimum of three digits. If the year has more than three significant digits, they are included in the result string. If the year has fewer than three digits, the number is padded with leading zeros to achieve three digits. The examples assume that V_date is set to July 8, 2009. Examples: Format(V_date,"#MMMM yyy") yields July 2009 Format(V_date,"#yyy") yields 2009
yyyy	Year	Represents the year as a four-digit number. If the year has more than four digits, only the four low-order digits appear in the result. If the year has fewer than four digits, the number is padded with leading zeros to achieve four digits. If there are additional y specifiers, the number is padded with as many leading zeroes as necessary to achieve the number of y specifiers. The examples assume that V_date is set to July 8, 2009. Examples: Format(V_date,"#MMMM yyyy") yields July 2009 Format(V_date,"#yyyy") yields 2009 Format(V_date,"#yyyyyy") yields 002009
z	Time zone offset	Represents the signed offset of the local operating system's time zone from Coordinated Universal Time (UTC), measured in hours. The offset is always displayed with a leading sign. A plus sign (+) indicates hours ahead of and a minus sign (-) indicates hours behind UTC. The offset ranges from –12 through +13. A single-digit offset is formatted without a leading zero. Note that if there are no other characters in the format mask, the % character must precede the z. The example assumes that the operating system Mobile Client is running on is set to Central Standard Time. Example: Format(V_date,"#%z") yields -5
zz	Time zone offset	Represents the signed offset of the local operating system's time zone from Coordinated Universal Time (UTC), measured in hours. The offset is always displayed with a leading sign. A plus sign (+) indicates hours ahead of and a minus sign (-) indicates hours behind UTC. The offset ranges from –12 through +13. A single-digit offset is formatted with a leading zero. The example assumes that the operating system Mobile Client is running on is set to Central Standard Time. Example: Format(V_date,"#zz") yields -05
zzz	Time zone offset	Represents the signed offset of the local operating system's time zone from Coordinated Universal Time (UTC), measured in hours and minutes. The offset is always displayed with a leading sign. A plus sign (+) indicates hours ahead of and a minus sign (-) indicates hours behind UTC. The offset ranges from –12 through +13. A single-digit offset is formatted with a leading zero. The examples assume that the operating system Mobile Client is running on is set to Central Standard Time. Examples: Format(V_date,"#zzz") yields -05:00

Invariant Culture

Group Separators are the grouping of 3 numbers that make a designation for every thousands place

Decimal Separators are the marks or ticks that separate whole numbers from fractional numbers.

Invariant culture is culture-insensitive. This is used when culture-independent results are needed.

For example: When formatting or parsing values in XML files.

En-US	It-IT
1,000.00	1.000,00

Format	Name	Description
L or l	Local	Format a number using the device's culture settings. **Chrome browser profile will over-ride the device on Windows operating system. [en-US device] Format(1234.54, "C","L")= $1234.54 [fr-FR device] Format(1234.54, "C", "L")= 1 234,54 €
I or i	Invariant	Format a number using the invariant culture settings. [en-US device] Format(1234.54, "C", “i”)= ¤1234.54 [fr-FR device] Format(1234.54, "C", "i")= ¤1234.54

Format

Name

Description

L or l

Local

Format a number using the device's culture settings. **Chrome browser profile will over-ride the device on Windows operating system.

[en-US device]

Format(1234.54, "C","L")= $1234.54

[fr-FR device]

Format(1234.54, "C", "L")= 1 234,54 €

I or i

Invariant

Format a number using the invariant culture settings.

[en-US device]

Format(1234.54, "C", “i”)= ¤1234.54

[fr-FR device]

Format(1234.54, "C", "i")= ¤1234.54

Replace

The Replace function searches the specified source string for a string value you want to replace, and then replaces all instances of that string with a new string.

Replace(originalString,oldValueString,newValueString)

Parameters

This function accepts the following parameters, all of which must be strings.

sourceString

Contains the original string that is searched. Replace() makes no changes if the sourceString is empty, or if there are no occurrences of the oldValueString.

oldValueString

Contains the string you want to replace. This parameter is case sensitive and cannot be empty.

newValueString

Replaces the content in the oldValueString.

You can use the following items within the parameters of the replace function.

Nested functions, including replace functions, can be used if they return a string.
Variables
Literals
Unicode characters
Escape sequences
White space
Control characters

Examples

Replace("This is a sample","was","is") returns "This is a sample"
Replace("This is a sample","is","was") returns "Thwas was a sample"
Replace("This is a sample"," ","") returns "Thisisasample"
Replace("This is a sample","","was") returns ERROR
Replace("This is a sample","IS","was") returns "This is a sample"
Replace("This is a sample"," is "," was ") returns "This was a sample"
Replace("","is","was") returns ""

IfElse

The IfElse function uses simple conditional logic to determine whether a condition is true or false, and then returns either the true value, or the false value.

IfElse(condition,trueValue,falseValue)

Parameters

This function accepts the following parameters.

condition

The “condition” parameter is similar to the conditional flowchart item. It must contain a condition that returns a value of 1 (True), or 0 (False). You can use conditional operators (<, <=, <>, =, >, >=, AND, OR) to specify the condition. For more information about conditional operators, refer to Conditional Operators (Boolean).

trueValue

Contains the value that will be returned if the specified condition is true. It can be formatted as a string, numeric value, or date, as long as the format type matches the falseValue. For example, you can't have one date value, and one numeric value.

falseValue

Contains the value that will be returned if the specified condition is false. It can be formatted as a string, numeric value, or date, as long as the format type matches the trueValue. For example, you can't have one date value, and one numeric value.

You can use the following items within the parameters of the IfElse function.

Nested functions, including IfElse functions.
Variables
Literals

IfElse() is the only function within Mobile Enterprise Platform that returns a value type based on its parameters. It will return the same data value type as trueValue and falseValue.

Examples

IfElse(10>2,"success","fail") returns "success"
IfElse(10>2,1,2) returns 1
IfElse(10>2,StrToDate("10/17/2017","MM/DD/YYYY"),StrToDate("10/27/2017","MM/DD/YYYY")) returns 10/17/2017
IfElse(10<1,1,2) returns 2

There was a problem loading this topic