Hi Dave,

The report you are looking at is run from the Customers module.

Therefore, it will base it's information only on the SI that has been raised, which in itself does not link to Products.

I would recommend editing one of our reports from the Invoices and Credits module, as this picks up information from Customers, Products and Invoicing.

You can then apply a criteria for Stock Category and get he report you need.

If you need any help with this, please let me know.

Hi Adam,

Thank you for your help, I'll give this a try and let you know how I get on.

Another issue I've come across -potentially only occurring in reports from Customer module, if so please ignore- is that certain entries are showing as a duplication, it seems to only be an issue with certain customers however doesn't happen with all their invoices.

Is this a common issue?

Regards,

Dave

Adam,

I've managed to filter by Stock Category Number after taking your advice and using the Invoices and Credits Module which is great, thanks! My only problem is I'm struggling to pull out only the invoices that have been paid.

I tried using AUDIT_SPLIT.PAIDFLAG = "Y" which works on the Customers Module but it doesn't seem to like it on the Invoices and Credits Module. Ideally I need to be able to filter by this and then use Payment Date as criteria before running the report.

Tomorrow is my last day before Christmas so if this only gets dealt with afterwards I will pick it up in the New Year.

Have a great Christmas and thanks for your help so far.

Hi Dave,

The Invoice reports will only deal with the Invoice list so unfortunately you cannot filter ones that have had a SR paid against the SI as it won't link to the transaction and payments themselves.

However as a workaround, you could type something like 'Unpaid' into one of the fields on the invoices that are still outstanding?  Perhaps the Notes field on the Order tab?

This way you can set up a filter to exclude these invoices from the list.

Regarding your duplication on the Customer reports, which reports specifically are duplicating?  Also does it duplicate when you preview on screen or does it only occur when you Print/Export etc?

Let me know the above and I'll be happy to help.

Hi Adam,

Happy New Year.

The duplication appears on Invoices and Credits reports too.

It seems to be to do with the number of entries on the actual order.

For instance if the order contains;

3 apples - £1 each
1 pear - £1
1 melon - £1

There will be 3 duplicates all showing the total order value of £6. Do you know of a way around this? I'm guessing this is more of an issue with the actual layout of the report/its expressions as oppose to any filters or joins.

Many thanks

Hi Dave,

If this is occurring on the Invoice/Credit layout itself, and occurs whether you print/preview/export etc, then I would suspect it can be due to multiple delivery addresses on the layout.

If you don't need to use multiple delivery addresses for your Invoices, or if the correct delivery address always shows in the Order tab on the Invoice, then please click here to view our help guide.

Select the Next button at the bottom of the guide > Email Invoices > No and then follow the steps within the guide.

Adam,

I tried to follow your steps but this report doesn't seem to contain any of the relevant expressions/criteria/filters/joins.

I am using a report which features a slight edit on your 'Sales by Analysis Code 1' report.

The only expressions on this layout are SALES_LEDGER.ACCOUNT_REF, INVOICE.ITEMS_NET, INVOICE.INVOICE_DATE and INVOICE.INVOICE_NUMBER.

The only enabled criteria being Invoice Date, Analysis 1 and Invoice Notes.

This is an example of what I see;
Customer REF Company Inv Ref Date Value(£)
A1 A1 Company 14319 03/10/2017 792
A1 A1 Company 14319 03/10/2017 792
A1 A1 Company 15065 01/12/2017 117
B1 B1 Company 14581 23/10/2017 99
B1 B1 Company 14581 23/10/2017 99
B1 B1 Company 14581 23/10/2017 99
B1 B1 Company 14581 23/10/2017 99
C5 C5 Company 14455 12/10/2017 228
C5 C5 Company 14455 12/10/2017 228
C5 C5 Company 14455 12/10/2017 228
C5 C5 Company 14747 06/11/2017 41
C5 C5 Company 14914 20/11/2017 13

Is there not an expression I can enter in the Invoice Ref area that ensures it only generates one line?

Regards, Tom

Hi Tom,

Due to the in depth nature of your query, it might be best if you can call us on 0845 111 55 55 so that we can discuss this directly with you.

I suspect you may be merging different variables together that would cause this duplication, but we would need to look at this with you to confirm!

Adam,

Managed to solve it by removing an unnecessary line in my Filter.

Thanks for all your help. I'm going to proceed using the Notes idea you suggested and see how I get on.

Kind regards,

Dave