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