+ Start a Discussion
Admin User 6112Admin User 6112 

Multiple nested picklist values, IF/ORs

Here's what I have in "English": If the value in a field is either of 2 picklist values, then put a 1 in the formula field. If it is either of two different picklist vales, place a 2, etc. There are 5 pairs of these. Do I need another layer of ORs, different punctuation? I don't know. Please help! Also, the "0" at the end is not a valid picklist value, it's just a placeholder.

IF(OR (ISPICKVAL(InspectionType__c="Type 1"),(ISPICKVAL(Inspectiontype="1. Complete Final Inspection with full H&S diagnostics and air leakage diagnostics")),"1",
IF(OR(ISPICKVAL (InspectionType__c="Type 2"),(ISPICKVAL(Inspectiontype="2. Final Inspection with full health and safety diagnostics")),"2",
IF (OR (ISPICKVAL(InspectionType__c="Type 3"),(ISPICKVAL(Inspectiontype="3. Final Inspection with air leakage diagnostics")),"3" , 
IF (OR (ISPICKVAL(InspectionType__c="Type 4"),(ISPICKVAL(Inspectiontype="4. Final Inspection with gas leak test only")),"4", 
IF (OR (ISPICKVAL(InspectionType__c="Type 5"),(ISPICKVALInspectiontype="5. Final Inspection with no diagnostics")),"5", "0"))))) 
Best Answer chosen by Admin User 6112
Parker EdelmannParker Edelmann
Now I see the problem. Actually, you are putting the formula as the Default Value which is why the error is being triggered. What I mean to say is that you aren't creating a true formula field, you're just supplying the "suggested value", so to speak, when a new record is created if that makes any sense.

If I understand correctly, the end goal is to automatically update the InspectionTypeForInvoice__c field whenever the InspectionType__c field is updated. Unfortunately, because we can't change an ordinary field to a formula field, you'll need to create a new field and select the formula data type and go from there. If you can/want to, you can delete the existing InspectionTypeForInvoice__c and create the new field with the same name.

Does this make sense?

All Answers

Parker EdelmannParker Edelmann
I think it has to do with your ISPICKVAL syntax. Check this article out: https://help.salesforce.com/articleView?id=customize_functions_i_z.htm&type=0&language=en_US&release=206.10

However, forget ISPICKVAL, you can use a much more efficient CASE() statement. Instead of referencing InspectionType__C 10 times, you would only have to reference it once:
CASE(InspectionType__c,
     'Type 1', '1',
     '1. Complete Final Inspection with full H&S diagnostics and air leakage diagnostics', '1',
     'Type 2', '2',
     '2. Final Inspection with full health and safety diagnostics', '2',
     'Type 3', '3',
     '3. Final Inspection with air leakage diagnostics', '3',
     'Type 4', '4',
     '4. Final Inspection with gas leak test only', '4',
     'Type 5', '5',
     '5. Final Inspection with no diagnostics', '5',
     '0')
I'm not going to go over how the formula actually works, you can see that here: https://help.salesforce.com/articleView?id=customize_functions_a_h.htm&type=0&language=en_US&release=206.10
If this works for you, please let me know by replying or by marking this response as "Best Answer", or follow up with any error you recieve if it doesn't.

Thanks,
Parker
Admin User 6112Admin User 6112
"Case" is certainly a cleaner syntax, thanks! I like it!  Howevere, when I use this in my formula box:

CASE(InspectionType__c,
'Type 1', '1',
'1. Complete Final Inspection with full H&S diagnostics and air leakage diagnostics', '1',
'Type 2', '2',
'2. Final Inspection with full health and safety diagnostics', '2',
'Type 3', '3',
'3. Final Inspection with air leakage diagnostics', '3',
'Type 4', '4',
'4. Final Inspection with gas leak test only', '4',
'Type 5', '5',
'5. Final Inspection with no diagnostics', '5',
'0')

SF tells me "Error: Field InspectionType__c may not be used in this type of formula" I rhink that's because InspectionType__c is a PICKLIST. Can you suggest soething else?
Parker EdelmannParker Edelmann

Hmm, that's very interesting. According to this help documentation, CASE is one of the three formula operators that actually are supported with Picklists: https://help.salesforce.com/articleView?id=tips_for_using_picklist_formula_fields.htm&type=0&language=en_US&release=206.10

Do you think that you could take a screenshot of the InspectionType__c field detail page? It may help me to see why it's not working properly.

A couple of things that you could try to make this formula work before moving on to a different solution:

  • Make sure that the formula return type is text. If you want it to be a number, remove the quotes from the number you wish to return
  • Try wrapping InspectionType__c in TEXT(), like this: TEXT(InspectionType__c)
  • Make sure that you're using the right API name for the Inspection Type field
If all else fails, then try this:
 
IF(ISPICKVAL(InspectionType__c, 'Type 1') || ISPICKVAL(InspectionType__c, '1. Complete Final Inspection with full H&S diagnostics and air leakage diagnostics'), '1',
IF(ISPICKVAL(InspectionType__c, 'Type 2') || ISPICKVAL(InspectionType__c, '2. Final Inspection with full health and safety diagnostics'), '2',
IF(ISPICKVAL(InspectionType__c, 'Type 3') || ISPICKVAL(InspectionType__c, '3. Final Inspection with air leakage diagnostics'), '3',
IF(ISPICKVAL(InspectionType__c, 'Type 4') || ISPICKVAL(InspectionType__c, '4. Final Inspection with gas leak test only'), '4',
IF(ISPICKVAL(InspectionType__c, 'Type 5') || ISPICKVAL(InspectionType__c, '5. Final Inspection with no diagnostics', '5',
'0')))))

Pardon me if I have any syntax errors in that, but that's along the lines of what you'll need to use if CASE won't work for us.
Admin User 6112Admin User 6112
Hi Parker, Here are the screen shots you asked for (I took 2, top and bottom).

User-added image
User-added image

Notice that I am not mapping some of the valid values, if a value is not listed in the formula, I just want to let it print.
I put the ISPICKVAL statement in the formula boc of my field InspectionTypeForInvoice__c:
IF(ISPICKVAL(InspectionType__c, 'Type 1') || ISPICKVAL(InspectionType__c, '1. Complete Final Inspection with full H&S diagnostics and air leakage diagnostics'), '1',
IF(ISPICKVAL(InspectionType__c, 'Type 2') || ISPICKVAL(InspectionType__c, '2. Final Inspection with full health and safety diagnostics'), '2',
IF(ISPICKVAL(InspectionType__c, 'Type 3') || ISPICKVAL(InspectionType__c, '3. Final Inspection with air leakage diagnostics'), '3',
IF(ISPICKVAL(InspectionType__c, 'Type 4') || ISPICKVAL(InspectionType__c, '4. Final Inspection with gas leak test only'), '4',
IF(ISPICKVAL(InspectionType__c, 'Type 5') || ISPICKVAL(InspectionType__c, '5. Final Inspection with no diagnostics', '5',
'0')))))

and SF responded "Error: Syntax error. Missing ')'"
Parker EdelmannParker Edelmann
My bad, I forgot a closing parenthesis of an ISPICKVAL. Try this:

IF(ISPICKVAL(InspectionType__c, 'Type 1') || ISPICKVAL(InspectionType__c, '1. Complete Final Inspection with full H&S diagnostics and air leakage diagnostics'), '1',
IF(ISPICKVAL(InspectionType__c, 'Type 2') || ISPICKVAL(InspectionType__c, '2. Final Inspection with full health and safety diagnostics'), '2',
IF(ISPICKVAL(InspectionType__c, 'Type 3') || ISPICKVAL(InspectionType__c, '3. Final Inspection with air leakage diagnostics'), '3',
IF(ISPICKVAL(InspectionType__c, 'Type 4') || ISPICKVAL(InspectionType__c, '4. Final Inspection with gas leak test only'), '4',
IF(ISPICKVAL(InspectionType__c, 'Type 5') || ISPICKVAL(InspectionType__c, '5. Final Inspection with no diagnostics'), '5',
'0')))))
Admin User 6112Admin User 6112
Parker,  Bad news, Now the error is " Error: Field InspectionType__c may not be used in this type of formula"
Parker EdelmannParker Edelmann
Okay, a quick search gave me this: https://developer.salesforce.com/forums?id=906F000000090cIIAQ
Is this a default value formula? If so, now I can see why CASE is giving you the error as well.
Admin User 6112Admin User 6112
This is not a default value formula. Here's the whole story:
InspectionType__c is set for one of two "programs": One program uses a short phrase, e.g. "Type 1," while the other uses a much longer phrase "1.Complete Final Inspection..." "Type 1" in the one program is not the same type of inspection as "1.Complete Final Inspection..." at the other. Furthermore, the SF record is populated in one of two ways. Either a user selects the Inspection Type from a dropdown in SF or the Inspection Type is selected in a field tool and the value is sent to SF via a Mule flow. To keep data in synch, both workflows are necessary.
We need to report on the Inspection Type in our invoices, but the invoicees do not want to see anything other than a 1 or 2, etc. for Inspection Type. At first I used the Process Builder to make a flow that wrote 1, 2, etc. into my field InspectionTypeForInvoice__c, but it only worked when InspectionType__c was set manually in SF. When a value came in via Mule from the online field tool (a Filemaker tool) the flow didn't "feel it" -- no translation happened.  InspectionType__c would have the new value, but InspectionTypeForInvoice__c did not change. So then I decided to write a formula in InspectionTypeForInvoice__c, and that's where we are now. Does this help?
Parker EdelmannParker Edelmann
Maybe a little. I'm still not sure why you're recieving that error. The last thing that I can think of to have you do is to take a screenshot of the entire window the formula is in with the error message that it gives you. If that doesn't yield any clues, then I have no other recommendations off of the top of my head.
Admin User 6112Admin User 6112
Thanks so much for looking at these, Parker. Error messages for both formulas are shown below...
User-added image
User-added image
Parker EdelmannParker Edelmann
Now I see the problem. Actually, you are putting the formula as the Default Value which is why the error is being triggered. What I mean to say is that you aren't creating a true formula field, you're just supplying the "suggested value", so to speak, when a new record is created if that makes any sense.

If I understand correctly, the end goal is to automatically update the InspectionTypeForInvoice__c field whenever the InspectionType__c field is updated. Unfortunately, because we can't change an ordinary field to a formula field, you'll need to create a new field and select the formula data type and go from there. If you can/want to, you can delete the existing InspectionTypeForInvoice__c and create the new field with the same name.

Does this make sense?
This was selected as the best answer
Admin User 6112Admin User 6112
Yes, I believe you're right about the "default value" problem. I will create the new field and let you know.