Dynamics Community Forum Thread Details

Custom API Query 2 tables one to many relationship with AL Code

Hello

I am creating a Query to use as a custom api.

query 50104 producersQuery

{

QueryType = API;

APIPublisher = 'testcompany';

APIGroup = 'demo';

APIVersion = 'v2.0';

EntityName = 'producer';

EntitySetName = 'producers';

elements

{

dataitem(Customer;Customer)

{

DataItemTableFilter = IsProducer = Const(True), IsActive = Const(True);

column(Id; SystemId) { }

column(customerNumber;"No."){}

column(producerName; Name) {}

column(IsActive;IsActive){}

dataitem(ShipToAdress;"Ship-to Address")

{

DataItemLink = "Customer No." = Customer."No.";

SqlJoinType = InnerJoin;

column(ShipToId;SystemId){}

column(Address;Address){}

column(Address_2;"Address 2"){}

column(City;City){}

column(Country_Region_Code;"Country/Region Code"){}

column(Post_Code;"Post Code")

}

api.businesscentral.dynamics.com/.../$metadata

the result i get is

{
"@odata.context": "">api.businesscentral.dynamics.com/.../$metadata
"value": [
{
"id": "a9c4c4d4-4b34-eb11-bb70-000d3a245cd7",
"customerNumber": "AB1051",
"producerName": "Coyote Flats",
"isActive": true,
"shipToId": "8c1d14be-be55-eb11-89f8-000d3a84b3f8",
"customerNo": "AB1051",
"address": "Box 54",
"auxiliaryIndex1": "BOX54"
},
{
"id": "a9c4c4d4-4b34-eb11-bb70-000d3a245cd7",
"customerNumber": "AB1051",
"producerName": "Coyote Flats",
"isActive": true,
"shipToId": "abf4b5e0-4b34-eb11-bb70-000d3a245cd7",
"customerNo": "AB1051",
"address": "6455 Macleod Trail SW",
"auxiliaryIndex1": "CALGARY"
}
]
}

Is there anything I need to do to get result as such

{
"@odata.context": "">api.businesscentral.dynamics.com/.../$metadata
"value": [
{
"id": "a9c4c4d4-4b34-eb11-bb70-000d3a245cd7",
"customerNumber": "AB1051",
"producerName": "Coyote Flats",
"isActive": true,
"shippingAdresses": [
{
"shipToId": "8c1d14be-be55-eb11-89f8-000d3a84b3f8",
"customerNo": "AB1051",
"address": "Box 54",
"auxiliaryIndex1": "BOX54"
},
{
"shipToId": "abf4b5e0-4b34-eb11-bb70-000d3a245cd7",
"customerNo": "AB1051",
"address": "6455 Macleod Trail SW",
"auxiliaryIndex1": "CALGARY"
}
]
}
]
}