Skip to main content

​
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.
ParameterTypeDescription
fieldstringThe 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.
ParameterTypeDescription
arrayarrayThe array to search
filterArrayarrayKey-value pairs to match
keystringThe field to return from the matched object
defaultanyOptional 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.
ParameterTypeDescription
fieldstringThe field containing the lookup key
objectNamestringThe Connect Object to search
objectFieldstringThe field in the object to match against
fieldLookupstringThe 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).
ParameterTypeDescription
entityNamestringThe Connect Object name
filtersarrayArray 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.
ParameterTypeDescription
textstringThe source text
startnumberStarting position (1-based)
lengthnumberNumber 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.
ParameterTypeDescription
numbernumberThe number to pad
digitsnumberDesired total length
characterstringPadding 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.
ParameterTypeDescription
datestringThe date to format
formatstringPHP 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.
ParameterTypeDescription
arrayarrayInput array
fieldMappingsJsonstringJSON 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.
ParameterTypeDescription
codestringSequence identifier
digitsstringNumber 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.
ParameterTypeDescription
fieldstringThe field name to read from
firstCharstringThe opening character to search for
firstNumnumberWhich occurrence of the opening character (1 = first)
lastCharstringThe closing character to search for
lastNumnumberWhich 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')))

​
Looking Up Related Data

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

Start with simple formulas and add complexity gradually. Test each step to ensure data flows correctly.
External data often contains null values. Use ifempty(), nullif(), or if(isblank()) to handle them gracefully.
When providing defaults, use values that make sense for your business logic rather than empty strings.
Break complex formulas into multiple mapping rows when possible, using intermediate fields.

​
Related Documentation

Inbound Connections

Use formulas in inbound field mapping

Outbound Connections

Use formulas in outbound field mapping

Connect Objects

Define fields for formula reference