Excel-like Function
This documentation provides comprehensive guidance on 30+ powerful functions for data processing, financial calculations, and business logic automation. These functions seamlessly integrate with Nected's token system, enabling dynamic workflows that process real-time data from user inputs, datasets, previous rule outputs, and external systems.
Function Categories:
Arithmetic: SUM, AVG, MIN, MAX (both single values and list operations)
Financial: PMT, PV, EMI for loan and investment calculations
Date/Time: NOW, TODAY, DATEDIFF, DATECOMPUTE, DATEPARSE, DATEFORMAT
Array Operations: FILTER, SORT, LIMIT, DISTINCT, COUNT
Mathematical: POW, SQRT for advanced calculations
Security: BASE64_ENCODE/DECODE, AES_ENCRYPT/DECRYPT
Data Processing: XMLPARSE for external data integration
Where to Access the Functions
1. Formula Editor Available across rule conditions, rule results, and workflow nodes. Ideal for straightforward calculations and expressions.
2. Custom JS Editor Available in rule results, custom code nodes, and workflow configurations. Provides full JavaScript flexibility for complex logic and data manipulation.
How to Write the Functions
1. Using Static Values Direct function calls with hardcoded parameters:
SUM(100, 200, 300) // Returns 600
AVG(10, 20, 30) // Returns 20
DATECOMPUTE("2024-01-01", "1", "month") // Adds 1 month
2. Using Tokens Dynamic function calls that process live data from your workflows:
Available Functions List
The available functions are grouped into categories based on their functionality. These include arithmetic functions, date and time functions, and array manipulation functions.
Arithmetic Functions
1. SUM
Calculates the total sum of multiple numeric arguments.
Syntax: SUM(number1, number2, ..., numberN)
Static Examples:
Token Examples:
2. SUM_LIST
Calculates the sum of all elements in an array, optionally by a specific key for objects.
Syntax: SUM_LIST(list, key)
Static Examples:
Token Examples:
3. AVG
Calculates the average of multiple numeric arguments.
Syntax: AVG(number1, number2, ..., numberN)
Static Examples:
Token Examples:
4. AVG_LIST
Calculates the average of all elements in an array, optionally by a specific key.
Syntax: AVG_LIST(list, key)
Static Examples:
Token Examples:
5. MIN
Finds the smallest value among multiple numeric arguments.
Syntax: MIN(number1, number2, ..., numberN)
Static Examples:
Token Examples:
6. MIN_LIST
Finds the smallest value in an array, optionally by a specific key.
Syntax: MIN_LIST(list, key)
Static Examples:
Token Examples:
7. MAX
Finds the largest value among multiple numeric arguments.
Syntax: MAX(number1, number2, ..., numberN)
Static Examples:
Token Examples:
8. MAX_LIST
Finds the largest value in an array, optionally by a specific key.
Syntax: MAX_LIST(list, key)
Static Examples:
Token Examples:
9. COUNT
Returns the total number of items in an array.
Syntax: COUNT(array)
Static Examples:
Token Examples:
Financial Functions
10. PMT
Calculates loan payment amount based on interest rate, number of payments, and principal.
Syntax: PMT(rate, nper, pv)
Static Examples:
Token Examples:
11. PV
Calculates present value of future payments.
Syntax: PV(rate, nper, pmt)
Static Examples:
Token Examples:
12. EMI
Calculates Equated Monthly Installment for loans.
Syntax: EMI(principal, rate, tenure)
Static Examples:
Token Examples:
Date/Time Functions
13. NOW
Returns the current date and time.
Syntax: NOW()
Static Examples:
Token Examples:
14. TODAY
Returns the current date without time.
Syntax: TODAY()
Static Examples:
Token Examples:
15. DATEDIFF
Calculates difference between two dates in specified units.
Syntax: DATEDIFF(date1, date2, unit)
Static Examples:
Token Examples:
16. DATECOMPUTE
Adds or subtracts time from a date.
Syntax: DATECOMPUTE(date, amount, unit)
Static Examples:
Token Examples:
17. DATEPARSE
Converts date string to standardized format.
Syntax: DATEPARSE(date_string, format)
Static Examples:
Token Examples:
18. DATEFORMAT
Formats date to specified string format.
Syntax: DATEFORMAT(date, format)
Static Examples:
Token Examples:
19-25. Date Component Functions (DAY, MONTH, YEAR, HOUR, MINUTE, SECOND, WEEK)
Extract specific components from dates.
Static Examples:
Token Examples:
Array Operations
26. FILTER
Filters array elements based on specified criteria.
Syntax: FILTER(list, condition, keysToReturn?)
Returns a filtered list/array based on the provided boolean condition and optionally returns only specified keys.
Static Example: Filter objects where k1 > 1 and only return k1.
Token Example: Filter customers whose total_spend is greater than the vip_threshold global variable.
27. SORT
Sorts array elements by specified criteria.
Syntax: SORT(array, key, order)
Static Examples:
Token Examples:
28. LIMIT
Limits the number of elements returned from an array.
Syntax: LIMIT(array, count)
Static Examples:
Token Examples:
29. DISTINCT
Returns unique elements from an array.
Syntax: DISTINCT(array, key)
Static Examples:
Token Examples:
Mathematical Functions
30. POW
Raises a number to the power of another number.
Syntax: POW(base, exponent)
Static Examples:
Token Examples:
31. SQRT
Returns the square root of a number.
Syntax: SQRT(number)
Static Examples:
Token Examples:
Security Functions
32. BASE64_ENCODE
Encodes data to Base64 format.
Syntax: BASE64_ENCODE(data)
Static Examples:
Token Examples:
33. BASE64_DECODE
Decodes Base64 encoded data.
Syntax: BASE64_DECODE(encoded_data)
Static Examples:
Token Examples:
34. AES_ENCRYPT
Encrypts data using AES encryption.
Syntax: AES_ENCRYPT(data, key)
Static Examples:
Token Examples:
35. AES_DECRYPT
Decrypts AES encrypted data.
Syntax: AES_DECRYPT(encrypted_data, key)
Static Examples:
Token Examples:
Data Processing
36. XMLPARSE
Parses XML data and extracts values.
Syntax: XMLPARSE(xml_string, xpath)
Static Examples:
Token Examples:
Conclusion
These notes give a tutorial-like explanation of all the facilities provided by our system with essential reference to arithmetic functions and their constants, date and time functions, and array-manipulating functions. For each defined function, the function description is given along with its purpose, syntax, examples of usage, and anticipated result.
The arithmetic functions such as SUM, SUM_LIST, AVG, AVG_LIST, MIN, MIN_LIST, MAX, and MAX_LIST enable users to do basic mathematical calculations of numbers or lists of numbers. These functions can accept not only static values but also the computed values by the application and other tokens making the functions useful in various points of applications.
The types of functions include the date and time functions which include; DAY, MONTH, YEAR, MINUTE, HOUR, second, week, now, today, DATEDIFF, and DATECOMPUTE. These functions provide for such basic functions as extraction of components of dates, calculations of differences between dates, and computations of new dates taking into consideration certain parameters.
The array manipulation functions including LIMIT, SORT, and FILTER enable proficient handling and processing of the arrays. These functions allow a programmer to sort, filter, and maybe limit the number of elements to be contained in an array; and as such, data manipulation of these arrays is simple and fast.
If one comprehends and applies these functions, then the users can improve their data processing to achieve efficiency and constructiveness in performances giving a view to the exactness of significance. This documentation is thus useful in the best exploitation of features in the various functions of our system.
Last updated