SageForum for UK versions of Sage 50- Help and support.

Hey Guys,

Just a quick question really regarding the database "structure"

I'm linking SAGE to Access in order to develop systems and automate a lot of currently manual procedures - Im using V22 of SAGE.

I'm struggling to get my head around the relationships in the SAGE database - particularly the Audit hierarchy.

So currently i've got:

AUDIT_HEADER.TRAN_NUMBER Joined to AUDIT_SPLIT.HEADER_NUMBER
AUDIT_HEADER.TRAN_NUMBER Joined to AUDIT_JOURNAL.TRAN_NUMBER

AUDIT_SPLIT.TRAN_NUMBER Joined to AUDIT_USAGE.SPLIT_NUMBER

AUDIT_USAGE.SPLIT_NUMBER Joined to AUDIT_JOURNAL.SPLIT_NUMBER

Ideally, I want the payments linked to their invoices and I believe that at some point I need to join on AUDIT_USAGE.SPLIT_CROSSREF at some point, but i'm not sure how?

Any ideas? Or is there a "map" somewhere that details this information?

There is no real direct link between invoices and the Audit trail.

INV_REF is the link, but only on Sales Invoices and Credit notes. And only at the header level.

There is no reliable way to link the splits in the audit trail to the items in the invoice.

The structure is a Header to one or more Splits which has one or more usages, these also cross link to another split and back to another header.

e.g. Invoice Header> Invoice Items > usages < Payment Split < Payment Header