Need more, need help now?
- - - - - - - - - - - - - - - - - - pay-as-you-go support - no contract - tenth of an hour billing - expert help - fast service - no call queues
Need integration?
- - - - - - - - - - - - - - - - - - with your shipping system - website - invoicing system - crm - cms - manufacturing - order import - back to back orders..
Need a report?
- - - - - - - - - - - - - - - - - - Excel reporting that pulls data from Sage - custom layouts - layouts that change adapt to your brands and/or for drop shipping.
Want web hosting?
- - - - - - - - - - - - - - - - - - Your own domain name - email - a shop - wordpress - woo commerce - ticket systems - help desks - forums - portals

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: 4619
Joined: 28 Feb 2006, 09:56
Sage Version: v28 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 :- Discount subscriptions, pay-as-you-go support, application integration, reports, layouts, linked excel spreadsheets, analysis or any other help making life with sage easier/less time consuming Contact me.

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: 4619
Joined: 28 Feb 2006, 09:56
Sage Version: v28 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.
Login to access the files attached to this post.
For just about anything Sage :- Discount subscriptions, pay-as-you-go support, application integration, reports, layouts, linked excel spreadsheets, analysis or any other help making life with sage easier/less time consuming Contact me.

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: Ahrefs [Bot], Google [Bot] and 22 guests