Formula Functions

Converts an expression defined in a function argument into the Boolean expression. If the expression's values is 0, False is returned; otherwise, True is returned. The return type is Boolean.

Example: =CBool(A11+B11-C11*0.05) (where A11, B11 and C11 are the links used in a function argument)

Converts an expression defined in a function argument to a value of the Date type. The CDate function argument should be a valid date expression. CDate recognizes date formats according to the locale setting of the system. The return type is DateTime.

Example: =CDate(A2-B2) (where A2 and B2 are the links used in a function argument)

Converts an expression defined in a function argument to a string. If the CStr function argument equals null, CStr returns a run-time error; otherwise, it returns a string of characters. The return type is String.

Example: =CStr(A12) (where A12 is the link used in a function argument)

Converts an expression defined in a function argument to a value of the Double type. The return type is Double.

Example: =CDbl(A12/B2) (where A12 and B2 are the links used in a function argument)

Converts an expression defined in a function argument to a value of the Single type. If the expression defined in the function argument lies outside the acceptable range for the Single type, an error occurs. The return type is Float.

Example: =CSng(A12) (where A12 is the link used in a function argument)

Converts an expression defined in a function argument to a value of the Decimal type. The return type is Decimal.

Example: =CDec(A12*0.15) (where A12 is the link used in a function argument)

Converts an expression defined in a function argument to a value of the Integer type. The return type is Int32.

Example: =CInt(A10) (where A10 is the link used in a function argument)

Converts a numeric value to a value of the Short type. The return type is Int16.

Example: =CShort(B2) (where B2 is the link used in a function argument)

Converts a numeric value to a value of the Long type. The return type is Int64.

Example: =CLong(B12) (where B12 is the link used in a function argument)

Removes all leading spaces or parsing characters from the specified character expression, or all leading 0 bytes from the specified binary expression. The return type is String.

Example: =LTrim(CStr(A12)) (where A12 is the link used in a function argument)

Removes all trailing spaces or parsing characters from the specified character expression, or all trailing 0 bytes from the specified binary expression. The return type is String.

Example: =RTrim(CStr(A12)) (where A12 is the link used in a function argument)

Removes all leading and trailing spaces or parsing characters from the specified character expression, or all leading and trailing 0 bytes from the specified binary expression. The return type is String.

Example: =Trim(CStr(A12)) (where A12 is the link used in a function argument)

Replaces the format item in a specified formatting string (format) with the text equivalent of the arguments (arguments). The return type is String.

Example: =Format('Currency: . . . . . . . . {0:C}; Account: . . . . . . . . {1:N}', A1, B1) (where A1 and B1 are the links used as a function arguments; 0, 1 are the specifiers indicating where the arguments will be inserted; C is the currency format specifier; and N is the number format specifier)

Returns a string that has been converted to uppercase. The string argument is any valid string expression. The return type is String. If string contains a null value, the null value is returned.

Example: =UCase(CStr(A12)) (where A12 is the link used in a function argument)

Returns a string that has been converted to lowercase. The string argument is any valid string expression. The return type is String. If string contains a null value, the null value is returned.

Example: =LCase(CStr(A12)) (where A12 is the link used in a function argument)

Returns the position of the first occurrence of one string (findString) within another (string). The return type is String.

Example: =InStr(CStr(A12), 'rur') (where A12 is the link used in a function argument)

Returns the position of the last occurrence of one string (findString) within another (string), starting from the right side of the string. The return type is String.

Example: =InStrRev(CStr(A12), 'rur') (where A12 is the link used in a function argument)

Returns an integer containing either the number of characters in a string or the nominal number of bytes required to store a variable. The return type is Int32.

Example: =Len(CStr(A12)) (where A12 is the link used in a function argument)

Returns a string containing a specified number of characters from the left side of a string. The return type is String. If string contains the null value, the null value is returned.

Example: =Left(CStr(A12), 3) (where A12 is the link used in a function argument)

Returns a string containing a specified number of characters from the right side of a string. The return type is String. If string contains a null value, the null value is returned.

Example: =Right(CStr(A12), 3) (where A12 is the link used in a function argument)

Returns a string in which a specified substring (oldValue) has been replaced with another substring (newValue). The return type is String.

Example: =Replace(CStr(A12), 'rur', 'eur') (where A12 is the link used in a function argument)

Right-aligns the characters in a specified string (string), padding with the specified characters (paddingChar) on the left for a specified total width (width). The return type is String.

Example: =PadLeft(CStr(A12), 3, '%') (where A12 is the link used in a function argument). In this example the PadLeft function adds padding character/s at the begining of the string.

Left-aligns the characters in a specified string (string), padding with the specified characters (paddingChar) on the right for a specified total width (width). The return type is String.

Example: =PadRight(CStr(A12), 3, '%') (where A12 is the link used in a function argument). In this example the PadRight function adds padding character/s at the end of the string.

Returns the absolute value of a number.

Example: =Abs(A10-B10)

Here A10 and B10 are the links used in a function argument.

Returns the largest integer that is not greater than the argument.

Example: =Floor(A10-A12)

Here A10 and A12 are the links used in a function argument.

Returns the smallest integer that is not less than the argument.

Example: =Ceiling(A10-C11)

Here A10 and C11 are the links used in a function argument.

Returns a numeric expression, rounded to the specified precision (decimals).

Example: =Round((A10-B12), 5)

Here A10 and B12 are the links used in a function argument, and 5 is the number of digits after the decimal separator.

Returns the smaller of the two values.

Example: =Min(A10, A12) =Min(A10, 12)

Here A10 and A12 are the links used as function arguments)

Returns the greater of the two values.

Example: =Max(A12, A14) =Max(A12, 240)

Here A12 and A14 are the links used as function arguments.

Computes the value of x raised to the specified power (power).

Example: =Pow((A12, 2))

Here A12 is the link used as a function argument, and 2 is the power index.

The return type is DateTime.

Example: =DateAdd(CDate('31/01/1995'), 'm', -2) =DateAdd(Today(), 'y', 3) =DateAdd(Now(), 'd', 1)

The return type is Double.

Returns the day component of the date. The return type is Int32.

Example: =Day(Now())

Returns the day of week for the date. The return type is Int32.

Example: =DayOfWeek(Today())

Returns the day of the year for the date. The return type is Int32.

Example: =DayOfYear(Now())

Converts the specified integer value (day) to a string that contains the ordinal number in the current locale. For example, for the English locale, the string contains the short form of the ordinal number, such as 1st or 2nd. For numbers that are less than 1 or greater than 31, the function returns an empty string.

The return type is String.

Example: =DayOrdinal(1)

Returns the hours of the date. The return type is Int32.

Example: =Hour(Now())

Returns the minutes of the date. The return type is Int32.

Example: =Minute(Now())

Returns the month component of the date. The return type is Int32.

Example: =Month(Now())

Returns a string that contains the name of the month extracted from the specified date (date) in the current locale.

The return type is String.

Example: =MonthName(CDate('3/12/2018'))

Returns the business date and time according to the system date and time on the local computer. The return type is DateTime.

Example: =Now()

The return type is DateTime.

Example: =NowUTC()

Returns the seconds of the date. The return type is Int32.

Example: =Second(Now())

Returns the business date according to the system date and time on the local computer. The return type is DateTime.

Example: =Today()

The return type is DateTime.

Example: =TodayUTC()

Returns the year component of the date. The return type is Int32.

Example: =Year(Now())

Returns one of two values, depending on the evaluation of the expression: If the expression evaluates to True, the function returns the truePart value; otherwise, it returns the falsePart value.

The return type is determined by the value that the function returns.

Example: =IIf((A10-B10)<>0), CStr(A12), 'No data available') (where A10, A12, and B10 are the links used as a function arguments)

You can also use this function to set a field value to NULL.

Example:: =IIf((A10-B10)<>0), CStr(A12), NULL)

Returns nullValue if value is NULL; otherwise, returns value.

The return type is determined by the value that the function returns.

Example: =IsNull(A10, '0') (if A10 is NULL, returns 0; otherwise, returns the A10 value)

Returns NULL if value1 is equal to value2.

Returns the values in the specified range of rows in the specified column sorted in ascending order.

Example: Sort('0100','0145','B') (the values in rows from 0100 to 0145 in the B column will be sorted in ascending order)

Returns the values in the specified range of rows in the specified column sorted in descending order.

Example: SortD('0100','0145','B') (the values in rows from 0100 to 0145 in the B column will be sorted in descending order)

Returns the sum of the values in the specified interval. The return type is determined by the value that the function returns.

Example: =Sum('A11','A100') (where A11 and A100 are the links used in a function argument)

Returns the value (value_n) that corresponds to the first expression (expression_n) that evaluates to True. For example, expression_1 and expression_2 are Boolean expressions.

The return type is determined by the value that the function returns.

Example: =Switch(((A10-B10)<>0), A12, ((A10-B10)>0), B35)

Converts the external format of the object field parameter into the internal object presentation (for example, converts AccountCD to AccountID). Both arguments of the ExtToInt function must have the same data type.

Converts the external format of the object field parameter into the UI format. Both arguments of the ExtToUI function must have the same data type.

Returns the branch name in the tenant for the specified branch identifier (branchID).

If the specified branch identifier does not exist, the function returns an empty string.

Example: Report.GetBranchText('SOFT')

Gets the default value of the object field parameter in the external format.

Gets the default value of the object field parameter in the UI format.

Returns the description of the object field parameter as it is defined in the PXSelectorAttribute. Both arguments of the GetDescription function must have the same data type.

Returns the localized name of the object field parameter.

Returns the data type of the object field parameter.

Returns the mask of the object field parameter.

Converts the external format of the object field parameter into the internal object presentation (for example, converts AccountID to AccountCD). Both arguments of the IntToExt function must have the same data type.

Converts the internal format of the object field parameter into the UI format. Both arguments of the IntToUI function must have the same data type.

Converts the UI format of the object field parameter into the external object format. Both arguments of the UIToExt function must have the same data type.

Converts the UI format of the object field parameter into the internal object format. Both arguments of the UIToInt function must have the same data type.

Performs ExtToUI data conversion for the object period parameter. This function is used to get the period defined by the report's @StartPeriod and @EndPeriod dates.

Performs ExtToUI data conversion for the object period parameter with a time shift defined by object period shift argument. This function is used to get the period defined by the report's @StartPeriod and @EndPeriod dates.

Performs ExtToUI data conversion for the object period parameter year part. This function is used to get the period defined by the report's @StartPeriod and @EndPeriod dates.

Performs ExtToUI data conversion for the object period parameter year part with a time shift defined by object period shift argument. This function is used to get the period defined by the report's @StartPeriod and @EndPeriod dates.