Help with selection formula 2

[rinder]

I have a single field that has invoice#'s and Credit Note# {*.DOCNUM) in one table, this field is linked to 2 fields in 2 other tables an invoice table and Credit note table (where IN's are separate from CN's).

The goal is to return the order number associated with the DOCNUM. I've created the formula below which returns the order number of DOCNUM's that are invoices but not for DOCNUM's that are Credit notes, can anyone help.

Thanks in advance,

Rinder


If {OECOMM.DOCNUM} = {OEINVH.INVNUMBER} then {OEINVH.ORDNUMBER}

else

if {OECOMM.DOCNUM} = {OECRDH.CRDNUMBER} then {OECRDH.ORDNUMBER}

 

[kenhamady]

How are these tables joined in the visual linking expert? Is this SQL Based? Are you using outer joins?

What is your current selection formula?

What is the result?

Remember that a "selection formula" determines which records are included in the report. It does not return a value, and can't be printed as a column on the report.

Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[rinder]

Ken,
Bear with me on the terminology, I'm relatively new at this...
The Docnum field in Table 1 is joined to the Invoice# field in Table 2 and Creditnote# field in Table 3 (Table 2 and 3 have an order# field which I'm trying to display).

The Database is pervasive Sql, but the joins are defaulted to "=", I don't have the option to change.

The current selection formula:
{OECOMM.SALESPER} in {?FROMSALESPERSON} to {?TOSALESPERSON} and
{@dTransDate} in {?From Date} to {?To Date}
Returns a list of invoices and Credit notes and $ amounts for each sales person in a defined period of time.

The report works great, I'm just trying to add a column which gives me the order# associated with the Inv# or CrdtNote#.

Any interesting discovery is if I reverse the IF statement in my pervious post, the report returns the order#'s associted with the Credit Notes only, as opposed to before which returned the order#'s associated with Inv#'s.

Thanks,

Rinder

 

[kenhamady]

I am concerned about the equal join, which should (in normal SQL) eliminate records if they don't have BOTH an invoice record and a credit not record. Make sure you aren't missing any records that have only one or the other.

Your formula behavior is common when you run into null values. Try:

If IsNull({OEINVH.ORDNUMBER})
then {OECRDH.ORDNUMBER}
else {OEINVH.ORDNUMBER} Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[MalcolmW]

If you are using left outer joins with the tables, then simply doing this should work.
If (NOT IsNull({OEINVH.INVNUMBER})) AND {OECOMM.DOCNUM} = {OEINVH.INVNUMBER} then
{OEINVH.ORDNUMBER}
Else if {OECOMM.DOCNUM} = {OECRDH.CRDNUMBER} then
{OECRDH.ORDNUMBER}

If you are not using left outer joins, probably the easiest solution is to change that. If you need help with the syntax for that, just cut and paste your query from Database|Show SQL Query. Malcolm