Dynamics Community Forum Thread Details

How to create an API query/page/codeunit with the following format

Hi All,

I’m actually trying to export all the released sales/purchase orders headers and it’s lines to JSON using API. I want the below format where header repeats only once.
{
“salesheaderNO”: “{{No}}”,
“salesOrderLines”: [
{
“itemId”: “{{itemId}}”,
“quantity”: 5
},
{
“itemId”: “{{itemId}}”,
“quantity”: 3
},
{
“itemId”: “{{itemId}}”,
“quantity”: 8
}
]
}{
“customerId2”: “{{customerId2}}”,
“salesOrderLines”: [
{
“itemId2”: “{{itemId2}}”,
“quantity”: 5
},
{
“itemId2”: “{{itemId2}}”,
“quantity”: 3
},
{
“itemId2”: “{{itemId2}}”,
“quantity”: 8
}
]
}

I have tried it in Query like below :
query 50111 “RelPurchaseOrders”
{
Caption = ‘Purchase orders’;
QueryType = API;
APIPublisher = ‘Test’;
EntitySetName = ‘PurchaseOrder’;
EntityName = ‘Test’;
APIGroup = ‘TestPOOrders’;
APIVersion = ‘v1.0’;

elements
{

    dataitem(Purchase_Header; "Purchase Header")
    {
        column(Document_Type; "Document Type") { }
        column(No_; "No.") { }
        column(Buy_from_Vendor_Name; "Buy-from Vendor Name") { }
        column(Vendor_Order_No_; "Vendor Order No.") { }
        column(Location_Code; "Location Code") { }
        column(Due_Date; "Due Date") { }

        dataitem(Purchase_Line; "Purchase Line")
        {
            column(Document_No_; "Document No.") { }
            column(No_; "No.") { }
            column(Type; Type) { }
            column(Quantity; Quantity) { }
            column(Location_Code; "Location Code") { }
            column(Unit_of_Measure; "Unit of Measure") { }
            column(Unit_Cost; "Unit Cost") { }
        }
    }
}

}

but the format I’m getting is header and lines are getting combined and they are not sepeated as I wanted above
“value”: [
{
“Document_Type”: “Order”,
“No_”: “106002”,
“Buy_from_Vendor_Name”: "A R Enterprise ",
“Vendor_Order_No_”: “”,
“Location_Code”: “EMIRATES”,
“Due_Date”: “2023-03-13”

        "Document_No_": "106002",
        "No_": "1006",
        "Type": "Item",
        "Quantity": 500,
        "Location_Code": "EMIRATES",
        "Unit_of_Measure": "Kilo",
        "Unit_Cost": 27.67,
        "AuxiliaryIndex1": "Order",
        "AuxiliaryIndex2": 10000
 
        "Document_No_": "106002",
        "No_": "FREIGHT",
        "Type": "Charge (Item)",
        "Quantity": 1,
        "Location_Code": "EMIRATES",
        "Unit_of_Measure": "Kilo",
        "Unit_Cost": 540,
        "AuxiliaryIndex1": "Order",
        "AuxiliaryIndex2": 20000
  
    },

If you see header is once but both the lines are combined I’ve just entered space to show you clearly. Let me know how can I achieve the required format?

Categories:

Dynamics 365 Business Central

Quick Links