Report problem - can someone help?

[jerichob]

First of all, I am not sure if this is the right place in this forum to post, so appologies if this is not the correct section.

I'm trying to design a report that would calculate interest and I cannot find a way to make it.

I will kinda walk you through my logic, please read and perhaps people here will have some good ideas, which could help me out.

-----
Database Source: I only use 1 table (it's a view actually), history of all debtors operations.

What I need to have on my report is the following:
Invoice Number
Invoice Date
Invoice Due Date
Debtor Number/Name
Amount

So far it seems simple. However in the table I use, there are both dates of the invoice and dates of payments. I know which one is which, based on the number, it's either the invoice number or the number of payment.

What I did is this, I created a mirror view (#1 vs. #0 the original one) I linked from #0 document number to the invoice number in #1 (document number contains both invoices and payments)

In Details section of the layout of my Crystal report I placed the date from both views. In that way I get all the info concerning my invoice and, at the same time in Details, all the info concerning payment/payments for this invoice (grouped by invoice #)

This is all great and works fine, but in this way I only get those invoices for which there were payments. I've also got a substantial number of invoices to be paid and which were not paid yet.

I'm wrestling with this problem for some time now and I cannot find the way to get all the invoices together with corresponding payments/partial payments.

I'm not goot at explaining things, I hope you can understand my problem. In case you have any questions, please ask.

Any ideas that might help me?

Jericho

 

[lyanch]

You need an outer join between your tables. Something like

#0.Doc Number (Invoice info) *= #1. Doc Number (payment info)

Then invoice info will show up even if there is no payment info.

Lisa

 

[jerichob]

Thanks! I didn't think about that, but it still does not solve my problem, although it is an excellent guide.

You see, I don't have separate tables for invoices and payments. It's all in one table "Debtors Operations History". I have a field with 'document's number'. This document can be either an invoice or payment. I have another field which is the 'invoice number' ONLY field, but I don't have a field with payment's number.

What I did, I created a copy of the table.

I linked the tables in the following way:

Table #0 Table #1
Doc.# -----> Inv.#

So now, in table #1 I will have invoice numbers to the corresponding payments.

Now when I select Right Outer Join, I still do not get all the invoices, but only those corresponding to the payments.

Argh... I am really kinda biting my nails... What do I do wrong?

 

[lyanch]

I would change my join direction to

Inv# -> Doc# but beyond that all you need to do is make sure that the '*' is on the invoice side so the join would be

Inv# *= Doc# or Doc# =* Inv#

Lisa

 

[jerichob]

Nope, it does not work. I understand your logic and I have the '*' on the invoice side. I tried both Right and Left Outer Joints and I even changed the direction of the link. Still nothing.

Maybe this is because I am working actually on the same table, the link goes from the table to its own copy.

I don't know, I'm guessing.

I'm not sure how to solve it... Lisa, thanks for your great help, if you can think of why this doesn't work, I'd greatly appreciate that.