IF THEN ELSE or CASE Formula with multiple statements

[former_member1013816]

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.

[abhilash_kumar · Accepted solution]

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

[former_member1013816 · Accepted solution]

This worked perfectly. Thank you for your assistance!