# 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.

10|10000 characters needed characters exceeded

View more ...

### Related questions

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

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

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