Conditional Variable

[dkrauseuf]

I am writing a variable using IF, THEN, ELSE:

= If <SR Status>="Closed" And <Activity Type>="Program Delivery" And <Activity Sub Type>="Reach to Recovery" Then "Closed" Else If <SR Service Type>="Reach to Recovery" And <SR Status>="Unable to Contact" Or <SR Service Type>="Reach to Recovery" And <SR Status>="Closed - Not Met" Or <SR Service Type>="Reach to Recovery" And <Activity Type> InList ("Call - Inbound" , "Call - Outbound" , "Email - Inbound" , "Email - Outbound" , "Fax - Inbound" , "Fax - Outbound") And <Activity Status>="Closed - No Reply" And <Activity Volunteer Last/Org Name>="#EMPTY" Then "Cancelled"

However, after the first Then statement (Then "Closed"), I have the criteria for which records meet Then "Cancelled". But I want to add in there that if the records meet the criteria for "Closed", to exclude them in the "Cancelled". I can't figure out how to do this.

 

[huggyd]

Are you actually seeing Closed items in your Cancelled items? An if statement returns on the first condition that it meets so it won't include those that qualify as "closed" in those that are cancelled. Therefore you should not have to exclude the closed in the cancelled.

If you do need to exclude the closed you can add in the following to the Cancelled part of the IF statement

AND NOT(<SR Status>="Closed" And <Activity Type>="Program Delivery" And <Activity Sub Type>="Reach to Recovery" )

It may be a good idea to put brackets around the separate parts of the cancelled clause i.e. after and before each OR statment. This will help the "readability" of the statement and will ensure you get the correct results.

 

[skrandel]

I agree. It's all about the parenthesis. Put them in where they belong and this will work just fine.

Steve Krandel
Symantec

 

[dkrauseuf]

The "Closed" seems to work fine - what I posted is only part of the variable. Part of the other clauses (I have one for "Cancelled", "Pending", "Declined", and "NA") needs to be that if the record meets the criteria for "Closed", it shouldn't show up at all in the other categories. So what's happening is that there are some records that show up as "Closed" appropriately - but they also have some criteria that meet "Pending", and they are showing up there too. I don't want them to show up in any other category if they appropriately meet the "Closed" criteria. I tried to use the AND NOT that was suggested, and that didn't help.

I'm not sure if I'm going about this the right way or not...is there an easier way?

 

[huggyd]

The easiest way is probably to post the entire variable ...

 

[dkrauseuf]

OK here goes:

= If (<SR Status>="Closed" And <Activity Type>="Program Delivery" And <Activity Sub Type>="Reach to Recovery") Then "Closed" Else If (<SR Service Type>="Reach to Recovery" And <SR Status>="Unable to Contact") Or (<SR Service Type>="Reach to Recovery" And <SR Status>="Closed - Not Met") Or (<SR Service Type>="Reach to Recovery" And <Activity Type> InList ("Call - Inbound" , "Call - Outbound" , "Email - Inbound" , "Email - Outbound" , "Fax - Inbound" , "Fax - Outbound") And <Activity Status>="Closed - No Reply" And <Activity Volunteer Last/Org Name>="#EMPTY") And Not (<SR Status>="Closed" And <Activity Type>="Program Delivery" And <Activity Sub Type>="Reach to Recovery") Then "Cancelled" Else If (<SR Service Type>="Reach to Recovery" And <SR Status>="Cancelled") Or (<SR Service Type>="Reach to Recovery" And <Activity Type> InList ("Call - Inbound" , "Call - Outbound" , "Email - Inbound" , "Email - Outbound" , "Fax - Inbound" , "Fax - Outbound") And <Activity Status>="Declined") And Not (<SR Status>="Closed" And <Activity Type>="Program Delivery" And <Activity Sub Type>="Reach to Recovery") Then "Declined" Else If (<SR Service Type>="Reach to Recovery" And <Activity Type> InList ("Call - Inbound" , "Call - Outbound" , "Email - Inbound" , "Email - Outbound" , "Fax - Inbound" , "Fax - Outbound") And <Activity Volunteer Last/Org Name> InList SELECT ALL EXCEPT EMPTY) And Not (<SR Status>="Closed" And <Activity Type>="Program Delivery" And <Activity Sub Type>="Reach to Recovery") Then "Pending" Else "Not Applicable"

A couple of additional points/questions, in addition to my original issue:

1) In the 2nd clause (for "Cancelled")I have And <Activity Volunteer Last/Org Name>="#EMPTY") - I am trying to get records where the Activity Volunteer Last/Org Name is Null or blank...is this the right way to get these?

2) In the last clause (for "Pending") I have And <Activity Volunteer Last/Org Name> InList SELECT ALL EXCEPT EMPTY - I opened this up and selected all in the list, but really I am trying to get records where the Activity Volunteer Last/Org name is Not Null - basically if there is anything in the field, I want those records...not sure if this is the best way to do this either?

Thanks to all for suggestions and help!

 

[huggyd]

What application are you writing this code in?

 

[dkrauseuf]

Business Objects

 

[huggyd]

When I said which application I was after which app within business objects and whcih version.

For your nulls look at the ISNULL() function. For blanks try <Activity Volunteer Last/Org Name> = "".

 

[dkrauseuf]

Sorry, I'm fairly new to this and am not a programmer, as you can tell. Looks like version 6.1b.

As far as which app within Business Objects, I'm not sure what that means.

I'll try your suggestions for Nulls - thanks in advance for your continued help!