• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 299
  • Last Modified:

CR9/SQL - Create Section only if 'field' contains "WORD"

Using CR 9 with a MS SQL db

Patient database, tables: tbPatients (name, ID, AlertID), tbPatientAlerts(alertID, alertDesc)

Alerts contains "Collection", "Call Dr. Office", "Print Film", etc.

Need to show Section with "Patient in Collection" if "Collection" is selected in table tbPatientAlerts, and not show if any other alerts or no alerts are selected.

I can do basic stuff but...
0
wcotis60
Asked:
wcotis60
  • 4
  • 4
1 Solution
 
frodomanCommented:
So you want to show all patients that have an AlertID equal to whatever ID is described as "Collection" - is that correct?

If so, Create a report and put both tables on the report.  Link them by AlertID.  Set your selection criteria to {tbPatientAlerts.alertDesc} = 'Collection'

That's all there is to it, so I'm guessing maybe I didn't understand what you want?  If I'm off target can you explain a little more what you're trying to do?

frodoman
0
 
wcotis60Author Commented:
Not exactly, I need to show all patients for {tbEncounters.ApptDate} = "10/8/04" and then IF they have a "Collection" alert show that alert in the report.

Sorry for the confusion.
0
 
frodomanCommented:
I'm assuming that each patient will only be in the table once.

Create your report using both tables.  Link them by AlertID.  Set your selection criteria to {tbEncounters.ApptDate} = "10/8/04".

Add your fields to the report, including the alert description.  If I understand correctly, you should not be viewing almost what you need - the difference being that all alert descriptions are shown, not just the Collection ones.

To fix this, you want to suppress the one's that aren't needed.  Right-click on the {tbPatientAlerts.alertDesc} field and select "Format Field".  On the 'Common' tab you want to click the formula button (X+2) that is to the right of the word "suppress".  Enter a formula that results in "True" when you want the field hidden and "False" when you want it visible.  In this case a good formula would be:

{tbPatientAlerts.alertDesc} <> 'Collection'

This will suppress this field whenever it's not equal to the word Collection.


A similar alternative would be to leave all of the alerts displayed, but change the color of the Collection alerts.  On the 'fonts' tab for this field enter a formula like this to the right of the 'color' dropdown:

If CurrentFieldValue = 'Collection' then crRed Else crBlack

This will color the word Collection red and leave all other text black.


HTH

frodoman
0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
wcotis60Author Commented:
When I do the first suggested formula, I am getting a patient line for each alert selected for that patient (or if no alerts), but only the "Collection" is printing.

  Pt 101    (empty alert)--> alert "Metal in Body"

  Pt 101    COLLECTION  --> alert"Collection"

 Pt 303     (empty)  --> no alerts

 Pt 404     (empty) --> alert "Print Film"

 Pt 404     (empty) ---> alert "Call Report

 Pt 733     (empty) --> no alerts

0
 
frodomanCommented:
So obviously each patient *is* in the table more than once then.  What I would do now is this:

Erase the conditional suppression formula - it isn't going to help you.

Report -> Group Expert:  Add a group based on Patient so your report (given example above) will look like this:

 Pt 101    
    "Metal in Body"
    COLLECTION  
 Pt 303
     no alerts
 Pt 404
     "Print Film"
     "Call Report
 Pt 733
      no alerts

Create a new formula and place it in the group header:
// @ResetGroup
shared numberVar IsCollection := 0;

Create a new formula and place it in the details section:
// @EvalGroup
if {tbPatientAlerts.alertDesc} = 'Collection' then shared numberVar IsCollection := 1;

Create a new formula and place it in the group footer:
// @ShowAlert
if shared numberVar IsCollection = 1 then 'Collection';

This should make your report look like this now:

GH Pt 101    
D    "Metal in Body"
D   COLLECTION  
GF Collection
GH Pt 303
D     no alerts
GF
GH Pt 404
D     "Print Film"
D     "Call Report
GF
GH Pt 733
D      no alerts
GF

Now drag the patient id / name down to the group footer.  Suppress the details section and the group header section and you'll be left with this:

GF PT101 Collection
GF PT303
GF PT404
GF Pt 733

I believe that's what you're trying to get to - let me know if we still aren't there.

frodoman
0
 
wcotis60Author Commented:
I will look at the above, but in the mean time I have created a subreport of the {alerts.desc} linked to main by PatientID.

The earlier formula does suppress the alerts that are not "Collection", but leaves a blank line in the subreport, so if the patient has 3 alerts and one is "Collection" the subreport looks like this:

   <blank>
   <blank>
   Collection

How can I suppress the blank lines within the subreport?
0
 
wcotis60Author Commented:
Forget the previous question - I got it to work.

Suppress blank section within the subreport!
Thanks!

points on the way
0
 
frodomanCommented:
Glad you got it working.  

If performance is a concern you may want to consider my last posting as an option.  Of course the subreport method works fine but it also requires Crystal to hit the db once for every patient instead of just one time in total.  If you're happy w/ current performance then no reason to change of course!

Cheers,

frodoman
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now