Get PurchaseOrder and Lineitems

Question

How to get PurchaseOrderNo using PurchaseOrder Details & LineItems data from Sage 100 desktop application using C#? 
 I have using DispatchObject and done login and select company functionality. 
 Can you please help me to get PO detail using C#?

David Speck · Accepted Answer

You may want to try searching for examples on here. Most scripting for sage 100 is done in VBScript but there are a few examples for other languages. 
 I found this post which should give you the bulk of what needs done. 
 https://www.sagecity.com/support_communities/sage100_erp/f/sage-100-business-object-interface/90544/c-po-write-fails-boi/297653#297653 
 Since you want the line item data, you can't use the PO_PurchaseOrder_Svc class, instead, you will have to use PO_PurchaseOrder_Bus so you can access the oLines property which is the object handle to the PO_PurchaseOrderDetail_Bus class which will allow you to loop through the lines in a similar fashion to looping through an ADO recordset. 
 When you use nSetKey on the header ( PO_PurchaseOrder_Bus), it should filter the detail (PO_PurchaseOrderDetail_Bus) to only loop through the respective purchase order line's vs every line in the table. 
 Alternatively, you can query the data via ODBC and loop through the resulting recordset. You would query data either individually from PO_PurchaseOrderHeader and PO_PurchaseOrderDetail or if you are going to join the tables into one query, use an INNER JOIN on the PurchaseOrderNo field.

Kevin M · Answer

This is a VBScript example, but I assume you can translate the concept to C#. (SOTAMAS90 is a user DSN created when logging in to Sage 100... the user / password / company code are set higher in the script, as are the Div / Vendor values). 
 None of this is specific to Sage 100, except for the connection string is the syntax for the Providex ODBC driver, so you should be able to Google to find C# examples of ODBC queries. 
 strConn = "DSN=SOTAMAS90;UID=" & sSageUser & ";PWD=" & sSagePassword & ";company=" & sSageCompanyCode 
 Set DBConn = CreateObject("ADODB.Connection") DBConn.open strConn sSelect = "SELECT CheckEntryNo FROM AP_CheckHeader WHERE APDivisionNo= '" & sAPDiv & "' " & "AND VendorNo = '" & sVendor & "' " Set ReturnSet = CreateObject("ADODB.RecordSet") ReturnSet.Open sSelect, DBConn do until ReturnSet.EoF ... ReturnSet.MoveNext loop ReturnSet.Close DBConn.Close

Kevin M · Answer

No $ for numeric fields. Use QuantityOrdered not QuantityOrdered$.

David Speck · Answer

Also, since i don't know C# syntax, i don't know for sure but i don't recognize your use of of the GetDataObject method on the sage BOI objects. 
 If I recall seeing other C# examples, they combine the C# InvokeMethod with the BOI object's GetValue method, the latter accepts two arguments, the first being the field name (add the "$" suffix for string fields only) and the variable you want to receive the value in. 
 It would probably help to search the forum for more C# examples for other purposes and objects besides purchase orders so you can see a broader range of typical use.

David Speck · Answer

The history object are sometimes a little different. 
 You should refer to the online file layouts and object reference. 
 https://help-sage100.na.sage.com/2019/FLOR/index.htm 
 Here is a link for AR_InvoiceHistoryInquiry_bus specifically. 
 https://help-sage100.na.sage.com/2019/FLOR/index.htm#Object_Reference/AR/AR_InvoiceHistoryInquiry_bus.html%3FTocPath%3DObject%2520Reference%7CAccounts%2520Receivable%7C_____134 
 If you look at the first few properties, you'll see one called "Detail_Object", this should point to the AR_InvoiceHistoryInquiryDetail_bus object. Since you are accessing the BOI externally, you will need to prefix the property, since it is an object handle, you should reference it by using "oDetail_Object". 
 If you are unsure of what methods or properties are available for any given object, locate it using the navigation on the left and then search the page for the method or property you are trying to use. If you don't get a result, try scrolling through everything listed and see if something stands out. 
 Keep in mind some basic properties and methods are inherited by other classes, these inherited classes are listed at the top of the page. So a method like SetKey is inherited from SY_Maint, methods like MoveNext and Find are inherited from SY_Service (SY_Maint inherits SY_Service by the way).

rajeshkannanait · Answer

I tried the ' AR_InvoiceHistoryInquiryDetail_bus' object. It is working for me. Thanks, David!!!

Kevin M · Answer

Do you need to write back to Sage 100? If not, ODBC may be easier.

Sage 100

Get PurchaseOrder and Lineitems

Top Replies

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING