> ## Documentation Index
> Fetch the complete documentation index at: https://docs.fentufsm.com/llms.txt
> Use this file to discover all available pages before exploring further.
# Formula Reference
> Complete reference for the Fentu Connect formula language used in field mapping
## 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')))
```
### 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
Use formulas in inbound field mapping
Use formulas in outbound field mapping
Define fields for formula reference