Skip to Content
author's profile photo Former Member
Former Member

Case Statement Formula

Can someone please help? I am having difficulties lableing blank fields using a case statement formula. Example:

select {Mtr_Test.Action_Type_Cd}

case ' ' : 'No Action Taken'

case '0' : 'No Action Taken'

case '1' : 'Send to Manufacturer RMA'

case '2' : 'Repaired in Shop'

case '3' : 'Retired'

default : 'Not Found'

I would like to treat a blank field the same as if it where case '0' : 'No Action Taken'.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Best Answer
    Posted on Mar 05, 2015 at 07:08 PM

    Hi Steve,

    Look for an option on the top in the Formula Editor called 'Exceptions for Nulls'; set that to 'Default Values for Nulls'.

    This will convert any NULLs in the {Mtr_Test.Action_Type_Cd} column to blanks if it is a string field.

    -Abhilash

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Abhilash,

      Awesome. That worked perfectly. {Mtr_Test.Action_Type_Cd}'s that are 0 and blank, now are listed in my report as 'No Action Taken'. Brian also suggested using the Report Options settings which accomplished the same thing. However, that seems to be a more global setting. I chose to use the 'Exception for Nulls' at the formula level. In either case, this new found knowledge will prove quite useul. Thanks again to both you and Brian!

      select {Mtr_Test.Action_Type_Cd}

      case ' ' : 'No Action Taken'

      case '0' : 'No Action Taken'

      case '1' : 'Send to Manufacturer RMA'

      case '2' : 'Repaired in Shop'

      case '3' : 'Retired'

      default : 'Not Found'

  • Posted on Mar 05, 2015 at 07:12 PM

    Hi Steve,

    Do you get an error when you run the formula or does it display Not Found?

    Is Action_Type_Cd a numeric field or is it a string?

    I suspect the empty value may actually be a null and not an empty string. You can tell the report to change the null to the default value for the field. When you open the report, go to File | Report Options. There is an option for Convert Null Values to Default. If the field is numeric, it will change the null to 0. If the field is a string it will change the null to an empty string.

    Good luck,

    Brian

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.