Skip to Content
0
Former Member
Jul 15, 2008 at 10:49 AM

Problem with linking

26 Views

I'm working on a monthly sales analysis report that contains invoiced sales and any related cost adjustments. I also need to add any credit note value's to ensure each customer total figure is accurate. I added the Credit Note Header table to the report and created a formula to pull out any related credit notes, based on an invoice date range parameter (monthly). The idea was to check each invoice, and if a credit note was linked to it then extract the credit note number and total amount. This would then be taken away from the customer's total monthly invoice value.

I set the formula to pull out the credit note number as follows:

If ({CreditNoteHeader.DateTimeReleased} = {?Invoice Date})

and {Customer.CustomerID} = {CreditNoteHeader.CustomerID}

and NOT IsNULL ({CreditNoteHeader.CreditNoteNumber})

then totext ({CreditNoteHeader.CreditNoteNumber},0)

else totext ("N/A")

If I link:

InvoiceHeader.InvoiceID - CreditNoteHeader.InvoiceID (Left Outer), I only get part of the data returned. For example, there should be 12 credit notes for June 2008 but only 2 are returned.

If I link:

Customer.CustomerID - CreditNoteHeader.CustomerID (Left Outer), all 12 credit notes are returned, but I get duplicates based on TOTAL number of credit notes per customer. If a customer has 3 total credit notes, 1 June invoice and 0 credit for June, I see 3 records on the report. I assume this is due to the fact I'm linking from the Customer table.

Can anyone advise how I can change my linking and / or amend the formula to resolve the non-returned data / duplicates? I did try other links and played around with the formula with no improvement. I also tried throwing the Credit Note Header into a subreport, but that also failed to fix the problem.

Thanks for any help.