Need to look in a second table when a record is not in the first table

[thethuthinnang]

I am trying to add a field to my report from one of two tables. If the record doesn't exist in the first table I want Crystal to look for it in the second. The result of this formula is a blank report:

If IsNull({arp.due_date}) Then {art.due_date} Else {arp.due_date}

Can I do this with a formula? Both tables are linked as Left Outer Join.

Thanks!

 

[Turkbear]

Hi,
How are the 2 tables linked in your report?

- which is first ( leftmost)?
- What field links them?






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[thethuthinnang]

The left table is orders_invoice and they are linked with doc_ctrl_num.

I am also experimenting with SQL - UNION but no luck with that yet. I'm really new to Crystal and databases in general.

 

[Turkbear]

Hi,
something to try:

Use a Right-Outer join between the tables

then use your formula to display that field:
(assuming arp is the left table)

@showit
If IsNull({arp.due_date}) Then {art.due_date} Else {arp.due_date}

This should be in the detail in place of those 2 database table fields, not used as a record selection criterion.




To Paraphrase:"The Help you get is proportional to the Help you give.."