Formula Guru needed with assistance with formula - CASE, ISPICKVAL or Workflow - Answers - Salesforce Trailblazer Community
Trailblazer Community
Ask Search:
T ByrdT Byrd 

Formula Guru needed with assistance with formula - CASE, ISPICKVAL or Workflow

Hello

I have built a custom object which has 7 picklist fields. Within each pick list fields are values.

For example one picklist field is called "Frequency_of_Communication__c" within that picklist are values such as "Daily", "Weekly" and "Occasionally".

The rest of the picklists have differenent answers and each answer has a weight to it.  Therefore the "Frequency_of_Communication__c" has the following values:

"Daily" = 3,
"Weekly" = 2 
"Occasionally" = 1

I want to capture the resulting value in a custom field called "Q1__c". So if "Daily is chosen in the picklist, the custom field will be "3". If later on the value is changed to "Weekly" then the value should be updated to 2.

I would also like the field "Q1__c" to be rolled-up to the contact level


Is this something achievable with formulas,if so, what would be the framework for the formula?

Or would it be a lot easier to setup an Field Update via a workflow (bearing in mind the limitations for Enterprise Edition).

Help is greatly appreciated!

gadget :)


Sunil SarillaSunil Sarilla

what is the relationship between Contact object and Custom object (Lookup or Master Detail)
If its lookup, you cannot roll up the value in Q1 to contact object.
Even if the relationship is Master Detail and Q1 is a formual field, you cannot rollup a formual field to Parent object.
Assuming you dont want to use Apex and the relationship between contact and custom object is Master Detail, create a formula field called Q1hidden (this will be hidden from all users)
you can either Case or pickval to write the formula, below is the example using ispickval
if(text(ISPICKVAL(Frequency_of_Communication__c,"Daily")),3,if(text(ISPICKVAL(Frequency_of_Communication__c,"Weekly")),2,1))
Use the Workflow rule to update the Q1 field with the value in Q1 hidden with the option "Everytime Record is created or Edited".
then you can roll up this field to contact.
else Apex code is the only solution

 

T ByrdT Byrd
Thanks for the reply, the relationship is Master Detail.

I created a FF "Q1 hidden", copied the example ISPICKVAL string from your example:

if(text(ISPICKVAL(Frequency_of_Communication__c,"Daily")),3,if(text(ISPICKVAL(Frequency_of_Communication__c,"Weekly")),2,1))

Checked syntax and I got the following error message:

"Error: Incorrect parameter for function 'text()'. Expected Number, Date, DateTime, Picklist, received Boolean"
Sunil SarillaSunil Sarilla

Hi there,
Please select the data type for the formula field to be number.
and use the below formula
if(ISPICKVAL(Frequency_of_Communication__c,"Daily"),3,if(ISPICKVAL(Frequency_of_Communication__c,"Weekly"),2,1))

T ByrdT Byrd
Thanks for your help. I have the following formula:

IF(ISPICKVAL( Frequency_of_Communication__c, "Daily (Av. more than once per week)"), 3,
IF(ISPICKVAL( Frequency_of_Communication__c, "Weekly (Av. weekly)"), 2,
IF(ISPICKVAL( Frequency_of_Communication__c, "Occasionally (Av. monthly)"), 1,
NULL)))

This works fine. I also have a multi-select picklist that I would also like to apply this formula to, so far I have this:

IF(INCLUDES( Areas_of_Interaction__c, "Sales"), 1,
IF(INCLUDES( Areas_of_Interaction__c, "Research"), 1,
IF(INCLUDES( Areas_of_Interaction__c, "Sales Trading / Market Making"), 1,
IF(INCLUDES( Areas_of_Interaction__c, "Special Sits"), 1,
IF(INCLUDES( Areas_of_Interaction__c, "Fixed Income"), 1,
NULL)))))

What I would like to do is edit this formula so that it provides a running total. For example, if "Sales" and "Research" is selected, that would equate to 2 points. Any idea on how to modfy the code above?



Sunil SarillaSunil Sarilla
IF(INCLUDES( Areas_of_Interaction__c, "Sales"), 1,0)+
IF(INCLUDES( Areas_of_Interaction__c, "Research"), 1,0)+
IF(INCLUDES( Areas_of_Interaction__c, "Sales Trading / Market Making"), 1, 0)+
IF(INCLUDES( Areas_of_Interaction__c, "Special Sits"), 1, 0)+
IF(INCLUDES( Areas_of_Interaction__c, "Fixed Income"), 1, 0)

T ByrdT Byrd
SunilS - thank you! Your help is much appreciated :)
Suman GaviniSuman Gavini
Hi All,

I have a field called Language__c.  
Can  we write like this": 
IF(ISPICKVAL( Language__c, "French"), False). I am using this in queue assignements. When user language is french then it should go to the different Queue. I tried the Case also.