Apex Code Development You need to sign in to do that
Don't have an account?
Adnan81 Formula field
I am new to formula fields. Need some assistance
I have a Picklist field "Service Purchased" and "Number of Lines ported" with 1-10.
Trying to create a formula field which will determine if it is a "Ported", "Non-Ported" or "Non-OV" order based off of these two fields.
If service purchased does not contains "OV" say "Non-OV" and If Number of Lines ported Not equal to 0 say "Ported" otherwise say "Non-Ported"?
I have this so far, but it needs help!!!!!
IF(
AND(
NOT(CONTAINS(TEXT(Services_Purchased__c) , "OV",""))),
"Non OV",
( Number_Lines_Ported__c <> 0,"Ported", "Non-Ported")
As an aside - did you consider making the "Number of lines ported" field a number, and enforcing the 0-10 values with validation rules, field help, and training? It seems like that would make your life a lot easier in the long run, especially if you want to do any calculations in reports or elsewhere on # of lines ported over multiple records. Search for "How to do arithmetic on picklist numbers?" in Help & Training to see what you're up against if you need to calculate with this field. Short version - picklist values are text, not numbers, and cannot have arithmetic comparison or operation done on them.
Picklist values are different in formulas than other data types - you can only use CASE and ISPICKVAL on them. So your formula should be:
IF(
NOT(ISPICKVAL(Services_Purchased__c , "OV")), "Non OV",
IF(ISPICKVAL(Number_Lines_Ported__c, "0"),"Non-Ported","Ported")
)
This assumes that there is a single option, "OV," in your Services_Purchased__c picklist. If it's the case that there are multiple picklist values that have "OV" in the name, then you have to enumerate them separately in a CASE(). If your picklist values are, say:
Alpha
Beta OV
Gamma OV
Delta
Epsilon
then test all the non-OV ones first, and put the Lines_Ported__c test at the end as the "else" option:
CASE(Services_Purchased__c,
"Alpha", "Non OV",
"Delta", "Non OV",
"Epsilon", "Non OV",
IF(ISPICKVAL(Number_Lines_Ported__c, "0"),"Non-Ported","Ported")
)
You'll also want to be doing data validation to ensure that Services_Purchased__c and Number_Lines_Ported__c aren't getting incompatible values, say, Alpha and 3.
All Answers
Are both fields Picklists? Or is 1 a Picklist and the other a Number?
Yes, Both are Picklist. Thanks Steven
Are both picklists mutually exclusive? Are you trying to evaluate both fields and display a single Text value, or are you trying to evaluate the first picklist and display one value, then evaluate the second piclist and display another value?
Both are exclusive and Yes, I want it to display a single text value, based on both fields.
"Service Purchased" picklist field if it does not contains "OV"then show "Non-Ov".
"Number of lines Ported" picklist field 1-10 display "Ported" or Non- Ported if picklist is not equal to 0?
As an aside - did you consider making the "Number of lines ported" field a number, and enforcing the 0-10 values with validation rules, field help, and training? It seems like that would make your life a lot easier in the long run, especially if you want to do any calculations in reports or elsewhere on # of lines ported over multiple records. Search for "How to do arithmetic on picklist numbers?" in Help & Training to see what you're up against if you need to calculate with this field. Short version - picklist values are text, not numbers, and cannot have arithmetic comparison or operation done on them.
Picklist values are different in formulas than other data types - you can only use CASE and ISPICKVAL on them. So your formula should be:
IF(
NOT(ISPICKVAL(Services_Purchased__c , "OV")), "Non OV",
IF(ISPICKVAL(Number_Lines_Ported__c, "0"),"Non-Ported","Ported")
)
This assumes that there is a single option, "OV," in your Services_Purchased__c picklist. If it's the case that there are multiple picklist values that have "OV" in the name, then you have to enumerate them separately in a CASE(). If your picklist values are, say:
Alpha
Beta OV
Gamma OV
Delta
Epsilon
then test all the non-OV ones first, and put the Lines_Ported__c test at the end as the "else" option:
CASE(Services_Purchased__c,
"Alpha", "Non OV",
"Delta", "Non OV",
"Epsilon", "Non OV",
IF(ISPICKVAL(Number_Lines_Ported__c, "0"),"Non-Ported","Ported")
)
You'll also want to be doing data validation to ensure that Services_Purchased__c and Number_Lines_Ported__c aren't getting incompatible values, say, Alpha and 3.
Steve,
Thank You so much. You really broke it down for me. I am now looking into making it a picklist rathen then leaving it as text field. This really solved my issue. Thank you.
I'm Thomas (new here), not Steve, but you're quite welcome. :-)
oops...Sorry. Thanks