Formual HELP!!!!!!!!!

[MMichaels]

I need HELP!!!!

I have 2 fields I need information for and the report is showing only info that is from both tables - for example:
I have a field for "Joint Names" and "Legal Names" - It's only showing records where both are true. How do I get the records to show that just have "Joint Names" and no "Legal Names". I have a formula for "Joint Names" and a different formula for "Legal Names". I am probably doing it totally wrong, but here is each formula:

"Joint Names" formula:
If isnull ({name_joint.jt_Name}) then "" else {name_joint.jt_Name}

"Legal Names" formula:
if {name_shr.shr_Name_Pos} = 1 then
"Legal Name 1"
else
if {name_shr.shr_Name_Pos} = 2 then
"Legal Name 2"
else
if {name_shr.shr_Name_Pos} = 3 then
"Legal Name 3"
else
if {name_shr.shr_Name_Pos} = 4 then
"Legal Name 4"
else
if isnull ({name_shr.shr_Name_Pos}) then
""

Anyone have any suggestions as to what I am doing wrong? I need help!!! If there is any other information you need, please ask.. I'm not sure what I'm doing here, I'm pretty new to this! THANKS SOOO MUCH!!!!

~ Melissa ~

 

[mbarron]

Make the IsNull test in your second formula be the first check, not the last. I'm not sure why, but it works better that way. Mike

 

[MMichaels]

Thanks for the tip, but it still isn't working.. Thank you though. I'll try to figure something out.

~ Melissa ~

 

[cmpgeek]

have you tried an outer join from the joint name table to the legal name table? if you do that it will pull every entry with a joint name regardless of the legal name fields; then you can select only those where the legal name field is null... (i think...) i am new to this as well; but i do think it will work...

good luck

 

[MMichaels]

Thanks, but that didn't seem to work either. I tried it with a Left Outer Join and a Right Outer Join. I am still in need of help. Does anyone think that it could really be something with my linking?

~ Melissa ~

 

[Naith]

Melissa,

sstatzer has hit it on the head. To respect data which has Joint records regardless of whether they have matching Legal records, you have to use a left outer join from Joint to Legal.

If you extract the SQL that Crystal is generating (Database/Show SQL) and stick it straight in your database client, you'll find that it won't return you all Joint records and matching Legal records unless you get the join correct.

Until then, there's very little point in trying to force your formula to do this for you, because the formula doesn't have any impact at all on the data that is returned from the database. But, when you do get back to looking at the formula, try to make your null checks the first priority of any formula. As mbarron says, Crystal is a bit of a scoundrel if you don't make it do this bit first.

Please let us know if you continue to have problems.

Naith