Complicated if/then formula

[jkupov]

OK- So complicated for me. Perhaps not for the experts on here. It seems as if you have seen everything under the sun.

I'm using CR 11 (not sure it matters here)

I have an existing basic if/then in place but the field which I'm looking at is not always populated by the user. Unfortunately the programmers made this an optional field in the software so it is sometimes left blank (in about 5% of the records).

What I want to do is to look at a secondary field if the main field is left blank to come up with a best guess. For example, if our customer came in complaining of a problem with the roller bar, it is initially entered in the database as a roller bar issue. But if during the repairs we discover that it is really an issue with the electrical switch, once the record is closed out the repair technician is supposed to enter a code in a different field (resolv_prob) indicating what was really done to fix it.

This "resolution" field is the one which i run the report off of for this report because I want to see what the most common problems are and not necessarily what the customer reported because they are sometimes wrong in their diagnosis when they bring it in.

The problem, as I mentioned, is that the resolution field is sometimes left blank and what i end up doing to get a best estimate is to "assume" that the customer had the right diagnosis to begin with and count it as what it was originally reported as.

So this brings me to my formula issue. I have an if/then in place to group the different resolution codes into repair categories, but if the field (resolv_prob) is blank, I want to look at the field which contains the customer's diagnosis (reported_prob)and use that field to fit the record into a category. So something like:

if {cust_repair.resolv_prob}like "M*" then "Motor" else
if {cust_repair.resolv_prob}like "E*" then "Electrical" else
(more conditions) . . . else

then here I need to say if it is blank (null?)to look at cust_repair.reported_prob field and then have another if/then series to put them into the repair categories.

Hopefully this makes sense. Thoughts?

 

[FireGeek21]

How about simply using a "" at the end of your IF statement? I also like being more specific than using "Like".

if Left({cust_repair.resolv_prob}, 1) = "M" then "Motor" else
if Left({cust_repair.resolv_prob}, 1) = "E" then "Electrical" else
""

FireGeek
(currently using Crystal Reports XI with Lawson 8.03)

 

[briangriffin]

Not tested, but should be pretty close:

if isnull({cust_repair.resolv_prob}) then

(
if {cust_repair.reported_prob}like "M*" then "Motor" else
if {cust_repair.reported_prob}like "E*" then "Electrical" else
(more conditions) . . . else "Other"
)

else

(
if {cust_repair.resolv_prob}like "M*" then "Motor" else
if {cust_repair.resolv_prob}like "E*" then "Electrical" else
(more conditions) . . . else "Other"
)

 

[jkupov]

Thanks FireGeek21-

Can you explain what the "" does? Also, what is the "if Left" and the 1 do? Sorry... not a programmer. I get this responsibility by default not by qualifying for it

 

[jkupov]

briangriffin,

thank you! That makes sense to me. So essentially I'd create an if/then to look at the first field and if it's null to look at the second? Then within both of those create secondary if/then statements with their own sets of conditions?

 

[FireGeek21]

The "" will return nothing, essentially you will see a blank in your output. You could use "Other" if you prefer to have the field filled in in your output.

The Left(Table.Field, 1) grabs the character in the 1st position from the left of the field. So, the 1st character from the left in Motor is M. There also is a Right(Table.Field, 1) function that grabs characters from the right. The number in the formula can change to be whatever you desire.

FireGeek
(currently using Crystal Reports XI with Lawson 8.03)

 

[jkupov]

Thanks firegeek. That makes sense to me. . . surprisingly.

 

[briangriffin]

You got it - always test for nulls first, then add the rest of the formula.

Don't forget the catchall "else "Other" (or whatever you want it to say). Sometimes it's not necessary but I think it's a good habit to get into.

And you should take firegeek's suggestion to use the Left function - that will be faster than using Like.

 

[jkupov]

Thanks briangriffin- I'll have to test this after lunch. Thanks both of you