If record exists in Table A and Table B display Table B record.

[locoproko]

Crystal Reports 10
Pervasive 10

I am trying to query two tables where when a record exists in Table A and not Table B then display record for Table A. If record exists for Table A and exists in Table B then display Table B. Table A will always have a record if not then one will not exist in Table B.

Table A has 2 fields Order_Number and Line_Number
Table B has 2 fields Order_Number and Record_Number

Data in each respective field is of same type from Table A to Table B.

Link from Table A.Order_Number to Table B.Order_Number with Left Outer Join

I have seached every where and just need to be kicked in the right direction please.

Thank You

Systems/IT Manager
Crystal Report Ver. 10.0.0.533
Pervasive 8.5 ODBC

 

[MCuthill]

locoproko,

Create a formula like:

IF IsNull({TableB.Record_Number}) OR {TableB.Record_Number}="" THEN {TableA.Line_Number} ELSE {TableB.Record_Number}

Hope this helps!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[locoproko]

I added the Code and changed the table.field to the correct ones placed in header of report and no difference. My report has all four fields in it. Not sure what I am doing wrong, Granted I am also selecting by date range.

IF IsNull({V_OE_SHIP_SCHED.ORDER_LINE}) OR {V_OE_SHIP_SCHED.ORDER_LINE}="" THEN {V_ORDER_LINES.RECORD_NO} ELSE {V_OE_SHIP_SCHED.ORDER_LINE}

Systems/IT Manager
Crystal Report Ver. 10.0.0.533
Pervasive 8.5 ODBC

 

[MCuthill]

Locoproko,

This formula should be placed in the details section of the report (or rather, it was written with the assumption to be placed in the details-level).

Can you please describe the structure of your report (are there any groups), and provide some sample-data of what you are seeing and/or what results you would like to see for the same data.

Thanks!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."