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

Business Central/NAV
1

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?

2

Hi @knsaicharan,

If you want to create a JSON with a header-lines structure, you can’t use a Query, because it will give you a “record” type structure. You need a page API for header and a listpart page for lines.

page 50000 "My Sales Order API"
{
    APIGroup = 'myApiGroup';
    APIPublisher = 'mySelf';
    APIVersion = 'v1.0';
    Caption = 'mySalesOrder', Locked = true;
    EntityName = 'mySalesOrder';
    EntitySetName = 'mySalesOrders';
    PageType = API;
    SourceTable = "Sales Header";
    SourceTableView = where("Document Type" = const(Order));

    layout
    {
        area(content)
        {
            repeater(General)
            {
                field(Document_Type; "Document Type") { }
                field(No_; "No.") { }
                field(Buy_from_Vendor_Name; "Buy-from Vendor Name") { }
                field(Vendor_Order_No_; "Vendor Order No.") { }
                field(Location_Code; "Location Code") { }
                field(Due_Date; "Due Date") { }
                part(Purchase_Line; "My Sales Line ListPart")
                {
                    EntityName = 'myPurchaseLine';
                    EntitySetName = 'myPurchaseLines';
                    SubPageLink = "Document No." = field("No.");
                }
            }
        }
    }
}

You must create the “My Sales Line ListPart” list part page (this must be a “ListPart” PageType, not an API one), and when you call the API entity, you must add the parameter “$expand=myPurchaseLines” to get the lines with every header record.

3

Sorry but I mixed sales and purchase… but I think you can get the idea.

4

Hi @pjllaneras
sometimes I get this error
“error”: {
“code”: “BadRequest”,
“message”: “Could not find a property named ‘myPurchaseLines’ on type ‘Microsoft.NAV.myPurchaseOrder’. CorrelationId: e484dd0b-c29a-4660-9481-871df4644fd1.”
}
even though I’ve published and created a webservice with “Purchase List Part” which is not an API list part page

page 50121 “Purchase List Part”
{
PageType = ListPart;
Caption = ‘Purchase Order List’;
SourceTable = “Purchase Line”;
layout
{
area(Content)
{
repeater(General)
{
field(“No.”; Rec.“No.”) { }
field(“Document No.”; Rec.“Document No.”) { }
field(Quantity; Rec.Quantity) { }
field(Type; Rec.Type) { }
}

    }
}

}

May I know why?

6

Hi @knsaicharan,

Try to change the part section in the page API:

                part(myPurchaseLines; "My Sales Line ListPart")
                {
                    EntityName = 'myPurchaseLine';
                    EntitySetName = 'myPurchaseLines';
                    SubPageLink = "Document No." = field("No.");
                }

I think this will solve the problem. Name the part section as the EntitySetName. This is something that is not well documented, but I think this way will solve your problem.

7

Hi @pjllaneras
It works now. I want to add filter totals by date in my api, is it possible?
image

8

Create the field as a regular field in the API page (field(dateFilter ; “Date Filter”) { }). When you do the request, add this parameter to URL: $filter=dateFilter eq 2023-12-12

Using Filters with API calls - Business Central | Microsoft Learn

Entering Criteria in Filters - Business Central | Microsoft Learn

1 Like
9

Hi @pjllaneras
Great man thankyou so much.
One last question can we create a flag so that when we get the data 2nd time the previous records should not be shown I mean once we get the record the particular records should be flagged/marked then then next time when we get the data only the records which are not marked should be fetched.

10

Hi @knsaicharan,

You’re wellcome.

Yes you can… try to create your own custom field for this, filter on SourceTableView en the API page, and in the OnAfterGetCurrentRecord, change the value of your new custom field.

I prefer using a 2 pass request… the first request I get the data in a normal way and the second request I mark the records as processed/exported (or whatever you want to name your flag field)… if the field is shown in the API page you can use the “PATCH” method to update the field (check microsoft update example for customer: Update customers - Business Central | Microsoft Learn), or you can create an action ( Creating and Interacting with an OData V4 Bound Action - Business Central | Microsoft Learn) in the API page to do the trick. In both cases, the actions must be done record by record, but you can use the batch request call ( Using OData Transactional $batch Requests - Business Central | Microsoft Learn)… bear in mind that there is maximum of 100 requests in a batch call (this is not documented, but I reached the limit and the systems fails with an error).

11

Hi @pjllaneras
I have tried but unable to get it. Can you explain to me clearly an example if possible? It would be so helpful.

12

HI @knsaicharan,

The “simple” option, si really easy, add the following code in your API page:

    trigger OnAfterGetCurrRecord()
    var
        PurchaseHeader: Record "Purchase Header";
    begin
        PurchaseHeader := Rec;
        PurchaseHeader.Exported := true; // This is your customized field, name it as you want
        PurchaseHeader.Modify();
    end;

You must modify the SourceTableView property in the API page (don’t replace other filters you need):

SourceTableView = where("Document Type" = const(Order), Exported = const(false));

With this code, everytime you get some data from this API page, records will be marked as exported and the next time, records served previously will be excluded and not served next time.

The second option requires more work, but in the request part, not in the API itself (create the custom field and add the action or the field in the API page). First try this and let me know if it works.

13

Hi @pjllaneras
yeah, that was easy it works but I think I wasn’t clear about my question previously.
I have created another API in which only has PK values and Exported boolean field(custom field) which I created. Initially, it would be false. So the requirement is after fetching the RelPurchaseOrder I want to then modify the Exported to True based on Purchase order No.

page 50130 “Response API”
{
APIGroup = ‘myApiGroup’;
APIPublisher = ‘mySelf’;
APIVersion = ‘v1.0’;
Caption = ‘myPurchaseOrder’, Locked = true;
EntityName = ‘myPurchaseOrder’;
EntitySetName = ‘myPurchaseOrders’;
PageType = API;
SourceTable = “Purchase Header”;
SourceTableView = where(“Document Type” = const(Order));

layout
{
    area(Content)
    {
        group(GroupName)
        {
            field(Id; Rec.SystemId)
            {
                Caption = 'Id';
                Editable = false;
            }
            field("Document_Type"; Rec."Document Type"::Order)
            {

            }

            field("No"; Rec."No.")
            {
            }

            field(Exported; Rec.Exported)
            {

            }
        }
    }
}

}

I want to use PATCH and then modify the below-fetched orders at a time by putting them in the Postman body, is that possible?
“Id”: “a66f5923-39e0-ed11-a7c8-000d3a3df77f”,
“Document_Type”: “Order”,
“No”: “106224”,
“Exported”: true
},
{
“@odata.etag”: “W/"JzIwOzE0NTM3NjE1ODc2Mzc2Mjg2MzQ0MTswMDsn"”,
“Id”: “e01ec59a-cbbf-ed11-9a88-000d3af0eae5”,
“Document_Type”: “Order”,
“No”: “106026”,
“Exported”: true
},
{
“Id”: “6efb2a6e-9ac8-ed11-9a78-0022486ea700”,
“Document_Type”: “Order”,
“No”: “106067”,
“Exported”: false
}

14

You can do PATCH request with next info:

URI: [base url]/api/mySelf/myApiGroup/v1.0/companies([company id])/myPurchaseOrders(0,‘106224’)
Body: { “Exported”: true }
Header: If-Match: *

2 relevant points:

  • field(Id; Rec.SystemId) is not necessary, you can use primary key and set the values separeted by comma: (0,‘106224’)
    If you want to use this field instead of primary key, you must add “ODataKeyFields = SystemId” in page porperties
  • Header: if you skip “If-Match” header, you will receive an error

Best regards.

15

Hi @pjllaneras
I have used as mentioned but getting below error. I have included
URI: [base url]/api/mySelf/myApiGroup/v1.0/companies([company id])/myPurchaseOrders(0,‘106224’)
Body: { “Exported”: true }
Header: If-Match: * and Content-Type:application/json

"error": {
    "code": "BadRequest_NotFound",
    "message": "Bad Request - Error in query syntax."
}

}

But I’m able to change when I use Id = e01ec59a-cbbf-ed11-9a88-000d3af0eae5 instead I want to use one or more order No’s and change all at once.
URI: [base url]/api/mySelf/myApiGroup/v1.0/companies([company id])/myPurchaseOrders(e01ec59a-cbbf-ed11-9a88-000d3af0eae5) this works though

16

To update “Exported” field in batch, you must use a batch request:

Using OData transactional $batch requests - Business Central | Microsoft Learn

Batch calls with Business Central APIs (1) – Basic operation – Kauffmann @ Dynamics 365 Business Central

Please, bear in mind that a maximum of 100 operations are allowed in a batch request.

1 Like
17

Hi @pjllaneras
While fetching the purchase orders using GET can I limit the number of records?

For example: If I have 5000 records PO I want to fetch 500 at a time and if I fetch first 500 then next how the system would know the first 500 are fetched and next 500 it should return is it possible using previous cursor I’d, current and next?

18

Hi @knsaicharan,

First, API has a limit of records that can return in a request, you can consult this limitation and others in https://learn.microsoft.com/en-us/dynamics365/business-central/dev-itpro/administration/operational-limits-online

If you want to get N records, you can use the “$top” parameter in your request (https://learn.microsoft.com/en-us/dynamics365/business-central/dev-itpro/webservices/odata-client-performance#limiting-the-set-filter-or-top-if-youre-using-an-expensive-expand-statement). Perhaps you will have to use “$skip” parameter.

At the end of the json you will receive after the request a field called “@odata.nextlink”, this field contains the next request you must do to get the next set of records.

I have never tried to get only a set of N records, but taking in consideration that BC API follow oData standard, you can try other options: Basic Tutorial · OData - the Best Way to REST

19

Hi @pjllaneras
Can I use two filters at a time, I want to expand the lines and get the records where SystemCreatedAt > 2023-12-12

https://api.businesscentral.dynamics.com/v2.0/d8f36038-1f93-4543-affc-5dc92b6ee871/Sandbox03/api/v2.0/companies(6325c2d6-c92a-eb11-846e-000d3ac98e05)/salesOrders?$expand=salesOrderLines and ?$filter=SystemCreatedAt > 2023-12-12

But I’m getting this error
{
“error”: {
“code”: “BadRequest”,
“message”: “Term ‘releasePurchaseLines and ?$filter=No eq ‘202401323’’ is not valid in a $select or $expand expression. CorrelationId: 76cc1d38-7594-4512-8daa-92e2ed6b4cf0.”
}
}

As SystemCreatedAt is datetime field how can I fetch SO based on particular filter like >,< or in between some dates?

20

Hi @knsaicharan,

This question is closed, in the future, open a new question and put a reference to the original question if you think is necessary.

Yes, you can use more than 1 filter… please check this page: Using Filter Expressions in OData URIs - Business Central | Microsoft Learn

Your OData query must be:

https://api.businesscentral.dynamics.com/v2.0/d8f36038-1f93-4543-affc-5dc92b6ee871/Sandbox03/api/v2.0/companies(6325c2d6-c92a-eb11-846e-000d3ac98e05)/salesOrders?$expand=salesOrderLines&$filter=SystemCreatedAt gt 2023-12-12T23:59:59.999Z

Few notes about the query:

You use ?#filter, that is incorrect. The symbol “?” is the separator between the base Url and its parameters, it must be used only once, in this case, the parameter is “$filter”.

To concatenate parameters you must use “&”. $expand is a parameter and $filter is another parameter: [base url]/?$expand=xxxxx&$fitter=yyyyyy

For the datetime filter format, you can get the correct format using “format” instruction, with last parameter “9”:

MyDateTimeFilter := Format(CurrentDateTime, 0, 9);

In the filter part, you cannot use symbols “<” , “>” or “=” to form a filter, you must use gt, lt or eq. You can concatenate filters using “and” and/or “or”. For instance:

$filter=SystemCreatedAt ge 2023-12-12T00:00:00Z and SystemCreatedAt le 2023-12-12T23:59:59.999Z

This filter will return all records modified at 2023-12-12: ge → greater or equal; le → lower or equal.

Last but not less, you can only filter fields that are in the page. I suspect that SystemCreatedAt is not in the page, because system puts the first letter in lowercase when you use a page API.

1 Like