Sage 50 SQL Payment matching to an Invoice or Invoice Item

I want to extract client payments and link these to lines in the invoice_item table to show date paid and amount paid. What fields tables can I use to link these

 

I have audit_header, audit_usage, audit_split, invoice , invoice_item extracted from sage50 into a sql database - any ideas !
Chris MichalczukConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nick67Commented:
Don't waste your effort.  Sage makes no attempt to keep your efforts from being wasted when they push through their updates.  The tables are a swamp.  There's no relationships defined at the db level and documentation is non existent.

If you want to do this, you must convince your outfit to switch to an accounting package that is supportive of third-party extensibility.  Sage 50 ain't it.
0
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
This is not really a SQL Server question but rather a SAGE question.  I suggest you extract a list of tables and send it to SAGE support to answer your question.  To extract the list of the tables, use the sys.objects view.  Hope this helps.
0
Bruce DenneySage 50 Consultant and IntegratorCommented:
Here is the deal...

The invoice Item table is not linked to the Audit Split table so you can't do what you are trying.

But perhaps you do not want to do what you have said.

The Audit Split is the definitive financial record, so perhaps you do not need to do that extra link to the invoice item table, you could just do it at the audit split level.

You can link from the audit header to the invoice but not the items.

Conceptually you could match the description on the audit splits and match them to the invoice items, but really it is a lot of work for nothing.  The splits are the core transactions, you can have additional things in splits that matchoff but are not on invoices at all, eg interest charges, late payments fees, write offs.  So even at an accounting level matching to invoice items is not logical.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Advice: do not try to retro engineer SAGE.  These are among the most poorly designed databases I have seen.
0
Nick67Commented:
"I have audit_header, audit_usage, audit_split, invoice , invoice_item extracted from sage50 into a sql database"
So you've stuck a fork into the MySQL monstrosity that is the Sage 50 engine these days.  When I was poking that bear it was still a renamed Access MDB file, but accessing the data isn't stupidly difficult.  But you've seen that the most table names are still 8 characters long, and you've seen that there are no relationships defined at the db level between the tables.  I phoned Sage about obtaining documentation, the short answer was they wanted about $11000 and for me to put a Sage partner on retainer.  And none of that guaranteed anything.  If their twice-yearly updates broke my stuff, tough-titty said the kitty.

Sage has facilities to export to Excel.  That's about the only useful facility that you're going to get.  Upgrade to ACCPAC, and hire a partner, or migrate to something else like Peachtree or Great Plains.

The only thing enterprise about Sage 50 Quantum Enterprise -- which is what our accountants use -- is the Enterprise name.  Let's not mention EFT.
0
Bruce DenneySage 50 Consultant and IntegratorCommented:
Which sage 50 are you talking about?  Is the the Rest of the world version-UK/CAN or US?
0
Chris MichalczukConsultantAuthor Commented:
I've got the data extracted but don't know the joins correctly to link a payment against an invoice_item line
0
Bruce DenneySage 50 Consultant and IntegratorCommented:
The links are from Audit_Split to Audit Usage to Audit Split

you link AUDIT_SPLIT.TRAN_NUMBER to AUDIT_USAGE.SPLIT_NUMBER and AUDIT_USAGE.SPLIT_CROSSREF TO AUDIT_SPLIT.TRAN_NUMBER
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.