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

Discounts on Sage products & subscriptions. Discounted subscriptions for customer using pay-as-you-go support or who are self-supporting.

Want web hosting, email, your own domain name, a shop, sage integration we can do it, from £12 a year!

Adding Transaction Number to Invoice Template

Sage 50 general help forum - Free help and support for all general issues
Post Reply
philthfc
User
User
Posts: 15
Joined: 18 May 2016, 18:42
Sage Version: Other

Adding Transaction Number to Invoice Template

Post by philthfc » 03 Jul 2018, 09:33

Hi all,

Hoping for some guidance and any guidance would be much appreciated.

I need to add transaction number to a new invoice template as we tend to use this more than the invoice number, but i am struggling to do so. Having found transaction number in a report i can see it is labelled audit_header.tran_number, however i don't seem to be able to pick this from within my invoice template.

I am assuming this is due their being no join to the audit_header table when i click on 'joins', having played about trying to add one using the graphical editor and the raw editor i seem to be struggling.

Is there an easy way of adding this join to let me add the transaction number?

Thanks!

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

Re: Adding Transaction Number to Invoice Template

Post by brucedenney » 03 Jul 2018, 10:31

At the point when the invoice is printed it may not have been updated to the ledger, so as a result it would not be possible to print the audit trail transaction number as the audit trail transaction might not exist.
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

philthfc
User
User
Posts: 15
Joined: 18 May 2016, 18:42
Sage Version: Other

Re: Adding Transaction Number to Invoice Template

Post by philthfc » 03 Jul 2018, 10:40

Thanks for taking the time to respond.

So do you think if you made sure you posted to ledgers first their may be a way of adding transaction number to an invoice?

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

Re: Adding Transaction Number to Invoice Template

Post by brucedenney » 03 Jul 2018, 11:18

You can!

You need to join the Invoice Table to the Audit_Header table

So the INVOICE.INVOICE_NUMBER need to match the AUDIT_HEADER.INV_REF

The issue is that one is text and the other is a number so they are incompatible for joining.

Sage have provided a way around this there is a field called INV_REF_NUMERIC which you can join on.

You also want to make the join a Parent Outer so that should the matching transaction not exist in the Audit Trail it will still return data.

There is still a minor flaw, what if there is another audit trail with that same number, a purchase invoice for example?

You could make the join more complicated, being both the account reference and the invoice reference, but this still has flaws a supplier account with the same account reference and same invoice reference would match. Also a sales ledger credit note on the same account with the same ref would match.

So I added a filter to limit it to SI transactions (or blank transactions should the invoice not have been posted to the ledger when printed)

I attach a sample of the above.
INVPLAINA4TranNo.LAYOUT
Have a look at each part and implement it in your own layout.

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

philthfc
User
User
Posts: 15
Joined: 18 May 2016, 18:42
Sage Version: Other

Re: Adding Transaction Number to Invoice Template

Post by philthfc » 04 Jul 2018, 14:43

Thanks fr this - i will give it a go!

Post Reply

Who is online

Users browsing this forum: No registered users and 1 guest