Hi guys,
I have created an external web api which returns the data in JSON format. Here is the sample response:
[{"webOrderHeader":{"OrderNo":1,"OrderDate":"2017-01-18T00:00:00","TotalPrice":100.0,"WebOrderLine":null},"webOrderLine":[{"No":1,"OrderNo":1,"ItemNo":"ABC1","Quantity":1,"UnitPrice":50.0,"WebOrderHeader":null},{"No":2,"OrderNo":1,"ItemNo":"ABC1","Quantity":1,"UnitPrice":50.0,"WebOrderHeader":null}]},{"webOrderHeader":{"OrderNo":2,"OrderDate":"2017-01-19T00:00:00","TotalPrice":100.0,"WebOrderLine":null},"webOrderLine":[{"No":3,"OrderNo":2,"ItemNo":"ABC1","Quantity":1,"UnitPrice":100.0,"WebOrderHeader":null}]},{"webOrderHeader":{"OrderNo":3,"OrderDate":"2017-01-18T00:00:00","TotalPrice":100.0,"WebOrderLine":null},"webOrderLine":[{"No":4,"OrderNo":3,"ItemNo":"XYZ1","Quantity":1,"UnitPrice":100.0,"WebOrderHeader":null}]},{"webOrderHeader":{"OrderNo":4,"OrderDate":"2017-01-20T00:00:00","TotalPrice":100.0,"WebOrderLine":null},"webOrderLine":null},{"webOrderHeader":{"OrderNo":5,"OrderDate":"2017-01-21T00:00:00","TotalPrice":100.0,"WebOrderLine":null},"webOrderLine":null}]
You can use this utility
http://jsonviewer.stack.hu/ to see the json response in a user friendly way.
Now the question is, I want to parse this JSON response and add the data in my NAV tables. So for this, i have write a custom code which basically parses the above response. The code are as under:
LOCAL ParseJsonResponse(Value : Text)
p := 0;
x := 1;
IF STRPOS(Value,'[]') > 0 THEN EXIT;
CurrentObject := COPYSTR(Value,STRPOS(Value,'{')+1,STRPOS(Value,':'));
Value := COPYSTR(Value,STRLEN(CurrentObject)+1);
CurrentObject := DELCHR(CurrentObject,'=',FormatChar);
CurrentObject := DELCHR(CurrentObject,'=',':');
Value := DELCHR(Value,'=','{}');
l := STRLEN(Value);
WHILE p < l DO BEGIN
ValuePair := SELECTSTR(x,Value); // get comma separated pairs of values and element names
p := STRPOS(Value,ValuePair)+STRLEN(ValuePair); // move pointer to the end of the current pair in Value
ValuePair := DELCHR(ValuePair,'=',FormatChar);
CurrentElement := COPYSTR(ValuePair,1,STRPOS(ValuePair,':'));
CurrentElement := DELCHR(CurrentElement,'=',':');
CurrentValue := COPYSTR(ValuePair,STRPOS(ValuePair,':'));
CurrentValue := DELCHR(CurrentValue,'=',':');
CASE CurrentObject OF
'webOrderHeader':
CASE CurrentElement OF
'OrderNo':
// must be a new record or lookup the existing record
BEGIN
WebOrderHeader.SETFILTER("Order No.",CurrentValue);
EVALUATE(RecId,CurrentValue);
IF NOT WebOrderHeader.FINDFIRST THEN BEGIN
WebOrderHeader.INIT;
WebOrderHeader."Order No." := FORMAT(RecId);
WebOrderHeader.INSERT;
END;
END;
'OrderDate':
//EVALUATE(WebOrderHeader."Order Date",CurrentValue);
WebOrderHeader."Order Date" := TODAY;
'TotalPrice':
BEGIN
EVALUATE(WebOrderHeader."Total Price",CurrentValue);
WebOrderHeader.MODIFY;
END;
END;
'webOrderLine':
CASE CurrentElement OF
'No':
// must be a new record or lookup the existing record
BEGIN
EVALUATE(RecId,CurrentValue);
WebOrderLine.SETRANGE("Line No.",RecId);
IF NOT WebOrderLine.FINDFIRST THEN BEGIN
WebOrderLine.INIT;
WebOrderLine."Line No." := RecId;
WebOrderLine.INSERT;
END;
END;
'OrderNo':
WebOrderLine."Order No." := CurrentValue;
'ItemNo':
WebOrderLine."Item No." := CurrentValue;
'Quantity':
EVALUATE(WebOrderLine.Quantity,CurrentValue);
'UnitPrice':
BEGIN
EVALUATE(WebOrderLine."Unit Price",CurrentValue);
WebOrderLine.MODIFY;
END;
END;
END;
x := x+1; // next pair
END;
The issue which I m facing is the above code is perfectly parsing the webOrderHeader information but it is unable to parse the webOrderLine.
Please help me and if possible tweak the code.
Looking forward for the positive response.
Comments
I've made some changes in your code:
LOCAL ParseJSONResponse(Value : Text)
p := 0;
x := 1;
IF STRPOS(Value,'[]') > 0 THEN EXIT;
CurrentObject := COPYSTR(Value,STRPOS(Value,'{')+1,STRPOS(Value,':'));
Value := COPYSTR(Value,STRLEN(CurrentObject)+1);
FormatChar := '"{}';
CurrentObject := DELCHR(CurrentObject,'=',FormatChar);
CurrentObject := DELCHR(CurrentObject,'=',':');
Value := DELCHR(Value,'=','{}');
l := STRLEN(Value);
WHILE p < l DO BEGIN
ValuePair := SELECTSTR(x,Value); // get comma separated pairs of values and element names
qPos := STRPOS(ValuePair, '[');
IF qPos > 0 THEN
ValuePair := COPYSTR(ValuePair, qPos+1);
qPos := STRPOS(ValuePair, 'webOrderHeader');
IF qPos > 0 THEN BEGIN
ValuePair := COPYSTR(ValuePair, qPos+16);
CurrentObject := 'webOrderHeader';
END;
p := STRPOS(Value,ValuePair)+STRLEN(ValuePair); // move pointer to the end of the current pair in Value
ValuePair := DELCHR(ValuePair,'=',FormatChar);
CurrentElement := COPYSTR(ValuePair,1,STRPOS(ValuePair,':'));
CurrentElement := DELCHR(CurrentElement,'=',':');
CurrentValue := COPYSTR(ValuePair,STRPOS(ValuePair,':'));
CurrentValue := DELCHR(CurrentValue,'=',':');
CASE CurrentObject OF
'webOrderHeader':
CASE CurrentElement OF
'OrderNo':
// must be a new record or lookup the existing record
BEGIN
WebOrderHeader.SETFILTER("Order No.",CurrentValue);
EVALUATE(RecId,CurrentValue);
IF NOT WebOrderHeader.FINDFIRST THEN BEGIN
WebOrderHeader.INIT;
WebOrderHeader."Order No." := FORMAT(RecId);
WebOrderHeader.INSERT;
END;
END;
'OrderDate':
//EVALUATE(WebOrderHeader."Order Date",CurrentValue);
WebOrderHeader."Order Date" := TODAY;
'TotalPrice':
BEGIN
EVALUATE(WebOrderHeader."Total Price",CurrentValue);
WebOrderHeader.MODIFY;
END;
'WebOrderLine': // End of Header
CurrentObject := 'webOrderLine';
END;
'webOrderLine':
CASE CurrentElement OF
'No':
// must be a new record or lookup the existing record
BEGIN
EVALUATE(RecId,CurrentValue);
WebOrderLine.SETRANGE("Line No.",RecId);
IF NOT WebOrderLine.FINDFIRST THEN BEGIN
WebOrderLine.INIT;
WebOrderLine."Line No." := RecId;
WebOrderLine.INSERT;
END;
END;
'OrderNo':
WebOrderLine."Order No." := CurrentValue;
'ItemNo':
WebOrderLine."Item No." := CurrentValue;
'Quantity':
EVALUATE(WebOrderLine.Quantity,CurrentValue);
'UnitPrice':
BEGIN
EVALUATE(WebOrderLine."Unit Price",CurrentValue);
WebOrderLine.MODIFY;
END;
END;
END;
x := x+1; // next pair
END;
Regards
Can you please tell me what this line is doing?
IF qPos > 0 THEN
BEGIN
ValuePair := COPYSTR(ValuePair, qPos+16); //this one?
CurrentObject := 'webOrderHeader';
END;
Zohaib Ahmed
Dynamics NAV ERP Technical Consultant.
please like / agree / verify my answer, if it was helpful for you. thanks.
{"webOrderHeader":{"OrderNo":1,"OrderDate":"2017-01-18T00:00:00","TotalPrice":100.0},"webOrderLine":[{"No":1,"OrderNo":1,"ItemNo":"ABC1","Quantity":1,"UnitPrice":50.0},{"No":2,"OrderNo":1,"ItemNo":"ABC1","Quantity":1,"UnitPrice":50.0}]},{"webOrderHeader":{"OrderNo":2,"OrderDate":"2017-01-19T00:00:00","TotalPrice":100.0},"webOrderLine":[{"No":3,"OrderNo":2,"ItemNo":"ABC1","Quantity":1,"UnitPrice":100.0}]},{"webOrderHeader":{"OrderNo":3,"OrderDate":"2017-01-18T00:00:00","TotalPrice":100.0},"webOrderLine":[{"No":4,"OrderNo":3,"ItemNo":"XYZ1","Quantity":1,"UnitPrice":100.0}]},{"webOrderHeader":{"OrderNo":4,"OrderDate":"2017-01-20T00:00:00","TotalPrice":100.0},"webOrderLine":null},{"webOrderHeader":{"OrderNo":5,"OrderDate":"2017-01-21T00:00:00","TotalPrice":100.0},"webOrderLine":null}]
Here is my code:
LOCAL TestParseJsonResponse(Value : Text)
p := 0;
x := 1;
IF STRPOS(Value,'[]') > 0 THEN EXIT;
CurrentObject := COPYSTR(Value,STRPOS(Value,'{')+1,STRPOS(Value,':'));
Value := COPYSTR(Value,STRLEN(CurrentObject)+1);
FormatChar := '"{}';
CurrentObject := DELCHR(CurrentObject,'=',FormatChar);
CurrentObject := DELCHR(CurrentObject,'=',':');
Value := DELCHR(Value,'=','{}');
l := STRLEN(Value);
WHILE p < l DO
BEGIN
ValuePair := SELECTSTR(x,Value); // get comma separated pairs of values and element names
qPos := STRPOS(ValuePair, '[');
IF qPos > 0 THEN
ValuePair := COPYSTR(ValuePair, qPos+1);
qPos := STRPOS(ValuePair, 'webOrderHeader');
IF qPos > 0 THEN
BEGIN
ValuePair := COPYSTR(ValuePair, qPos+16);
CurrentObject := 'webOrderHeader';
END;
p := STRPOS(Value,ValuePair) + STRLEN(ValuePair); // move pointer to the end of the current pair in Value
ValuePair := DELCHR(ValuePair,'=',FormatChar);
CurrentElement := COPYSTR(ValuePair,1,STRPOS(ValuePair,':'));
CurrentElement := DELCHR(CurrentElement,'=',':');
CurrentValue := COPYSTR(ValuePair,STRPOS(ValuePair,':'));
CurrentValue := DELCHR(CurrentValue,'=',':');
CASE CurrentObject OF
'webOrderHeader':
CASE CurrentElement OF
'OrderNo': // must be a new record or lookup the existing record
BEGIN
WebOrderHeader.SETFILTER("Order No.",CurrentValue);
EVALUATE(RecId,CurrentValue);
IF NOT WebOrderHeader.FINDFIRST THEN
BEGIN
WebOrderHeader.INIT;
WebOrderHeader."Order No." := FORMAT(RecId);
WebOrderHeader.INSERT;
END;
END;
'OrderDate':
WebOrderHeader."Order Date" := TODAY;
'TotalPrice':
BEGIN
EVALUATE(WebOrderHeader."Total Price",CurrentValue);
WebOrderHeader.MODIFY;
CurrentObject := 'webOrderLine';
END;
END;
'webOrderLine':
CASE CurrentElement OF
'No': // must be a new record or lookup the existing record
BEGIN
EVALUATE(RecId,CurrentValue);
WebOrderLine.SETRANGE("Line No.",RecId);
IF NOT WebOrderLine.FINDFIRST THEN
BEGIN
WebOrderLine.INIT;
WebOrderLine."Line No." := RecId;
WebOrderLine.INSERT;
END;
END;
'OrderNo':
WebOrderLine."Order No." := CurrentValue;
'ItemNo':
WebOrderLine."Item No." := CurrentValue;
'Quantity':
EVALUATE(WebOrderLine.Quantity,CurrentValue);
'UnitPrice':
BEGIN
EVALUATE(WebOrderLine."Unit Price",CurrentValue);
WebOrderLine.MODIFY;
END;
END;
END;
x := x+1; // next pair
END;
Zohaib Ahmed
Dynamics NAV ERP Technical Consultant.
please like / agree / verify my answer, if it was helpful for you. thanks.
It's hardcoded, but it's to skip webOrderHeader plus the ", total 16 chars
qPos := STRPOS(ValuePair, 'webOrderHeader');
Regards
You can do this changes:
>>FormatChar := '"{}';
<<FormatChar := '"{}]';
>> qPos := STRPOS(ValuePair, '[');
>> IF qPos > 0 THEN
>> ValuePair := COPYSTR(ValuePair, qPos+1);
<< qPos := STRPOS(ValuePair, '[');
<< IF qPos > 0 THEN BEGIN
<< ValuePair := COPYSTR(ValuePair, qPos+1);
<< CurrentObject := 'webOrderLine';
<< END;
Regards.
Can you please tell me the overall algorithm of this logic?
Zohaib Ahmed
Dynamics NAV ERP Technical Consultant.
please like / agree / verify my answer, if it was helpful for you. thanks.
This is all the code:
LOCAL ParseJSONResponse(Value : Text)
p := 0;
x := 1;
IF STRPOS(Value,'[]') > 0 THEN EXIT;
CurrentObject := COPYSTR(Value,STRPOS(Value,'{')+1,STRPOS(Value,':'));
Value := COPYSTR(Value,STRLEN(CurrentObject)+1);
FormatChar := '"{}]';
CurrentObject := DELCHR(CurrentObject,'=',FormatChar);
CurrentObject := DELCHR(CurrentObject,'=',':');
Value := DELCHR(Value,'=','{}');
l := STRLEN(Value);
WHILE p < l DO BEGIN
ValuePair := SELECTSTR(x,Value); // get comma separated pairs of values and element names
qPos := STRPOS(ValuePair, '[');
IF qPos > 0 THEN BEGIN
ValuePair := COPYSTR(ValuePair, qPos+1);
CurrentObject := 'webOrderLine';
END;
qPos := STRPOS(ValuePair, 'webOrderHeader');
IF qPos > 0 THEN BEGIN
ValuePair := COPYSTR(ValuePair, qPos+16);
CurrentObject := 'webOrderHeader';
END;
p := STRPOS(Value,ValuePair)+STRLEN(ValuePair); // move pointer to the end of the current pair in Value
ValuePair := DELCHR(ValuePair,'=',FormatChar);
CurrentElement := COPYSTR(ValuePair,1,STRPOS(ValuePair,':'));
CurrentElement := DELCHR(CurrentElement,'=',':');
CurrentValue := COPYSTR(ValuePair,STRPOS(ValuePair,':'));
CurrentValue := DELCHR(CurrentValue,'=',':');
CASE CurrentObject OF
'webOrderHeader':
CASE CurrentElement OF
'OrderNo':
// must be a new record or lookup the existing record
BEGIN
WebOrderHeader.SETFILTER("Order No.",CurrentValue);
EVALUATE(RecId,CurrentValue);
IF NOT WebOrderHeader.FINDFIRST THEN BEGIN
WebOrderHeader.INIT;
WebOrderHeader."Order No." := FORMAT(RecId);
WebOrderHeader.INSERT;
END;
END;
'OrderDate':
//EVALUATE(WebOrderHeader."Order Date",CurrentValue);
WebOrderHeader."Order Date" := TODAY;
'TotalPrice':
BEGIN
EVALUATE(WebOrderHeader."Total Price",CurrentValue);
WebOrderHeader.MODIFY;
END;
'WebOrderLine': // End of Header
BEGIN
CurrentObject := 'webOrderLine';
END;
END;
'webOrderLine':
CASE CurrentElement OF
'No':
// must be a new record or lookup the existing record
BEGIN
EVALUATE(RecId,CurrentValue);
WebOrderLine.SETRANGE("Line No.",RecId);
IF NOT WebOrderLine.FINDFIRST THEN BEGIN
WebOrderLine.INIT;
WebOrderLine."Line No." := RecId;
WebOrderLine.INSERT;
END;
END;
'OrderNo':
WebOrderLine."Order No." := CurrentValue;
'ItemNo':
WebOrderLine."Item No." := CurrentValue;
'Quantity':
EVALUATE(WebOrderLine.Quantity,CurrentValue);
'UnitPrice':
BEGIN
EVALUATE(WebOrderLine."Unit Price",CurrentValue);
WebOrderLine.MODIFY;
END;
END;
END;
x := x+1; // next pair
END;
Last thing, can you please share the algorithm which you have followed for this?
Zohaib Ahmed
Dynamics NAV ERP Technical Consultant.
please like / agree / verify my answer, if it was helpful for you. thanks.
how is FormatChar variable is working?
Also what is hte purpose of qPos variable??
Zohaib Ahmed
Dynamics NAV ERP Technical Consultant.
please like / agree / verify my answer, if it was helpful for you. thanks.
// Here I tested if the ValuePair have a [, meaning is the start of a webOrderLine
// So I skip this part and chabge the CurrentObject.
qPos := STRPOS(ValuePair, '[');
IF qPos > 0 THEN BEGIN
ValuePair := COPYSTR(ValuePair, qPos+1);
CurrentObject := 'webOrderLine';
END;
//Here I tested if ValuePair have webOrderHeader, so is the start of a
// webOrderHeader and I skip this part too.
qPos := STRPOS(ValuePair, 'webOrderHeader');
IF qPos > 0 THEN BEGIN
ValuePair := COPYSTR(ValuePair, qPos+16);
CurrentObject := 'webOrderHeader';
END;
The rest it's yours.
Regards
I'm using FormatChar because is in your original code and I understand must be a text constant but how I don't know its valueI created a text variable and asigned the value of '{}' and later I changed to '{}]', to avoid a problem with the CurrentValue.
And the purpose of qPos it's only to check if the value tested with STRPOS exists and in this case it's used to skip that value.
Regards
Zohaib Ahmed
Dynamics NAV ERP Technical Consultant.
please like / agree / verify my answer, if it was helpful for you. thanks.
If you'd like to do that then have a look at Codeunit 1273 "Get Json Structure" (in NAV 2016) - it has a function called JsonToXML()
Blog - rockwithnav.wordpress.com/
Twitter - https://twitter.com/RockwithNav
Facebook - https://facebook.com/rockwithnav/
check this resource here:
http://www.dynamics.is/?p=2303
including code-example for download.
Cheers,
Hannes
Just create a .NET class of the object you want to deserialize, and use Json.NET to deserialize the repsonse. 4 lines of NAV code, max!
JSON format is more common and more efficient now a days. Converting it into xml will cause an overhead.
I have create a manual parser for json. btw a good way is to create custom DLL and parse json there and return the extracted data back to NAV.
Zohaib Ahmed
Dynamics NAV ERP Technical Consultant.
please like / agree / verify my answer, if it was helpful for you. thanks.
@EvR i know about that i have created one for my old client but i am avoiding .NET as last time when i was deploying the solution i have faced a lot of challenges specially when i have to reschedule the job using JOB Queues. That is why i was trying to parse the JSON in C/AL.
Btw i have solved the issue thanks to @ftornero .
Zohaib Ahmed
Dynamics NAV ERP Technical Consultant.
please like / agree / verify my answer, if it was helpful for you. thanks.
Zohaib Ahmed
Dynamics NAV ERP Technical Consultant.
please like / agree / verify my answer, if it was helpful for you. thanks.
I have seen this code and try to use it but it is using the "Data Exch. Field" table. and when i try to parse my json response it is giving some error. So after trying to understand that code i thought creating custom parse is the good way to go.
Zohaib Ahmed
Dynamics NAV ERP Technical Consultant.
please like / agree / verify my answer, if it was helpful for you. thanks.