Hello,
Been a while since I posted anything, and I think I may have posted this example before, but another question on GetResultSets just reminded me of it, and I thought someone might find it a useful reference. It is basically a VB .NET console app that does a series of GetResultSets, each more complex in regards to the data they return, until finally, the last one, actually returns data from a numeric column.
It is correct in you can only return string values, but you can wrap numerics in the ProvideX STR() function. See yellow highlight below.
Basically, GetResultSets can return any valid string expression, in the context that it is called in of course.
Sorry about the format and line wrapping.
Thanks
Bret Richion,
Sage Software Engineer
Module AdvGetResultSets
' * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
' Example: Advanced usage of the SY_Service.GetResultSets() method *
' *
' Bret Richion, Sage Software, Inc. *
' *
' ***************************************************************************
' This test returns a list of all Orders with a order status New or Open and
' type is Standard or Backorder, for customer 01-ABF with the same ShipToName
' and ShipToZipCode. It then uses that list to return a record set for all
' Inventory Item Costing records for all the Lot/Serial items on those orders
' * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
Dim SOTAMAS90_DSN As Object
Dim PVXCOM_InitPath As String
Dim oPVX As Object
Dim oSS As Object
' Common ProvideX System Variables
Dim NUL As Char = Chr(0) ' HEX 00
Dim QUO As Char = Chr(34) ' HEX 22
Dim SEP As Char = Chr(138) ' HEX 8A
Dim HexFE As Char = Chr(254) ' HEX FE
Sub Main()
' Set MAS90/Home path that will be used to bind Pvxwin32.DLL to pvxcom.exe
PVXCOM_InitPath = "C:\Sage\Sage 100 Premium ERP\MAS90\Home"
' Create and initialize PVXCOM
oPVX = CreateObject("ProvideX.Script")
oPVX.Init(PVXCOM_InitPath)
' Create and initialize Session Object
oSS = oPVX.NewObject("SY_Session")
oSS.nSetUser("bret", "")
oSS.nSetCompany("ABC")
oSS.nSetDate("S/O", "20100531")
oSS.nSetModule("S/O")
' Set security and create the business objects
oSS.nSetProgram(oSS.nLookupTask("SO_SalesOrder_ui"))
Dim oOrders As Object = oPVX.NewObject("SO_SalesOrder_bus", oSS)
Dim oItems As Object = oPVX.NewObject("IM_ItemCost_svc", oSS)
' Assume these values
Dim ShipName As String = "American Business Futures"
Dim ZipCode As String = "53120"
Dim DivNo As String = "01"
Dim CustNo As String = "ABF"
' Use dictionary object to get column lengths for correct key padding
Dim CustNoLen As Integer = oSS.oFileObj.nGetColumnLength("AR_Customer", "CustomerNo$")
Dim CustNoPadded As String = CustNo & StrDup(CustNoLen - Len(CustNo), NUL)
' WhereClause used against SO_SalesOrderHeader.M4T
Dim OrdersWhere As String = _
"(OrderStatus$=" + QUO + "N" + QUO + " OR " + _
"OrderStatus$=" + QUO + "O" + QUO + ") AND " + _
"(OrderType$=" + QUO + "S" + QUO + " OR " + _
"OrderType$=" + QUO + "B" + QUO + ") AND " + _
"ShipToName$=" + QUO + ShipName + QUO + " AND " + _
"ShipToZipCode$=" + QUO + ZipCode + QUO
Dim arrayORDERS() As String
Dim retORDERS As String = ""
Dim retJUNK As String = ""
'Pvx:SELECT SalesOrderNo$ from cFH BEGIN "01ABF" END PAD("01ABF",7,$00$) WHERE OrdersWhere$
oOrders.nGetResultSets("SalesOrderNo$", "Nothing$", retORDERS, retJUNK, OrdersWhere, DivNo + CustNo, CustNoPadded + HexFE, "kCUSTOMER")
' MID used to strip off leading space+SEP and trailing SEP!!
arrayORDERS = Split(Mid(retORDERS, 3, Len(retORDERS) - 3), SEP)
Dim LinesWhere As String = "POS(SalesOrderNo$=" + QUO
Dim OrderNoLen As Integer = oSS.oFileObj.nGetColumnLength("SO_SalesOrderDetail", "SalesOrderNo$")
Dim lowOrder As String = StrDup(OrderNoLen, HexFE)
Dim hiOrder As String = ""
' Get BEGIN / END and Order No's for WhereClause
For Each Order As String In arrayORDERS
If Order < lowOrder Then lowOrder = Order
If Order > hiOrder Then hiOrder = Order
LinesWhere = LinesWhere + Order
Next Order
' Add ItemType$="1" to WhereClause
LinesWhere = LinesWhere + QUO + ") AND ItemType$=" + QUO + "1" + QUO ' only get inventory items
Dim retITEMS As String = ""
Dim arrayITEMS() As String
Dim retWHSE As String = ""
Dim arrayWHSE() As String
' Select against SO_SalesOrderDetail.M4T
' SELECT ItemCode$,WareHouseCode$ from cFH BEGIN lowOrder END hiOrder WHERE EVN(LinesWhere)
oOrders.oLines.nGetResultSets("ItemCode$", "WarehouseCode$", retITEMS, retWHSE, LinesWhere, lowOrder, hiOrder + HexFE, "", 1)
arrayITEMS = Split(Mid(retITEMS, 3, Len(retITEMS) - 3), SEP)
arrayWHSE = Split(Mid(retWHSE, 3, Len(retWHSE) - 3), SEP)
Dim ItemsWhere As String = "POS(ItemCode$+" + QUO + "~" + QUO + "+WarehouseCode$=" + QUO
Dim ItemCodeLen As Integer = oSS.oFileObj.nGetColumnLength("IM_ItemCost", "ItemCode$")
Dim WhseCodeLen As Integer = oSS.oFileObj.nGetColumnLength("IM_ItemCost", "WarehouseCode$")
Dim lowItem As String = StrDup(ItemCodeLen + WhseCodeLen, HexFE)
Dim hiItem As String = ""
Dim i As Integer = 0
Dim Whse As String = ""
Dim ItemWhsePadded As String
' Get BEGIN / END and ItemCode+WareHouse for WhereClause against IM_ItemCost
For Each ItemCode As String In arrayITEMS
Whse = arrayWHSE(i)
ItemWhsePadded = ItemCode & StrDup(ItemCodeLen - Len(ItemCode), NUL) & _
Whse & StrDup(WhseCodeLen - Len(Whse), NUL)
If ItemWhsePadded < lowItem Then lowItem = ItemWhsePadded
If ItemWhsePadded > hiItem Then hiItem = ItemWhsePadded
ItemsWhere = ItemsWhere + ItemCode + "~" + Whse + SEP
Next ItemCode
' WhereClause used against IM_ItemCost
' PVX:WHERE EVN(POS(ItemCode$+WareHouseCode$=ItemsWhere$) AND (TierType$="3") OR TierType$="4"))
ItemsWhere = ItemsWhere + QUO + ") AND " + _
"(TierType$=" + QUO + "3" + QUO + " OR TierType$=" + QUO + "4" + QUO + ")" 'only lot/serial items
Dim arrayItemCostRecs() As String
Dim ItemCostRecs As String = ""
Dim ItemCostRecIOL As String = "ItemCode$+" + QUO + "," + QUO + _
"+WarehouseCode$+" + QUO + "," + QUO + _
"+TierType$+" + QUO + "," + QUO + _
"+GroupSort$+" + QUO + "," + QUO + _
"+LotSerialNo$+" + QUO + "," + QUO + _
"+ReceiptNo$+" + QUO + "," + QUO + _
"+STR(QuantityOnHand-QuantityCommitted)"
' This call returns a list of "," delimited columns (ItemCostRecIOL) separated by
' HEX 8A as the record separator
oItems.nGetResultSets(ItemCostRecIOL, "ItemCode$", ItemCostRecs, retJUNK, ItemsWhere, lowItem, hiItem + HexFE)
arrayItemCostRecs = Split(Mid(ItemCostRecs, 3, Len(ItemCostRecs) - 3), SEP)
For Each ItemCostRec As String In arrayItemCostRecs
Console.WriteLine(ItemCostRec)
Next
' Drop Objects and clean up
oOrders.DropObject()
oItems.DropObject()
oSS.nCleanUp()
oSS.DropObject()
Console.WriteLine("End AdvGetResultSets")
End Sub
End Module