×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Developing a Search Page with multiple options

Developing a Search Page with multiple options

Developing a Search Page with multiple options

(OP)
I am using Crystal Reports 2016

I have been tasked with developing a report that is based off a user performing several search options. They can search by 1 or multiple fields. I developed parameters for each field that can be searched on with the optional prompt set to true. I then added the prompts to the record selections as shown below.

(not HasValue({?Social Security #}) OR {MH_CMHC_PATIENT_DEMOGRAPHICS.ID_SSN} = {?Social Security #}) and
(not HasValue({?Client CIN Number}) OR {MH_CMHC_PATIENT_DEMOGRAPHICS.CSI_CIN} = {?Client CIN Number}) and
(not HasValue({?AKA - First Name}) OR {MH_CMHC_PATIENT_DEMOGRAPHICS.AKA_FIRST_NAME} = {?AKA - First Name}) and
(not HasValue({?AKA - Last Name}) OR {MH_CMHC_PATIENT_DEMOGRAPHICS.AKA_LAST_NAME} = {?AKA - Last Name}) and
(not HasValue({?Maiden Name}) OR {MH_CMHC_PATIENT_DEMOGRAPHICS.MADIEN_NAME} = {?Maiden Name}) and
(not HasValue({?First Name}) OR {MH_CMHC_PATIENT_DEMOGRAPHICS.FIRST_NAME} = {?First Name}) or
(not HasValue({?Last Name (with Soundex)}) or Soundex({MH_CMHC_PATIENT_DEMOGRAPHICS.LAST_NAME}) = Soundex({?Last Name (with Soundex)})) and
(not HasValue({?Last Name}) OR {MH_CMHC_PATIENT_DEMOGRAPHICS.LAST_NAME} = {?Last Name}) and
(not HasValue({?Case Number}) OR {MH_CMHC_PATIENT_DEMOGRAPHICS.CID} = {?Case Number})

The problem I am finding is that if I enter the Case Number prompt or the Last Name I get what is expected, but not for some of the other prompts where I get all the records. Does anyone have a better way of doing this?

Thank you.
Rose Mary

RE: Developing a Search Page with multiple options

I am a little concerned with the or after the (not HasValue({?First Name}) OR {MH_CMHC_PATIENT_DEMOGRAPHICS.FIRST_NAME} = {?First Name}) line. I think you might get some odd results because it not clear what the 'OR' belongs to. I normally put a set of parenthesis around the statements (or set of statements) that the "OR" should belong to (I hope this makes sense).

RE: Developing a Search Page with multiple options

(OP)
I changed the 'or' to 'and' and put parenthesis around the entire statement. Still does not work.

RE: Developing a Search Page with multiple options

(OP)
SOLVED

Since there were 3 possible outcomes for a Last Name search, I had to generate 3 sets of record filter possibilities which worked. A user can now search on any field or combination of fields and will get results for the prompts completed even using the soundex.

if Not HasValue({?Is Last Name with Soundex}) then
(not HasValue({?First Name}) OR {MH_CMHC_PATIENT_DEMOGRAPHICS.FIRST_NAME} = {?First Name}) and
(not HasValue({?Case Number}) OR {MH_CMHC_PATIENT_DEMOGRAPHICS.CID} = {?Case Number})and
(not HasValue({?Maiden Name}) OR {MH_CMHC_PATIENT_DEMOGRAPHICS.MADIEN_NAME} = {?Maiden Name}) and
(not HasValue({?AKA - Last Name}) OR {MH_CMHC_PATIENT_DEMOGRAPHICS.AKA_LAST_NAME} = {?AKA - Last Name}) and
(not HasValue({?AKA - First Name}) OR {MH_CMHC_PATIENT_DEMOGRAPHICS.AKA_FIRST_NAME} = {?AKA - First Name}) and
(not HasValue({?Age}) OR {@Age} = {?Age}) and
(not HasValue({?City}) OR {ADDRESSRECORDHISTORY.CITY} = {?City}) and
(not HasValue({?County of Residence}) OR {ADDRESSRECORDHISTORY.COUNTY} = {?County of Residence}) and
(not HasValue({?Client CIN Number}) OR {MH_CMHC_PATIENT_DEMOGRAPHICS.CSI_CIN} = {?Client CIN Number}) and
(not HasValue({?Social Security #}) OR {MH_CMHC_PATIENT_DEMOGRAPHICS.ID_SSN} = {?Social Security #}) and
(not HasValue({?Gender}) OR {MH_CMHC_PATIENT_DEMOGRAPHICS.SEX} = {?Gender}) and
(not HasValue({?Birthdate}) OR {@DOB} = {?Birthdate}) and
(not HasValue({?Zip Code}) OR {ADDRESSRECORDHISTORY.ZIP} = {?Zip Code})

else if {?Is Last Name with Soundex} = 'Using Soundex' then
(Soundex({MH_CMHC_PATIENT_DEMOGRAPHICS.LAST_NAME}) = SoundexW({?Last Name (with Soundex)})) and
(not HasValue({?First Name}) OR {MH_CMHC_PATIENT_DEMOGRAPHICS.FIRST_NAME} = {?First Name}) and
(not HasValue({?Case Number}) OR {MH_CMHC_PATIENT_DEMOGRAPHICS.CID} = {?Case Number})and
(not HasValue({?Maiden Name}) OR {MH_CMHC_PATIENT_DEMOGRAPHICS.MADIEN_NAME} = {?Maiden Name}) and
(not HasValue({?AKA - Last Name}) OR {MH_CMHC_PATIENT_DEMOGRAPHICS.AKA_LAST_NAME} = {?AKA - Last Name}) and
(not HasValue({?AKA - First Name}) OR {MH_CMHC_PATIENT_DEMOGRAPHICS.AKA_FIRST_NAME} = {?AKA - First Name}) and
(not HasValue({?Age}) OR {@Age} = {?Age}) and
(not HasValue({?City}) OR {ADDRESSRECORDHISTORY.CITY} = {?City}) and
(not HasValue({?County of Residence}) OR {ADDRESSRECORDHISTORY.COUNTY} = {?County of Residence}) and
(not HasValue({?Client CIN Number}) OR {MH_CMHC_PATIENT_DEMOGRAPHICS.CSI_CIN} = {?Client CIN Number}) and
(not HasValue({?Social Security #}) OR {MH_CMHC_PATIENT_DEMOGRAPHICS.ID_SSN} = {?Social Security #}) and
(not HasValue({?Gender}) OR {MH_CMHC_PATIENT_DEMOGRAPHICS.SEX} = {?Gender}) and
(not HasValue({?Birthdate}) OR {@DOB} = {?Birthdate}) and
(not HasValue({?Zip Code}) OR {ADDRESSRECORDHISTORY.ZIP} = {?Zip Code})

else if {?Is Last Name with Soundex} = 'Spell Last Name' then
({MH_CMHC_PATIENT_DEMOGRAPHICS.LAST_NAME} = {?Last Name (exact spelling)}) and
(not HasValue({?First Name}) OR {MH_CMHC_PATIENT_DEMOGRAPHICS.FIRST_NAME} = {?First Name}) and
(not HasValue({?Case Number}) OR {MH_CMHC_PATIENT_DEMOGRAPHICS.CID} = {?Case Number})and
(not HasValue({?Maiden Name}) OR {MH_CMHC_PATIENT_DEMOGRAPHICS.MADIEN_NAME} = {?Maiden Name}) and
(not HasValue({?AKA - Last Name}) OR {MH_CMHC_PATIENT_DEMOGRAPHICS.AKA_LAST_NAME} = {?AKA - Last Name}) and
(not HasValue({?AKA - First Name}) OR {MH_CMHC_PATIENT_DEMOGRAPHICS.AKA_FIRST_NAME} = {?AKA - First Name}) and
(not HasValue({?Age}) OR {@Age} = {?Age}) and
(not HasValue({?City}) OR {ADDRESSRECORDHISTORY.CITY} = {?City}) and
(not HasValue({?County of Residence}) OR {ADDRESSRECORDHISTORY.COUNTY} = {?County of Residence}) and
(not HasValue({?Client CIN Number}) OR {MH_CMHC_PATIENT_DEMOGRAPHICS.CSI_CIN} = {?Client CIN Number}) and
(not HasValue({?Social Security #}) OR {MH_CMHC_PATIENT_DEMOGRAPHICS.ID_SSN} = {?Social Security #}) and
(not HasValue({?Gender}) OR {MH_CMHC_PATIENT_DEMOGRAPHICS.SEX} = {?Gender}) and
(not HasValue({?Birthdate}) OR {@DOB} = {?Birthdate}) and
(not HasValue({?Zip Code}) OR {ADDRESSRECORDHISTORY.ZIP} = {?Zip Code})

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close