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.

[DellSC]

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

[former_member1013816]

Thank you for taking the time to provide me with this formula. When I attempted to use it, the groupings adjusted to group by primeunits as opposed to agency/districts. Not sure what aspect of the formula caused this, but I appreciate your assistance nonetheless!

[DellSC]

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