Small and medium business | Business Central, N...

Answered

Reading multiple line items from a nested-JSON Sales Invoice API and writing them into a table in Business Central (via AL code)

(0) Share

Report

Posted on by rtechc

Having referred couple of videos on AL with JSON and reading 1-2 community Q&A here, I still haven't figured out the correct way to read multiple line items from a Invoice record, which is being fetched into Business Central via an API.

Notes:

1. Read from the Response text, the first node, which starts with 'list', Invoice header is entered successfully

2. Each header now has multiple "line_items" associated

3. For some reason, my AL code is unable to fetch/read/recognize "line_items" array from the Response Text, but successfully recognizes "list" array. Only the header record in BC is created successfully with no lines associated

Present Outcome: Can read & insert header values, but cannot locate/read line values from the API, due to nested API

Expected Outcome: To be able to fetch each line_items "ID" and corresponding line_items data in a loop and insert all the line_items values into a table, whenever each/single header is inserted

Below Images showcase, 1-2 AL code trials which I have conducted, all with no positive results. Also pasted image of the Incoming JSON structure

I know I am doing something incorrect, either logically or syntactically. Community, please advise / assist

4762.Img1.png

pastedimage1671350010336v1.png_2D00_640x480.png

Stefano Demiliani waldo Marco Mels ajkauffmann yzhums Nitin Verma

All responses (4)

Answers (1)

rtechc 20 on at

Like (1)

Report

RE: Reading multiple line items from a nested-JSON Sales Invoice API and writing them into a table in Business Central (via AL code)

Thankyou Arend-Jan, you are a superstar as always. I spent a couple days debugging on this silly logic error
Verified answer

ajkauffmann 117 on at

Like (1)

Report

RE: Reading multiple line items from a nested-JSON Sales Invoice API and writing them into a table in Business Central (via AL code)

You are pretty close.

Function CreateSalesDocuments(TransToken, CGBSalesHeader) receives the JsonToken that holds the invoice. You are already reading the id with 'invoice.id'. The array of line_items can be retrieved with TransToken.SelectToken('invoice.line_items', JsonToken).

Then use JsonToken.AsArray() and loop through the resulting array.
Suggested answer

DAnny3211 9,272 Super User 2024 Season 1 on at

Like (1)

Report

RE: Reading multiple line items from a nested-JSON Sales Invoice API and writing them into a table in Business Central (via AL code)

hi

look this

community.dynamics.com/.../multiple-layers-json-reading

DAniele

Suggested answer

Nitin Verma

21,091 Super User 2024 Season 1 on at

Like (0)

Report

RE: Reading multiple line items from a nested-JSON Sales Invoice API and writing them into a table in Business Central (via AL code)

Hi,

Try to read and consume as per below request I did.

if not JToken.ReadFrom(serviceResponse) then
Error('Invalid Label response.');
if JToken.ReadFrom(serviceResponse) then
if JToken.IsObject then begin
jsObject := JToken.AsObject();
if jsObject.get('response', JToken) then begin
labelid := GetTokenValue(JToken, 'id');
jorder := JToken.AsObject();
if jorder.Get('order', JToken) then begin
trackingno := GetTokenValue(JToken, 'tracking_number');
labelstate := GetTokenValue(JToken, 'state');
Jparcel := JToken.AsObject();
if Jparcel.get('parcels', JParcelToken) then
if JParcelToken.IsArray then begin
jsArray := JParcelToken.AsArray();
for i := 0 to jsArray.Count - 1 do begin
jsArray.get(i, JParcelToken);
if JParcelToken.IsObject then begin
jsObject := JParcelToken.AsObject();
if not SelectJsonToken(jsObject, 'label_number', false).AsValue().IsNull then begin
OderResponse.Init();
OderResponse."API Type" := 'Label';
OderResponse."API Date Time" := CurrentDateTime;
OderResponse.ID := labelid;
OderResponse.tracking_number := trackingno;
OderResponse.state := labelstate;
OderResponse."Courier Label No." := SelectJsonToken(jsObject, 'label_number', false).AsValue().AsText();
jdocument := JToken.AsObject();
if jdocument.get('documents', JToken) then begin
JshipLabel := JToken.AsObject();
if JshipLabel.get('shipping_label', JToken) then
labelurl := GetTokenValue(JToken, 'url');
end;
OderResponse."Label URL" := labelurl;
OderResponse."Manifest No." := ManifestNo;
OderResponse."Manifest Line No." := Lineno;
end;
clear(client);
clear(response);
clear(inStrm);
if client.Get(OderResponse."Label URL", response) then begin
response.Content.ReadAs(inStrm);
OderResponse."Label URL File".ImportStream(inStrm, OderResponse."Courier Label No.", 'application/pdf');

end;
OderResponse.Insert(true);
end;
end;
end;

Reading multiple line items from a nested-JSON Sales Invoice API and writing them into a table in Business Central (via AL code)

Helpful resources

News and Announcements

Quick Links

December Spotlight Star - Muhammad Affan

Top 10 leaders for November!

Tips for Writing Effective Verified Answers

Leaderboard

Featured topics

Product updates

Reading multiple line items from a nested-JSON Sales Invoice API and writing them into a table in Business Central (via AL code)

Helpful resources

News and Announcements

Quick Links

December Spotlight Star - Muhammad Affan

Top 10 leaders for November!

Tips for Writing Effective Verified Answers

Subscribe to this forum!

Select categories

Leaderboard

Featured topics

Product updates