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