Conditional Formula

[lindagoodwin]

Hi, I'm using Crystal 10. I have a report that lists the patient's address in the Page Header. A patient can have addresses in {PATIENT.PERMANENT_ADDRESS}&{PATIENT.PERMANENT_ADDRESS_2}and we needed them both if available, so I wrote this formula that worked perfectly until I had a patient with a Foreign Country address.

The formula that worked is:

if isnull({PATIENT.PERMANENT_ADDRESS_2})
then
{PATIENT.PERMANENT_ADDRESS} & Chr(13) & {PATIENT.PERMANENT_CITY}& ", "& {V_PATIENT_CONCISE.PERMANENT_STATE_CODE} &
" "
& {PATIENT.PERMANENT_ZIP_CODE}
else
{PATIENT.PERMANENT_ADDRESS} & Chr(13) &
{PATIENT.PERMANENT_ADDRESS_2} & chr (13)& {PATIENT.PERMANENT_CITY} & ", "& {V_PATIENT_CONCISE.PERMANENT_STATE_CODE}
& " "& {PATIENT.PERMANENT_ZIP_CODE}

So now I have another formula for Foreign Countries but don't know how to combine them into one formula. In Registration process if they put FC in the State Code field it opens up the Foreign Country field.

if{V_PATIENT_CONCISE.PERMANENT_STATE_CODE}="FC" then

{PATIENT.PERMANENT_ADDRESS} & Chr(13) & {PATIENT.PERMANENT_CITY}& ", "& {FOREIGN_COUNTRY.DESCRIPTION}

We don't see that many foreign country patients so I need it to default to my original formula but if the State Code = FC then print the Foreign Country formula.

I have been working on this report for months, it's very complicated and thought I finally had exactly what they wanted but now this popped up yesterday!! Please help!!!

I will appreciate any help I can get at this point!!

 

[BryanLane]

A nest If statement should work. I use the same type of logic to handle our US customers versus those in Canada.

Shorten it up a bit for clarity:

if {V_PATIENT_CONCISE.PERMANENT_STATE_CODE}="FC"
then Foreign Address
else if isnull({PATIENT.PERMANENT_ADDRESS_2})
then Address1
else Address2



"The problems we face today cannot be solved by the minds that created them."
--Albert Einstein

 

[lindagoodwin]

I tried your suggestion but the report came back blank when there were over 380+ records with one Foreign Country patient. This is the formula I wrote:

if {V_PATIENT_CONCISE.PERMANENT_STATE_CODE}="FC"
then {@Foreign address}
else {@Address}

{@Foreign address}formula is:

{PATIENT.PERMANENT_ADDRESS} & Chr(13) & {PATIENT.PERMANENT_CITY}& ", "& {FOREIGN_COUNTRY.DESCRIPTION}

and the {@Address}formula is:

if isnull({PATIENT.PERMANENT_ADDRESS_2})
then
{PATIENT.PERMANENT_ADDRESS} & Chr(13) & {PATIENT.PERMANENT_CITY}& ", "& {V_PATIENT_CONCISE.PERMANENT_STATE_CODE} &
" "
& {PATIENT.PERMANENT_ZIP_CODE}
else
{PATIENT.PERMANENT_ADDRESS} & Chr(13) &
{PATIENT.PERMANENT_ADDRESS_2} & chr (13)& {PATIENT.PERMANENT_CITY} & ", "& {V_PATIENT_CONCISE.PERMANENT_STATE_CODE}
& " "& {PATIENT.PERMANENT_ZIP_CODE}

What am I missing?
Thanks

 

[lbass]

How is the foreign country table joined to the others? It should be added with a left join from the parent table TO the foreign country table.

If you still have trouble, you should place your fields in the detail section and observe whether other fields might be null.

-LB

 

[lindagoodwin]

That worked!! I'm fairly new at Crystal Reports and never understood the joins. I will be researching more and finding out how that relates to my reports.

Thanks Ibass. You're the best.

 

[lbass]

It is critical that you learn about joins--if you get that first step wrong, your reports will be inaccurate.

-LB