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:
// Process customer transaction amounts
SUM_LIST({{.dataSet.transaction_amounts}})
// Calculate loan payments using customer input
PMT({{.customInput.interest_rate}}, {{.customInput.loan_term}}, {{.customInput.principal}})
// Filter high-value customers from dataset
FILTER({{.dataSet.customers}}, "amount > {{.globalVar.premium_threshold}}")
// Calculate days since last transaction
DATEDIFF({{.customInput.last_transaction_date}}, NOW())
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:
SUM(100, 200, 300) // 600 - Total order value
SUM(1, 2, 3, 4) > 5 // true - Validation check
Token Examples:
// Sum multiple fee components for a customer
SUM({{.customInput.base_fee}}, {{.customInput.processing_fee}}, {{.globalVar.tax_amount}})
// Calculate total budget allocation
SUM({{.dataSet.marketing_budget}}, {{.dataSet.development_budget}}, {{.dataSet.operations_budget}})
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:
SUM_LIST([100, 200, 300]) // 600 - Sum array values
SUM_LIST([{amount: 100}, {amount: 200}], 'amount') // 300 - Sum by key
Token Examples:
// Sum customer transaction amounts
SUM_LIST({{.dataSet.customer_transactions}}, 'amount')
// Calculate total loan applications value
SUM_LIST({{.customInput.loan_applications}}, 'principal') > {{.globalVar.daily_limit}}
// Sum product prices in shopping cart
SUM_LIST({{.outputData.cart_items}}, 'price')
3. AVG
Calculates the average of multiple numeric arguments.
Syntax: AVG(number1, number2, ..., numberN)
Static Examples:
AVG(10, 20, 30) // 20 - Average score
AVG(85, 92, 78, 95) // 87.5 - Student grade average
Token Examples:
// Average customer satisfaction scores
AVG({{.customInput.q1_score}}, {{.customInput.q2_score}}, {{.customInput.q3_score}})
// Compare average with benchmark
AVG({{.dataSet.monthly_revenue}}) > {{.globalVar.target_average}}
4. AVG_LIST
Calculates the average of all elements in an array, optionally by a specific key.
Syntax: AVG_LIST(list, key)
Static Examples:
AVG_LIST([10, 20, 30]) // 20 - Average of array
AVG_LIST([{score: 85}, {score: 92}], 'score') // 88.5 - Average by key
Token Examples:
// Average customer age for segmentation
AVG_LIST({{.dataSet.customer_profiles}}, 'age')
// Average product rating threshold check
AVG_LIST({{.customInput.product_reviews}}, 'rating') >= {{.globalVar.quality_threshold}}
// Portfolio performance average
AVG_LIST({{.outputData.investment_returns}}, 'return_percentage')
5. MIN
Finds the smallest value among multiple numeric arguments.
Syntax: MIN(number1, number2, ..., numberN)
Static Examples:
MIN(10, 5, 20, 3) // 3 - Lowest value
MIN(100, 200) < 150 // true - Validation check
Token Examples:
// Find minimum required down payment
MIN({{.customInput.down_payment_option1}}, {{.customInput.down_payment_option2}})
// Ensure minimum credit score requirement
MIN({{.dataSet.credit_scores}}) >= {{.globalVar.min_credit_score}}
6. MIN_LIST
Finds the smallest value in an array, optionally by a specific key.
Syntax: MIN_LIST(list, key)
Static Examples:
MIN_LIST([10, 5, 20]) // 5 - Minimum array value
MIN_LIST([{price: 100}, {price: 50}], 'price') // 50 - Min by key
Token Examples:
// Find cheapest product price
MIN_LIST({{.dataSet.product_catalog}}, 'price')
// Minimum order value check
MIN_LIST({{.customInput.order_items}}, 'amount') >= {{.globalVar.min_order_threshold}}
// Lowest customer risk score
MIN_LIST({{.outputData.risk_assessments}}, 'risk_score') < {{.globalVar.high_risk_threshold}}
7. MAX
Finds the largest value among multiple numeric arguments.
Syntax: MAX(number1, number2, ..., numberN)
Static Examples:
MAX(10, 5, 20, 3) // 20 - Highest value
MAX(100, 200) > 150 // true - Validation check
Token Examples:
// Find maximum loan amount eligible
MAX({{.customInput.salary_based_limit}}, {{.customInput.collateral_based_limit}})
// Check if any transaction exceeds limit
MAX({{.dataSet.daily_transactions}}) <= {{.globalVar.transaction_limit}}
8. MAX_LIST
Finds the largest value in an array, optionally by a specific key.
Syntax: MAX_LIST(list, key)
Static Examples:
MAX_LIST([10, 5, 20]) // 20 - Maximum array value
MAX_LIST([{salary: 50000}, {salary: 75000}], 'salary') // 75000 - Max by key
Token Examples:
// Find highest customer spend
MAX_LIST({{.dataSet.customer_transactions}}, 'amount')
// Premium tier eligibility check
MAX_LIST({{.customInput.purchase_history}}, 'order_value') >= {{.globalVar.premium_threshold}}
// Highest performing investment
MAX_LIST({{.outputData.portfolio_performance}}, 'returns') > {{.globalVar.target_return}}
9. COUNT
Returns the total number of items in an array.
Syntax: COUNT(array)
Static Examples:
COUNT([1, 2, 3, 4]) // 4 - Array length
COUNT(['apple', 'banana', 'orange']) // 3 - String array count
Token Examples:
// Count customer orders for loyalty tier
COUNT({{.dataSet.customer_orders}}) >= {{.globalVar.gold_tier_minimum}}
// Validate minimum product selection
COUNT({{.customInput.selected_products}}) > 0
// Track application submissions
COUNT({{.outputData.monthly_applications}}) <= {{.globalVar.processing_capacity}}
Financial Functions
10. PMT
Calculates loan payment amount based on interest rate, number of payments, and principal.
Syntax: PMT(rate, nper, pv)
Static Examples:
PMT(0.05/12, 360, 100000) // Monthly payment for $100K loan at 5% for 30 years
PMT(0.08, 5, 50000) // Annual payment for $50K loan at 8% for 5 years
Token Examples:
// Calculate customer's monthly loan payment
PMT({{.customInput.interest_rate}}/12, {{.customInput.loan_term}}*12, {{.customInput.loan_amount}})
// Affordability check against customer income
PMT({{.dataSet.current_rates}}/12, {{.customInput.years}}*12, {{.customInput.principal}}) <= {{.customInput.monthly_income}} * 0.28
// Variable rate loan calculation
PMT(({{.globalVar.base_rate}} + {{.customInput.risk_premium}})/12, {{.customInput.term_months}}, {{.customInput.amount}})
11. PV
Calculates present value of future payments.
Syntax: PV(rate, nper, pmt)
Static Examples:
PV(0.05, 10, 1000) // Present value of $1000 annual payments for 10 years at 5%
PV(0.08/12, 360, 1500) // Present value of $1500 monthly payments
Token Examples:
// Calculate present value of customer's future payments
PV({{.globalVar.discount_rate}}, {{.customInput.payment_periods}}, {{.customInput.payment_amount}})
// Investment valuation check
PV({{.dataSet.market_rate}}, {{.customInput.investment_term}}, {{.customInput.annual_return}}) >= {{.customInput.minimum_investment}}
// Pension fund calculation
PV({{.globalVar.inflation_rate}}, {{.customInput.retirement_years}}, {{.outputData.monthly_pension}})
12. EMI
Calculates Equated Monthly Installment for loans.
Syntax: EMI(principal, rate, tenure)
Static Examples:
EMI(500000, 0.10, 20) // EMI for ₹5L loan at 10% for 20 years
EMI(1000000, 0.085, 15) // EMI for ₹10L loan at 8.5% for 15 years
Token Examples:
// Customer EMI calculation
EMI({{.customInput.loan_amount}}, {{.dataSet.current_interest_rate}}, {{.customInput.tenure_years}})
// EMI affordability validation
EMI({{.customInput.principal}}, {{.globalVar.standard_rate}}, {{.customInput.years}}) <= {{.customInput.net_income}} * 0.4
// Compare EMI options
EMI({{.customInput.amount}}, {{.dataSet.bank_rate}}, {{.customInput.term}}) < EMI({{.customInput.amount}}, {{.dataSet.competitor_rate}}, {{.customInput.term}})
Date/Time Functions
13. NOW
Returns the current date and time.
Syntax: NOW()
Static Examples:
NOW() // Current timestamp: "2024-06-19T10:30:00Z"
DATEDIFF(NOW(), "2024-01-01") // Days since New Year
Token Examples:
// Calculate account age
DATEDIFF(NOW(), {{.dataSet.account_creation_date}}) > {{.globalVar.maturity_days}}
// Time-sensitive offer validation
DATEDIFF({{.customInput.offer_expiry}}, NOW()) > 0
// Real-time age calculation
DATEDIFF(NOW(), {{.customInput.birth_date}}) / 365 >= {{.globalVar.minimum_age}}
14. TODAY
Returns the current date without time.
Syntax: TODAY()
Static Examples:
TODAY() // Current date: "2024-06-19"
DATEDIFF(TODAY(), "2024-06-01") // Days this month
Token Examples:
// Check if application is submitted today
DATEFORMAT({{.customInput.submission_date}}, "YYYY-MM-DD") === DATEFORMAT(TODAY(), "YYYY-MM-DD")
// Calculate days until deadline
DATEDIFF({{.globalVar.deadline_date}}, TODAY()) <= {{.globalVar.warning_days}}
// Weekend validation
DAY(TODAY()) !== 0 && DAY(TODAY()) !== 6 // Not Saturday or Sunday
15. DATEDIFF
Calculates difference between two dates in specified units.
Syntax: DATEDIFF(date1, date2, unit)
Static Examples:
DATEDIFF("2024-12-31", "2024-01-01", "days") // 365 - Days in year
DATEDIFF("2024-06-01", "2024-01-01", "months") // 5 - Months difference
Token Examples:
// Customer tenure calculation
DATEDIFF(TODAY(), {{.dataSet.customer_since}}, "years") >= {{.globalVar.loyal_customer_years}}
// Loan maturity check
DATEDIFF({{.customInput.maturity_date}}, TODAY(), "days") <= {{.globalVar.early_settlement_window}}
// Employment duration validation
DATEDIFF(TODAY(), {{.customInput.employment_start}}, "months") >= {{.globalVar.min_employment_months}}
16. DATECOMPUTE
Adds or subtracts time from a date.
Syntax: DATECOMPUTE(date, amount, unit)
Static Examples:
DATECOMPUTE("2024-01-01", "30", "days") // "2024-01-31" - Add 30 days
DATECOMPUTE("2024-06-15", "-3", "months") // "2024-03-15" - Subtract 3 months
Token Examples:
// Calculate loan maturity date
DATECOMPUTE({{.customInput.disbursement_date}}, {{.customInput.tenure_years}}, "years")
// Payment due date calculation
DATECOMPUTE(TODAY(), {{.globalVar.payment_grace_period}}, "days")
// Contract renewal date
DATECOMPUTE({{.dataSet.contract_start}}, {{.customInput.contract_duration}}, "months") <= DATECOMPUTE(TODAY(), "90", "days")
17. DATEPARSE
Converts date string to standardized format.
Syntax: DATEPARSE(date_string, format)
Static Examples:
DATEPARSE("15/06/2024", "DD/MM/YYYY") // Standardize date format
DATEPARSE("Jun 15, 2024", "MMM DD, YYYY") // Parse text date
Token Examples:
// Parse customer birth date
DATEPARSE({{.customInput.birth_date}}, {{.globalVar.input_date_format}})
// Standardize transaction dates
DATEPARSE({{.dataSet.transaction_date}}, "MM-DD-YYYY")
// Convert legacy date formats
DATEPARSE({{.outputData.legacy_date}}, {{.globalVar.legacy_format}})
18. DATEFORMAT
Formats date to specified string format.
Syntax: DATEFORMAT(date, format)
Static Examples:
DATEFORMAT("2024-06-19", "DD/MM/YYYY") // "19/06/2024" - European format
DATEFORMAT(NOW(), "YYYY-MM-DD HH:mm:ss") // "2024-06-19 10:30:00" - ISO format
Token Examples:
// Format customer display date
DATEFORMAT({{.customInput.appointment_date}}, {{.globalVar.display_format}})
// Generate report timestamps
DATEFORMAT(NOW(), "DD MMM YYYY") + " Report"
// Localized date formatting
DATEFORMAT({{.dataSet.event_date}}, {{.customInput.preferred_format}})19-25. Date Component Functions (DAY, MONTH, YEAR, HOUR, MINUTE, SECOND, WEEK)
Extract specific components from dates.
Static Examples:
DAY("2024-06-19") // 19 - Day of month
MONTH("2024-06-19") // 6 - Month number
YEAR("2024-06-19") // 2024 - Year
HOUR("10:30:45") // 10 - Hour
MINUTE("10:30:45") // 30 - Minute
SECOND("10:30:45") // 45 - Second
WEEK("2024-06-19") // 25 - Week number
Token Examples:
// Age verification
YEAR(TODAY()) - YEAR({{.customInput.birth_date}}) >= {{.globalVar.legal_age}}
// Monthly report filtering
MONTH({{.dataSet.transaction_date}}) === MONTH(TODAY())
// Business hours validation
HOUR(NOW()) >= {{.globalVar.business_start}} && HOUR(NOW()) <= {{.globalVar.business_end}}
// Weekend processing check
DAY({{.customInput.processing_date}}) !== 0 && DAY({{.customInput.processing_date}}) !== 6
Array Operations
26. FILTER
Filters array elements based on specified criteria.
Syntax: FILTER(array, condition)
Static Examples:
FILTER([1, 2, 3, 4, 5], "x > 3") // [4, 5] - Numbers greater than 3
FILTER([{age: 25}, {age: 35}], "age >= 30") // [{age: 35}] - Filter by age
Token Examples:
// High-value customers
FILTER({{.dataSet.customers}}, "total_spend > {{.globalVar.vip_threshold}}")
// Active loan applications
FILTER({{.customInput.applications}}, "status === 'active' && amount >= {{.globalVar.min_amount}}")
// Recent transactions
FILTER({{.outputData.transactions}}, "DATEDIFF(TODAY(), transaction_date, 'days') <= 30")
27. SORT
Sorts array elements by specified criteria.
Syntax: SORT(array, key, order)
Static Examples:
SORT([3, 1, 4, 2], "", "asc") // [1, 2, 3, 4] - Ascending order
SORT([{name: "John", age: 25}, {name: "Jane", age: 30}], "age", "desc") // Sort by age descending
Token Examples:
// Sort customers by credit score
SORT({{.dataSet.loan_applicants}}, "credit_score", "desc")
// Priority queue for support tickets
SORT({{.customInput.support_tickets}}, "priority_level", "asc")
// Revenue ranking
SORT({{.outputData.monthly_revenue}}, "amount", "desc")
28. LIMIT
Limits the number of elements returned from an array.
Syntax: LIMIT(array, count)
Static Examples:
LIMIT([1, 2, 3, 4, 5], 3) // [1, 2, 3] - First 3 elements
LIMIT([{name: "A"}, {name: "B"}, {name: "C"}], 2) // First 2 objects
Token Examples:
// Top 10 customers by spend
LIMIT(SORT({{.dataSet.customers}}, "annual_spend", "desc"), 10)
// Process only first 5 applications daily
LIMIT({{.customInput.daily_applications}}, {{.globalVar.daily_processing_limit}})
// Recent transaction preview
LIMIT(FILTER({{.outputData.transactions}}, "DATEDIFF(TODAY(), date, 'days') <= 7"), 5)
29. DISTINCT
Returns unique elements from an array.
Syntax: DISTINCT(array, key)
Static Examples:
DISTINCT([1, 2, 2, 3, 3, 4]) // [1, 2, 3, 4] - Unique numbers
DISTINCT([{city: "NYC"}, {city: "LA"}, {city: "NYC"}], "city") // Unique cities
Token Examples:
// Unique customer locations
DISTINCT({{.dataSet.customers}}, "city")
// Count unique product categories
COUNT(DISTINCT({{.customInput.order_items}}, "category"))
// Unique transaction types
DISTINCT({{.outputData.payment_history}}, "transaction_type")
Mathematical Functions
30. POW
Raises a number to the power of another number.
Syntax: POW(base, exponent)
Static Examples:
POW(2, 3) // 8 - 2 to the power of 3
POW(10, 2) // 100 - 10 squared
Token Examples:
// Compound interest calculation
{{.customInput.principal}} * POW(1 + {{.dataSet.interest_rate}}, {{.customInput.years}})
// Risk score calculation (exponential growth)
POW({{.customInput.base_risk}}, {{.globalVar.risk_multiplier}})
// Performance index calculation
POW({{.outputData.efficiency_ratio}}, 2) * {{.globalVar.performance_weight}}
31. SQRT
Returns the square root of a number.
Syntax: SQRT(number)
Static Examples:
SQRT(16) // 4 - Square root of 16
SQRT(25) // 5 - Square root of 25
Token Examples:
// Standard deviation calculation component
SQRT({{.outputData.variance}})
// Distance calculation in coordinate system
SQRT(POW({{.customInput.x2}} - {{.customInput.x1}}, 2) + POW({{.customInput.y2}} - {{.customInput.y1}}, 2))
// Risk assessment normalization
SQRT({{.dataSet.volatility_factor}}) * {{.globalVar.adjustment_coefficient}}
Security Functions
32. BASE64_ENCODE
Encodes data to Base64 format.
Syntax: BASE64_ENCODE(data)
Static Examples:
BASE64_ENCODE("Hello World") // "SGVsbG8gV29ybGQ=" - Encoded string
Token Examples:
// Encode customer data for secure transmission
BASE64_ENCODE({{.customInput.customer_id}} + ":" + {{.customInput.session_token}})
// Encode file content
BASE64_ENCODE({{.dataSet.document_content}})
// API authentication token encoding
BASE64_ENCODE({{.globalVar.api_key}} + ":" + {{.systemVar.ExecutionId}})
33. BASE64_DECODE
Decodes Base64 encoded data.
Syntax: BASE64_DECODE(encoded_data)
Static Examples:
BASE64_DECODE("SGVsbG8gV29ybGQ=") // "Hello World" - Decoded string
Token Examples:
// Decode received credentials
BASE64_DECODE({{.customInput.encoded_credentials}})
// Decode stored configuration
BASE64_DECODE({{.dataSet.config_data}})
// Decode API response payload
BASE64_DECODE({{.outputData.response_body}})
34. AES_ENCRYPT
Encrypts data using AES encryption.
Syntax: AES_ENCRYPT(data, key)
Static Examples:
AES_ENCRYPT("sensitive data", "encryption_key") // Encrypted output
Token Examples:
// Encrypt customer PII
AES_ENCRYPT({{.customInput.ssn}}, {{.globalVar.encryption_key}})
// Encrypt financial data
AES_ENCRYPT({{.dataSet.account_number}}, {{.systemVar.Environment}} + {{.globalVar.master_key}})
// Encrypt transaction details
AES_ENCRYPT(JSON.stringify({{.outputData.transaction_data}}), {{.globalVar.transaction_key}})
35. AES_DECRYPT
Decrypts AES encrypted data.
Syntax: AES_DECRYPT(encrypted_data, key)
Static Examples:
AES_DECRYPT("encrypted_string", "encryption_key") // Original data
Token Examples:
// Decrypt stored customer data
AES_DECRYPT({{.dataSet.encrypted_profile}}, {{.globalVar.decryption_key}})
// Decrypt payment information
AES_DECRYPT({{.customInput.encrypted_payment}}, {{.globalVar.payment_key}})
// Decrypt audit logs
AES_DECRYPT({{.outputData.encrypted_logs}}, {{.systemVar.Environment}} + {{.globalVar.audit_key}})
Data Processing
36. XMLPARSE
Parses XML data and extracts values.
Syntax: XMLPARSE(xml_string, xpath)
Static Examples:
XMLPARSE("<person><name>John</name></person>", "//name") // "John" - Extract name
XMLPARSE("<data><item id='1'>Value</item></data>", "//item/@id") // "1" - Extract attribute
Token Examples:
// Parse API response XML
XMLPARSE({{.customInput.xml_response}}, "//result/status")
// Extract customer data from XML
XMLPARSE({{.dataSet.customer_xml}}, "//customer/profile/credit_score")
// Parse configuration XML
XMLPARSE({{.globalVar.system_config}}, "//settings/feature[@name='loan_processing']/enabled")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