Using Calculated Fields

Calculated Fields in Spiro give users the ability to write simple expressions that can perform calculations using data in Spiro -- similar to functions in Excel.  Calculated fields have built-in functions, including:  if, not, min, max, sum, and round.


BUILT-IN OPERATORS AND FUNCTIONS

Math: +, -, *, /, %, ^, |, &

Also, all functions from Ruby's Math module, including SIN, COS, TAN, etc.

Comparison: <, >, <=, >=, <>, !=, =,

Logic: IF, AND, OR, NOT, SWITCH

Numeric: MIN, MAX, SUM, AVG, COUNT, ROUND, ROUNDDOWN, ROUNDUP

Selections: CASE (syntax see spec)

String: LEFT, RIGHT, MID, LEN, FIND, SUBSTITUTE, CONCAT, CONTAINS

EXAMPLES


amount * 0.2
amount * 10 / 100^2
AVG(amount, 10)
ROUND(amount % tax_custom_field)
CONCAT(user.first_name, ' ', user.last_name)
IF(amount < 10, 'Less than 10', 'More than 10')
IF(CONTAINS('Won', opportunity_stage), 'WON', 'LOST')

# CALCULATED FIELD TO (almost) LOOK LIKE CURRENCY:

CONCAT('$', license_price*12*no_users,'.00')

Variables can be any column that exists in the record, custom field or related record:

Variable name examples:
name
amount
user.email
organization.name
opportunity_stage.name

You can also use custom fields as variables.

----------

Populate Fields from Company on Contact using calculated fields: 

- account_name 
- account_address 
- account_city
- account_zipcode 
- account_country 
- account_phone
- account_description 
- account_state

Currently we can't bring custom fields from company to contact.

ISSUES

If a user writes a formula that causes argument error, the Calculated Field will display “Invalid Expression”.  Meaning, you have to go back into your field and write a valid expression that Spiro can understand.

The library that is used https://github.com/rubysolo/dentaku

Still need help? Contact Us Contact Us