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!

Remittances Extra Ref-Batch Invoice(showing) vs Update Ledger(not showing)

Report design issues and solutions.
Post Reply
pauljj
User
User
Posts: 48
Joined: 11 Sep 2014, 11:38
Sage Version: v2018 24 UK/Europe/Africa

Remittances Extra Ref-Batch Invoice(showing) vs Update Ledger(not showing)

Post by pauljj » 11 Jul 2018, 12:58

Hi Bruce,

Since working with the Extra Ref field we gave our remittance templates a makeover to suit our visual needs. Nothing changed in the joins etc.
Part of this work was to bring the Extra Ref field in from the Remittances table, so we could see our purchase order number and the suppliers invoice number on the report.
This works fine for the invoices (it appears) that are processed through the Batch Invoice function, but it seems that the Extra Ref is missing when we use the Update Ledger method. I do not work the accounts, so i'm a little unfamiliar with the two different methods.

I had a quick look at the joins and "just for fun" i added the audit split table in and joined the account ref field on inner joins to the Purchase Ledger table, and ran it - so of course it returned a lot of duplicated lines, but as a test, in that run, it worked, the Inv Ref and the Extra Ref appeared at some point together as the layout would want.

So, the question is, is this the right approach? to add in the Audit Split table too? have it on it's own and remove the remittance table ?
If i add it in, of course i would need to work out how to stop the duplication etc.

Just wondered if this has been asked before ?
thanks
Paul

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

Re: Remittances Extra Ref-Batch Invoice(showing) vs Update Ledger(not showing)

Post by brucedenney » 11 Jul 2018, 13:33

I am not sure what report you are doing but...

The remittance table is a special table that only exists for doing remittance advices.

I am guessing that you want to do a report that currently runs on the AUDIT_HEADER table but you want to include data from the AUDIT_SPLIT table (for the Extra Ref Field)

The issue here is that each split on the Header could have a different Extra Ref (or you might always have the same value on each split)

If you have different values then you need to alter the report to have a line for each split and show each different "Extra Ref"

If they are the same value then you can do the same thing, do the report at the split level, and have a footer for each header, in that footer you can add up the value of all the splits and include the Extra Ref (off of the last split), you can then hide the details section so all you see in the report is the one footer line for each AUDIT_HEADER record.

I hope this helps.
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 0 guests