Developing a Search Page with multiple options

[ramblinrose]

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

 

[kray4660]

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).

 

[ramblinrose]

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

 

[ramblinrose]

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})