Details about this thread
  • State Not Answered
  • Replies 3 replies
  • Subscribers 310 subscribers
  • Views 1965 views
  • Users 0 members are here
Related content from around SugarClub

Reference: Calculated Field formulas!

Kyle Smith

I'm converting this post into a compendium of useful calculated field formulas that do not require any code customization to be effective.  Feel free to post some of your own calculated field formulas below (or make the existing ones more efficient) and I'll update the OP.  

 

1) Convert text string to formatted USA Phone Number

It's not pretty, but it'll take up to 17 characters/numbers (even 234///567////8910)and convert it to a standard USA format: +1 (234) 567-8910.  

 

Just copy and paste the below into Notepad, find & replace $phone_work with whatever phone number field you need.  Then put it in a calculated field > formula. 

concat("+1(",subStr(concat(toString(number(subStr($phone_work,0,1))),toString(number(subStr($phone_work,1,1))),toString(number(subStr($phone_work,2,1))),toString(number(subStr($phone_work,3,1))),toString(number(subStr($phone_work,4,1))),toString(number(subStr($phone_work,5,1))),toString(number(subStr($phone_work,6,1))),toString(number(subStr($phone_work,7,1))),toString(number(subStr($phone_work,8,1))),toString(number(subStr($phone_work,9,1))),toString(number(subStr($phone_work,10,1))),toString(number(subStr($phone_work,11,1))),toString(number(subStr($phone_work,12,1))),toString(number(subStr($phone_work,13,1))),toString(number(subStr($phone_work,14,1))),toString(number(subStr($phone_work,15,1))),toString(number(subStr($phone_work,16,1))),toString(number(subStr($phone_work,17,1)))),0,3),") ",subStr(concat(toString(number(subStr($phone_work,0,1))),toString(number(subStr($phone_work,1,1))),toString(number(subStr($phone_work,2,1))),toString(number(subStr($phone_work,3,1))),toString(number(subStr($phone_work,4,1))),toString(number(subStr($phone_work,5,1))),toString(number(subStr($phone_work,6,1))),toString(number(subStr($phone_work,7,1))),toString(number(subStr($phone_work,8,1))),toString(number(subStr($phone_work,9,1))),toString(number(subStr($phone_work,10,1))),toString(number(subStr($phone_work,11,1))),toString(number(subStr($phone_work,12,1))),toString(number(subStr($phone_work,13,1))),toString(number(subStr($phone_work,14,1))),toString(number(subStr($phone_work,15,1))),toString(number(subStr($phone_work,16,1))),toString(number(subStr($phone_work,17,1)))),3,3),"-",subStr(concat(toString(number(subStr($phone_work,0,1))),toString(number(subStr($phone_work,1,1))),toString(number(subStr($phone_work,2,1))),toString(number(subStr($phone_work,3,1))),toString(number(subStr($phone_work,4,1))),toString(number(subStr($phone_work,5,1))),toString(number(subStr($phone_work,6,1))),toString(number(subStr($phone_work,7,1))),toString(number(subStr($phone_work,8,1))),toString(number(subStr($phone_work,9,1))),toString(number(subStr($phone_work,10,1))),toString(number(subStr($phone_work,11,1))),toString(number(subStr($phone_work,12,1))),toString(number(subStr($phone_work,13,1))),toString(number(subStr($phone_work,14,1))),toString(number(subStr($phone_work,15,1))),toString(number(subStr($phone_work,16,1))),toString(number(subStr($phone_work,17,1)))),6,4))

I usually also throw in a dependency: 

greaterThan(strlen($phone_work),6)

This can probably be done cleaner with code customization, but the above works well for OnDemand users that aren't customizing yet.  

 

 

2) Calculating Age (Integer) using a Birthdate

Copy the below into notepad- find and replace $birthdate with the birthdate field of your choice.  

add(subtract(number(subStr(toString(today()),12,4)),number(subStr(toString($birthdate),6,4))),ifElse(greaterThan(monthofyear($birthdate),monthofyear(today())),-1,ifElse(greaterThan(monthofyear(today()),monthofyear($birthdate)),0,ifElse(greaterThan(subtract(number(subStr(toString(today()),5,2)),number(subStr(toString($birthdate),3,2))),-1),0,-1))))

This one was awful to test; toString(today()) gives you like 30 characters including a timestamp, even though it's just supposed to be days.  

 

 

3) Day of the week by name (submitter: Arpad Szabo)

The below formula evaluates a Date field and returns a String of which day of the week that date is on.  Copy the below to notepad, find & replace custom_date_field_c with your date field, then paste into a calculated Text Field.  

ifElse(equal(dayofweek($custom_date_field_c),1),"Monday",ifElse(equal(dayofweek($custom_date_field_c),2),"Tuesday",ifElse(equal(dayofweek($custom_date_field_c),3),"Wednesday",ifElse(equal(dayofweek($custom_date_field_c),4),"Thursday",ifElse(equal(dayofweek($custom_date_field_c),5),"Friday",ifElse(equal(dayofweek($custom_date_field_c),6),"Saturday","Sunday"))))))
Parents Reply Children
  • 0 in reply to Tony Romano

    Hi all, I'm struggling with calculated fields in general!

     

    1) I have a field which contains %s are text, for instance 66%, 100% etc. I need to convert this to a number to use it in other calculations and also because the search function on listview is useless (I believe so anyway) when it comes to searching based on conditions like <> 100% - because the 100% is stored as text. I can only search for exact match or starts with. If this was a number it would be much easier - so i tried to create a calculated field with the formula: number(field1). Just returns a blank even though the field contains the value 100%.

     

    2) My second problem is that calculated fields do not update automatically - I have tried using recalulate values within listview but that doesn't do anything. I need to edit individual records and save them for the formula to work (in the above example even this doesn't work!).

     

    I'm lost!

     

    PS: I'm not a developer so please bear with me!