Need more, need help now? we offer pay-as-you-go support, no contract, tenth of an hour billing, expert help, fast service, no call queue
We can offer lower cost Sage50cloud subscriptions for customer using pay-as-you-go support or who are self-supporting.
Need integration, your shipping system, your website, your invoicing system, your crm.
Need a bespoke solution, Mini Manufacturing, Spreadsheet order import, Back to back orders.
Want web hosting, email, your own domain name, a shop, we do it all!

Aged Creditors Report - duplicates invoices when using Extra Ref field

Report design issues and solutions.
Post Reply
ByGosh
User
User
Posts: 7
Joined: 30 Apr 2020, 14:20
Sage Version: v2016 22 UK/Europe/Africa

Aged Creditors Report - duplicates invoices when using Extra Ref field

Post by ByGosh » 30 Apr 2020, 15:04

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?

User avatar
brucedenney
Site Admin
Site Admin
Posts: 4331
Joined: 28 Feb 2006, 09:56
Sage Version: v2017 23 UK/Europe/Africa

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

Post by brucedenney » 30 Apr 2020, 15:55

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.
For just about anything Sage :- switch to subscription, pay-as-you-go support, sagecover, upgrades, application integration, reports, layouts, analysis or any other help making life with sage easier/less time consuming Contact me. Image

ByGosh
User
User
Posts: 7
Joined: 30 Apr 2020, 14:20
Sage Version: v2016 22 UK/Europe/Africa

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

Post by ByGosh » 30 Apr 2020, 16:10

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
You do not have the required permissions to view the files attached to this post.

User avatar
brucedenney
Site Admin
Site Admin
Posts: 4331
Joined: 28 Feb 2006, 09:56
Sage Version: v2017 23 UK/Europe/Africa

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

Post by brucedenney » 01 May 2020, 09:16

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.
You do not have the required permissions to view the files attached to this post.
For just about anything Sage :- switch to subscription, pay-as-you-go support, sagecover, upgrades, application integration, reports, layouts, analysis or any other help making life with sage easier/less time consuming Contact me. Image

ByGosh
User
User
Posts: 7
Joined: 30 Apr 2020, 14:20
Sage Version: v2016 22 UK/Europe/Africa

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

Post by ByGosh » 01 May 2020, 15:15

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

ByGosh
User
User
Posts: 7
Joined: 30 Apr 2020, 14:20
Sage Version: v2016 22 UK/Europe/Africa

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

Post by ByGosh » 04 Jun 2020, 15:12

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?
You do not have the required permissions to view the files attached to this post.

User avatar
brucedenney
Site Admin
Site Admin
Posts: 4331
Joined: 28 Feb 2006, 09:56
Sage Version: v2017 23 UK/Europe/Africa

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

Post by brucedenney » 08 Jun 2020, 10:30

Were the totals wrong all along?

I suspect the doubled total is adding up the headers rather than the splits.
For just about anything Sage :- switch to subscription, pay-as-you-go support, sagecover, upgrades, application integration, reports, layouts, analysis or any other help making life with sage easier/less time consuming Contact me. Image

ByGosh
User
User
Posts: 7
Joined: 30 Apr 2020, 14:20
Sage Version: v2016 22 UK/Europe/Africa

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

Post by ByGosh » 08 Jun 2020, 15:38

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.

User avatar
brucedenney
Site Admin
Site Admin
Posts: 4331
Joined: 28 Feb 2006, 09:56
Sage Version: v2017 23 UK/Europe/Africa

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

Post by brucedenney » 09 Jun 2020, 10:59

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.
For just about anything Sage :- switch to subscription, pay-as-you-go support, sagecover, upgrades, application integration, reports, layouts, analysis or any other help making life with sage easier/less time consuming Contact me. Image

ByGosh
User
User
Posts: 7
Joined: 30 Apr 2020, 14:20
Sage Version: v2016 22 UK/Europe/Africa

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

Post by ByGosh » 09 Jun 2020, 13:01

All sorted now thank you. I wasn't sure whether to change it to Audit_Split but your confirmation has corrected the problem.

User avatar
brucedenney
Site Admin
Site Admin
Posts: 4331
Joined: 28 Feb 2006, 09:56
Sage Version: v2017 23 UK/Europe/Africa

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

Post by brucedenney » 09 Jun 2020, 13:10

Great stuff.
For just about anything Sage :- switch to subscription, pay-as-you-go support, sagecover, upgrades, application integration, reports, layouts, analysis or any other help making life with sage easier/less time consuming Contact me. Image

Post Reply

Who is online

Users browsing this forum: No registered users and 1 guest