Data Dictionary For Sage Accounts?

I've taken the liberty of cross-posting this item from the IT Zone discussion group to see if any of our new members here can help:

Posted by NigelJustinDay on Sun, 15/11/2009 - 19:06
"I am trying to devise a report that will show all my purchases and include the department and project ref field. I have tried looking at the fields available under the Supplier reports option and I can't find these 2 fields. Can someone please point me in the right direction or do I need to extract this information via a SQL query?"

__________________________
John Stokdyk, Technology editor

Comments

Join function

Alistair Hindmarsh | | Permalink

I assume this is Sage 50 ? In which case it's not possible to do a SQL query as currently Sage 50 is not written in SQL. What you can do, if you have the new report designer (2007 onwards) you have a Join functionality on the menu which allows you to add extra tables. Just be careful in how you join the tables as it can give strange results.

 

Alistair

stevie's picture

Tables

stevie | | Permalink

Most of the data you require is contained in the following table:

AUDIT_JOURNAL
- TRAN_NUMBER
- TYPE
- DATE
- AMOUNT
- DEPT_NAME
- etc

You need to link TRAN_NUMBER to AUDIT_TRAIL_ID in the following table:

PROJECT_TRAN

And also select PROJECT_ID from this table.

You could filter on TYPE in AUDIT_JOURNAL to get just PI documents.

I tested this using an ODBC connection from Excel and it seemed to do the trick!

Grab youself a copy of Microsoft Access

JC | | Permalink

Once you have a copy of Access

  • ensure Sage ODBC is on your PC
  • create a blank database
  • File-->Get External Data-->Link Tables (must be link because you don't want to load contents to Access)
  • select sage ODBC under 'files of type'

This will allow all the Sage tables to appear in Access

Then either have a look at the table structure through Tables or use the WISIWIG facility in Query to generate a SQL query to retrieve the data you want

Much more powerful & simpler than Excel

 

 

Project Reports

sarah douglas | | Permalink

 Hi John 

This report at the moment does not show all purchases but it does show Purchase Invoices to Projects .  I am sure it would probably be easy to adapt.

  • Go to the Projects Module 
  • Reports 
  • Day Book Reports 
  • Purchase Invoices to Projects Detailed or 
  • Purchase Invoices to Projects Summary

Sarah @ Douglas Accountancy and Bookkeeping Services. Glasgow 

 

 

Agree with Stevie - use ODBC and Audit_Journal

David Carter | | Permalink

Agree with Stevie above.   The easiest route is to link Sage to Excel via ODBC and pull in the Audit_Journal table.  If you just want to see Purchases, filter on Type = PI and PC.

Audit_Journal contains Department but not Project or Cost Code.  To get these, join Audit_Journal to Project_Transactions using the fields Tran_Number and Audit_Trail.

 

I posted a series of tutorials on how to import data from the Audit_Journal table of Sage into Excel via ODBC a year or two back.  Here’s the URL to the first one:

http://www.accountingweb.co.uk/item/162950

MSQuery and Sage, Tutorial #1: How to Set up Import External Data

Joined tables - thanks!

kathgibson | | Permalink

I've had a long-standing problem of having recorded project information in Sage, but with limited reporting functionality, being unable to print off any meaningful report to compare our excel-based Production Tracking with the final recorded data in Sage.

Thanks to this information on joining tables in the new report designer, I will now be able to provide our Producers with customised line by line project costs reports and will be the most popular girl in the studio (without the need for chocolate bribery) :o)

Thanks for this info - four years of puzzling, resolved!

 

SQL what tables link payments against invoice items

chrismic | | Permalink

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 !

Add comment
Log in or register to post comments
Group: Sage 50 Accounts discussion group
An independent forum for Sage users to share ideas, tips and gripes