Forum

I have following code:

SEL ECT
   PaymentLineItems.Payment AS Payment,
   PurchaseInvoice.Date AS DatePurchase,
   PurchaseInvoice.Ref AS RefPurchase,
   PurchaseInvoice.DocumentTotalRC AS PurchaseAmount,
   PaymentLineItems.Ref.Date AS PaymentDate,
   PaymentLineItems.Ref AS RefLines,
   PurchaseInvoice.Company,
   PurchaseInvoice.Memo,
   CashDiscountSupplierLineItems.Ref AS CDRef,
   CashDiscountSupplierLineItems.CashDiscountAmount AS CDAmount
FR OM
   Document.Payment.LineItems AS PaymentLineItems
      FULL JOIN Document.PurchaseInvoice AS PurchaseInvoice
         {FULL JOIN Document.CashDiscountSupplier.LineItems AS CashDiscountSupplierLineItems
         ON (CashDiscountSupplierLineItems.Document = PurchaseInvoice.Ref)}
      ON PaymentLineItems.Document = PurchaseInvoice.Ref

Additionally:

It should look like that:

Date         Company     InvoiceAmount  Payment   CashDiscount

04.04.12   Elektrolux     350,00              200,00         8,00

Main mistake is that the report is generated on documents, not on their register records. The accumulation registers should be used to accumulate amounts you need.

For your case doubles are the result of
{FULL JOIN Document.CashDiscountSupplier.LineItems AS CashDiscountSupplierLineItems
ON (CashDiscountSupplierLineItems.Document = PurchaseInvoice.Ref)}
There are several lines in one of documents having reverences to a single payment document. You need to use

SELECT
   MIN(CashDiscountSupplierLineItems.Ref),
   SUM(CashDiscountSupplierLineItems.CashDiscountAmount),
   CashDiscountSupplierLineItems.Document
FROM
Document.CashDiscountSupplier.LineItems AS CashDiscountSupplierLineItems
GROUP BY
CashDiscountSupplierLineItems.Document

Thanks,

Unfortunately, I still haven't managed to inlude the code suggested by you, but it doesn't matter.

You wrote:

"There are several lines in one of documents having reverences to a single payment document."

I deleted "odd" lines from documents Payment and CashDiscountSupplier so that in every of them is only one line left. But I get the same output in the table

You had in both on the first line Purchase Invoice 2 and on the second Purchase Invoice 3 when you deleted odd lines you have Purchase Invoice 2 in 2 documents, you need to group them any way before joining.

Maybe it was misunderstanding, I need 3 matched documents in one line. The case is:
1) There is some PurchaseInvoice
2) This PurchaseInvoice was payed (Document Payment)
3) For this PurchaseInvoice was got a CashDiscount (very similar to Payment)
4) ... I need also PurchaseReturn...., but later about it....
The Idea of this report should be:

We got a Purchase Invoice
We got a discount
The rest we payed (PurchaseInvoice - Discount - Payment = 0)

bugt,

I've got what you mean... That's fine so far, cause usually a User shouldn't enter two CashDiscounts for one PurchaseInvoice. My trial client is satisfied.