jsonxmlrestsage-erpsdata

Sage 50 SData XML to JSON POST Request


My end goal is to push orders from a csv into Sage 50 using SData and Python Requests.

I found a sippet of XML body schema (see below) to construct and push a sales order into Sage 50 using SData. I have successfully been able to push a sales order using this snippet through using Python requests POST.

I have read in the Documentation also this link that you can interact with SData using JSON which would be much more preferable within Python by adding ?format=json to the end of the URL. I have been able to use GET requests and make it return a JSON response, however, I am unsure how to change the body of my POST sales order request from XML to JSON.

I have made the GET request http://xxxxxx/sdata/accounts50/GCRM/{xxxxxx}/salesOrders?include=SalesOrderLines&format=json

This returned a structure in JSON for a sales order with the order lines included so I tried to use this to create my JSON POST request however it seems to error out every time I make the request.

Any advice would be great!

Working XML body of request:

<?xml version="1.0" encoding="utf-8"?>
<entry xmlns:atom="http://www.w3.org/2005/Atom" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:cf="http://www.microsoft.com/schemas/rss/core/2005" xmlns="http://www.w3.org/2005/Atom" xmlns:http="http://schemas.sage.com/sdata/http/2008/1" xmlns:sdatasync="http://schemas.sage.com/sdata/sync/2008/1" xmlns:opensearch="http://a9.com/-/spec/opensearch/1.1/" xmlns:sme="http://schemas.sage.com/sdata/sme/2007" xmlns:sdata="http://schemas.sage.com/sdata/2008/1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <author />
    <sdata:payload>
        <salesOrder xmlns="http://schemas.sage.com/crmErp/2008">
            <active xsi:nil="true" />
            <reference xsi:nil="true" />
            <reference2 xsi:nil="true" />
            <status xsi:nil="true" />
            <allocationStatus xsi:nil="true" />
            <deliveryStatus xsi:nil="true" />
            <invoiceStatus xsi:nil="true" />
            <statusFlag xsi:nil="true" />
            <statusFlagText xsi:nil="true" />
            <tradingAccount sdata:url="http://xxxxx/sdata/accounts50/GCRM/{xxxxxxxxxxx}/tradingAccountCustomer(xxxxxxx)?format=atomentry" sdata:uuid="xxxxxxxxxx">
      </tradingAccount>
            <customerReference xsi:nil="true" />
            <opportunity xsi:nil="true" />
            <pricelist xsi:nil="true" />
            <supplierTradingAccount xsi:nil="true" />
            <supplierReference xsi:nil="true" />
            <supplierPurchaseOrders xsi:nil="true" />
            <type xsi:nil="true" />
            <copyFlag xsi:nil="true" />
            <originatorDocument xsi:nil="true" />
            <quotation xsi:nil="true" />
            <postalAddresses xsi:nil="true" />
            <deliveryMethod xsi:nil="true" />
            <deliveryRule xsi:nil="true" />
            <deliveryTerms xsi:nil="true" />
            <deliveryDate xsi:nil="true" />
            <dueDate xsi:nil="true" />
            <carrierCompany xsi:nil="true" />
            <carrierTradingAccount xsi:nil="true" />
            <carrierNetPrice xsi:nil="true" />
            <carrierTaxPrice xsi:nil="true" />
            <carrierTotalPrice xsi:nil="true" />
            <carrierSalesInvoice xsi:nil="true" />
            <carrierTaxCodes xsi:nil="true" />
            <carrierReference xsi:nil="true" />
            <currency xsi:nil="true" />
            <operatingCompanyCurrency xsi:nil="true" />
            <operatingCompanyCurrencyExchangeRate xsi:nil="true" />
            <operatingCompanyCurrencyExchangeRateOperator xsi:nil="true" />
            <operatingCompanyCurrencyExchangeRateDate xsi:nil="true" />
            <invoiceCurrency xsi:nil="true" />
            <invoiceCurrencyExchangeRate xsi:nil="true" />
            <invoiceCurrencyExchangeRateOperator xsi:nil="true" />
            <invoiceCurrencyExchangeRateDate xsi:nil="true" />
            <customerTradingAccountCurrency xsi:nil="true" />
            <customerTradingAccountCurrencyExchangeRate xsi:nil="true" />
            <customerTradingAccountCurrencyExchangeRateOperator xsi:nil="true" />
            <customerTradingAccountCurrencyExchangeRateDate xsi:nil="true" />
            <date xsi:nil="true" />
            <time xsi:nil="true" />
            <validity xsi:nil="true" />
            <expirationDate xsi:nil="true" />
            <taxCodes>
                <taxCode>
                    <notes xsi:nil="true" />
                </taxCode>
            </taxCodes>
            <buyerContact xsi:nil="true" />
            <salesPersons xsi:nil="true" />
            <user xsi:nil="true" />
            <salesOrderLines>
                <salesOrderLine>
                    <salesOrder xsi:nil="true" />
                    <active xsi:nil="true" />
                    <reference xsi:nil="true" />
                    <reference2 xsi:nil="true" />
                    <number xsi:nil="true" />
                    <status xsi:nil="true" />
                    <type>Standard</type>
                    <deliveryTerms xsi:nil="true" />
                    <commodity>
          </commodity>
                    <text xsi:nil="true" />
                    <associatedCommodity xsi:nil="true" />
                    <fulfillmentLocation xsi:nil="true" />
                    <deliveryDate xsi:nil="true" />
                    <dueDate xsi:nil="true" />
                    <carrierTradingAccount xsi:nil="true" />
                    <carrierNetPrice xsi:nil="true" />
                    <carrierTaxPrice xsi:nil="true" />
                    <carrierTotalPrice xsi:nil="true" />
                    <carrierSalesInvoiceLine xsi:nil="true" />
                    <carrierTaxCodes xsi:nil="true" />
                    <carrierReference xsi:nil="true" />
                    <unitOfMeasure xsi:nil="true" />
                    <quantity>2.00</quantity>
                    <initialPrice xsi:nil="true" />
                    <pricelist xsi:nil="true" />
                    <supplierTradingAccount xsi:nil="true" />
                    <supplierReference xsi:nil="true" />
                    <supplierPurchaseOrder xsi:nil="true" />
                    <supplierPurchaseOrderLine xsi:nil="true" />
                    <quotationLine xsi:nil="true" />
                    <postalAddresses xsi:nil="true" />
                    <orderLineDiscountType xsi:nil="true" />
                    <orderLineDiscountAmount xsi:nil="true" />
                    <orderLineDiscountPercent xsi:nil="true" />
                    <orderLineSubtotalDiscountType xsi:nil="true" />
                    <orderLineSubtotalDiscountAmount xsi:nil="true" />
                    <orderLineSubtotalDiscountPercent xsi:nil="true" />
                    <FOBPoint xsi:nil="true" />
                    <actualPrice>20.00</actualPrice>
                    <netTotal>50.00</netTotal>
                    <chargesTotal xsi:nil="true" />
                    <discountTotal xsi:nil="true" />
                    <taxCodes>
                        <taxCode>
                            <notes xsi:nil="true" />
                        </taxCode>
                    </taxCodes>
                    <priceTax xsi:nil="true" />
                    <taxTotal>10.00</taxTotal>
                    <grossTotal>60.00</grossTotal>
                    <costTotal xsi:nil="true" />
                    <profitTotal xsi:nil="true" />
                    <salesInvoiceLines xsi:nil="true" />
                    <receipts xsi:nil="true" />
                    <buyerContact xsi:nil="true" />
                    <project xsi:nil="true" />
                    <salesOrderDeliveryLines xsi:nil="true" />
                    <BOMLine xsi:nil="true" />
                    <financialAccounts xsi:nil="true" />
                </salesOrderLine>
                <salesOrderLine>
                    <salesOrder xsi:nil="true" />
                    <active xsi:nil="true" />
                    <reference xsi:nil="true" />
                    <reference2 xsi:nil="true" />
                    <number xsi:nil="true" />
                    <status xsi:nil="true" />
                    <type>Free Text</type>
                    <deliveryTerms xsi:nil="true" />
                    <commodity xsi:nil="true" />
                    <text>An example product</text>
                    <associatedCommodity xsi:nil="true" />
                    <fulfillmentLocation xsi:nil="true" />
                    <deliveryDate xsi:nil="true" />
                    <dueDate xsi:nil="true" />
                    <carrierTradingAccount xsi:nil="true" />
                    <carrierNetPrice xsi:nil="true" />
                    <carrierTaxPrice xsi:nil="true" />
                    <carrierTotalPrice xsi:nil="true" />
                    <carrierSalesInvoiceLine xsi:nil="true" />
                    <carrierTaxCodes xsi:nil="true" />
                    <carrierReference xsi:nil="true" />
                    <unitOfMeasure xsi:nil="true" />
                    <quantity>5.00</quantity>
                    <initialPrice xsi:nil="true" />
                    <pricelist xsi:nil="true" />
                    <supplierTradingAccount xsi:nil="true" />
                    <supplierReference xsi:nil="true" />
                    <supplierPurchaseOrder xsi:nil="true" />
                    <supplierPurchaseOrderLine xsi:nil="true" />
                    <quotationLine xsi:nil="true" />
                    <postalAddresses xsi:nil="true" />
                    <orderLineDiscountType xsi:nil="true" />
                    <orderLineDiscountAmount xsi:nil="true" />
                    <orderLineDiscountPercent xsi:nil="true" />
                    <orderLineSubtotalDiscountType xsi:nil="true" />
                    <orderLineSubtotalDiscountAmount xsi:nil="true" />
                    <orderLineSubtotalDiscountPercent xsi:nil="true" />
                    <FOBPoint xsi:nil="true" />
                    <actualPrice xsi:nil="true" />
                    <netTotal>100.00</netTotal>
                    <chargesTotal xsi:nil="true" />
                    <discountTotal xsi:nil="true" />
                    <taxCodes>
                        <taxCode>
                            <notes xsi:nil="true" />
                        </taxCode>
                    </taxCodes>
                    <priceTax xsi:nil="true" />
                    <taxTotal>20.00</taxTotal>
                    <grossTotal>120.00</grossTotal>
                    <costTotal xsi:nil="true" />
                    <profitTotal xsi:nil="true" />
                    <salesInvoiceLines xsi:nil="true" />
                    <receipts xsi:nil="true" />
                    <buyerContact xsi:nil="true" />
                    <project xsi:nil="true" />
                    <salesOrderDeliveryLines xsi:nil="true" />
                    <BOMLine xsi:nil="true" />
                    <financialAccounts xsi:nil="true" />
                </salesOrderLine>
            </salesOrderLines>
            <lineCount xsi:nil="true" />
            <orderDiscountType xsi:nil="true" />
            <orderDiscountAmount xsi:nil="true" />
            <orderDiscountPercent xsi:nil="true" />
            <orderAdditionalDiscount1Type xsi:nil="true" />
            <orderAdditionalDiscount1Amount xsi:nil="true" />
            <orderAdditionalDiscount1Percent xsi:nil="true" />
            <orderAdditionalDiscount2 xsi:nil="true" />
            <orderAdditionalDiscount2Amount xsi:nil="true" />
            <orderAdditionalDiscount2Percent xsi:nil="true" />
            <text1 xsi:nil="true" />
            <text2 xsi:nil="true" />
            <netTotal xsi:nil="true" />
            <discountTotal xsi:nil="true" />
            <chargesTotal xsi:nil="true" />
            <taxTotal xsi:nil="true" />
            <grossTotal xsi:nil="true" />
            <costTotal xsi:nil="true" />
            <profitTotal xsi:nil="true" />
            <receipts xsi:nil="true" />
            <depositFlag xsi:nil="true" />
            <contract xsi:nil="true" />
            <invoiceTradingAccount xsi:nil="true" />
            <invoiceCountry xsi:nil="true" />
            <orderCountry xsi:nil="true" />
            <projects xsi:nil="true" />
            <salesOrderDeliveries xsi:nil="true" />
            <fulfillmentLocations xsi:nil="true" />
            <financialAccounts xsi:nil="true" />
            <interactions xsi:nil="true" />
            <notes xsi:nil="true" />
        </salesOrder>
    </sdata:payload>
</entry>

Failed attempted at converting to JSON:

{
  "$descriptor": "Sage Accounts 50 | salesOrder",
  "$url": "http://xxxxxx/sdata/accounts50/GCRM/{xxxxxxxxxx}/salesOrders",
  "$resources": [
    {
      "active": true,
      "reference": "1",
      "reference2": "",
      "status": "In Progress",
      "allocationStatus": "Full",
      "deliveryStatus": "",
      "invoiceStatus": "",
      "statusFlag": null,
      "statusFlagText": null,
      "tradingAccount": null,
      "customerReference": "",
      "opportunity": null,
      "pricelist": null,
      "supplierTradingAccount": null,
      "supplierReference": null,
      "supplierPurchaseOrders": null,
      "type": "Sales Order",
      "copyFlag": false,
      "originatorDocument": null,
      "quotation": null,
      "postalAddresses": null,
      "deliveryMethod": null,
      "deliveryRule": null,
      "deliveryTerms": null,
      "deliveryDate": null,
      "dueDate": null,
      "carrierCompany": null,
      "carrierTradingAccount": null,
      "carrierNetPrice": 0.0,
      "carrierTaxPrice": 0.0,
      "carrierTotalPrice": 0.0,
      "carrierSalesInvoice": null,
      "carrierTaxCodes": null,
      "carrierReference": "",
      "currency": "GBP",
      "operatingCompanyCurrency": "GBP",
      "operatingCompanyCurrencyExchangeRate": 1.0,
      "operatingCompanyCurrencyExchangeRateOperator": "/",
      "operatingCompanyCurrencyExchangeRateDate": null,
      "invoiceCurrency": null,
      "invoiceCurrencyExchangeRate": null,
      "invoiceCurrencyExchangeRateOperator": null,
      "invoiceCurrencyExchangeRateDate": null,
      "customerTradingAccountCurrency": null,
      "customerTradingAccountCurrencyExchangeRate": null,
      "customerTradingAccountCurrencyExchangeRateOperator": null,
      "customerTradingAccountCurrencyExchangeRateDate": null,
      "date": null,
      "time": null,
      "validity": null,
      "expirationDate": null,
      "taxCodes": null,
      "buyerContact": null,
      "salesPersons": null,
      "user": "MANAGER",
      "salesOrderLines": {
        "$url": "http://xxxxxxxxxxx/sdata/accounts50/GCRM/{xxxxxxxxxxxxxx}/SalesOrderLines",
        "$resources": [
          {
            "salesOrder": null,
            "active": true,
            "reference": "1",
            "reference2": "",
            "number": 1.0,
            "status": null,
            "type": "0",
            "deliveryTerms": null,
            "commodity": null,
            "text": "kll",
            "associatedCommodity": null,
            "fulfillmentLocation": null,
            "deliveryDate": null,
            "dueDate": null,
            "carrierTradingAccount": null,
            "carrierNetPrice": null,
            "carrierTaxPrice": null,
            "carrierTotalPrice": null,
            "carrierSalesInvoiceLine": null,
            "carrierTaxCodes": null,
            "carrierReference": "",
            "unitOfMeasure": null,
            "quantity": 1.0,
            "initialPrice": 1.0,
            "pricelist": null,
            "supplierTradingAccount": null,
            "supplierReference": null,
            "supplierPurchaseOrder": null,
            "supplierPurchaseOrderLine": null,
            "quotationLine": null,
            "postalAddresses": null,
            "orderLineDiscountType": null,
            "orderLineDiscountAmount": 0.0,
            "orderLineDiscountPercent": 0.0,
            "orderLineSubtotalDiscountType": null,
            "orderLineSubtotalDiscountAmount": null,
            "orderLineSubtotalDiscountPercent": null,
            "FOBPoint": null,
            "actualPrice": 1.0,
            "netTotal": 1.0,
            "chargesTotal": null,
            "discountTotal": 0.0,
            "taxCodes": null,
            "priceTax": null,
            "taxTotal": 0.0,
            "grossTotal": 1.0,
            "costTotal": null,
            "profitTotal": null,
            "salesInvoiceLines": null,
            "receipts": null,
            "buyerContact": null,
            "project": null,
            "salesOrderDeliveryLines": null,
            "BOMLine": null,
            "financialAccounts": null
          }
        ]
      },
      "lineCount": 1.0,
      "orderDiscountType": null,
      "orderDiscountAmount": 0.0,
      "orderDiscountPercent": 0.0,
      "orderAdditionalDiscount1Type": null,
      "orderAdditionalDiscount1Amount": null,
      "orderAdditionalDiscount1Percent": null,
      "orderAdditionalDiscount2": null,
      "orderAdditionalDiscount2Amount": null,
      "orderAdditionalDiscount2Percent": null,
      "text1": "",
      "text2": "",
      "netTotal": 1.0,
      "discountTotal": null,
      "chargesTotal": null,
      "taxTotal": 0.0,
      "grossTotal": 1.0,
      "costTotal": null,
      "profitTotal": null,
      "receipts": null,
      "depositFlag": null,
      "contract": null,
      "invoiceTradingAccount": null,
      "invoiceCountry": null,
      "orderCountry": null,
      "projects": null,
      "salesOrderDeliveries": null,
      "fulfillmentLocations": null,
      "financialAccounts": null,
      "interactions": null,
      "notes": null,
      "CustomerId": "xxxxxxxxxxxxxx"
    }]}

Solution

  • The documentation link you have posted is for Sdata 2.0 but unfortunately the Sage 50 Accounts Sdata adapter only supports Sdata 1.0/1.1. This means it does not support POST/PUT using JSON and you can only use XML for these operations.