Skip to Content

How to get a structured list of sales orders using SAP B1 DI .NET API?

I guess it begins with:

SAPbobsCOM.Recordset rs = company.GetBusinessObject(BoObjectTypes.BoRecordset);
rs.DoQuery("SELECT TOP 5 DocEntry, DocNum, DocDate, TaxDate, CardCode, CardName, DocTotal FROM ORDR ORDER BY DocDate DESC");

How to write this code fully to get a structured object (JSON like) of the filtered orders (by date, or last 5 etc) with line items, prices, total, customer name, card code? [VB.NET or c#]

Thank you.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Nov 01, 2017 at 04:10 PM

    Hi Waseem,

    If you want provide full informations about those orders, you can do something like this:

    try
    {
        XDocument bom = null;
        //Setup your company to work with xml as string
        oCompany.XMLAsString = true;
        oCompany.XmlExportType = BoXmlExportTypes.xet_ExportImportMode;
    
    
        //Open a recordset to perform a query on database
        Recordset oRec = oCompany.GetBusinessObject(BoObjectTypes.BoRecordset);
        Documents oOrder = oCompany.GetBusinessObject(BoObjectTypes.oOrders);
    
    
        //Do your query
        string sql = "SELECT TOP 5 DocEntry, DocDate FROM ORDR ORDER BY DocDate DESC";
        oRec.DoQuery(sql);
    
    
        //Load the results to an order object
        oOrder.Browser.Recordset = oRec;
    
    
        //Walk through the results, in this situation sap will fill all properties of the orders object
        while(!oOrder.Browser.EoF)
        {
            //Converts your object into xml
            XDocument tempDoc = XDocument.Parse(oOrder.GetAsXML());
            if (bom == null)
                bom = tempDoc;
            else
            {
                XElement tempBO = tempDoc.Element("BOM").Element("BO");
                if (tempBO != null)
                    bom.Element("BOM").Add(tempBO);
            }
            //Go to the next order
            oOrder.Browser.MoveNext();
        }
    
        //Now, convert your xml in a Json with Newtonsoft.Json dll
        JObject jObj = JObject.Parse(JsonConvert.SerializeXNode(bom));
        Console.WriteLine(jObj.ToString());
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
    

    If you want just the info that you showed in your sql above, create you object with that properties, fill it with the results of your recordset, and then using Newtonsoft.Json dll to convert your list of your object to a JSON.

    Hope it helps.

    Kind Regards,

    Diego Lother

    Add comment
    10|10000 characters needed characters exceeded