Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
linshelllinshell 

Formula Field Trouble

I am trying to create a formula field that has critieria relating to several other fields but can't figure it out.  I started with two different formulas that were requested by the user at different times then received new criteria to be added to the formula so I have each individual formula that has been tested and works.  The formula field name is "Migration Eligible" and the criteria is the following:

- If the Type field = "Inactive" the Migration Eligible field = No regardless of other criteria

- If the Initial Ship Date field is blank and the Manufacturer field = "Company 1" or "Company 2" the Migration Eligible field = Yes

OR

- If the Years since last activity field >= 5 the Migration Eligible field =  Yes

 

Here are the individual formulas that were tested and worked:

 

IF(NOT(ISPICKVAL (Type,"Inactive")),"Yes","No")

IF (Years_since_last_activity__c >= 5), "Yes","No"

IF (AND (ISBLANK(Initial_Ship_Date__c),
OR (ISPICKVAL (Manufacturer__c,"PlantCML"), 
ISPICKVAL (Manufacturer__c,"TCI"))),"Yes","No"
)

 


Thanks for any help!

Linda  

 

 

Best Answer chosen by Admin (Salesforce Developers) 
SwarnasankhaSwarnasankha

Try this :

 

 

IF
(
  NOT(ISPICKVAL (Type,"Inactive")),
  "Yes",
  IF 
  (
  Years_since_last_activity__c >= 5, 
  "Yes",
  IF
  (
  AND
(
ISBLANK(Initial_Ship_Date__c),
OR
(
ISPICKVAL (Manufacturer__c,"PlantCML"),
ISPICKVAL (Manufacturer__c,"TCI")
)
),
"Yes",
"No"
)
)
)

 

All Answers

Steve :-/Steve :-/

Can you list the Field Labels and Datatype (Text, Picklist, Number, Date, Date/Time, etc, etc.) of each field in your Formula?  
linshelllinshell

No problem.  Here you go-

 

Type - Picklist

 

Years since last activity - Formula (Number)    IF (ISNULL (Upgrade_Date__c), (TODAY() - Initial_Ship_Date__c ) / 365, (TODAY() - Upgrade_Date__c) / 365)

 

Initial Ship Date - Date

 

Manufacturer - Picklist

 

 

 
Steve :-/Steve :-/

Hi Linda,

 

So do I have this correct that you're trying to concatenate these 3 formulas into a single Formula that will return either "Yes" or "No" in a Formula(Text) field? 

 

If it is, then you could try something like this:

 

IF(NOT(ISPICKVAL (Type,"Inactive")),"Yes",
IF (Years_since_last_activity__c >= 5), "Yes",
IF (AND (ISBLANK(Initial_Ship_Date__c),OR (ISPICKVAL (Manufacturer__c,"PlantCML"),ISPICKVAL (Manufacturer__c,"TCI"))),"Yes",
"No")))

 

 
linshelllinshell

Yes, that is exactly what I am trying to do,  thanks for the reply!! ...but I get the following error with your formula:  Error: Syntax error. Extra ')'

 
SwarnasankhaSwarnasankha

Try this :

 

 

IF
(
  NOT(ISPICKVAL (Type,"Inactive")),
  "Yes",
  IF 
  (
  Years_since_last_activity__c >= 5, 
  "Yes",
  IF
  (
  AND
(
ISBLANK(Initial_Ship_Date__c),
OR
(
ISPICKVAL (Manufacturer__c,"PlantCML"),
ISPICKVAL (Manufacturer__c,"TCI")
)
),
"Yes",
"No"
)
)
)

 

This was selected as the best answer
linshelllinshell

Thanks Swarnasankha!  It worked but had to change "NOT(ISPICKVAL (Type,"Inactive"))" to "ISPICKVAL (Type,"Inactive")" for the logic to be correct. 

 
Why does the lack of hard-returns and indents make a difference and return the missing ")" error?  Should be a different error message you would think.

 

Thanks to you both!

Linda
SwarnasankhaSwarnasankha

The indentation did not fix the problem; it just helps you see through the entire code more comfortably.

 

The problem was in the 2nd line of the previous expression:

 

IF (Years_since_last_activity__c >= 5), "Yes",

If you observe carefully, you will see a 'close' parenthesis after the condition which happened to be the culprit.  
linshelllinshell

Yep, missed it thanks!