[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 297
  • Last Modified:

Crystal Reports include two books

Why can't I do this to look for people who should be under 2 books.  "CARDIOPW"  is one book and I want to see if anyone else is in the other books.  I did test so I know I should get at least a test person but I get nothing.

{BOOK.ABBREV} = "DIOPW" and
{BOOK.ABBREV} in ["DIO", "DIOH", "DIOMI", "DIOR", "DIOSM", "DIOWR", "DIOWS"] and
{APPT.DATE} in DateTime (2012, 01, 01, 00, 00, 00) to DateTime (2012, 04, 01, 00, 00, 00)
0
trinisunset
Asked:
trinisunset
  • 3
  • 2
  • 2
  • +1
3 Solutions
 
Éric MoreauSenior .Net ConsultantCommented:
it can't be a AND because the abbrev can't be DIOPW and something else at the same time!

can't you include DIOPW inside the IN clause?

or maybe something like (depending on how the date should be treated):
{BOOK.ABBREV} = "DIOPW" OR
({BOOK.ABBREV} in ["DIO", "DIOH", "DIOMI", "DIOR", "DIOSM", "DIOWR", "DIOWS"] and
{APPT.DATE} in DateTime (2012, 01, 01, 00, 00, 00) to DateTime (2012, 04, 01, 00, 00, 00) )
0
 
trinisunsetAuthor Commented:
The people have to be in the DIOPW book so don't think that the "OR" would work or including it in the "IN" list.  
They have to belong to the mandatory book of DIOPW and I would like to find out if any of them also belong to this list ["DIO", "DIOH", "DIOMI", "DIOR", "DIOSM", "DIOWR", "DIOWS"]
 
I added in a test person so I know, at least, I should pull that test person up but I don't get anything.
0
 
Éric MoreauSenior .Net ConsultantCommented:
I don't get it. Somebody can't be it 2 list at the same time (and)! It has to be DIOPW OR something else!

can you show sample data and the expected results?
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
mlmccCommented:
Try this

You will have to build a view in the database or use a Crystal COMMAND for the source

SELECT FIELDS YOU NEED
FROM
BOOK  INNER JOIN APPT ON appropriate join
WHERE Book.Name  IN (SELECT Book1.Name FROM Book Book1 WHERE {BOOK.ABBREV} = "DIOPW" )
AND
({BOOK.ABBREV} in ["DIO", "DIOH", "DIOMI", "DIOR", "DIOSM", "DIOWR", "DIOWS"]
{APPT.DATE} in DateTime (2012, 01, 01, 00, 00, 00) to DateTime (2012, 04, 01, 00, 00, 00) )

mlmcc
0
 
James0628Commented:
I'm guessing that the 2 books that you're talking about are in 2 different records.  For example, you've got one record in BOOK where ABBREV is "DIOPW", and a second record in BOOK where ABBREV is "DIO".

 If so, then, as emoreau said, a record selection simply isn't going to work, because it's only seeing one record at a time, so ABBREV can only have one value at a time.

 You could use a query like the one that mlmcc posted (that would be the new datasource for your report).  It uses a sub-query to get a list of the DIOPW books, and then checks to see if the current person is in that list.

 You could probably also do this in a report, in one way or another, but doing it in the db would probably be much more efficient.


 Also, FWIW, I'm a little confused by your first post.  You said ' "CARDIOPW"  is one book and I want to see if anyone else is in the other books', but then you have {BOOK.ABBREV} = "DIOPW".

 Are you looking for "CARDIOPW" or "DIOPW"?

 If "DIOPW" is supposed to match "CARDIOPW", you would need to use Like, instead of = (eg. {BOOK.ABBREV} like "*DIOPW").

 James

 PS: mlmcc, I guess this should be moved to the CR zone?
0
 
James0628Commented:
I got a "neglected question" email.  I almost missed the zone too.

 James
0
 
Éric MoreauSenior .Net ConsultantCommented:
can you show sample data and the expected results?
0
 
trinisunsetAuthor Commented:
James, Sorry "CARDIOPW" is another name for  "DIOPW" sorry for the mix up.  I will try what mlmcc suggested and let you know if it works.  Thank you for the suggestions.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now