Feature Overview: Formula Columns

Formula columns are a type of Custom Column that lets you define a formula that gets evaluated to return a value. Formulas work similar to how they work in Excel with a few differences:

  • First, you can only refer to other cells in the same row
  • Also, formulas are written in JavaScript notation. For more information and tips for writing JavaScript, visit http://www.w3schools.com/js.
 Tip: For guided step-by-step instructions in the extension, follow the In-App Tour "Formula Column Complete Reference"

Add A Formula Column To Your Pipeline

  1. Click on a column header to insert a new Column
  2. Select the Formula option from the list
  3. Once the Formula Column has been added to the pipeline, click into one of the cells to add your JavaScript formula
  4. When finished, use Command + Enter to save your changes and compute the formula

Formula Scopes

By default, the formulas in a column are the same for every Box in your Pipeline, however, you can customize the formulas such that they are a different in each stage of the Pipeline, or even have a formula specific to one Box. 

  • Entire Column - the formula will execute for every Box in the Pipeline
  • Just this stage - overrides a formula that is defined on the entire column. The formula will only execute for the Boxes that are in the particular stage.
  • Just this Box - overrides stage scoped formulas and column scoped formulas. The formula will only execute for this particular Box.

Column Reference

To reference a column in a formula the syntax is:

$'Column Name'

For example, a "Deal Size" formula column can be a product of two Free Form columns:

= $'Number of Units' * $'Price per Unit'

Return Types

In your formula you should include an explicit "return" statement. The formula can return only one value.

Numbers- floats and integers are supported

= 1

String

= 'Hello World'

Date

= new Date (2014,1,28)

Boolean

= true

Array - only arrays of primitive types are allowed, and all elements of the array must be of the same primitive type

= [1,1,2,3,5,8]

Object - the values of the object can only be primitive types, and all values of the object must be of the same primitive type

= {'first': 10, 'second': 20}

Basic Math

= 1+1= 5*25= Math.pow(10, 10)

Date Convenience Functions

Working with dates in Javascript is quite painful, so we've added a few convenience functions. 

The following date convenience functions will compute the amount of time between two dates:

Streak.hourDifference(date1, date2) - returns the number of hours between two dates

Streak.dayDifference(date1, date2) - returns the number of days between two dates

Streak.weekDifference(date1, date2) - returns the number of weeks between two dates

Streak.monthDifference(date1, date2) - returns the number of months between two dates

Streak.yearDifference(date1, date2) - returns the number of years between two dates

Streak.workdayDifference(date1, date2) - returns the number of non-weekdays between two dates

For example, to know how many work days it took for a deal to close you can use the workday convenience function along with your Magic Columns:

= Streak.workdayDifference($'Date Created', $'Date Closed')

In addition, we've included the following date convenience functions to calculate a date after adding (or subtracting) a specified number of days to another date. 

Streak.addDays(date1, numDays) - this will add numDays to date1

Streak.addWorkdays(date1, numDays) - this will add numDays worth of workdays to date1

Conditional Formulas

Preform different actions under different conditions with 'if' statements:

= if($'Number of Units' > 5 {
     return 'true';
}
else {
     return 'false';
}
Contact Us