Can't see INVOICE or INVOICE_ITEM through ODBC

SUGGESTED

We are trying to retrieve Invoice line item data through the ODBC connection. We can connect to Sage and retrieve the Customers tables (and other tables) just fine. I do not see INVOICE or INVOICE_ITEM table in the list of tables. Trying to pull into Excel via ODBC from Sage 50 2021. Do I have the right table names, for invoice info? Is there something I have to set up in Sage in order to see the tables through ODBC?

Any help is appreciated. Have no desire to write data, just read.

Thanks

  • 0

    In SAge 50, there is only one ledger - or one journal.

  • 0
    SUGGESTED

    Look for JrnlHdr which would be the Invoice table

    JrnlRow contains the invoice Lines. 

    InventoryCost contains Costs related to inventory transactions.  If you are using Crystal, you can use the GetPeach custom User functions.  

    LineItem contains is the Inventory Master table

  • 0

    Thanks for your help Salim! I've been trying to wrap my head around the tables you pointed to. I cant find the 'customer number' or salesman credited with the sales in either table, but I do see some invoice data. JrnRow seems to contain other records as well as sales transactions data. Do you know which column contains an Identifier as to what type (AP/AR etc) the row is?

    Is there any definitive book, or online resource you can recommend, that can step me through the connections between the various tables?

    Thanks again for the reply - it definitely got me started on the path!

    Ben

  • 0 in reply to Ben Tarver

    I have not found much in the way of documentation from Sage. There are a number of consultants who use the ODBC engine with Crystal Reports and may be able to help you.

  • 0 in reply to Ben Tarver

    The closest thing to documentation that i found was the documentation for PawCom from Mutliware Inc.

    Pawcom is a third party tool that interfaces with Peachtree.  It is able to read from, and write data to, Peachtree, by making calls to a few APIs exposed by Sage.  I have only used it to read.  it gave me some insight into the data.  It can be quite slow.

    Multiware's model of the data in Peachtree is at times quite different from the DDFs from Sage.  That is not to say that it is wrong, just a different perspective.

    Look for

    JrnlHdr.CustVendId - customer or Vendor ID depending on the transaction

    EmpRecordNumber - Employee id - salespersons are a type of employee.

    Bill Bach from GoldStarSoftware was a tremendous resource when I was poking around trying to understand Peachtree's data model so that we could build reports on Peachtree data in Mercury.

    It took a lot of effort to replicate the GetPeachRowAmount function.  We are now able to deliver a GL Trial balance that tallies with Peachtree.  and with that, we can report on any data in Peachtree/Sage 50.  

  • 0 in reply to Ben Tarver

    If you will click on the Help Menu and then select Help Topics, go into the Index tab and type ODBC, then press enter. On the right side of the screen, click on the hyperlink for See a list of the tables of data. This provides you with a list of the DAT tables that information can be extracted from. Clicking on each tables provides you with a list of fields, and each field's characteristics. This list is not all inclusive, but it does give you the majority of the DAT files available to extract data from. As for the two fields that you were specifically asking about, the Customer Number is really the CustomerID found in the CUSTOMER.DAT file, and the Invoice Number is really the Reference found in the JRNLHDR.DAT file. As for the individual line items of the Invoice, once you have linked the JRNLROW.DAT and LINEITEM.DAT tables together using the ItemRecordNumber field, you can then pull the ItemID field to get this information. Please reach out to us for additional assistance, https://pcosupport.com 800-780-0700. We have over 30 years of experience with writing custom reports for Peachtree and Sage 50.