Custom Calculated Field examples

With custom calculated fields, you can create mathematical formulas using C# (C sharp). The formulas and their resulting values are based on the data in your records.

Below are some examples of custom calculated fields and their use cases. We know that many of our users are not familiar with C# (C sharp) or any other C programming language. Don't worry - you can start using these formulas even if you have no coding knowledge.

Just copy and paste the formulas below into the Field Formula text box when you are creating a field.

Custom Calculated Field Examples

Disclaimer: Insightly provides this article for informational purposes only. 

Example #1: Remaining balance

Use this formula to calculate the remaining balance of payment needed. The example below can be used by a Professional Services team to multiply the hours worked by the PS team's rate, then subtracts the down payment the customer may have paid in advance.

You will need to create the following custom fields first:

  • Hours Used
    • Field Type = Number
    • Label = Hours Used
  • Down Payment Amount
    • Field Type = Number
    • Label = Down Payment Amount
  • Professional Services Rate
    • Field Type = Number
    • Label = PS Rate

screenshot-crm.na1.insightly.com-2019.04.03-10-39-08.png

 

if (Record.Hours_Used__c !=null && Record.Down_Payment_Amount__c__c !=null)
{
return Record.Hours_Used__c * Record.PS_Rate__c - Record.Down_Payment_Amount__c__c;
} else {
 return 0;
}

Example #2: Calculate the number of days between two dates

This formula can be used to calculate how many days between the start date of a record and the date it will be closed. 

screenshot-crm.na1.insightly.com-2019.04.03-10-46-40.png

if (Record.ACTUAL_CLOSE_DATE !=null && Record.DATE_CREATED_UTC !=null)
{
 var close = Record.ACTUAL_CLOSE_DATE;
 var created = Record.DATE_CREATED_UTC;
 TimeSpan t = created - close;
Double numOfDays = t.TotalDays;
 return numOfDays + " days";
}

Example #3: Calculate future dates

Use this formula to calculate a future date in days, months, or annually. If your business is a  subscription-based service, this can be used to calculate an expiration or renewal date.

screenshot-crm.na1.insightly.com-2019.04.03-10-51-03.png

Annual

if(Record.DATE_CREATED_UTC != null)
{
 DateTime CreatedDate = Record.DATE_CREATED_UTC;
 DateTime renewalDate = CreatedDate.AddYears(1);
 return renewalDate;
}

Months

if(Record.DATE_CREATED_UTC != null)
{
 DateTime CreatedDate = Record.DATE_CREATED_UTC;
 DateTime renewalDate = CreatedDate.AddMonths(12);
 return renewalDate;
}

Days

if(Record.DATE_CREATED_UTC != null)
{
  DateTime CreatedDate = Record.DATE_CREATED_UTC;
 DateTime renewalDate = CreatedDate.AddDays(365);
 return renewalDate;
}

Example #4: Assign a region

This formula is used to identify the region of a United States-based Contact or Lead based on the State abbreviation recorded in their billing address. This could be used for lead assignment rules or for workflow automation. 

screenshot-crm.na1.insightly.com-2019.04.03-10-57-35.png

if (Record.ADDRESS_BILLING_STATE.Contains("AK,AZ,CA,HA,NV,NM,OR,UT,WA")) {
return "West";
} else if (Record.ADDRESS_BILLING_STATE.Contains("CO:ID:MT:KS:OK:TX:WY")) {
return "Central";
} else if (Record.ADDRESS_BILLING_STATE.Contains("CT,ME,MA,NH,NY,PA,RI,VT")) {
return "East";
} else if (Record.ADDRESS_BILLING_STATE.Contains("CO,ID,MT,KS,OK,TX,WY")) {
return "South";
} else if (Record.ADDRESS_BILLING_STATE.Contains("IL,IN,IA,MI,MN,MO,NE,ND,OH,SD,WI")) {
return "North";
}

 

Example #5: Calculating commission

This formula calculates the commission that will be earned from an Opportunity. It multiplies the commission rate by the Opportunity's value.

You will need to create the following custom field first:

  • Commission Rate Percent
    • Field Type = Number
    • Label = Commission Rate Percent

screenshot-crm.na1.insightly.com-2019.04.03-11-00-41.png

return (Record.Commission_Rate_Percent__c / 100) * Record.OPPORTUNITY_VALUE;

 

Example #6: Add two sets of hour fields together

This formula adds two sets of hours fields and produces the total of the two. You can use this to calculate the number of hours an employee works in a day.

You will need to create the following custom fields first:

  • Record Hour 1
    • Field type = Number
    • Label = Hour 1
  • Record Hour 2
    • Field type = Number
    • Label = Hour 2

screenshot-crm.na1.insightly.com-2019.04.03-11-02-24.png 

if (Record.Hour_1__c !=null && Record.Hour_2__c !=null)
{
return Record.Hour_1__c + Record.Hour_2__c;
}
Have more questions? Submit a request
Top