Sample ODBC/SQL queries for getting Sales Order information, Customer Information, and inventory information?

Question

Can anyone offer any sample SQL scripts for getting information, such as Customer Information (given a customer ID), sales order information (given a sales order number or customer ID), and/or inventory information (given an Item ID) out of the database using an ODBC connection? 
 I've gone through some of the documentation in the Sage 50 help file, but this only really givens a general overview of certain files used in the database, primarily for use with Crystal Reports which I will not be using. 
 I'm needing to write an app for our business that will utilize this information (as I can't find a suitable third-party app for our specific needs at the moment). Specifically, I will be using an ODBC connection to the Sage 50 database, and will be working in C# (Visual C#). I will only be reading information, so I will not be changing or writing information back to the Sage 50 database. 
 
 Thanks

datasoft · Answer

This should be enough to get you going. The following SQL Statement will give you information on a specific Sales Journal with a reference of 12345. You can add more fields and change your where clause for your needs. 
 JrnlHdr.JrnlKey_Journal where 3 = Sales Journal, 11 = Sales Orders 
 
 SELECT Customers.CustomerID, Customers.Customer_Bill_Name, JrnlHdr.TransactionDate, JrnlHdr.Reference, LineItem.ItemID, JrnlRow.RowDescription, JrnlRow.Amount, JrnlRow.Quantity FROM (JrnlHdr INNER JOIN (JrnlRow INNER JOIN LineItem ON JrnlRow.ItemRecordNumber = LineItem.ItemRecordNumber) ON JrnlHdr.PostOrder = JrnlRow.PostOrder) INNER JOIN Customers ON JrnlHdr.CustVendId = Customers.CustomerRecordNumber WHERE (((JrnlHdr.JrnlKey_Journal)=3) AND ((JrnlHdr.Reference)="12345")); 
 
 https://dscorp.com

Sage 50 US

Sample ODBC/SQL queries for getting Sales Order information, Customer Information, and inventory information?

Top Replies

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING