How to create a custom calculated field

In this Article

• How Calculated Fields Work
• How to Create a Calculated Field
• How to Edit a Calculated Field
• A Note About Dividing

How Calculated Fields Work

Custom calculated fields are created using C# (C sharp). These fields are read-only since their values come from your formulas. You can create simple formulas using IF statements to return the calculated values, but your statements cannot contain for loops, do while loops, or method declarations.

When a calculated field is saved on the create and edit pages, Insightly will run a process in the background to automatically calculate all the record values for a custom field on newly created and existing records. This background process triggers a few seconds after the field definition is saved. If the field is saved multiple times, the background process will cancel the existing process and start again with the new field definition.

If you're not familiar with C# (C sharp) or any C programming language, you may want to get assistance from someone who is more familiar with coding. You can create 20 calculated fields per object. To save the calculated field, you must return a value and validate the formula.

Insightly has disabled the record change notification system when a calculated field is added or when the formula has been changed and all the values need to be recalculated to prevent users from receiving an overwhelming number of notifications.

How to Create a Calculated Field

1. Click the profile icon and click System Settings.

2. Click Objects and Fields.

3. Select the object you want to create the field for (you can create a custom calculated field for contacts, organizations, opportunities, leads, projects, and custom objects).

4. Select Object Fields from the Object Management menu.

5. Click New Field. 

6. Choose Calculated Field. 

7. Complete the required fields: 

   1. Field Label – Enter the name for your new field. For example, we are going to create a new field under Opportunities called “Revenue minus expenses (you won't be able to use the same field name twice)."

   2. Field Name - The name will automatically fill after you've added your Field Label, but you can alter it if needed. Note: Field Names are followed by __c (two low dashes, then c).

8. Select the Field Type for the returned value. 

   1. Calculated fields can be built using differing field types. For example, (Record_Id + Description) or (Bid_Amount + Created_Date). Calculated fields built using mixed field types will return text strings when saved.

9. Enter your formula in the Field formula. To help build the formula, click the Helpers button. 

   1. You can add values to the formula box by double-clicking or dragging any item from the Objects Fields or Functions list, typing directly in the field, or by copying and pasting a provided formula. 

   2. Under Object Fields, the different field types are sorted by type. For example, if you want to view the string fields, select String Fields. The Functions list is also sorted by their function types, such as Boolean (True or False), DateTime, and Decimal. When typing your formula, Insightly also has a built-in IntelliSense that provides drop-down options to help complete your code. To learn more about the functions, click any function and read the description.

10. Click Validate Formula to check your formula can compute. If Insightly cannot validate your formula, an error message will display above the formula field stating why.

11. Click Save Custom Field.

How to Edit a Calculated Field

If any changes need to be made to the calculated field, click on edit icon or click on edit this field via actions. While editing the calculated field, a checkbox option ‘trigger any related workflow automation processes’ will be displayed in the Calculated Field screen. The checkbox will be unchecked (disabled) by default. To trigger the related workflow automation processes, you need to enable (check) this option and save Custom Field.

A Note About Dividing

If you need to create a field that requires division, you will need to require the if/then statement to return 0 if the numeric field you are dividing by equals 0.

if ( Record.Hour3__c == 0) { return 0; } return (Record.Hour1__c - Record.Hour2__c) / Record.Hour3__c;

return (Record.Hour1__c - Record.Hour2__c) / Record.Hour3__c; 

Sample Formulas

Below are some sample formulas to get you started. If you need help more help building fields, contact our sales team at sales@insight.ly.

return DateTime.Now;

return Record.Revenue__c - Record.Cost__c;

 return CurrentUser.FIRST_NAME + " " + CurrentUser.LAST_NAME;