Skip to Content

IF THEN ELSE or CASE Formula with multiple statements

Hello,

I seem to be hitting a wall with a formula for a Group.

Here is the logic for the formula:

If the AGENCY does not equal WCFD, then show me (group by) the AGENCY. If the AGENCY equals WCFD, show me (group by) the DISTRICT. However, if the DISTRICT is BLANK/NULL, then show me (group by) the PRIMEUNIT. However, if the PRIMEUNIT starts with "HU", then add this to the agency group "HUFD" that already exists. However, if the PRIMEUNIT starts with "FL", then add this to the agency group "FLFD" that already exists. And so on...

Basically, I'm trying to get all the BLANK/NULL districts that fall under the blanket WCFD agency, into their respective agencies. And the PRIMEUNIT is a good way of finding out what agency that particular incident belongs to.


The formula I started with was:

if{inmain.agency}="WCFD" then {inmain.district} else

{inmain.agency}


This gets me to a decent point, but then there are districts that are BLANK or NULL valued. So then I attempted the following formula:

if{inmain.agency}="WCFD" then {inmain.district} else
if{inmain.district}="" then {inmain.primeunit} else
{inmain.agency}


But this doesn't work as it gives me the primeunit for ALL agencies that have a BLANK/NULL district, rather then just the WCFD agencies that have BLANK/NULL districts.

However, my trouble doesn't stop here. Once I were to get the primeunits for only WCFD incidents, I would then need to do something like the following:

if{inmain.primeunit} = "HU*" then "HUFD"
if{inmain.primeunit} = "FL*" then "FLFD"

etc.
and so on.

I tried using a CASE formula, but it didn't work out the way I wanted it to or I'm not quite understanding how to use it.

Does this make sense? Any ideas? Thank you for your help.

Add a comment
10|10000 characters needed characters exceeded

Related questions

2 Answers

  • Best Answer
    Posted on 2 days ago

    Hi Andrew,

    Something like this should hopefully work:

    if {inmain.agency} = "WCFD" then 
    (
    If {inmain.district} = '' Then 
    (
    If {inmain.primeunit} LIKE "HU*" then "HUFD"
    Else If {inmain.primeunit} LIKE "FL*" then "FLFD"
    )
    Else
    {inmain.district}
    )
    Else {inmain.agency}

    Before you save the formula, look for a dropdown in the formula editor that says 'Exceptions for Nulls'; make sure it's set to 'Default Values for Nulls'

    -Abhilash

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on 2 days ago

    Or you could do something like this:

    if{inmain.agency}="WCFD" then 
      if {inmain.district} = "" then
        switch (
    	  left({inmain.primeunit}, 2) = 'HU', "HUFD",
    	  left({inmain.primeunit}, 2) = 'FL', "FLFD",
    	  true, {inmain.primeunit} 
    	)
    else
      {inmain.agency}
    

    This is Crystal syntax. The Switch statement is like a case statement - the first part of each pair is a condition and the second is the result if that condition is true. I, personally, find it can be easier to read than an if statement that has multiple "else if" sections.

    If {inmain.primeunit} is always uppercase, this will work. If it could potentially be mixed case or lower case, you would change "left({inmain.primeunit}, 2)" to "Uppercase(left({inmain.primeunit}, 2))".

    As Abhilash notes, you'll need to set the null handling at the top right to "default values for null" for this to work.

    -Dell

    Add a comment
    10|10000 characters needed characters exceeded

    • I see the problem - the formula is missing an Else statement. I think it should probably be the:

      if{inmain.agency}="WCFD" then 
        if {inmain.district} = "" then
          switch (
      	  left({inmain.primeunit}, 2) = 'HU', "HUFD",
      	  left({inmain.primeunit}, 2) = 'FL', "FLFD",
      	  true, {inmain.primeunit} 
      	)
        else
          {inmain.district}
      else
        {inmain.agency}

      You may need to tweak this, but I think it should be correct.

      -Dell

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.