Formula Function Reference

Overview

1. Type Conversion

Converting between different data types is the most fundamental operation in an integration flow.

1. toint — Convert to integer

toint(value)

toint("42")        → 42
toint(3.99)        → 3     (truncates directly; does not round)
toint("abc")       → 0     (returns 0 when parsing fails)

• Truncates the decimal directly, does not round. Use math.round when you need rounding
• Returns 0 for unconvertible values; check the source data carefully
• If the source may be nil, check with presence first

2. tonumber — Convert to number

tonumber(value)

tonumber("42")         → 42
tonumber("3.14159")    → 3.14159
tonumber("")           → 0

• Preserves decimal precision
• Empty strings convert to 0
• Prefer toint for integers, tonumber when you need precision

3. tofloat — Convert to float

tofloat(value)

tofloat("99.99")    → 99.99
tofloat(42)         → 42.0
tofloat("abc")      → 0.0

• tonumber and tofloat behave essentially the same in daily use
• Returns 0.0 when parsing fails

4. tostring — Convert to string

tostring(value)

tostring(3.14)         → "3.14"
tostring(false)        → "false"
tostring(nil)          → ""      (nil converts to empty string)

• Use tostring first when type mismatch occurs during string concatenation
• nil becomes an empty string ""

5. tobool — Convert to boolean

tobool(value)

tobool("TRUE")    → true    (case-insensitive)
tobool("YES")     → true
tobool(1)         → true
tobool(0)         → false
tobool("no")      → false
tobool("")        → false

• Case-insensitive: "TRUE" and "True" are both treated as true
• "yes" and "YES" are also treated as true
• Everything except true / 1 / yes is false

2. String Operations

String processing is the most used scenario in integration flows. Mastering these formulas handles 90% of data-cleaning needs.

6. string.split — Split a string

string.split(string, separator)

string.split("Zhang San,Li Si,Wang Wu", ",")    → ["Zhang San", "Li Si", "Wang Wu"]
string.split("a|b|c", "|")          → ["a", "b", "c"]
string.split("hello world", " ")    → ["hello", "world"]

• The separator is removed from the result
• Supports regular expressions as the separator
• This is the same formula as Workato’s native .split, just written differently

7. string.join — Join a string

string.join(array, separator)

string.join(["Zhang San", "Li Si"], "、")        → "Zhang San、Li Si"
string.join(["2026", "07", "02"], "-")   → "2026-07-02"
string.join([], ",")                     → ""

• The reverse of split; the two are often used together
• Empty arrays return an empty string
• The separator can be any string, such as " - " or "、"

8. string.substring — Extract a substring

string.substring(string, startIndex, endIndex)

string.substring("hello world", 6, 11)   → "world"
string.substring("1234567890", 0, 4)     → "1234"
string.substring("hello", 1, -1)         → "ell"

• Indexes start at 0; the range is left-closed and right-open [start, end)
• Use a negative index or omit endIndex to extract to the end
• Commonly used to extract the last 4 digits of a phone number or identity information

9. string.contains — Check if a string contains

string.contains(string, substring)

string.contains("Zhang San Feng", "Zhang")       → true
string.contains("hello", "xyz")      → false
string.contains("abc", "")           → true    (empty string always matches)

• Case-sensitive: "Hello" does not contain "hello"
• Commonly used in conditional branching
• An empty substring always returns true

10. string.startsWith — Check if a string starts with

string.startsWith(string, prefix)

string.startsWith("CN13800138000", "CN")     → true
string.startsWith("hello", "he")             → true
string.startsWith("hello", "lo")             → false

• Commonly used to check field prefixes and coding rules
• Case-sensitive

11. string.endsWith — Check if a string ends with

string.endsWith(string, suffix)

string.endsWith("report.pdf", ".pdf")        → true
string.endsWith("hello world", "world")      → true
string.endsWith("hello", "he")               → false

• Commonly used to check file extensions and URL suffixes
• Case-sensitive

12. string.indexOf — Find the substring position

string.indexOf(string, substring)

string.indexOf("hello world", "o")     → 4
string.indexOf("hello", "xyz")         → -1
string.indexOf("abcabc", "b")          → 1     (the first occurrence)

• Indexes start at 0; the first character is at position 0
• Returns -1 (not 0) when not found
• Usually paired with string.contains: first check existence, then get the position

13. string.replace_first — Replace a substring

string.replace_first(string, old, new)

string.replace_first("a-b-c-d", "-", "/")          → "a/b-c-d"    (replaces only the first)
string.replace_first("13800138000", "138", "CN")    → "CN00138000"

• Replaces only the first match, not a global replacement
• For global replacement, call multiple times or combine with other methods

14. string.length — String length

string.length(string)

string.length("Hello World")    → 11
string.length("")           → 0
string.length("a b c")      → 5

• Each Chinese character counts as 1
• Spaces also count as 1
• Commonly used to validate phone number length (11 digits) and identity number length (18 digits)

15. string.upper — Convert to uppercase

string.upper(string)

string.upper("Abc123")     → "ABC123"
string.upper("hello world")   → "HELLO WORLD"

• Only converts English letters; Chinese and numbers are unaffected
• Commonly used to unify code formats and case-insensitive comparisons

16. string.lower — Convert to lowercase

string.lower(string)

string.lower("ABC")        → "abc"
string.lower("USER123")    → "user123"

• The reverse of string.upper
• Commonly used for case-insensitive matching: string.lower(a) == string.lower(b)

17. string.trim — Trim leading and trailing whitespace

string.trim(string)

string.trim("   Zhang San   ")    → "Zhang San"
string.trim("\nhello\n")    → "hello"
string.trim("a b c")        → "a b c"    (inner spaces are kept)

• Only removes leading and trailing whitespace; inner spaces are kept
• Almost always needed when importing data from external systems (Excel, forms, and so on)
• Can remove newline \n and tab \t characters

18. capitalize — Capitalize the first letter

capitalize(string)

capitalize("john")      → "John"
capitalize("ABC")       → "ABC"    (already all uppercase, unchanged)
capitalize("")          → ""

• Only capitalizes the first letter, not every word (use titleize for per-word capitalization)
• Unaffected when the string starts with a Chinese character

19. titleize — Capitalize each word

titleize(string)

titleize("john smith")          → "John Smith"
titleize("HELLO WORLD")         → "Hello World"
titleize("a tale of two")       → "A Tale Of Two"

• Difference from capitalize: capitalize only handles the first character of the whole string, while titleize handles each word
• Commonly used to format names and titles

20. to_phone — Filter phone characters

to_phone(string)

to_phone("138-1234-5678")         → "138-1234-5678"
to_phone("(010) 1234 5678")       → "(010)12345678"
to_phone("Phone: 13800138000")       → "13800138000"

• Automatically removes non-phone characters such as spaces and Chinese characters
• +, -, and () are kept
• Very useful for phone numbers imported from Excel or forms

21. smart_join — Smart join

smart_join(array)

smart_join(["Zhang San", "", "Li Si"])       → "Zhang San Li Si"
smart_join(["a", "  b  ", "c"])      → "a b c"
smart_join(["", "", ""])             → ""

• Smarter than string.join, handling empty values and whitespace automatically
• The separator is fixed to a space
• Especially useful for concatenating names and addresses

3. Math Operations

Basic operations such as rounding, flooring, and summation.

22. math.round — Round

math.round(number)

math.round(3.4)    → 3
math.round(3.5)    → 4
math.round(-1.5)   → -1

• 3.5 rounds to 4
• Negative numbers follow the same rule
• Use ceil or floor when you need a specific number of decimal places

23. math.max — Maximum

math.max(value1, value2, ...)

math.max(1, 2, 3, 4)     → 4
math.max(-1, -5, -3)     → -1
math.max(100, 50)        → 100

• Accepts any number of arguments
• Commonly used to pick the larger of two values

24. math.min — Minimum

math.min(value1, value2, ...)

math.min(1, 2, 3, 4)     → 1
math.min(-1, -5, -3)     → -5

• Pair with math.max to implement range limits

25. math.sqrt — Square root

math.sqrt(number)

math.sqrt(16)    → 4.0
math.sqrt(2)     → 1.414...
math.sqrt(0)     → 0.0

• Returns a floating-point number
• Negative numbers cause an error; ensure the value is ≥ 0 before calling

26. math.abs — Absolute value

math.abs(number)

math.abs(10)     → 10
math.abs(-3.14)  → 3.14
math.abs(0)      → 0

• Commonly used to compute differences and deviations

27. ceil — Round up

ceil(number, precision)

ceil(5.126)         → 6     (no precision, rounds up to integer)
ceil(5.129, 2)      → 5.13
ceil(5.001, 2)      → 5.01
ceil(-1.5)          → -1

• Equivalent to rounding up to an integer when the second argument is omitted
• The second argument specifies the number of decimal places
• Commonly used for “at least N” calculations

28. floor — Round down

floor(number, precision)

floor(5.129)         → 5     (no precision, rounds down to integer)
floor(5.999, 2)      → 5.99
floor(-1.5)          → -2

• The opposite of ceil
• Commonly used for “at most N” scenarios

29. sum — Sum

sum(array)

sum([10, 20, 30])    → 60
sum([])              → 0
sum([-1, 2, -3, 4])  → 2

• Empty arrays return 0
• Commonly used to total amounts and quantities
• Pair with pluck to extract a field first, then sum

4. Date and Time

High-frequency operations in integration flows for timestamps, date format conversion, and time zone conversion.

30. sysdate — System date and time

sysdate()

• Requires no parameters
• Returns a formatted string, not a timestamp
• Use now when you need a timestamp

31. now — Current timestamp

now()

• Returns a millisecond timestamp (13 digits)
• Commonly used to compute time differences and record operation times
• Convert to a readable date with long2date

32. ago — Past time

ago(amount, unit)

ago(1, "hour")       → timestamp one hour ago
ago(7, "day")        → timestamp seven days ago
ago(1, "month")      → timestamp one month ago

• Common scenarios: querying the last 7 days, getting yesterday’s timestamp
• Pair with from_now to represent a time range

33. from_now — Future time

from_now(amount, unit)

from_now(1, "hour")      → timestamp one hour later
from_now(30, "minute")   → timestamp 30 minutes later
from_now(1, "year")      → timestamp one year later

• The opposite direction of ago
• Commonly used to set deadlines and expiry times

34. date_add — Add or subtract a date

date_add(timestamp, amount, unit)

date_add(1743523200000, -1, "day")     → 1743436800000    (subtract one day)
date_add(1743523200000, 1, "month")    → 1746035200000    (add one month)
date_add(1743523200000, 1, "year")     → 1775059200000    (add one year)

• amount can be negative to subtract
• More flexible than ago/from_now, computing from any timestamp

35. date2long — Date to timestamp

date2long(dateString, format, unit)

date2long("2026-04-02", "yyyy-MM-dd", "ms")             → 1743523200000
date2long("04/02/2026", "MM/dd/yyyy", "ms")             → 1743523200000
date2long("20260402", "yyyyMMdd", "ms")                 → 1743523200000

• The third parameter unit: "ms" means milliseconds, "s" means seconds
• format must match the actual format of the date string
• Common format symbols: yyyy=year, MM=month, dd=day, HH=hour, mm=minute, ss=second

36. long2date — Timestamp to date

long2date(timestamp, format, unit)

long2date(1743565200000, "yyyy-MM-dd", "ms")              → "2026-04-02"
long2date(1743565200000, "yyyy-MM-dd HH:mm:ss", "ms")      → "2026-04-02 10:00:00"

• The reverse of date2long; the two are a pair
• format can be any custom format

37. string_to_date — String to date

string_to_date(dateString, format)

string_to_date("2026-04-02", "yyyy-MM-dd")          → 2026-04-02 00:00:00
string_to_date("20260402", "yyyyMMdd")              → 2026-04-02 00:00:00

• Returns a date object, not a timestamp
• Use this when you only need date comparison, not a timestamp

38. yweek — Week of the year

yweek(timestamp)

yweek(now())       → the current week of the year

• Returns an integer from 1 to 53
• Commonly used for weekly reports

39. yday — Day of the year

yday(timestamp)

yday(now())       → the current day of the year

• January 1 is day 1
• Leap years go up to 366

40. wday — Day of the week

wday(timestamp)

wday(now())       → today's day of the week (as a number)

• The week starts at 0: Sunday is 0, Monday is 1
• Check for weekdays: wday(now()) >= 1 and wday(now()) <= 5

41. beginning_of_month — Start of the month

beginning_of_month(timestamp)

beginning_of_month(now())   → timestamp of the first day of this month at 00:00:00

• Note: the example input is an April date, but it returns May 1
• Commonly used as the start time for monthly queries

42. end_of_month — End of the month

end_of_month(timestamp)

end_of_month(now())   → timestamp of the last day of this month at 23:59:59

• Returns 23:59:59, covering the last second
• Pair with beginning_of_month to define a monthly time range

43. in_time_zone — Time zone conversion

in_time_zone(timestamp, fromZone, toZone)

in_time_zone(now(), "Asia/Shanghai", "Asia/Tokyo")           → Tokyo time
in_time_zone(now(), "Asia/Shanghai", "Europe/London")        → London time

• Use standard time zone names: "Asia/Shanghai", "America/New_York", and so on
• Returns a timestamp, so format it with long2date

5. Lists and Collections

Data in integration flows often appears as arrays or lists. These formulas help you process them efficiently.

44. first — Get the first element

first(array, n?)

first(["Zhang San", "Li Si", "Wang Wu"])        → "Zhang San"
first(["Zhang San", "Li Si", "Wang Wu"], 1)     → ["Zhang San"]     (passing 1 returns an array)
first([])                            → nil

• Empty arrays return nil; handle null values carefully
• The return type differs with and without n: the former returns an element, the latter an array
• Very common when getting the latest record from a list

45. last — Get the last element

last(array, n?)

last(["Zhang San", "Li Si", "Wang Wu"])      → "Wang Wu"
last([])                          → nil

• Similar to first, empty arrays return nil
• Commonly used to get the latest record

46. count — Count

count(array)

count([])           → 0
count(["a", "b"])   → 2
count("hello")      → 5     (works on strings too)

• Works on strings too, returning the character count
• Empty arrays return 0

47. where — Conditional filter

where(array, condition)

where([1, 2, 3, 4], "item % 2 == 0")                    → [2, 4]      (even numbers)
where(["a", "ab", "abc"], "string.length(item) > 1")    → ["ab", "abc"]

• Always use item to refer to the current element in the condition
• The condition is written like a boolean expression in programming
• Object arrays can be filtered by field: "item.age > 18"

48. include — Check if included

include(array, value)

include(["Zhang San", "Li Si"], "Zhang San")     → true
include("hello", "he")              → true
include([1, 2, 3], 5)              → false

• Equivalent to string.contains when used on a string
• Checks element existence when used on an array

49. exclude — Check if excluded

exclude(collection, value)

exclude([1, 2, 3], 5)         → true     (does not contain 5)
exclude(["Zhang San"], "Zhang San")       → false    (contains)

• exclude(x, y) equals !include(x, y)
• Easy to reason about: exclude = not included

50. uniq — Deduplicate

uniq(array)

uniq(["a", "b", "a", "c"])       → ["a", "b", "c"]
uniq([1])                        → [1]
uniq([])                         → []

• Preserves the order of first occurrence
• Empty arrays return an empty array

51. pluck — Extract a field

pluck(array, fieldName)

pluck([{"age": 25}, {"age": 30}], "age")        → [25, 30]
pluck([{"name": "Zhang San", "age": 25}], "name")     → ["Zhang San"]

• A very high-frequency formula used in almost every integration flow
• Often chained with sum, where, and uniq

52. flatten — Flatten

flatten(array)

flatten([[1], [2, 3], [4]])       → [1, 2, 3, 4]
flatten([1, 2, 3])                → [1, 2, 3]     (already flat, unchanged)

• Handles multi-level nesting, recursively flattening all levels
• Commonly used after aggregating data from multiple APIs

53. seq.add_all — Concatenate lists

seq.add_all(array1, array2)

seq.add_all(["a"], ["b", "c"])         → ["a", "b", "c"]
seq.add_all([], [1, 2])               → [1, 2]

• Supports only two arrays; call multiple times for more
• Essentially concatenates two arrays end to end

54. tuple — Build a collection

tuple(value1, value2, ...)

tuple("Zhang San", 25, true)       → ["Zhang San", 25, true]
tuple(a, b)                  → [value of a, value of b]

• Can mix different types
• Commonly used to combine scattered variables into one array for further processing

6. Conditional Logic

Used for conditional branching, data validation, and null handling.

55. present — Existence check

present(value)

present(0)          → true     (0 is a valid value, exists)
present(nil)        → false
present([])         → false    (empty array does not "exist")
present(" ")        → true     (a whitespace string is not empty)

• Mutually exclusive with blank: present(x) equals !blank(x)
• 0 and " " (whitespace) are considered present; this is a common pitfall
• Commonly used in if branches: “continue only when the field has a value”

56. presence — Return the value if present

presence(value)

presence(nil)        → nil
presence("Zhang San")      → "Zhang San"
presence(0)          → 0      (0 is considered present, returns the original value)

• Difference from present: present returns a boolean, while presence returns the original value or nil
• Commonly used to provide a default value: presence(field) or "default"

57. blank — Blank check

blank(value)

blank(nil)         → true
blank(false)       → true
blank([])          → true
blank(0)           → false
blank(" ")         → false    (whitespace is not blank)

• Mutually exclusive with present
• blank(0) returns false, which differs from some languages
• Commonly used for data validation: “error when the field is blank”

58. is_empty — Check if empty

is_empty(value)

is_empty("")           → true
is_empty([1, 2, 3])    → false

• Similar to blank, but the logic is slightly narrower
• present / blank are more commonly used in daily work

7. Formatting and Tools

Practical utility formulas for data formatting, mapping lookup, and JSON parsing.

59. format_map — Format mapping

format_map(template, objectArray)

format_map("%{name} - %{amount} yuan", [{"name":"Product A", "amount":100}, {"name":"Product B", "amount":200}])
→ ["Product A - 100 yuan", "Product B - 200 yuan"]

• %{fieldName} is replaced with the value of the corresponding field in the object
• Useful for batch-generating formatted text, such as notification messages and report rows
• Maps multiple objects at once, returning an array

60. to_currency — Currency formatting

to_currency(number, symbol?, precision?)

to_currency(1000000)            → "¥1,000,000.00"
to_currency(99.9, "€", 2)      → "€99.90"

• The default currency symbol is ¥ (RMB)
• Automatically adds thousands separators
• Commonly used for quotations and invoices

61. lookup — Lookup table

lookup(map, key)

lookup({"status": "Approved", "code": 200}, "code")     → 200
lookup({"a": 1}, "b")                                → nil    (key does not exist)

• Returns nil when the key does not exist
• Similar to map[key] in programming
• Commonly used to read values from configuration objects

62. lookupMapping — Look up a mapping object

lookupMapping(objectName, mappingCategory, mappingValue, convertType)

• This is a platform-specific formula that depends on the data mapping table configured on the platform
• You must establish the mapping relationship on the platform before using it
• Commonly used for data dictionary conversion between systems

63. parseJsonStr2Map — JSON string to Map

parseJsonStr2Map(jsonString)

parseJsonStr2Map('{"status":"ok","data":[1,2,3]}')
→ {status: "ok", data: [1, 2, 3]}

• The JSON string must be valid JSON
• After parsing, access internal data with lookup or .fieldName
• API responses are often JSON strings; this formula is the first step of parsing

Appendix: Common Combinations

Pattern 1: Extract data from an API response

Parse JSON → extract fields → format output
parseJsonStr2Map(resp)  →  pluck(data, "name")  →  format_map("Name: %{name}", ...)

Pattern 2: Date range filtering

Get time range → filter data
beginning_of_month(now())  ~  end_of_month(now())  →  where(items, "item.date >= start and item.date <= end")

Pattern 3: Null-safe handling

presence(field) or "default value"

Pattern 4: Data cleaning pipeline

Trim → filter empty values → deduplicate
string.trim(field)  →  where(list, "not blank(item)")  →  uniq(list)

Pattern 5: String ↔ array conversion

String  →  split  →  [array processing]  →  join  →  string
"a,b,c" → ["a","b","c"] → dedupe/filter/sort → "a,b"

Tip: It is recommended to test each formula hands-on in an actual integration flow. When you encounter an error, first check whether the parameter types match and whether null values are handled.