Overview

  • Formulas can be used to manipulate or transform the data on the fly. In this way you can adapt the data to match the destination system at the same time as transferring the data.
  • A formula can be used in the Source Field in either the Table Link or in the Field List.
  • To tell the system that you want this field to be a formula, you have to start the line with two #-signs. For example:  ##CURRENTDATE().
  • In formulas, single quote '' is used to enclose a text string - like in 'this is a text string'. And double quote "" is used to enclose field names (only needed if the field name contains blanks or special characters) - like in "Address 2".

 

Functions and Operators

Funtions and Operators can be used in Formulas to manipulate data during transfer.  

Operators in formulas

The following operators can be ...

 

ABS    

ABS();

Returns the absolute value of a numeric value (according to normal math).   Example:
##ABS("Number")
 
...

 

AFTER    

AFTER(data,substring);

Using this function, the string after the specified substring will be returned.   Example: Field name is "Postal Code and City" and the field ...

 

BEFORE    

BEFORE(data,substring);

Using this function, the data before the specified substring will be returned. The result is returned as a text.   Example: We have a ...

 

BeforeLast(data,substring)

Returns all data before the last occurence of .   Example:
BeforeLast('Here we are', ' ')  will return 'Here we'
  This ...

 

BlankValue(expr, expr2)

The BlankValue(expression, expression-if-blank) returns "expression-if-blank" if the "expression" evaluates to blank or null, otherwise it returns "expression". The function can be used instead ...

 

CASE    

Case (fields, value1, result1, value2, result2, default result);

  If you want a value dependent on the result of a comparison you use the formula ...

 

CONVERT    

CONVERT(data, 'from', 'to', ...);

 

Overview

  • Convert can be used to convert a string, part of a string or single character.
  • Using Convert, you
...

 

COPY    

Copy(data, from, length);

Using the Copy function, you can copy a section of a text.   Example 1:
The value of the field "Name"
...

 

CURRENTDATE();

CurrentDate will return current systemdate. Date format will automatically be converted to fit the destination format. If mapped to a text field, syntax
...

 

CURRENTDATETIME();

CurrentDateTime will return current systemdate and systemtime. DateTime format will automatically be converted to fit the destination format. If mapped to a text
...

 

CURRENTTIME();

CurrentTime will return current systemtime. Time format will automatically be converted to fit the destination format. If mapped to a text field, syntax
...

 

DBLOOKUP(DataSource, TableName, KeyField-1, KeyValue1, KeyField-n, KeyValue-n, ResultFieldName)

  With this function it is possible to lookup a value from a specific field in another table. ...

 

DEL    

DEL();

Deletes a specific character (or string of characters) in the beginning of a field.   Example 1:
##DEL("Name", 'Mr.') will delete 'Mr.' from
...

 

EQUALS    

EQUALS(data1, data2);

Formula for comparing data. Used within other formula instead of '=' which is reserved as assign parameter.   Example:
##CASE(EQUALS("Status", 'Closed'), 0,
...

 

ERROR    

ERROR(string);

Using this function you can force a Transfer to end with a specific error message. It makes most sence to use this function as ...

 

FORMAT    

Format(data, format);

Used to format the way data is inserted in the destination. Often used on Date fields when Rapidi has problems converting automatically or ...

 

GETBITS    

GETBITS(integer, bitstartpos, length);

  Example:
##GETBITS("NUM", 5, 3) If "NUM" has the value "01100101" (binary data) it will return the result "101".

 

GETBIT    

GETBITS(integer, bitstartpos, length);

  Example:
##GETBITS("NUM", 5, 3) If "NUM" has the value "01100101" (binary data) it will return the result "101".

 

GETFIELDVALUE(parent-record-field);

Used on Subtransfers to get the value of a field in the parent record.   Example:
##GETFIELDVALUE('Customer Number') used on a subtransfer under
...

 

IF    

IF(condition1, data1, condition2, data2, ... ,data-else);

Used to insert different values according to conditions set in relation to Source value   Example:
##IF("Account Balance"<("CreditLimit"+1000),'Take
...

 

IMPLODE    

IMPLODE(data, character [add on character]);

This function is used with the feature Gather. Using this, you can add together data from several records into one ...

 

ISBLANK    

IsBlank(expr)

The IsBlank(expression) function takes a field or an expression and return 1 (or true) if that field is blank or null or if the ...

 

ISEMAIL    

 IsEmail(<data>)

Returns true (1) if <data> is valid email address, else false (0). This functions uses a number of rules to test if a text ...

 

ISPHONE    

IsPhone(<data>)

Returns true (1) if <data> is a phone number, else false (0). This functions uses a number of rules to test if a text ...

 

LAST    

LAST(data, substring);

Using this function your program will return the string after the last occurrence according to what you specify.   Example:
If Source
...

 

LENGTH    

Length(<data>)

Returns the length of a text string

 

Localtime (<datetime>, <timezone>, <daylight saving>)

The Formula will adjust the specified <datetime> from UTC time to the timezone entered.
  • <datetime> is a variable
...

 

 LSLookup(<Link Storage Code>, <Link Field value>)

  With the LSLookup function, you can retrieve values from Link Storages. You ...

 

MESSAGE    

MESSAGE(string);

Using this function you can force a message to be written to the Log. It makes most sence to use this function as part ...

 

MIDDLE    

MIDDLE(data, substring);

Using this function, your program will return the middle string according to what you specify.   Example:
The field value is  'One
...

 

NULL    

NULL("FieldName");

The formula can be called with 1 argument. In this case it will check the value of the field and return 1 (true) if ...

 

POS    

POS(data, substring);

Position is location of the 1. Character in the substring. The returned position is 0-based - i.e. the first position in the string ...

 

POSTDEL    

POSTDEL(<field>,<chars>)

Will delete one or more chars at the end of a string field. <chars> can be a number field-list, like  '<10>' , which will delete ...

 

PREPEND    

PREPEND(data, substring);

Formula used in e.g. Axapta and XAL to where certain values are prepended with blanks.   Example 1:
##PREPEND("NAME",' ',30) Will insert
...

 

ReadFile(directory, filename);

Formula ReadFile will allow you to get files from your local file system. It takes two parameters, a directory and file name. It ...

 

SkipField();

Formula will omit sending the field to the destination system   Example 1:
##BlankValue(DBLookup('DESTDS', 'User', 'Alias', "Salesperson", 'Id'), SkipField()) Will send the Id
...

 

SPLIT    

SPLIT(data, integer[size] | character[split on character]);

 

Overview

  • Used with Scatter to split 1 field into several records.
  • Some databases has a big textfield
...

 

TODATE    

TODATE(date, month, year);

The function Todate can be used if you want to transfer date, month and year to a single field in the destination. ...

 

TODATETIME(date, month, year, hour, minute, second);

Todatetime can be used if you need to transfer date, month, year, hour, minute and seconds to a single ...

 

TOTIME    

TOTIME(hour, minute, second);

Totime can be used if you need to transfer hour, minute and seconds to a single field in the destination.   Example: ...

 

TOFLOAT    

TOFLOAT(data);

Often needed when using operators in other formulas. It converts the value to FLOAT which ensures a correct comparison on lookup when e.g. transferring ...

 

TOINTEGER(data);

This function can be used if you want to transfer data from a text field (date etc.) to an Integer.  It converts the value ...

 

This function returns a string where all characters have been converted to UpperCase.   Example:
##UPPERCASE(fieldA)   If fieldA contains the text "hello", the
...

 

  1. Getting Started
  2. Documentation
  3. HowTo
  4. Standard Integrations

 

Service News can now be found on our Product Updates Blog 

 

Remember to Subscribe to our Product Updates Blog to receive updates and product news.

 

 

 

 

Wheeled by Wagn v. 1.9.1