This is the second part of a using NAV OData web services directly from C/AL code. First part can be found here. In that post it was demonstrated how to call NAV OData web services from C/AL code and read data without using any custom dll.
In this second part we will look at writing data to Dynamics NAV using the same OData web services. Which is more challenging, to be honest. I learned a lot about OData web services in general and about NAV web services in particular.
The code is available for download from GitHub:
https://github.com/ajkauffmann/DynamicsNAV
I have included an example Codeunit 72001 NAV WS Write Demo. In this Codeunit you can find these scenarios:
- Create a new sales order
- Create a new sales line
- Update a sales order
- Delete a sales order
Let’s discuss the scenarios one by one.
Create a new sales order
To create a new sales order we must at least provide some information, like the customer number. For this, we need to create a JSON string that will be sent to the web service. You may expect that I had to switch to XML here, but that’s not the case. Dynamics NAV web services do not only produce JSON strings, they also consume JSON strings.
Until now, we have only requested data from OData web services, for which we used the REST method GET. Now that we are going to send data to the web service, we need to use a different REST method. To create an entity via OData web services, we need to use the POST method. More information about this can be found here: https://msdn.microsoft.com/en-us/library/dn182582(v=nav.90).aspx.
One important remark, before we jump to the code: the user that is used to call the web service must have permissions. Also the page that we are using must have InsertAllowed set to TRUE. The same applies to modify or delete, of course.
LOCAL CreateNewSalesOrder(VAR OrderNo : Code[20])
JTokenWriter := JTokenWriter.JTokenWriter;
WITH JTokenWriter DO BEGIN
WriteStartObject;
WritePropertyName(‘Sell_to_Customer_No’);
WriteValue(‘46251425′);
WriteEndObject;
JObject := Token;
END;
json := JObject.ToString;
StringContent := StringContent.StringContent(json,Encoding.UTF8,’application/json’);
Parameters := Parameters.Dictionary();
Parameters.Add(‘baseurl’,BaseUrl);
Parameters.Add(‘path’,GetODataPath(‘SalesOrder’));
Parameters.Add(‘restmethod’,’POST’);
Parameters.Add(‘accept’,’application/json’);
Parameters.Add(‘username’,GetUserName);
Parameters.Add(‘password’,GetPassword);
Parameters.Add(‘httpcontent’,StringContent);
RESTWSManagement.CallRESTWebService(Parameters,HttpResponseMessage);
result := HttpResponseMessage.Content.ReadAsStringAsync.Result;
JObject := JObject.Parse(result);
OrderNo := JsonHelperFunctions.GetValueAsText(JObject,’No’);
MESSAGE(‘Sales Order %1 has been created’,OrderNo);
The first lines create a JTokenWriter object that we use to compose a Json object. This is pretty straightforward, no hidden secrets here.
The resulting JSON string is then stored in a StringContent object. This is similar to what we did in other web services examples, for example in the send SMS text message.
Next lines create the set of arguments that need to passed into the generic function CallRESTWebService. For more info about this parameters object, see the part 1 of this post. Look at the line that specifies the rest method, we are now using POST.
Well, that’s basically all we need to create a new sales order. The call to the web service responds with a JSON string that contains the created order, including the order number and validated fields like customer name, etc.
To be able to run the next example, we need to read the order number from the response message and pass it to the next function.
Create a new sales line
Create a new sales line is very much the same as creating a new sales order. There are two important things that we need to pay attention to, though. The subpage Sales Lines is published together with the main page Sales Order and must be called with a unique URL. The basic functionality like automatic fill in of primary key fields and even automatic line numbering is completely supported with OData web services! This means that we can just specify a JSON string that contains information like type, item number, quantity, etc. Basically the same as we do when we manually enter data in this page.
To get that to work, we need to specify the sales order number in the URL. This is the same as with reading a specific sales order, which was already explained in part 1.
Overall, the code is exactly the same as with creating a new sales order.
LOCAL CreateNewSalesLine(OrderNo : Code[20])
JTokenWriter := JTokenWriter.JTokenWriter;
WITH JTokenWriter DO BEGIN
WriteStartObject;
WritePropertyName(‘Type’);
WriteValue(‘Item’);
WritePropertyName(‘No’);
WriteValue(‘1000’);
WritePropertyName(‘Quantity’);
WriteValue(FORMAT(5,0,9));
WritePropertyName(‘Requested_Delivery_Date’);
WriteValue(FORMAT(011216D,0,9));
WriteEndObject;
JObject := Token;
END;
json := JObject.ToString;
StringContent := StringContent.StringContent(json,Encoding.UTF8,’application/json’);
Parameters := Parameters.Dictionary();
Parameters.Add(‘baseurl’,BaseUrl);
Parameters.Add(‘path’,GetODataPath(‘SalesOrder(Document_Type=”Order”,No=”’ + OrderNo + ”’)/SalesOrderSalesLines’));
Parameters.Add(‘restmethod’,’POST’);
Parameters.Add(‘accept’,’application/json’);
Parameters.Add(‘username’,GetUserName);
Parameters.Add(‘password’,GetPassword);
Parameters.Add(‘httpcontent’,StringContent);
RESTWSManagement.CallRESTWebService(Parameters,HttpResponseMessage);
result := HttpResponseMessage.Content.ReadAsStringAsync.Result;
JObject := JObject.Parse(result);
MESSAGE(‘Sales Line created with Line Amount: %1′,JsonHelperFunctions.GetValueAsDecimal(JObject,’Line_Amount’));
Update a sales order
Now we are getting to the more tricky part of calling OData web services. Imagine that you are going to update a certain field in an existing record, e.g. the external document number in an existing sales order. Now take a moment to answer this question: would you expect to send the complete sales order as a JSON string, or just the external document number?
Ok, what was your answer? I guess you would like to just send the field that needs to be updated rather than sending the whole document. You start thinking about what will happen to data in fields that is not updated. Will those fields be validated, regardless if the data was unchanged? But then you start to be curious, could it be that simple?
And yes, it is that simple. Just create a simple JSON string that only contains the fields that need to updated.
But that’s not the whole story…
According to the documentation from Microsoft, we need to use PUT or MERGE to modify an existing entry. Well… that turned out to be not true.
The PUT command is used to replace an existing entry, so all property values must be passed by in the JSON entry. Tried that, and I can tell you, that didn’t work. I ended up with completely empty sales orders. So DO NOT use the PUT command.
What about MERGE? Well, that’s not an official HTTP verb. It is a custom method that is used in OData 1.0 and 2.0 protocols. Since it is not an official HTTP verb, it might not be supported in newer OData versions. Instead, the HTTP verb PATCH is preferred over MERGE when working with data services that support OData 3.0 protocol. Which is the case with OData web services in Dynamics NAV. So for this reason I use PATCH instead of MERGE. Both methods specify that the update is to be completed by using merge semantics. Which basically means that only the specified properties get updated while leaving the other data intact.
Ok, so far so good. We use PATCH and send over only the fields that needs to be updated. Are we done yet?
Not really…
There is another part that we must take care of. That’s about the version of record that we are updating. What do I mean? Well, we need to deal with the well-known message from Dynamics NAV ‘Another user has modified the record…’. Also known as optimistic concurrency exception. This isn’t exclusively for user sessions, it applies to NAV web services as well!
So, if we want to update an existing record over OData web services, we must specify which version of the record we want to update. To be more precise, we must specify the datetime stamp of the record. The OData specification supports this scenario by using the HTTP ETag header value.
When a record is read from Dynamics NAV OData web service, the response contains always an ETag value. You may have seen that before and wondered what that value is. Well, this value contains the recordid including the timestamp. And we need to pass this value back to the web service when we want to update the record.
This ETag value is not only passed with the JSON string. If we read a single record from the database, then the ETag value is also part of the response header values. Why is that important? Well, because the ETag that you see here is not complete.
Let me prove that with this example:
As you can see, the ETag in the response header is encoded. It also contains quotes, both double and single quotes and it is preceded by W/. Al of this is not part of the ETag in the JSON string.
The W/ part means that the ETag value is weakly validating. I’m skipping the exact meaning of that, but as you can imagine, it would be easier if we can use the exact value as passed by the response header. We can compose the header value based on the ETag value in the JSON string if we need to. So I have provided examples of both usages.
To get the ETag value from the response header, we use a function to read a specific sales order. This function returns both the JSON string and the ETag value.
GetSalesOrder(OrderNo : Code[20];VAR ETag : DotNet "System.Net.Http.Headers.EntityTagHeaderValue") result : Text
Initialize;
Parameters := Parameters.Dictionary();
Parameters.Add(‘baseurl’,BaseUrl);
Parameters.Add(‘path’,GetODataPath(‘SalesOrder(Document_Type=”Order”,No=”’ + OrderNo + ”’)’));
Parameters.Add(‘restmethod’,’GET’);
Parameters.Add(‘accept’,’application/json’);
Parameters.Add(‘username’,GetUserName);
Parameters.Add(‘password’,GetPassword);
RESTWSManagement.CallRESTWebService(Parameters,HttpResponseMessage);
result := HttpResponseMessage.Content.ReadAsStringAsync.Result;
ETag := HttpResponseMessage.Headers.ETag;
Next step is to add the ETag to the parameters collection. Here is the complete code of the update function.
LOCAL UpdateSalesOrder(Orderno : Code[20])
json := NAVWSReadDemo.GetSalesOrder(Orderno,ETag);
//Alternatively, get ETag from json:
//GetETagFromJson(json,ETag);
JTokenWriter := JTokenWriter.JTokenWriter;
WITH JTokenWriter DO BEGIN
WriteStartObject;
WritePropertyName(‘External_Document_No’);
WriteValue(‘PO0192837465′);
WriteEndObject;
JObject := Token;
END;
json := JObject.ToString;
StringContent := StringContent.StringContent(json,Encoding.UTF8,’application/json’);
Parameters := Parameters.Dictionary();
Parameters.Add(‘baseurl’,BaseUrl);
Parameters.Add(‘path’,GetODataPath(‘SalesOrder(Document_Type=”Order”,No=”’ + Orderno + ”’)’));
Parameters.Add(‘restmethod’,’PATCH’);
Parameters.Add(‘accept’,’application/json’);
Parameters.Add(‘username’,GetUserName);
Parameters.Add(‘password’,GetPassword);
Parameters.Add(‘httpcontent’,StringContent);
Parameters.Add(‘etag’,ETag);
IF NOT RESTWSManagement.CallRESTWebService(Parameters,HttpResponseMessage) THEN BEGIN
result := HttpResponseMessage.Content.ReadAsStringAsync.Result;
IF JsonHelperFunctions.TryParse(result,JToken) THEN
MESSAGE(JsonHelperFunctions.GetODataErrorMessage(JToken))
ELSE
MESSAGE(‘An error as occurred during the web service call.\\%1\\%2’,HttpResponseMessage.StatusCode,result);
END;
LOCAL GetETagFromJson(json : Text;VAR ETag : DotNet "System.Net.Http.Headers.EntityTagHeaderValue")
IF JsonHelperFunctions.TryParse(json,JToken) THEN
ETag := ETag.EntityTagHeaderValue(STRSUBSTNO(‘"”%1”"’,EscapeDataString(JsonHelperFunctions.GetValueAsText(JToken,’ETag’))),TRUE);
Final step is to pass the ETag into the request header value. This is done in the CallRESTWebService function:
IF Parameters.ContainsKey(‘etag’) THEN
HttpRequestMessage.Headers.IfMatch.Add(Parameters.Item(‘etag’));
What is that IfMatch property on the header. This is a header field that is part of the HTTP specifiation. The purpose is, besides caching, ‘to prevent inadvertent modification of the wrong version of a resource’.
Now take one step back. Look at the big picture. Dynamics NAV has concurrency check. OData implements HTTP ETag values for the same purpose. How nice is it to discover that the Dynamics NAV team use these standard techniques rather than implementing a NAV specific mechanism over OData? I like this!
Ok, over to the final scenario.
Delete a sales order
Well, no surprises or hidden features here. To delete an existing entity, we need to use the DELETE method. And of course, the whole ETag story applies to this scenario as well. You shouldn’t be surprised about that.
LOCAL DeleteSalesOrder(Orderno : Code[20])
result := NAVWSReadDemo.GetSalesOrder(Orderno,ETag);
Parameters := Parameters.Dictionary();
Parameters.Add(‘baseurl’,BaseUrl);
Parameters.Add(‘path’,GetODataPath(‘SalesOrder(Document_Type=”Order”,No=”’ + Orderno + ”’)’));
Parameters.Add(‘restmethod’,’DELETE’);
Parameters.Add(‘accept’,’application/json’);
Parameters.Add(‘username’,GetUserName);
Parameters.Add(‘password’,GetPassword);
Parameters.Add(‘etag’,ETag);
IF NOT RESTWSManagement.CallRESTWebService(Parameters,HttpResponseMessage) THEN BEGIN
result := HttpResponseMessage.Content.ReadAsStringAsync.Result;
IF JsonHelperFunctions.TryParse(result,JToken) THEN
MESSAGE(JsonHelperFunctions.GetODataErrorMessage(JToken))
ELSE
MESSAGE(‘An error as occurred during the web service call.\\%1\\%2’,HttpResponseMessage.StatusCode,result);
END;
Conclusion
It’s perfectly possible to both read from and write to Dynamics NAV web services directly from C/AL code. Without the use of any external .Net assembly or whatsoever.
In my examples I have used a generic function to read data from the returned JSON string. Recently my fellow MVP Gunnar Gestsson has posted another blog about using REST/JSON from NAV. He uses Codeunit 1297 Http Web Request Mgt. where I have created my own generic Codeunit for that. Both ways are possible, in my opinion. The difference is that HttpWebRequest that is used in Codeunit 1297 is an object that gives a lot of low level control over the request. The HttpClient object that I’m using is at a different level. For more information read this excellent explanation.
What I specifically like about the blog post from Gunnar is the way how to create a temporary record from a JSON response. This is an intereseting approach to create for example a temporary Sales Header record directly from the JSON response.
That’s it, hope you enjoyed!
Pingback: Web Services Example 7 – Call NAV OData Web Services (Part 2) - Kauffmann @ Dynamics NAV - Dynamics NAV Users - DUG
Hi,
Your site is very useful and I have had lot of help especially to web services, so thank you very much!
I am trying to call REST with GET and I should send Content-MD5 in header with my request. If I understand correctly .NET does not allow to send this if content is empty with HttpClient.
I found some posts about this on stackoverflow, but this seems quite complicated and I would like to make calls without any DLL’s.
http://stackoverflow.com/questions/10679214/how-do-you-set-the-content-type-header-for-an-httpclient-request
If you have some ideas, I would appreciate it 🙂
Br,
Tomi
So I’m using this example to put together a solution for my company to integrate directly with a customer. We have a spec that requires a nested array of values. Example below . . .
I can’t quite figure out how to add the “standard_product_codes” array into the json using the jtokenwriter. Any ideas?
{
“product_title”: “My Product”,
“jet_browse_node_id”: 1000001,
“standard_product_codes”: [
{
“standard_product_code”: “123456789012”,
“standard_product_code_type”: “UPC”
}
]
}
Thank you for your clear explanation. However, while this example excellently shows how to insert one record per call, how would you need to adapt the POST to insert multiple records in one call?
I have tried to post multiple records in one call by combining them together in an array. Unfortunately, that didn’t work. I’m afraid it is just not possible at all. Not 100% about that, though.
Thank you for your reply, ajk.
Yes, I attempted that as well. I tried arrays, objects containing arrays, adding a repeater to the published page and then trying arrays and many a variation thereof. The only things I have not tried is a using a POST on a list page or using a published query; I seem to have read somewhere that list pages and NAV queries only support GET requests.
Hi,
Thanks’ a lot for this samples that was a big help for me. I’m able to run it in NAV 2016 and 2017 but not in NAV 2015. An error ‘not able to load assembly …’ occurs on line ‘JToken := JToken.Parse(result) occurs’.
The version of the dll Newtonsoft.Json.dll in the program folder is 4.5.11.15520. What is confusing me is, that the object designer shows two dll’s. One with version 10.0.0.0 and one with version 6.0.0.0. In the program and add-ins folder I only found one dll.
I tried different things to make it run. Changing the variables to version 10.0.0.0 did not help. Not even copy the dll from NAV 2016 to an new subfolder Json in the add-in folder.
Any ideas what to do? Kind Regards, Lorenz
When you see two versions, it is most probably that the same dll exists twice in the Add-ins folder.
I don’t have a NAV2015 installation, so I can’t check for you. What I would try is to to find the Json dll in the server folder, copy it to the add-ins folder and use that one. The copy is only needed for development, not for production. When running the code, the NAV server will load the dll from the server folder and not from the add-in folder.
Hi Arend-Jan,
I tried a few downloaded dll’s but not the one from the server folder. Now everything works fine.
Thanks’ a lot for your help.
Kind Regards, Lorenz
All of thise was super super helpful, so thank you very much.
I ran into one issue on NAV 2016, and I don’t know if it’s something I’m doing wrong since we can’t debug this and NAV doesn’t log OData errors server-side. The example logic for Creating Sales Lines does not appear to work, no matter what I do. I’m wondering if it’s an issue with the inability to do Create/POST on Lines. I’d give more useful info, but all I get from NAV is “400 Bad Request”.
The entire POST is nothing crazy, basically just against CRONUS:
—-
POST /[MyServiceName]/OData/Company(‘CRONUS%20Sverige%20AB’)/SalesQuote(Document_Type=’Quote’,No=’1004′)/SalesQuoteSalesLines HTTP/1.1
Host: localhost:7048
Content-Type: application/json
Authorization: Basic [Redacted]
Cache-Control: no-cache
{“Type”:”Item”,”No”:”1928-W”,”Quantity”:1}
—-
For sanity, I was using Postman to just try the raw HTTP POST. Get works fine against the headers and lines, as do POST and PATCH against the headers, but for whatever reason, POST against the /SalesQuoteSalesLines just will not work.
Welp, the rule is “you always find the problem the minute you ask support/an expert”. It holds true.
{“Type”:”Item”,”No”:”1928-W”,”Quantity”:1} is invalid.
{“Type”:”Item”,”No”:”1928-W”,”Quantity”:”1″} is valid.
Now it works just fine.
With NAV 2017 you should try the same with the ODataV4 endpoint. Good luck! 😉
Any chance you could provide an example of updating a sales line? We are trying to set the description and with the latest version, it does not accept any description value we provide. It defaults to the item name instead. It was suggested that we create and then update the sales line, but I cannot find a way to update a sales line in the ODATAv4 web services.