I've asked a similar question earlier regarding the database layout/design and got a piece of code from someone (which I will paste below). This piece of code gets the Shipping info, the order number, and other info. What I'm looking for is how to get the customer PO number and a few other fields. I'm hoping that someone maybe has an idea of how to get this info from Sage 50 maybe using the code I've listed below for getting the Shipping info. I am accessing the database via ODBC/DSN connection.
(Ignore the quotation marks.. I copied it directly from my C# program)
"SELECT C.CustomerId, E.EmployeeId, J.TransactionDate, J.SalesTaxCode, "
"J.TermsDescription, J.ShipToName, J.ShipToAddress1, J.ShipToAddress2, "
"J.ShipToCity, J.ShipToState, J.ShipToZip, J.ShipToCountry, J.ShipVia, J.PrintCommentAtEnd, "
"J.Comment, J.Comment2, J.Comment3, J.ShipByDate, J.CustomerInvoiceNo, G.AccountID, J.reference "
"FROM JrnlHdr J "
"LEFT OUTER JOIN Customers C ON J.CustVendId = C.CustomerRecordNumber "
"LEFT OUTER JOIN Employee E ON J.EmpRecordNumber = E.EmpRecordNumber "
"LEFT OUTER JOIN Chart G ON J.GLAcntNumber = G.GLAcntNumber WHERE J.reference = '100000' AND J.JrnlKey_Journal = 11 ";
This SQL segment will basically get information about shipping for a particular order (in this case, 100000). I need to get the PO number.
Ideas or suggestions, and if anyone has any information on how to tables are setup (attribute nates, etc), that would be appreciated to?
