CR9- Table Linking, Null Field...?

[OrionStar]

I have to different tables in my report that share a linking data element. INVOICE NUMBER. Please see sample below:

TABLE1
INVOICE# FEEamt COSTamt
12345 100.00 0.00
12346 0.00 25.00
12347 100.00 25.00

This is an example of what my invoices may look like. I'm having a problem having the items print on a report WHEN THEY CONTAIN A ZERO (0.00)IN EITHER THE FEE OR COST AMT FIELD.

Can you tell me what I might be able to do in order to include ALL of the above items on my report. Currently...only invoice #12347 is printing on my report.

THanks


"A man is only as happy,... as he makes up his mind to be...

 

[OrionStar]

I'm sorry let me expand see below first:

TABLE1 FEES
INVOICE# FEEamt
12345 100.00
12346 0.00
12347 200.00

TABLE2 COSTS
Invoice# COSTS
12345 0.00
12346 25.00
12347 75.00


REPORT ONLY PRINTS the item that has BOTH a fee and a cost in the records???

INVOICE # FEEAMT COSTAMT TOTAL
12347 200.00 75.00 275.00

I need for ALL of the items to print like this.

INVOICE # FEEAMT COSTAMT TOTAL
12345 100.00 0.00 100.00
12346 0.00 25.00 25.00
12347 200.00 75.00 275.00


I hope this helps in explaining..



"A man is only as happy,... as he makes up his mind to be...

 

[adhhealth]

how did u link the tables,
i have a feeling if you linked table1.invoice# to table2.invoice# ,
it should print what you need.
check to see if record selection formula or suppressing sections on any condition are leading to this out put ..
also look at the linking of the tables one more time

 

[kskid]

Post the formula you have for total? Shuld be something like

numbervar x := 0;
if isnull({feeamt}) then
  x
else 
  x = {feeamt};
if isnull(constant) then
  x := x
else
  x := x + {constant};

x

-lw

 

[kskid]

Ooops... slight error on assignment

numbervar x := 0;
if isnull({feeamt}) then
  x
else 
  [b][COLOR=red]x := {feeamt};[/color][/b]
if isnull(constant) then
  x := x
else
  x := x + {constant};

x

 

[OrionStar]

I'm getting an error on the CONSTANT text...what should go THERE????

Thanks.


"A man is only as happy,... as he makes up his mind to be...

 

[kskid]

typo on my part... should be your CostAmt (Cost Amount?)

 

[kskid]

Substitute the actuual field names for {FeeAmt} and {CostAmt}

-LW

 

[OrionStar]

Yes, I am.... and my data is still not showing up.. I'm trying to play with the Linking which appears to be the problem.

The issue is that TABLE#1 will only have THE FEES and TABLE#2 will only have THE COSTS.

My report is automatically dropping any invoice that does NOT have BOTH A FEE and A COST..

????


"A man is only as happy,... as he makes up his mind to be...

 

[kskid]

You should have a master invoice table that will link the fee and cost together. If so, add the table and perform a Left Outer Join (LOJ) using Database->Visual Linking

INVOICE.INVOICENUMBER ->LOJ-> FEE.INVOICENUMBER
->LOJ-> COST.INVOICENUMBER

-LW