Formula Help for selecting different data rom two fields

[VBugDenver]

I need help with a report on two field with two separate data selections on these two field and show the results in the same report.

The two fields are {Schedule.Activity} and {Schedule.Status}

I need to select:

From the {Schedule.Activity} I need to select the activity [“1”, “2”, “3”] where {schedule.Status} <> “X” and
I need to select {Schedule.Activity} where the activity is [“4”, “5”, “6”] where {schedule.Status} = “S”

Here is the formula I have for this:

select {Schedule.Activity} in ["1", "2", "3"] and {Schedule.Status} <> "X" and
select {Schedule.Activity} in ["4", "5", "6"] and {Schedule.Status} = “S”

This formula works but when it runs I get all six of the {Schedule.Activity} where {Schedule.Status} equals “S” and does not equal “X”. I need these two selections to run independently and show in the same report.

Does anyone have an idea what I am doing wrong?

 

[NiceArms]

select {Schedule.Activity} in ["1", "2", "3"] and {Schedule.Status} <> "X" and
select {Schedule.Activity} in ["4", "5", "6"] and {Schedule.Status} = "S"

in the above you are asking for Activity that is 1,2or3 and status is not X. S is not X and may have activity values of 1,2or3 and will therefore pick up data that has activity of 1,2or3 with a status of S. you need to exclude S from your firstline....... untested so please let me know if you have any issues.

select {Schedule.Activity} in ["1", "2", "3"] and NOT({{Schedule.Status} IN ["X","S"]) 
and select {Schedule.Activity} in ["4", "5", "6"] and {Schedule.Status} = "S"

I feel sorry for people who don't drink, when they wake up in the morning that's as good as the're going to feel all day!

 

[VBugDenver]

Thanks NiceArms! It did work for me and pointed me in the right direction for some other reports I am writing. I did exclude all status X and S and them selected only those activities that I needed to display that did have the status S.

You Rock!

 

[lbass]

I don't think that select statement would return any records, since one row can't have values 1, 2, or 3 AND values 4,5,or 6. I think it should be:

(
(
{Schedule.Activity} in ["1", "2", "3"] and
{Schedule.Status} <> "X"
) or
(
{Schedule.Activity} in ["4", "5", "6"] and
{Schedule.Status} = "S"
)
)

-LB

 

[NiceArms]

If you are to put the this into crystal "select" is not needed (at least on the version I use)

(
(
{Schedule.Activity} in ["1", "2", "3"] and
{Schedule.Status} <> "X"
) or
(
{Schedule.Activity} in ["4", "5", "6"] and
{Schedule.Status} = "S"
)
)

I am sure that the above would still return everything that has an Activity of 1,2,3 and the Status is not X, or in other words:
Activity 1,2,3 Status A-ZZ but not X (assuming only letters are in the field) which would include Activity 1,2,3 and Status S which is not needed.

If I am "off base" please let me know


I feel sorry for people who don't drink, when they wake up in the morning that's as good as the're going to feel all day!

 

[VBugDenver]

{Schedule.Status} <> "X" and
{Schedule.Status} <> "S" and
select {Schedule.Activity} in ["1", "2", "3"] and
select {Schedule.Activity} in ["4", "5", "6"] and
{Schedule.SchStatus_Hist_SD} = "S"

This is how I wrote the formula. But when I run it; activities 1,2,3 work fine, but I still get all 4,5,6 with and without status S.

I guess to explain what I want simply, I want to display activities 1,2,3,4,5,6 where status does not equal X and I only want to show activities 4,5,6 where status is S.

Thanks for your help!

 

[VBugDenver]

If I do not use SELECT I get nothing.

 

[NiceArms]

LBass is right - it's the OR that you need. For some reason unknown to me I was thinking of the 2 lines as 2 different formulas!!!!!!

Try LBass's code.

I feel sorry for people who don't drink, when they wake up in the morning that's as good as the're going to feel all day!

 

[VBugDenver]

I ran the report that way and I got all my data except the activities 4,5,6 equaled S.

Would the OR confuse the formula, it is one OR the other, not both?

 

[lbass]

Your original post indicated you wanted 4,5, and 6 only if status equaled S. Is this NOT the case?

-LB

 

[VBugDenver]

Yes, that is the case. The report worked fine. Thank you very much lbass, this was a big help.