ConnectIT 365

Functions

Owned by idyn | Support team
Last updated: Jun 04, 2024 by Help Desk Agent
8 min read

Functions

In this topic all available functions *are explained. The *FieldValue is the value before executing the function on it, while Par1, Par2 *and *Par3 are parameters that can be provided in the import/export function line.
For export functions, in some cases it can be usefull to provide an output format. For more information on formatting, please check the Microsoft Documentation.

An important function is the SETTEMPVAL function. You may use this function to store a value for using it later on.
In all other functions, in the parameter fields you can enter the code of the saved value OR type your own text, number etc.
In case a temporary value code is entered in the parameter field, the value for that code will be used. If the text in the parameter field is not recognized as the code of a temporary value, the entered value from the parameter field itself will be used.

Example: Using the SETTEMPVAL function on a field in the definition

  • FieldValue = 2

  • Par1 = FIELD_A

  • Saved temporary value:

    • Code = FIELD_A

    • Value = 2

      • Now the value ‘2’ is saved for using later on in the definition

  • On a later field in the definition for example use the ADD function

    • FieldValue = 8

    • Par1 = FIELD_A

      • ReturnValue = 10, here the value with code FIELD_A was added to the FieldValue, so: 8 + 2 = 10

  • Or

    • FieldValue = 8

    • Par1 = 4

      • ReturnValue = 12, here the number 4 was added to the FieldValue, so 8 + 4 = 12

In the next part you will find an overview of the available functions. In most of the examples no temporary values are used as this would make the manual to long, but keep in mind, it is almost always possible to use temporary values.

ABS
Calculates the absolute value of a number (Decimal, Integer or BigInteger). ABS always returns a positive numeric value or zero.
Example:

  • FieldValue = -36.44

  • ReturnValue = 36.44

ADD
Adds the value of Par1 to the FieldValue.
Example:

  • FieldValue = 6.3

  • Par1 = 2

  • ReturnValue = 8.3

ADDSPACEBEFOREUPPER
Adds a white space before each upper case character in a string.
Example: FieldValue = SalesHeader ReturnValue = Sales Header

CALCDATE
Calculates a new date that is based on a date expression and a reference date.
For further reference check: https://docs.microsoft.com/en-us/dynamics-nav/calcdate-function—date-
Example:

  • FieldValue = 16-12-2020

  • Par1 =

  • ReturnValue = 15-01-2021

COMPANYNAME
Returns the CompanyName of the current company.

CONCAT
Concatenates two or three values. Par1 will be added before the FieldValue, while Par2 will be added behind the FieldValue.
Example:

  • FieldValue = B

  • Par1 = A

  • Par2 = C

  • ReturnValue = ABC

CONVERTSTR
Replaces all chars in the FieldValue found in FromCharacters (=Par1) with the corresponding char in ToCharacters (=Par2) and returns the converted string. If the length of the FromCharacters parameter and the ToChars parameter are different, an exception is thrown. If the parameter FromCharacters or the parameter ToChars is empty, the FieldValeu is returned unmodified.
Example:

  • FieldValue = ABBDEGBR

  • Par1 = B

  • Par2 = X

  • ReturnValue = AXXDEGXR

CONVINTTOEXT (export only)
Converts the FieldValue to an external value, using the conversion table.
Advanced Example:

  • Make a conversion via ConnectIT Adminitration > Conversions

  • On the Conversion card in the lines section add the translation values.

  • In the import/export function line for Par1 select the Conversion Code.

CONVEXTTOINT (import only)
Converts the FieldValue to an internal value, using the conversion table.
Advanced Example:

  • Make a conversion via ConnectIT Adminitration > Conversions

  • On the Conversion card in the lines section add the translation values.

  • In the import/export function line for Par1 select the Conversion Code.

COPYSTR
Copies a substring of any length from a specific position in a string the FieldValue) to a new string.
Example: FieldValue = Hey hello you Par1 = 5 Par2 = 5 ReturnValue = hello

CREATEGUID
Returns a newly generated Guid.

DATETIME
Returns the current DateTime.

DATE2DMY
Return the day, month or year from the give date (FieldValue).
In case you want to return the day, par1 = 1.
In case you want to return the month, par1 = 2.
In case you want to return the year, par1 = 3.
Example:

  • FieldValue = 19/05/2022

  • Par1 = 2

  • ReturnValue = 5

DATE2DWY
Return the day, week or year from the give date (FieldValue).
In case you want to return the day, par1 = 1.
In case you want to return the week, par1 = 2.
In case you want to return the year, par1 = 3.
Example:

  • FieldValue = 19/05/2022

  • Par1 = 3

  • ReturnValue = 2022

DT2DATE
Converts a DateTime value (FieldValue) to a Data value (ReturnValue).

DT2TIME
Converts a DateTime value (FieldValue) to a Time value (ReturnValue).

DMY2DATE
Converts day, month, year information to a date.
Par1 = Day
Par2 = Month
Par3 = Year
The parameters can be either hard values or you can choose a temporary value code from an earlier set value (see SETTEMPVAL function).
Advanced Example:

  • For external property ‘day’

    • Function: SETTEMPVAL

    • FieldValue = 3

    • Par1 = TMPD

  • For external property ‘month’

    • Function: SETTEMPVAL

    • FieldValue = 11

    • Par1 = TMPM

  • For external property ‘year’

    • Function: SETTEMPVAL

    • FieldValue = 2023

    • Par1 = TMPY

  • For internal Date field

    • Function DMY2DATE

    • Par1 = TMPD

    • Par2 = TMPM

    • Par3 = TMPY

      • ReturnValue = 03-11-2023

  • DWY2DATE*
    Converts day, week, year information to a date.
    Par1 = Day
    Par2 = Week
    Par3 = Year
    The parameters can be either hard values or you can choose a temporary value code from an earlier set value (see SETTEMPVAL function).
    For example, see the DMY2DATE function, where month is week.

DELCHR
Deletes one or more characters in a string. For further information check https://docs.microsoft.com/en-us/dynamics-nav/delchr-function—code—text-.
Example:

  • FieldValue = Where will this go

  • Par1 = <>

  • Par2 = W

  • ReturnValue = here will this go

DELSTR
Deletes a substring from the FieldValue.
Par1 is the starting position from where to start deleting.
Par2 is the length and is an optional parameter.
Example:

  • FieldValue = X Product 5000

  • Par1 = 2

  • Par2 = 9

  • ReturnValue = X5000

DIVIDE
Divides the FieldValue by Par1.
Example:

  • FieldValue := 10

  • Par1 = 2

  • ReturnValue = 5

EXECUTE TASK (import only)
Par1 = the Task that has to be executed. For more information on Tasks, see the Task section.

FORMAT
Formats the FieldValue into a string, where Par1 = length and Par2 (optional parameter) is the Format String or Format Number.
For more info on format strings and number please check: https://docs.microsoft.com/en-us/dynamics-nav/format-function—code—text-.

FORMATDECIMAL (import only)
Returns the decimal value as expected by your Business Central DB.
Par1 = the decimal separator character.

FORMATDTZ
Formats the current datetime (if fieldvalue = empty) or date from the fieldvalue. For more information on formatting dates, check custom date and time format strings

GETTEMPVAL
Retrieves a formerly saved (with the SETTEMPVAL function) temporary value.

GETCOUNTRYNAME (export only)
Gets the country name for the country code (FieldValue).

GETCURRENTVALUE (import only)

Retrieves the current field value from the record that is currently being processed.
Expects the field number to be retrieved as parameter.
This can be used to retrieve default values that are not directly set in the import, but as a result of insert/modify triggers or field validations.

GETFIELDVALUE

Gets a field value from an unrelated record based on a table number (parameter 1), field number (parameter 2) and input value (field value).
This expects that based on the input value only 1 record can be found to return the value from the output field value (parameter 3).
If no record is found, this will result in an error.
If multiple records are found, this will also result in an error.

GETFIELDVALUE2

Gets a field value from an unrelated record based on a table number (parameter 1), field number (parameter 2) and input value (field value).
This expects that based on the input value only 1 record can be found to return the value from the output field value (parameter 3).
This function behaves similar to GETFIELDVALUE, except if 0 or more than 1 record is found based on the input.
If no record is found, this will result in an empty output value.
If multiple records are found, the value from the first record is returned.

GETNEXTLINENO
Gets the next line no. or entry no. You will need to provide the field no. of the line no. or entry no. field and on what the increment should be. Ususally, for line nos. the increment is 10000 and for entry nos. the increment is 1.

GLOBALLANGUAGE
Gets the global language ID.

INCSTR
Increases a positive number or decrease a negative number inside a string (the FieldValue) by one.

INSSTR
Inserts a substring (Par1) into a string (the FieldValue) at the given position (Par2).
Example:

  • FieldValue = The colors are pink and blue

  • Par1 = , orange

  • Par2 = 14

  • ReturnValue = The colors are pink, orange and blue

LINENO
Returns a line number, where Par1 is the interval between two numbers.

LOWERCASE
Changes uppercase chars to lowercase chars.
Example:

  • FieldValue = HeLLo

  • ReturnValue = hello

MULTIPLY
Multiplies the FieldValue with the value of Par1.

NOSERIES
Gets the next number in the number series with the code from Par1.

PADSTR
Changes the length of a string (the FieldValue) to a length that you define. Par1 is the lenth and optional Par2 is the Fill Character.
When Par2 is left empty spaces will be added to reach the length from Par1.
Example:

  • FieldValue = Hello

  • Par1 = 10

  • ReturnValue = “Hello “
    or:

  • FieldValue = Hello

  • Par1 = 10

  • Par2 = x

  • ReturnValue = Helloxxxxx

PARSEDATETIME
Returns a DateTime with the format as given in Par1.

POWER
Raises a number to a power. For example, you can use this function to square the number 2 to get the result of 4.
Example:

  • FieldValue = 64

  • Par1 = 0.5

  • ReturnValue = 8

RANDOM
Returns a pseudo random number, where Par1 is the maximum number.

RANDOMIZE
Call this function before RANDOM in case you do not want to use the same set of numbers to pick from each time.

REST / AZUREFUNCTION
See the chapter about REST functions.

ROUND
Rounds the value of a numeric variable (the FieldValue), where Par1 is the precision and Par2 is the direction. Both parameters are optional.
The default precision is the Amount Rounding Precision field from the GLSetup table.
The default direction is =, which means it rounds up or down to the nearest value.
For further info check the Microsoft Documentation.

ROUNDDATETIME
Rounds a DateTime value (the FieldValue) where Par1 is the precision and Par2 is the direction. Both parameters are optional.
The default precision is 1000.
The default direction is =, which means it rounds up or down to the nearest value.
Example:

  • FieldValue = 11/05/20 09:35:24.567

  • ReturnValue = 11/05/20 09:35:25.000

SERIALNUMBER
Gets a string that contains the serial number of the license file for your system.

SETTEMPVAL
Saves the FieldValue temporary in the Temporary Values table.
Example:

  • FieldValue = 2

  • Par1 = FIELD_A

SLEEP
Returns control to the operating system for a specified time (Par1, in milliseconds). When you use the SLEEP function, control is guaranteed to return to the operating system for at least Par1 milliseconds.

SPLITDESC
Splits the value at the last space or split character. Par1 is the maximum length of the result and Par2 (optional) is the split character.
If the length of FieldValue is no bigger than length, the FieldValue is returned unmodified.
Else, the string will be split at the last space or split character within the maximum length.

STRLEN
Returns the length of FieldValue.

STRPOS
Returns the position of a substring (Par1) in the FieldValue.

SUBSTRACT
Substracts Par1 from the FieldValue.
Example:

  • FieldValue = 8

  • Par1 = 3

  • ReturnValue = 5

TASK (import only)
Writes the FieldValue to the parameter value field of the selected parameter (Par2) for a certain task (Par1).
For more information on Tasks, see the Task section.

TIME
Returns the current time.

TODAY
Returns the current date.

TRIGGERIMP
Triggers the in the parameter of this function choses import definition to import data. This function is usually used together with the URLVALUE function.

UPPERCASE
Changes lowercase characters to uppercase characters.

URLVALUE
Stores the value for later use in a REST URL.
Usage example:
REST call 1: GetNewOrders. The response is a list with order nos. without further order details.
the next call, REST call 2: GetOrderDetails/ needs an order no. in the URL.
On de order no. field in the response of call 1, use function URLVALUE abd give it a name. For example ORDNO.
Then alter the REST URL of call 2 to GetOrderDetails/[ORDNO].
Make sure to trigger the import directly after setting the ORDNO value, else it will be overwritten with the next order no. from the first response. To do this, add function TRIGGERIMP right below function URLVALUE.

USERID
Returns the UserId.

Conditional use of fieldvalue
USECURRENT
Use the current fieldvalue, unless current fieldvalue is empty, then use the temp value that was set earlier (SETTEMPVAL) in the definition lines (Former Value).
There is one parameter to set, ‘Former Value’.
Usage Example
On line 5000, with the value of address 1, use the function SETTEMPVAL, name it ADD01.
On line 8000, with the value of address 2, use the function USECURRENT, for parameter Former Value select ADD01.
Now if address 2 has no value, the value of address 1 will be used. If address 2 has a value, that value will be used.
USEFORMER
This function does the opposite of USECURRENT.
Use the former fieldvalue that was set earlier (SETTEMPVAL) in the definition lines (Former Value), unless former fieldvalue is empty, then use the current value.
There is one parameter to set, ‘Former Value’.
Usage Example
On line 5000, with the value of address 1, use the function SETTEMPVAL, name it ADD01.
On line 8000, with the value of address 2, use the function USEFORMER, for parameter Former Value select ADD01.
Now if address 1 has no value, the value of address 2 will be used. If address 1 has a value, that value will be used.
USEDEFAULT
When the fieldvalue is empty, use the default value. This function has one parameter to set, ‘Default Value’.

WORKDATE
Returns the current session’s work date.

IDYN 2024