4

I am trying CASE function inside a BlankValue function and I'm having trouble with the second expression (the substitute one). The first expression works fine with the case function, but the substitute expression would only return the "else result" of the case function..

Here's my formula:

BlankValue( CASE(TEXT(Nombre_de_la_Entidad_r.Subtipo_c), 
                   "Municipio", "Municipio", 
                   "Gobierno Provincial", "Gobierno", 
                   "Gobierno Nacional", "Gobierno", 
                   "Empresa Internacional", "Empresa", 
                   "Empresa Nacional", "Empresa", "-"),    
            CASE(TEXT(Nombre_de_la_Entidad__r.Type), 
                    "Empresa", "Empresa", 
                    "Cooperativa de recicladores", "Institución / Organización",
                    "Organización de la Sociedad Civil", "Institución / Organización",
                    "Universidad", "Institución / Organización", 
                    "Gobierno", "Gobierno", 
                    "Consultor", "-", 
                    "Participante individual", "-", "-"))

What could be wrong?

  • The second one will return - for Consultor, Participante individual, and no match. Is that what you expect? – Mike Chale Nov 15 '13 at 20:13
  • Yes, it could be replaced by another value like 0, or whatever. The thing is even if I try for instance "Universidad" it will pull "-" instead... – TamaraU Nov 15 '13 at 20:16
  • Actually, I have just realized the "-" belongs to the first expression of the BlankValue function... – TamaraU Nov 15 '13 at 20:38
1

I think this'll fix it. The very last value of a CASE statment is the value that is to be returned if NONE of the case-values match the CompareTo-value.

Note: Adding the spaces makes it really easy to see what parameters belong to which functions.

BlankValue( 
    CASE(
        TEXT(Nombre_de_la_Entidad_r.Subtipo_c), 
            "Municipio", "Municipio", 
             "Gobierno Provincial", "Gobierno", 
             "Gobierno Nacional", "Gobierno", 
             "Empresa Internacional", "Empresa", 
             "Empresa Nacional", "Empresa", 
             "-"
     ),    
     CASE(   
         TEXT(Nombre_de_la_Entidad__r.Type), 
             "Empresa", "Empresa", 
             "Cooperativa de recicladores", "Institución / Organización",
             "Organización de la Sociedad Civil", "Institución / Organización",
             "Universidad", "Institución / Organización", 
             "Gobierno", "Gobierno", 
             "Consultor", "-", 
             "Participante individual","-"
             "-"
      )
)
| improve this answer | |
  • 1
    Ah I do wish Salesforce would have a "pretty print my formula" button! :) +1 – Andrew Fawcett Nov 15 '13 at 21:03
  • I can't believe it took em til Winter '13 (?) to get a monospace font for formulas – Scott Pelak Nov 15 '13 at 21:04
  • 1
    @amatorVitae: I think there's one other thing here: The first case shouldn't have the -; shouldn't its default value be the second CASE? If Subtipo_c is blank, it returns -, and then Blankvalue returns -. – Mike Chale Nov 15 '13 at 21:06
  • yup, edited. i missed the end of the first CASE and had to lookup BLANKVALUE to make sure the parameters were correct. – Scott Pelak Nov 15 '13 at 21:07
  • hmmm, i'm not sure if my code makes business sense. the basic jist of getting it to work is in my answer though – Scott Pelak Nov 15 '13 at 21:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.