Hi how do i get the seconds like 0-60 (so counting and resetting) from something like Now()-CreatedDate?

Hi All,

     I want to create an url-field within the Opportunity page that contains value an url that in concatinated. 

It should combine a html-textstring and add FirstName + underscore + LastName+Space+Description  in one string, something like this

           Just like this:john_smith Hydrocarbon Area Remediation Mar13.

how to resolve this issues

Hi, I am very new to formulas and SF and can't seem to get SF syntac right. I keep getting errors on this formula. I would really appreciate some help! 

 

If ((RecordType.Id), ("012E0000000MoYF") || ( RecordType.Id), ("012E0000000Mu8O") || ( RecordType.Id), ("012E0000000MoYN") || ( RecordType.Id), ("012E0000000MoYQ")),
( IF(ISPICKVAL(npe01__PreferredPhone__c, "Home"), HomePhone,
IF(ISPICKVAL(npe01__PreferredPhone__c, "Other"), OtherPhone,
IF(ISPICKVAL(npe01__PreferredPhone__c, "Work"), npe01__WorkPhone__c,
IF(ISPICKVAL(npe01__PreferredPhone__c, "Mobile"), MobilePhone, Account.Phone
),
( IF(ISPICKVAL(npe01__PreferredPhone__c, "Home"), HomePhone,
IF(ISPICKVAL(npe01__PreferredPhone__c, "Other"), OtherPhone,
IF(ISPICKVAL(npe01__PreferredPhone__c, "Work"), npe01__WorkPhone__c,
IF(ISPICKVAL(npe01__PreferredPhone__c, "Mobile"), MobilePhone, Employer_Number__c
)

I am trying to come up with a validation rule that will keep us from deleting executed contracts.  Can you please help with the formula?

I have custom object which is a child of Opportunity Object through a master detail relation ship.

 

I want to get the recordtype name of Opportunity on the child custom object record.

 

Formula on Opportunity Fied(Opp_rec_name__c):$ recordtype.name

 

Formula on Custom Object(child):Opportunity__r.Opp_rec_name__c. Do a formula field like print the current Child Object record type name. Not the Opportunity record type name.

 

I do not want  to use the trigger to print the parent object record type name on the child object.

 

Is there any other way I can print the name???

 

 

 

 

First off im very new to salesforce

 

Basically I want to build model number field based on 3 other fields (two of which are picklist). But it seems i can only use one case statement in the forumla field

 

Currently this works

---------------------------------

CASE( Color_Display__c,
"Tri Color", "LSS-TC",
"Full Color", "LSS-FC",
"Single Color", "LSS-SC","")

 

(oh also i dont know why i need the last comma and double quotes at the end of this case. If i leave it "Single Color", "LSS-SC") it gives me error when i check my syntax. It says, "Error: Incorrect number of parameters for function 'CASE()'. Expected 6, received 7" If anyone can explain that to me that would be great. But anyways the above works, i just would like to know why i need the last  ,""

 

So on with the question:

 

CASE( Color_Display__c,
"Tri Color", "LSS-TC",
"Full Color", "LSS-FC",
"Single Color", "LSS-SC","")

 

CASE(Software__c,
"Video Star", Model__c = Model__c & "VS-",
"Image Star", Model__c = Model__c &  "IS-","")

 

Model__c = Model__c & Pitch

 

Also i know it gives an error referencing its own self. This is how i build string in VB or any other programming language. How am i to do this in the formula field?

 

So at the end i just want the model field to read for example

LSS-TCVS-20

if the options Tri Color, Video Star and what ever numeric value is entered into pitch.

 

Thanks for any and all help in advance.

 

Hi Team,

 

I'm trying to understand how custom settings work.

 

I created a hierarchical custom setting called Tax and it's got two fields in it.

 

Role(text)

Percent(percent)

 

I created two data sets, one where the assigned profile is System Administrator and another one where the assigned profile is Standard User.

 

The data sets are as follows:

 

Name:Admin

Location: System Administrator

Role:System Administrator

Percent: 10

 

Name:stdUser

Location: Standard User

Role: Standard User

Percent: 32

 

Then, I created a formula text field with the following syntax

 

"Your role is $Setup.Taxes__c.Role__c"

 What I wanted to happen,is that if I'm logged in as an Admin, I should see "Your role is System Administrator" but if I'm logged in as a standard user I should then see "Your role is Standard User" but regardless of the user I'm logged in as, the formula displays Your role is $Setup.Taxes__c.Role__c

 

Any advise?

 

Thanks

 

Pablo Gonzalez

 

 

 

Hi,

In a worflow, can /i set the field update to set a field value to anoither field value. 

 

The field type is User(lookup).

 

Right now I'm being foirced to choose a specific user, but like to just set the value of this field to another field in the same object.

 

Thanks!

 

-Haroon

Hi everyone,

 

we are trying to find a way on adding optional products on quotes. Since you cannot change the quantity to "Zero" we've tried to find a way in displaying "not offered" in the total price for those products. I've created a text field formula, that would work perfectly, if we weren't using 3 different currencies in our company EUR, GBP, USD).

Unfortunately the formula is too long (Error message: Compiled formula is too big to execute (5,341 characters). Maximum size is 5,000 characters).

 

Therefore we need to find a way to shorten it. Unfortunately we've couldn't find a way internally, that's why I'm asking the community.

 

The formula, that needs to be shortened is the following. SP_Q__c is a currency formula field totaling the price of a product incl. any discounts. The formula should convert the number into a text, while keeping the decimals.

 

CASE(Description,

"Optional", "not offered",

 "optional", "not offered",

 CASE( Quote.Opportunity.CurrencyIsoCode ,

"EUR",  ( "EUR"  & " " &

 

IF(FIND(".",TEXT(SP_Q__c))=0, TEXT(SP_Q__c)+".00",

IF (LEN(TEXT(SP_Q__c))-FIND(".",TEXT(SP_Q__c))=1, TEXT(SP_Q__c)+"0",

     LEFT(TEXT(SP_Q__c),FIND(".",TEXT(ROUND(SP_Q__c,2)))+2)))),

 

"GBP",  ( "GBP"  & " " &

IF(FIND(".",TEXT(SP_Q__c))=0, TEXT(SP_Q__c)+".00",

IF (LEN(TEXT(SP_Q__c))-FIND(".",TEXT(SP_Q__c))=1, TEXT(SP_Q__c)+"0",

     LEFT(TEXT(SP_Q__c),FIND(".",TEXT(ROUND(SP_Q__c,2)))+2)))),

 

 ( "USD"  & " " &

IF(FIND(".",TEXT(SP_Q__c))=0, TEXT(SP_Q__c)+".00",

IF (LEN(TEXT(SP_Q__c))-FIND(".",TEXT(SP_Q__c))=1, TEXT(SP_Q__c)+"0",

     LEFT(TEXT(SP_Q__c),FIND(".",TEXT(ROUND(SP_Q__c,2)))+2))))))

 

Your help is really appreciated.

 

Thanks

 

Kathi

I thought of sharing a validation rule which will work only while creation of record and it will not work while editing the record

 

--> just give one additional condition as && ( ISNULL(CreatedDate )

 

(Since validation rule will check the createdDate field before its been created in DB)

 

 

Hi,

 

I have a check box in one of my object and its functioanlity is, if a record is created with that check box "checked", no other records can be created with that checkbox "checked" , need to show an error that " other record has been checked "

 

So I have written a validation rule as below :

 

AND(VLOOKUP($ObjectType.xenonproto__Level2__c.Fields.xenonproto__Goal_Level2__c , $ObjectType.xenonproto__Level2__c.Fields.Name , Name)==True,
VLOOKUP($ObjectType.xenonproto__Level2__c.Fields.Id , $ObjectType.xenonproto__Level2__c.Fields.Name , Name) != Id)

 

 

Note: here Goal_Level2__c is of type checkbox.

 

but the above validation is not working for me.

 

Please anyone suggest me.

 

Thanks and Regards,

Haripriya.

HI I would like to set a workflow rule that when is met, gives as workflow action an email alert.

 

My problem is about editing the rule criteria. As I'm totally new to formulas I don't understand what sintax to use to say:

 

ISCHANGED ( Amount) or that the close date or the probability are changed..I don't unedrstand the use of the operators and functions...

 

Can please someone help me?

 

Many thanks

Hi,

 

I working on a billing application where I have bill custom object that contains a balance custom field.

 

I want to set the balance custom field to the total amount on the bill on save when the bill is created. Should this be done through a trigger or a formula field?

 

i also need to update the balance field whenever a payment is made, and would like to know if a formula field can be updated, through  a workflow where a payment is made against a bill?

 

If there are better ways to do this please let me know.

 

Cheers

Hello - I am trying to calculate the number of business hours it took for one of my data analsys to triage a new Regestration (account), i need to have business hours between 7:00 am to 6:00pm and excluding weekends. 

 

Any help would be AWESOME!

 

Cheers,

Niki

Hi,

     I have date field , suppose i am selecting 4/3/2013 in date field

 

 

i have 3 more fields

 

Date( text) 

Month( Picklist)

Year( Text) 

 

i want to auto populate date field value in to these 3 fields by date,month, year 

 

how can i do this, any help will be appriciated

 

Thanks in Advance.

Banti

Hello!

 

I have a large amount of data that needs to be cleaned up through salesforce inorder to create reports. There are numbers in the following formats:

 

                                            ABC##x##ABC, ABC-####-ABC, ABC100mABC, ABC100MABC

                                                   The ABC* represent text strings of varying lengths.

 

I would like to "pull out" the areas numbers/ text I idicated in blue in a seperate filed,

 

I have created a formula field as follows:

 

IF(CONTAINS("x", Item_Name__c), MID(Item_Name__c, FIND("x", Item_Name__c), 5), IF(CONTAINS("100M:100m", Item_Name__c), "100m", IF(CONTAINS("1630", Item_Name__c), "16x30", IF(CONTAINS("2040", Item_Name__c), "20x40", IF(CONTAINS("3050", Item_Name__c), "30x50", IF(CONTAINS("4070", Item_Name__c), "40x70", NULL))))))

 

When I uploaded the data however, the formula did not work and returned only "blank" values. I am worried it is too complex or incorrectly formatted. Any help or suggestions would be greatly appreciated!

 

Thank you.

I have found this formula which works as a charm to calculate the number of days/hours/min between to date/time fields. I am trying to understand how the formula works but I am on a dead end. Please help. First, you need to create a formula field (number) that subtracts one date/time field from the other date/time. In my case, this formula field is called Time_to_approve__c

The formula that calculates the days/hour/min looks like this:

IF ( Time_to_approve__c > 0,
TEXT(FLOOR(Time_to_approve__c)) & " days " &
TEXT( FLOOR( 24 * ( Time_to_approve__c - FLOOR(Time_to_approve__c) ))) & " hours " &
TEXT( ROUND(60 * (ROUND( 24 * ( Time_to_approve__c - FLOOR(Time_to_approve__c) ),8) - FLOOR(
ROUND( 24 * ( Time_to_approve__c - FLOOR(Time_to_approve__c) ),8)) ),0)) & " mn "
, "")

The formula field looks like this: 2 days 1 hours 12 mn

 

Right now, the value of Time_to_approve__c is 2 (***again, this is the result of a date/field value being substraced from another date/time field value***)

 

 I understand the first part of the formula :
TEXT(FLOOR(Time_to_approve__c)) & " days " it rounds the number to the nearest integer, which is 2. So this results in " 2 days" which makes sense.

The issue is on the 2nd part of the formula, which calculates the hours. The syntax says:

TEXT( FLOOR( 24 * ( Time_to_approve__c - FLOOR(Time_to_approve__c) )))

This currently returns a value of 1.

FLOOR(Time_to_approve__c) is 2 (I confirmed this with another formula field) and again, Time_to_approve__c is 2. So, (Time_to_approve__c) - FLOOR(Time_to_approve__c) is 0, multiplied by 24 is 0 too, rounded to its nearest integer would return 0 as 0 is also considered a whole number...then, why does this part of the formula returns a value of 1?

Thank you.

I have created workflow rule for one of my object, and I want to specify a "lookup field" in the "Rule Criteria" section. But no look up fields are visible in the drop down. How can I achieve this.

 

Plz any one suggest me.

 

Thanks,

Haripriya.

Hi Folks,

 

I have a custom field (Score) on both my contact and lead objects that I want to includein campaign member reports. Since the report interface does not allow me include custom fields, I need to create a custom formula field (let's call it CMScore) on the Camapign Member object that pulls this data from the lead/ contact field. Can someone explain to me what syntax to use for the formula- i'm a newbee and nto familiar with using formulae.

 

This is essentially what it needs to do:

 

CMScore=

Lead.Score if CampaignMember is a Lead

Contact.Score if CamapignMember is a Contact

 

Any help is greatly appreciated!

 

Thanks!

 

Hi I am trying to create a formula that will take the first 60 characters from a field and add ... to the end of the formula, however if the field is blank it would remain blank.

 

I've figured out the trim, I just can't figure out the "..." part. 

 

TRIM(LEFT(ECS__Shipping_Detail__r.ECS__Shipping_Instructions__c,60))

 

I appreciate the help.

 

Thanks!

I was trying to derive the state from area codes in leads and my compiled formula is too large.

I am trying to figure out a way to make it work.  Here is a sample of the formula...

if(ISBLANK(Phone),"--",
if(CONTAINS("205:251:256:334",left(SUBSTITUTE(Phone,"(",""),3)),"Alabama",
if(CONTAINS("907",left(SUBSTITUTE(Phone,"(",""),3)),"Alaska",

and so on and so forth through all of the states with their respective area codes.  Seems simple enough, but needs to be simplified.

Any thoughts are much appreciated!

Hello!

I am looking for some help shortening this case function.  I am trying to pull over a field that has every lead source channel tracked in it's values and I need to create this field for grouping them into their various marketing groups.  There are a few explicit terms that are spelled out in the case function, but then I need an if statement to say "IF CONTAINS( the category has a "." in it (meaning it is a website url containing .com, .org) then list it in the group "Ref" ) AND IF CONTAINS( different sources that all start with "Organic - Something" and I need those all to be group into Organic), Else the Category is Offline.

My error is that the function is too big to execute (28,242 characters). Maximum size is 5,000 characters. I am unsure how this is possible because this is the most condensed version of the function I could manage.  

Any help would be lovely!

CASE( RT_Category__c, "MailChimp", "Email", "Vertical Response", "Email", "Google AdWords", "Paid", "ICIMS", "Paid", "LinkedIn Paid", "Paid", "Facebook", "Social", "LinkedIn", "Social", "Web Direct", "Web", IF(CONTAINS(RT_Category__c, "Organic"), "Organic", IF(CONTAINS(RT_Category__c, "."), "Referral", "Offline")))

I have one last question maybe you could help me with.  For opportunities we have set up a few pick lists for products to choose from.  I tried creating a formula but it says I have 42,000 Characters even though it is only 3,200.  From my searches it shows that it is taking formulas and combining them which makes it more characters.  So my question is, is it possible to write a formula based off of a pick lists?  For example "If SKU__c  pick list contains GREENC than Opp_Description_1__c = Green Coffee, If Product Sku 2 = RKTVTL, Opp_Description_1__c = Raspberry ketone.  I have several pick lists on my opportunity, and if multiple are chosen I need the description to be added for all of them.  I hope I am making sense. Each pick list has several options. So something like this, but the Product Sku is a pick list

 

If SKU__c = GREENC than add Green Coffee to Description_1__c

If SKU__c = RBKVTL than add Rapsberry Keton to Description_1__c

 

IF Product_Sku_2__c = GREENC than add Green Coffee to Description_1__c

IF Product_Sku_2__c = RBKVTL than add Raspberry Ketone to Description_1__c

 

So on and so forth for Product_Sku_3__c, Product_Sku_4__c and Product_Sku_5__c.

 

There are approximatly 20 Sku's total in each pick list.

 

Thanks in advance.

 

# Are you salesforce smart?

pick list 

yes 

no 

 

or checkbox 

 

Please explain why?

Guys, would like to have some help here.

I don't have an idea why this is not working...

 

I have a workflow rule where the formula looks like this:

OR
(ISCHANGED(BillingCity)||
ISCHANGED(BillingCountry)||
ISCHANGED(BillingPostalCode)||
ISCHANGED(BillingState)||
ISCHANGED(BillingStreet)
)

 It's set when created account and always when changed, it was supposed to create a taks for users with role "finance" and send them also an e-mail.

Well, it's not working... any clue?

 

The idea is to notify finance team when any billing info is changed on account's info.

 

Thanks in advance!!

why does the following compile to 14K:

 

CASE(FLOOR(Case_Age_by_Business_Hours__c),
 0,
  IMAGE("/servlet/servlet.FileDownload?file=015P000000010v6", "Green Flag"),
 1,
  IMAGE("/servlet/servlet.FileDownload?file=015P000000010v6", "Green Flag"),
 2,
  IMAGE("/servlet/servlet.FileDownload?file=015P000000010v6", "Green Flag"),
 3,
  IMAGE("/servlet/servlet.FileDownload?file=015P000000010vB", "Yellow Flag"),
 /* 4 hours and above */
  IMAGE("/servlet/servlet.FileDownload?file=015P000000010vG", "Red Flag")
)

 

Case_Age_by_Business_Hours__c is s formula that compiles to 4K.

Hi how do i get the seconds like 0-60 (so counting and resetting) from something like Now()-CreatedDate?

Hi All,

     I want to create an url-field within the Opportunity page that contains value an url that in concatinated. 

It should combine a html-textstring and add FirstName + underscore + LastName+Space+Description  in one string, something like this

           Just like this:john_smith Hydrocarbon Area Remediation Mar13.

how to resolve this issues

Opportunity owner is a Salesforce user and that Salesforce user must have an 'Super User - xxx' user profile on their user record.

 

Please help me with the code to achieve this.

 

Thx

 

Hello All,

 

I currently have a working workflow formula as follows:

 

IF(Product2.Name = "13-Credits" && Quantity = 1, 13, 
IF(Product2.Name = "13-Credits" && Quantity = 2, 13 * 2, 
IF(Product2.Name = "13-Credits" && Quantity = 3, 13 * 3, 
0 
)))

 

I know I can either keep this formula as is or use a Case function. However, if I need to 100 different Quantity values, this will require me to have 100 different lines on the formula for each Quantity value. Even worst if I have 10 different Product2.Name requiring 100 different Quantity variations, the number of lines in this formula can easily = 10 * 100 = 1000 lines!

 

Is there any way I can reduce the number of lines in the this formula? 

 

Please respond with specific working formula examples.

 

Thanks,

NOT(DAY(EndDate) =28 || DAY(EndDate) =29 || DAY(EndDate) =30 || DAY(EndDate) =31)

 

This covers all possible last day of the month days - but if someone enters June 28th, it will still allow for it to be entered.

 

How can I change this to only allow for the last day of the month to be entered as a date?

 

i.e. 

Jan 31

Feb 28 or 29

Mar 31

Apr 30

May 31

Jun 30

Jul 31

Aug 31

Sep 30

Oct 31

Nov 30

Dec 31

 

Hi, I am very new to formulas and SF and can't seem to get SF syntac right. I keep getting errors on this formula. I would really appreciate some help! 

 

If ((RecordType.Id), ("012E0000000MoYF") || ( RecordType.Id), ("012E0000000Mu8O") || ( RecordType.Id), ("012E0000000MoYN") || ( RecordType.Id), ("012E0000000MoYQ")),
( IF(ISPICKVAL(npe01__PreferredPhone__c, "Home"), HomePhone,
IF(ISPICKVAL(npe01__PreferredPhone__c, "Other"), OtherPhone,
IF(ISPICKVAL(npe01__PreferredPhone__c, "Work"), npe01__WorkPhone__c,
IF(ISPICKVAL(npe01__PreferredPhone__c, "Mobile"), MobilePhone, Account.Phone
),
( IF(ISPICKVAL(npe01__PreferredPhone__c, "Home"), HomePhone,
IF(ISPICKVAL(npe01__PreferredPhone__c, "Other"), OtherPhone,
IF(ISPICKVAL(npe01__PreferredPhone__c, "Work"), npe01__WorkPhone__c,
IF(ISPICKVAL(npe01__PreferredPhone__c, "Mobile"), MobilePhone, Employer_Number__c
)

How to use workflow rule to map values of two fields in an object using a custom setting

 

I have two fields in opportunity object. I need to update field1, using a workflow, when field2 is assigned a value.

 

the maping of the values are stored in Custome setting "Map" as below.

 

CRY   |     CTI

------------------

US      |   New york

US      |   Denver

US      |   Washington

US      |   California

UK      |   London

UK      |   Cambrige

UK      |   Manchester

 

Example : If i select "London" in field2, UK should come in field1.

 

How to write a worlklow for this.

 

 

 

 

 

I am trying to come up with a validation rule that will keep us from deleting executed contracts.  Can you please help with the formula?

I have custom object which is a child of Opportunity Object through a master detail relation ship.

 

I want to get the recordtype name of Opportunity on the child custom object record.

 

Formula on Opportunity Fied(Opp_rec_name__c):$ recordtype.name

 

Formula on Custom Object(child):Opportunity__r.Opp_rec_name__c. Do a formula field like print the current Child Object record type name. Not the Opportunity record type name.

 

I do not want  to use the trigger to print the parent object record type name on the child object.

 

Is there any other way I can print the name???

 

 

 

 

First off im very new to salesforce

 

Basically I want to build model number field based on 3 other fields (two of which are picklist). But it seems i can only use one case statement in the forumla field

 

Currently this works

---------------------------------

CASE( Color_Display__c,
"Tri Color", "LSS-TC",
"Full Color", "LSS-FC",
"Single Color", "LSS-SC","")

 

(oh also i dont know why i need the last comma and double quotes at the end of this case. If i leave it "Single Color", "LSS-SC") it gives me error when i check my syntax. It says, "Error: Incorrect number of parameters for function 'CASE()'. Expected 6, received 7" If anyone can explain that to me that would be great. But anyways the above works, i just would like to know why i need the last  ,""

 

So on with the question:

 

CASE( Color_Display__c,
"Tri Color", "LSS-TC",
"Full Color", "LSS-FC",
"Single Color", "LSS-SC","")

 

CASE(Software__c,
"Video Star", Model__c = Model__c & "VS-",
"Image Star", Model__c = Model__c &  "IS-","")

 

Model__c = Model__c & Pitch

 

Also i know it gives an error referencing its own self. This is how i build string in VB or any other programming language. How am i to do this in the formula field?

 

So at the end i just want the model field to read for example

LSS-TCVS-20

if the options Tri Color, Video Star and what ever numeric value is entered into pitch.

 

Thanks for any and all help in advance.