Page 1 of 1

Aged Creditors Report - duplicates invoices when using Extra Ref field

Posted: 30 Apr 2020, 15:04
by ByGosh
Hi,

I have been trying to adjust the Detailed Aged Creditors report to show a payment date for each invoice.

When I enter invoices I type in the date I want the invoice to be paid in the Ex. Ref box, usually in the format 31.10.20, and in the case that some invoices are paid by direct debit I would just enter DD. I don't use the Sage payment due system because I have all sorts of payment windows from 14 to 90 days depending upon my payment terms.

I have moved some of the columns in the report closer together and deleted a couple that I never use. In their place I have added a tick box to show they have been paid and I have inserted a column from the AUDIT_SPLIT table with the data field EXTRA_REF. I want to be able to print off this creditors report on a weekly basis so that I know who needs paying off each week.

It works for the majority of invoices, however when I enter a supplier invoice in Batch Invoices that requires splitting into two departments (with or without the same nominal code) the report shows both entries at the the full value. i.e. it is duplicating the total value of the invoice in the report.

I have added a couple of jpg attachments, 1 with and 1 without the EXTRA_REF data field to show what is happening (I have hidden the full business account names for this example).

I am using Sage 50 Accounts v22.1.10.394 with Report Designer v1.4.2015.05. I have not worked extensively with Report Designer so my knowledge is a bit limited, but I can follow good instructions really well!

Can anybody suggest a remedy to stop the duplication?

Re: Aged Creditors Report - duplicates invoices when using Extra Ref field

Posted: 30 Apr 2020, 15:55
by brucedenney
I think you need to do a footer for the audit header and total the split items.

If you attach the layout to a reply, I can be a bit more sure that that is the case and guide you further.

Re: Aged Creditors Report - duplicates invoices when using Extra Ref field

Posted: 30 Apr 2020, 16:10
by ByGosh
Thank you for your reply.

You are probably correct as I have compacted the report as much as possible and deleted some of the fields that I thought I wouldn't need.

As suggested I have attached the report.

Many thanks, John

Re: Aged Creditors Report - duplicates invoices when using Extra Ref field

Posted: 01 May 2020, 09:16
by brucedenney
It is as I suspected.

Imagine you enter an invoice split into 2

On each split you enter a different Extra Ref.

In your report do you want to see 2 lines for the 2 splits each and each extra ref
or
do you want to see one line for the header with the extra ref from the last split?

At the moment you are printing the Header information for each split (due to the extra ref being present) and so seeing a duplication.

Now you can fix this 2 ways, either by making the values shown the values form the splits rather than the header.

Or by creating a footer section for the Audit Header and printing the Audit header data and the last Audit Split value in that footer. You will still need a details section with the audit split information but that can be hidden so you see the footer section as if it were the details.

I think you probably want to see just the last extra ref. Attached you will find a rework of your layout, all I have done is to add a Group Section on Audit Header Transaction number, move the fields in the details section into the footer then hidden the header and the details section.

I hope this explains what the problem was with your layout and you have a better understanding of why you got the results you were not expecting and how to rectify this sort of issue in future.

Re: Aged Creditors Report - duplicates invoices when using Extra Ref field

Posted: 01 May 2020, 15:15
by ByGosh
You have summised correctly and your revision to the report works exactly as I was trying to create.

I have yet to examine your alterations in the designer and how you have achieved the result.

I am very grateful for the work you have done as it has probably saved me a few hours of trying to figure out the problem. It has been quite a few years since I last used Sage 50 but as the part time secretary has gone into a 3 month isolation I have had to take it on again. I couldn't find a report to show which suppliers needed paying on a weekly basis so I thought I could adapt it myself, but I couldn't quite get the correct result.

Many many thanks for your help,

John

Re: Aged Creditors Report - duplicates invoices when using Extra Ref field

Posted: 04 Jun 2020, 15:12
by ByGosh
Although your previous fix seemed to work, it seems to have reverted back to the initial problem.

I have downloaded the report file again just to make sure I still had the one that had been corrected.

All I have done since downloading the new report is to add a couple of months data and carry out a year end without clearing the audit trail.

The problem of a double-value balance only happens when one invoice has two or more entries that need to be placed under two or more different nominal codes.

I have attached a pdf to show what I have entered, together with the result in the report file and the balance showing correctly in the original Sage report.

I have looked at the alterations that were previously discussed below but I cannot see what is happening to be giving the wrong balance. I have checked both the Sage report (SPLAGED.REPORT) that is working correctly and the lastest one that has been edited from the original, and apart from the sections I have deleted there doesn't seem to be anything different, except for the additional Extra_Ref column.

Does anybody have any further suggestions?

Re: Aged Creditors Report - duplicates invoices when using Extra Ref field

Posted: 08 Jun 2020, 10:30
by brucedenney
Were the totals wrong all along?

I suspect the doubled total is adding up the headers rather than the splits.

Re: Aged Creditors Report - duplicates invoices when using Extra Ref field

Posted: 08 Jun 2020, 15:38
by ByGosh
Could both have been wrong before, but one fault was giving the same result as the other?

I assumed that as both splits were showing the full invoice total on each line that the total had added the full invoice twice.
Now I think that although one split is hidden it is adding the full invoice twice rather than adding the split values.

Looking back perhaps it would have been better to list each split and its value seperately so at least it can be visually checked before payment is made to make sure the total value to be paid is correct. I don't want us to start paying some customers double the amount due to them by accident.

The date added to the extra ref will always be the same for all the splits unless there is a typo on one of the lines. 3 or 4 of our suppliers keep a monthly list of all our collections and send them all as one invoice at the end of a month, so we could have up to 4 or 5 splits on one invoice.

I have just printed off what you explained in your last post, and although I can see what you have done I am not confident that I have fully understood why it is happening, after all I have only added a text column (extra_ref) to the report - all the monetary calculations have remained the same, even when splits were in the original Sage report they added up correctly.

Re: Aged Creditors Report - duplicates invoices when using Extra Ref field

Posted: 09 Jun 2020, 10:59
by brucedenney
My sample was only to illustrate how to do it.

You need to alter the total so it calculates correctly, by adding up the split value rather than the header value.

Re: Aged Creditors Report - duplicates invoices when using Extra Ref field

Posted: 09 Jun 2020, 13:01
by ByGosh
All sorted now thank you. I wasn't sure whether to change it to Audit_Split but your confirmation has corrected the problem.

Re: Aged Creditors Report - duplicates invoices when using Extra Ref field

Posted: 09 Jun 2020, 13:10
by brucedenney
Great stuff.