Record Select Formula: How specify two separate criteria

[JonAtHQ]

Hello,

I don't know how to combine two separate queries into one formula so that my report will print records based on two separate criteria from two separate tables in my database. (Using CR 11)

1) Print employee records if table.flag = 1
Formula example: {table.flag} = 1

2) Print employee records if employee.state = MD or VA or DC
Formula example: {employee.state} in ["MD", "VA", "DC"]

If I run either query separately, the report works for that formula. But when I combine the queries, it's not reporting on all the records. I've tried AND and OR strategies, but nothing's working.

Thanks!

 

[Charliy]

is your desired result:
1. flag = 1 and state in list
2. all flag = 1 and all state in list

How are these tables linked?

 

[JonAtHQ]

I'm desiring your Number 2: All Flag = 1 (from one table) AND all states in the list (from another table)

It's been quite a while since I linked this database (and I'm no expert in this), but the Database Expert window is telling me that the tables are Auto-Linked By Key. The Links window shows that there is a separate table in between (starts with "V"), and that the lniks are both Inner Joins, Not Enforced, and Link Type is "=". Does this help?

 

[Charliy]

You need to do a left outer join.

 

[JonAtHQ]

@Charliy,

Thanks. Let me give that a try. It might take a few days (or over a week), as I'm slammed over the next few days, and on vacation at the end of this week. But I'll get back and let you know how it went.

But in the meantime, what's the syntax? Do I add an "OR" or "AND" between both formulas?

 

[Charliy]

OR

 

[JonAtHQ]

It's getting closer. Here is my formula:

{Employee.U_Facility} in ["Corporate Facilities", "Company HQ"]
or
{Plan.U_AlertFind} = 1 AND not isnull ({Employee.U_Facility})

However, when the report runs, it's omitting records with no value in the {Employee.U_Facility} field. What's strange to me is that if either record select is run independently, the correct records are being reported.

 

[Charliy]

You said there was a third table in between the other two. Make that table the primary table, and left outer join to the other two tables.

 

[adnil]

Hi, we are using Crystal XI and we have the following data:

Employee name Start date End date Reason
ABC 10/9/2010 12/9/2010 Fever
ABC 15/9/2011 16/9/2011 Migraine
DEF 10/10/2011 30/11/2011 Depression

that needs to be presented as below. Users will enter a date range (ie 15/9/2011 to 15/9/2011) and the report will show all the records where the start date falls between these date range and also to show the last Reason, if any.

Group by reason: Migraine
Employee name Start date End date Last Reason
ABC 15/9/2011 16/9/2011 Fever

DEF should not appear as he does not have a previous Reason. We are almost done with the report but we are stuck at Last Reason. How can we show the last reason please?

Thank you.

 

[Charliy]

Your grouping makes using Previous or Next impossible. If I had to do that I would use a subreport at the group level nd pass the reason as a parameter.