Formula Reference - Fentu Documentation

Overview

The Formula language in Fentu Connect allows you to transform, calculate, and manipulate data during field mapping. Formulas can combine multiple source fields, apply conditional logic, look up related data, and format values.

Formulas are case-insensitive. You can write data(), DATA(), or Data() - they all work the same.

Basic Syntax

Formulas use function-based syntax with parentheses:

functionName(argument1, argument2, ...)

You can nest functions:

concat(data('firstName'), ' ', data('lastName'))

Data Access Functions

data(field)

Retrieves a value from the current record.

Parameter	Type	Description
`field`	string	The field name to retrieve

Examples:

data('CardCode')
data('CustomerName')
data('BPAddresses')

Returns: The field value, or null if the field doesn’t exist.

findvalue(array, filterArray, key, default)

Finds a value in an array of objects based on filter criteria.

Parameter	Type	Description
`array`	array	The array to search
`filterArray`	array	Key-value pairs to match
`key`	string	The field to return from the matched object
`default`	any	Optional default value if no match found

Examples:

findvalue(data('BPAddresses'),['AddressName'=>'Ship to'],'Street')
findvalue(data('Contacts'),['Type'=>'Primary'],'Email','no-email@example.com')

Returns: The value of key from the first matching object, or default if no match.

vlookup(field, objectName, objectField, fieldLookup)

Looks up a value from another Connect Object.

Parameter	Type	Description
`field`	string	The field containing the lookup key
`objectName`	string	The Connect Object to search
`objectField`	string	The field in the object to match against
`fieldLookup`	string	The field to return

Example:

vlookup('CardCode', 'fsmcustomer', 'erp_id', 'customer_id')

Returns: The lookup field value, or null if no match.

textvlookup(text, objectName, objectField, fieldLookup)

Like vlookup but accepts a direct text value (or formula result) instead of a field name. This is essential for chaining lookups — passing the result of one lookup into another.

When multiple records match, textvlookup returns the most recently updated one (ordered by updated_at descending).

Examples:

textvlookup(concat('BP-', data('Code')), 'fsmcustomer', 'erp_id', 'customer_id')

Chaining pattern — resolve a value through multiple tables:

textvlookup(
  vlookup('Asset', 'FsmAsset', 'fsm_id', 'fsmcustomerid'),
  'fsmcustomer', 'fentufsm_external_id', 'erp_id'
)

textvlookuplike(text, objectName, objectField, fieldLookup)

Like textvlookup but uses case-insensitive partial matching (ILIKE ‘%text%’).

Unlike textvlookup, textvlookuplike returns the oldest match (ordered by updated_at ascending).

Example:

textvlookuplike(data('PostalCode'), 'regions', 'postal_codes', 'region_name')

get_entity_data(entityName, filters)

Retrieves records from a Connect Object with optional filters. Results are returned ordered by created_at descending (newest first).

Parameter	Type	Description
`entityName`	string	The Connect Object name
`filters`	array	Array of filter conditions: `[field, operator, value]`

Example:

get_entity_data('fsmcustomer', [['status', '=', 'active']])

String Functions

concat(value1, value2, …)

Concatenates multiple values into a single string. Examples:

concat(data('FirstName'), ' ', data('LastName'))
concat('WO-', data('DocNum'), '-', year(now()))

mid(text, start, length)

Extracts a substring from text.

Parameter	Type	Description
`text`	string	The source text
`start`	number	Starting position (1-based)
`length`	number	Number of characters to extract

Example:

mid(data('Phone'), 1, 3)  // First 3 characters

len(text)

Returns the length of a text string. Example:

len(data('Description'))

trim(text)

Removes leading and trailing whitespace. Example:

trim(data('CustomerName'))

replace(search, replace, subject)

Replaces text in a string. (Note: parameter order follows PHP str_replace) Example:

replace('-', '', data('Phone'))  // Remove dashes from phone

begins(text, match)

Checks if text starts with a specific string. Example:

begins(data('CardCode'), 'C')  // Returns true if starts with 'C'

find(char, text)

Finds the position of a character in text. Returns a 1-based position, or false if not found. Example:

find('@', data('Email'))  // 1-based position of @ in email

striptags(text)

Removes HTML tags from text. Example:

striptags(data('Description'))

splitindex(text, delimiter, index)

Splits text by delimiter and returns the element at index. Example:

splitindex(data('FullName'), ' ', 0)  // First part (first name)
splitindex(data('FullName'), ' ', 1)  // Second part (last name)

leadingcharacters(number, digits, character)

Pads a number with leading characters.

Parameter	Type	Description
`number`	number	The number to pad
`digits`	number	Desired total length
`character`	string	Padding character (default: ‘0’)

Example:

leadingcharacters(data('DocNum'), 8, '0')  // "00001234"

Date and Time Functions

now()

Returns the current date and time. Example:

now()  // "2024-01-15 10:30:45"

today()

Returns the current date (without time). Example:

today()  // "2024-01-15"

year(date)

Extracts the year from a date. Example:

year(data('DocDate'))  // "2024"
year(now())

month(date)

Extracts the month from a date (01-12). Example:

month(data('DocDate'))  // "01"

day(date)

Extracts the day from a date (01-31). Example:

day(data('DocDate'))  // "15"

date(year, month, day)

Creates a date from components. Example:

date(2024, 1, 15)  // "2024-01-15"

datetimeformat(date, format)

Formats a date/time string.

Parameter	Type	Description
`date`	string	The date to format
`format`	string	PHP date format string

Common format codes:

Y - 4-digit year
m - 2-digit month
d - 2-digit day
H - 24-hour hour
i - Minutes
s - Seconds

Example:

datetimeformat(data('CreateDate'), 'Y-m-d')
datetimeformat(now(), 'd/m/Y H:i')

timeformat(hours, minutes, seconds, format)

Formats time components. Example:

timeformat(data('Hours'), data('Minutes'), 0, 'H:i')

Conditional Functions

if(condition, trueValue, falseValue)

Returns different values based on a condition.

The if() function uses PHP truthiness for the condition. This means "0", 0, "", null, and false are all treated as false. If a field could legitimately contain "0" as a valid value, use an explicit comparison like != null instead of a bare truthy check.

Examples:

if(data('Amount') > 1000, 'Large', 'Small')
if(isblank(data('Email')), 'no-email@example.com', data('Email'))
if(vlookup('Code', 'table', 'field', 'result') != null, 'Found', 'Not found')

When checking if a lookup returned a value, prefer ifempty() over if() to avoid repeating the lookup:

ifempty(vlookup('Code', 'table', 'field', 'result'), 'fallback')

case(value, match1, result1, match2, result2, …, default)

Switch/case statement - returns result for first matching value. Example:

case(data('Status'),
  'O', 'Open',
  'C', 'Closed',
  'P', 'Pending',
  'Unknown'
)

ifempty(value1, value2)

Returns value2 if value1 is empty. Uses PHP’s empty() check internally.

Like isblank(), this treats "0", 0, "", null, false, and [] as empty. If "0" is a valid value in your data, use if(value != null, value, fallback) instead.

Example:

ifempty(data('MobilePhone'), data('Phone'))

nullif(field)

Returns the field value or null if empty. Takes a field name (not a value) and reads directly from the current record.

Uses PHP’s empty() check — "0", 0, "", and null all return null.

Example:

nullif('OptionalField')

or(value1, value2, …)

Logical OR - returns true if any value is true. Example:

or(data('IsActive'), data('IsPending'))

and(value1, value2, …)

Logical AND - returns true only if all values are true. Example:

and(data('IsActive'), data('HasContract'))

not(value)

Logical NOT - inverts a boolean value. Example:

not(data('IsDeleted'))

isblank(value)

Returns true if value is empty/null. Maps directly to PHP’s empty() function.

empty() in PHP treats ALL of the following as blank: null, "", "0", 0, false, []. If you need to distinguish between null and "0", use an explicit == null comparison instead.

Example:

isblank(data('Email'))

isnumber(value)

Returns true if value is numeric. Example:

isnumber(data('Quantity'))

ispickval(value, compareValue)

Checks if value equals compareValue. Example:

ispickval(data('Status'), 'Active')

Array Functions

maparray(array, fieldMappingsJson)

Transforms an array of objects using field mappings.

Parameter	Type	Description
`array`	array	Input array
`fieldMappingsJson`	string	JSON string defining the mapping

Example:

maparray(data('LineItems'), '[{"source":"ItemCode","target":"sku","type":"field"}]')

setarray(array, value, key)

Sets a value for all items in an array. Example:

setarray(data('Items'), 'USD', 'Currency')

getobjectarray(object, key, default)

Gets a value from an object/array by key. Example:

getobjectarray(data('Address'), 'City', 'Unknown')

reversearray(array)

Reverses an array. Example:

reversearray(data('Items'))

upsert_array(array, search, replace)

Updates or inserts values in an array. Example:

upsert_array(data('Addresses'), ['AddressType'=>'Shipping'], ['Street'=>'123 Main St'])

merge_array_by_key(array1, array2, key)

Merges two arrays by matching a key field. Example:

merge_array_by_key(data('Items'), data('Prices'), 'ItemCode')

explode(delimiter, string)

Splits a string into an array. Example:

explode(',', data('Tags'))

implode(glue, array)

Joins an array into a string. Example:

implode(', ', data('Categories'))

JSON Functions

jsondecode(jsonString, default)

Parses a JSON string into an array. Always returns arrays (not objects). Example:

jsondecode(data('MetaData'), [])

get_json_value(jsonText, key)

Gets a value from JSON using dot notation. Example:

get_json_value(data('JsonField'), 'address.city')

Utility Functions

text(value)

Converts a value to string. Example:

text(data('Amount'))

bool(field)

Converts a field to boolean. Takes a field name (not a value) and reads from the current record. Uses PHP’s FILTER_VALIDATE_BOOLEAN, which understands string representations of booleans. Recognized values:

True: "true", "yes", "on", "1", 1, true
False: "false", "no", "off", "0", 0, false

Example:

bool('IsActive')

email(field)

Extracts an email address from text. Takes a field name (not a value) and reads from the current record. Uses regex to find the first email address in the field’s text content. Example:

email('ContactInfo')  // Extracts first email found in the field

runnumber(code, digits)

Generates a running number sequence.

Parameter	Type	Description
`code`	string	Sequence identifier
`digits`	string	Number of digits (default: ‘08’)

Example:

runnumber('WO', '06')  // Returns next number like "000123"

setdata(field, value)

Sets a value in the data context and returns it. Example:

setdata('calculatedTotal', data('Quantity') * data('Price'))

extractstring(field, firstChar, firstNum, lastChar, lastNum)

Extracts text between the nth occurrence of two characters. Takes a field name and reads from the current record.

Parameter	Type	Description
`field`	string	The field name to read from
`firstChar`	string	The opening character to search for
`firstNum`	number	Which occurrence of the opening character (1 = first)
`lastChar`	string	The closing character to search for
`lastNum`	number	Which occurrence of the closing character (1 = first)

Example:

extractstring('Description', '[', 1, ']', 1)  // Text between first [ and first ]
extractstring('Code', '-', 2, '-', 3)  // Text between 2nd and 3rd dash

extractvalues(pattern, input, matchIndex, index)

Extracts values using regex. Example:

extractvalues('/\d+/', data('Text'), 0, 0)  // First number found

Common Formula Patterns

Handling Null Values

ifempty(data('MobilePhone'), ifempty(data('Phone'), 'No phone'))

Formatting Names

concat(data('FirstName'), ' ', data('LastName'))

Conditional Field Value

if(ispickval(data('Type'), 'Company'), data('CompanyName'), concat(data('FirstName'), ' ', data('LastName')))

vlookup('CategoryID', 'categories', 'id', 'name')

Extracting from Nested Arrays

findvalue(data('BPAddresses'), ['AddressType'=>'Billing'], 'Street')

Date Formatting

datetimeformat(data('CreateDate'), 'Y-m-d')

Generating Unique IDs

concat('FSM-', runnumber('FSM', '08'))

Tips and Best Practices

Test Formulas Incrementally

Start with simple formulas and add complexity gradually. Test each step to ensure data flows correctly.

Handle Nulls Explicitly

External data often contains null values. Use ifempty(), nullif(), or if(isblank()) to handle them gracefully.

Use Meaningful Default Values

When providing defaults, use values that make sense for your business logic rather than empty strings.

Keep Formulas Readable

Break complex formulas into multiple mapping rows when possible, using intermediate fields.

Inbound Connections

Use formulas in inbound field mapping

Outbound Connections

Use formulas in outbound field mapping

Connect Objects

Define fields for formula reference

​Overview

​Basic Syntax

​Data Access Functions

​data(field)

​findvalue(array, filterArray, key, default)

​vlookup(field, objectName, objectField, fieldLookup)

​textvlookup(text, objectName, objectField, fieldLookup)

​textvlookuplike(text, objectName, objectField, fieldLookup)

​get_entity_data(entityName, filters)

​String Functions

​concat(value1, value2, …)

​mid(text, start, length)

​len(text)

​trim(text)

​replace(search, replace, subject)

​begins(text, match)

​find(char, text)

​striptags(text)

​splitindex(text, delimiter, index)

​leadingcharacters(number, digits, character)

​Date and Time Functions

​now()

​today()

​year(date)

​month(date)

​day(date)

​date(year, month, day)

​datetimeformat(date, format)

​timeformat(hours, minutes, seconds, format)

​Conditional Functions

​if(condition, trueValue, falseValue)

​case(value, match1, result1, match2, result2, …, default)

​ifempty(value1, value2)

​nullif(field)

​or(value1, value2, …)

​and(value1, value2, …)

​not(value)

​isblank(value)

​isnumber(value)

​ispickval(value, compareValue)

​Array Functions

​maparray(array, fieldMappingsJson)

​setarray(array, value, key)

​getobjectarray(object, key, default)

​reversearray(array)

​upsert_array(array, search, replace)

​merge_array_by_key(array1, array2, key)

​explode(delimiter, string)

​implode(glue, array)

​JSON Functions

​jsondecode(jsonString, default)

​get_json_value(jsonText, key)

​Utility Functions

​text(value)

​bool(field)

​email(field)

​runnumber(code, digits)

​setdata(field, value)

​extractstring(field, firstChar, firstNum, lastChar, lastNum)

​extractvalues(pattern, input, matchIndex, index)

​Common Formula Patterns

​Handling Null Values

​Formatting Names

​Conditional Field Value

​Looking Up Related Data

​Extracting from Nested Arrays

​Date Formatting

​Generating Unique IDs

​Tips and Best Practices

​Related Documentation

Inbound Connections

Outbound Connections

Connect Objects

Overview

Basic Syntax

Data Access Functions

data(field)

findvalue(array, filterArray, key, default)

vlookup(field, objectName, objectField, fieldLookup)

textvlookup(text, objectName, objectField, fieldLookup)

textvlookuplike(text, objectName, objectField, fieldLookup)

get_entity_data(entityName, filters)

String Functions

concat(value1, value2, …)

mid(text, start, length)

len(text)

trim(text)

replace(search, replace, subject)

begins(text, match)

find(char, text)

striptags(text)

splitindex(text, delimiter, index)

leadingcharacters(number, digits, character)

Date and Time Functions

now()

today()

year(date)

month(date)

day(date)

date(year, month, day)

datetimeformat(date, format)

timeformat(hours, minutes, seconds, format)

Conditional Functions

if(condition, trueValue, falseValue)

case(value, match1, result1, match2, result2, …, default)

ifempty(value1, value2)

nullif(field)

or(value1, value2, …)

and(value1, value2, …)

not(value)

isblank(value)

isnumber(value)

ispickval(value, compareValue)

Array Functions

maparray(array, fieldMappingsJson)

setarray(array, value, key)

getobjectarray(object, key, default)

reversearray(array)

upsert_array(array, search, replace)

merge_array_by_key(array1, array2, key)

explode(delimiter, string)

implode(glue, array)

JSON Functions

jsondecode(jsonString, default)

get_json_value(jsonText, key)

Utility Functions

text(value)

bool(field)

email(field)

runnumber(code, digits)

setdata(field, value)

extractstring(field, firstChar, firstNum, lastChar, lastNum)

extractvalues(pattern, input, matchIndex, index)

Common Formula Patterns

Handling Null Values

Formatting Names

Conditional Field Value

Looking Up Related Data

Extracting from Nested Arrays

Date Formatting

Generating Unique IDs

Tips and Best Practices

Related Documentation