Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Formula Help for selecting different data rom two fields

Status
Not open for further replies.

VBugDenver

Technical User
Feb 18, 2011
16
US
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?
 
Code:
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.

Code:
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!
 
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!
 
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
 
If you are to put the this into crystal "select" is not needed (at least on the version I use)

Code:
(
(
{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 :D


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!
 
{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!
 
If I do not use SELECT I get nothing.
 
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!
 
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?
 
Your original post indicated you wanted 4,5, and 6 only if status equaled S. Is this NOT the case?

-LB
 
Yes, that is the case. The report worked fine. Thank you very much lbass, this was a big help.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top