Need help with formula, Crystal XI 2

[jpeters01]

Hi,

I need to pull patients first locations when being admitted to the hospital into a report. The fields in the database are pat_loc1, pat_loc2, pat_loc3, pat_loc4, pat_loc5, etc. The values of the location fields are updated in the database so that the most current location is #1, for example, if a patient comes in through the ED, then pat_loc1 = ED, but if the patient is admitted to 4South, pat_loc1 = 4South and pat_loc2 = ED. If the patient is then transferred to ICU, pat_loc1 = ICU, pat_loc2 = 4South, pat_loc3 = ED. ED is the location that I need in this instance to display on the report.

I appreciate your assistance

 

[kray4660]

So if any of the Pat_loc fields contain ED, you want that record?

If so, then for the selection criteria
pat_loc1 = 'ED'
or pat_loc2 ='ED'
or pat_loc3 ='ED'
or pat_loc4 ='ED'
or pat_loc5 ='ED' and so on. I do not know how many pat_loc fields there are (hopefully not a lot).

 

[kenhamady]

I read your question differently than kray did. I think you want to find the location that came first in each set, which would be the value in the field with the highest numbered location excluding blank locations. Try it this way:

if pat_loc2 = '' then pat_loc1 else
if pat_loc3 = '' then pat_loc2 else
if pat_loc4 = '' then pat_loc3 else
if pat_loc5 = '' then pat_loc4 else pat_loc5

This assumes that there are never blanks between filled locations and that there is never a blank location 1. Also, make sure you flip the switch at the top of the formula so instead of "Exception for Nulls" it says "Default Value for Nulls".

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[Charliy]

if not(isnull({table.pat_loc_5}))
then {table.pat_loc_5}
else
if not(isnull({table.pat_loc_4}))
then {table.pat_loc_4}
else
if not(isnull({table.pat_loc_3}))
then {table.pat_loc_3}
else
if not(isnull({table.pat_loc_2}))
then {table.pat_loc_2}
else {table.pat_loc_1}

 

[jpeters01]

I am finally able to work on this again and both KENHAMADY and CHARLIY's suggestions work...Oh Happy Day, thank you so much!

Jan