Page 1 of 1

Adding Transaction Number to Invoice Template

Posted: 03 Jul 2018, 09:33
by philthfc
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!

Re: Adding Transaction Number to Invoice Template

Posted: 03 Jul 2018, 10:31
by brucedenney
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.

Re: Adding Transaction Number to Invoice Template

Posted: 03 Jul 2018, 10:40
by philthfc
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?

Re: Adding Transaction Number to Invoice Template

Posted: 03 Jul 2018, 11:18
by brucedenney
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.

Re: Adding Transaction Number to Invoice Template

Posted: 04 Jul 2018, 14:43
by philthfc
Thanks fr this - i will give it a go!