Create an Exact Online Sales Order with Details through REST Calls

Wiki (en)
, ,
1

This article illustrates how to create a sales order with lines on Exact Online using the REST-based tables.

Creation of sales order is generally recommended using the Exact Online XML APIs for reasons of an approximately 3 times better throughput and an order of magnitude higher daily quotas. The XML APIs can be accessed using UploadXMLTopics or https://get-my-report.com with an Excel-sheet as input. More tips are in the Performance section below.

Instructions to create Sales Orders and Sales Order Lines

Exact Online has the peculiarity that it requires each transaction to have at least one line, which holds for sales orders, sales invoices and general ledger transactions. However, a standard insert statement creates just the header. Therefor, the header and the lines must be sent to Exact Online in one transaction.

Invantive SQL provides transaction-logic to accomplish this. The same transaction logic is also used for other connectors such as when creating EDIFACT files.

Execution of the Invantive SQL statements below will create a sales order like:

Exact Online Sales order
Exact Online Sales order1227×806 48.9 KB

To create such a sales execute the following steps:

  • Choose the Exact Online company:
use COMPANYNUMBER
  • Enable native call logging when you want to keep an eye on the actual communication with Exact Online using:
set trace-native-calls true

set log-native-calls-to-disk

begin transaction
  • Define the contents of a sales order:
insert into exactonlinerest..salesorders
( orderedby
, description
) 
--
-- Select a random customer and define a transaction 'TRANSACTION1',
-- one per sales order.
-- You can define and upload multiple Exact Online sales orders
-- and send them off in one Invantive SQL transaction.
--
select act.id
,      'buy some items' description
from   exactonlinerest..accounts act
where  act.status = 'C'
limit  1
identified by 'TRANSACTION1'
  • Add ten sales order lines to the sales order, each on a different randomly selected item:
insert into ExactOnlineREST..salesorderlines 
( item
, linenumber
)
select itm.id
,      row_number()
       linenumber
from   exactonlinerest..items itm
where  itm.IsSalesItem = true
limit 10
attach to 'TRANSACTION1'
  • Hand over the sales order and the associated sales order lines to Exact Online in one REST API call:
commit transaction
  • After the commit, Exact Online will return validation errors if any.
  • The outgoing native call found in the log folder will resemble (line breaks added for readability):
Outbound https://start.exactonline.nl/api/v1/102673/salesorder/SalesOrders Request using POST started at 2020-06-02 16:43:01 (UTC).
Object: ExactOnlineREST.SalesOrder.SalesOrders
Partition: 102673
Data container ID: https://start.exactonline.nl/8045575
User: john.doe@acme.com

{"Description":"buy some items","OrderedBy":"6d0bfbeb-730f-4290-b886-007392d22a0f"
,"SalesOrderLines":
[ {"Item":"1db1fa03-4b2d-4a54-bee2-005edae3934c","LineNumber":21}
,{"Item":"127fba93-64ce-4cb8-bd9c-00653edf0a13","LineNumber":22}
,{"Item":"46dd7805-15ab-4360-a383-008ce7f46811","LineNumber":23}
,{"Item":"0ed58c10-be4d-4cb9-9ffd-0100d261dc3b","LineNumber":24}
,{"Item":"8194860a-4624-4517-a54f-01194fe2f97d","LineNumber":25}
,{"Item":"cd09577e-e541-4e99-a495-020ab56faa78","LineNumber":26}
,{"Item":"65484fc2-c60c-4b3d-8538-021ad2036ee5","LineNumber":27}
,{"Item":"6e65e4cd-130c-4b3c-8f20-0255283e6368","LineNumber":28}
,{"Item":"0d998f82-fc15-48a8-bc6e-0261a89f0645","LineNumber":29}
,{"Item":"8fa70684-cce7-4916-b4fd-028a7c5af6d4","LineNumber":30}
]
}
  • The native answer from Exact Online interpreted by Invantive SQL will resemble:
Inbound https://start.exactonline.nl/api/v1/102673/salesorder/SalesOrders POST started at 2020-06-02 16:43:01 (UTC).
Object: ExactOnlineREST.SalesOrder.SalesOrders
Partition: 102673
Data container ID: https://start.exactonline.nl/8045575
User: john.doe@acme.com

{
"d" : {
"__metadata": {
"uri": "https://start.exactonline.nl/api/v1/102673/salesorder/SalesOrders(guid'94feaad3-69cc-46ad-9925-d02d2b7f994a')"
, "type": "Exact.Web.Api.Models.SalesOrder"
}
...
, "Created": "\/Date(1591123382670)\/"
, "Creator": "bd0e191e-6fb4-4537-bf91-9f68d4e62744"
, "CreatorFullName": "John Doe"
, "Currency": "EUR"
, "DeliverTo": "6d0bfbeb-730f-4290-b886-007392d22a0f"
, "DeliverToContactPerson": null
, "DeliverToContactPersonFullName": null
, "DeliverToName": "             61602"
, "DeliveryDate": "\/Date(1591056000000)\/"
, "DeliveryStatus": 12
, "DeliveryStatusDescription": "Open"
, "DeliveryAddress": "bb8da22d-fd4f-4f8e-bd96-b0b1de61bd6a"
, "Description": "buy some items"
, "Division": 102673
...
, "InvoiceStatus": 12
, "InvoiceStatusDescription": "Open"
, "InvoiceTo": "6d0bfbeb-730f-4290-b886-007392d22a0f"
...
, "OrderedBy": "6d0bfbeb-730f-4290-b886-007392d22a0f"
...
, "OrderNumber": 42
...
, "Status": 12
, "StatusDescription": "Open"
...
, "SalesOrderLines": 
{
"__deferred": {
"uri": "https://start.exactonline.nl/api/v1/102673/salesorder/SalesOrders(guid'94feaad3-69cc-46ad-9925-d02d2b7f994a')/SalesOrderLines"
}
}
}
}

Supported Tables

On the following Exact Online tables it is possible to perform transactions, combining lines and headers into one transaction:

In case Exact Online adds new tables with transaction logic but your release does not yet support them, use NativeScalarRequests to upload into these new tables (for an example see Triggering the new error rate limit active of 10 per app, endpoint, Exact Online division, user and hour).

Performance of Uploads into Exact Online

The performance of the Exact Online REST API is limited. Historical measurements are documented in Does Invantive SQL match the speed of Infinite Probability Drive?. For large volume uploads it is often desirable to use the Exact Online XML API instead. Standard uploads using the Exact Online XML API are available on

Relevant articles are:

The UploadXMLTopics table of Invantive SQL automatically breaks the upload in manageable pieces within the limitations of the Exact Online API, but with a significant higher throughput than using the Exact Online REST APIs.