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

API development - combine data from 2 tables

(2) Share

Report

Posted on by Samantha73

3,297

Hi Everyone

Can a API combine data from two table for example sales posted invoice line and posted credit line so the total Qty by item is sum of invoice qty - Credit Qty?

Categories:

Dynamics 365 Business Central

Microsoft Dynamics NAV

I have the same question (0)

All responses (6)

Answers (0)

Suggested answer

Jainam M. Kothari 15,666 Super User 2026 Season 1 on at

Like (1)

Report
Copy link

Link copied!

May this can help : https://learn.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/devenv-query-links-joins

https://ivansingleton.dev/how-to-use-the-query-object-as-an-api-in-business-central/

Was this reply helpful? Yes No
Suggested answer

Khushbu Rajvi. 21,573 Super User 2026 Season 1 on at

Like (1)

Report
Copy link

Link copied!

you can create an API query in Business Central that combines data from the Sales Invoice Line and Sales Cr. Memo Line tables to calculate the net quantity per item.

Something like below.

query 50100 "Sales Net Qty API"
{
APIPublisher = 'yourPublisher';
APIGroup = 'yourGroup';
APIVersion = 'v1.0';
EntityName = 'SalesNetQty';
EntitySetName = 'SalesNetQtys';

elements
{
dataitem(SalesInvoiceLine; "Sales Invoice Line")
{
column(ItemNo; "No.") { }
column(InvoiceQty; Sum("Quantity")) { }

dataitem(SalesCreditMemoLine; "Sales Cr.Memo Line")
{
DataItemLink = "No." = SalesInvoiceLine."No.";
column(CreditQty; Sum("Quantity")) { }
}

column(NetQty; InvoiceQty - CreditQty) { }
}
}
}

Adding more: https://community.dynamics.com/forums/thread/details/?threadid=db20aa0d-d086-4d45-bb24-3d15df4e60c5

https://www.youtube.com/watch?v=blXCX2rXDwQ

https://madhda.com/it-blog/deep-insert-into-nested-tables-via-api/

Was this reply helpful? Yes No
Gerardo Rentería Ga... 25,545 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!

Hi, good day
I hope this can help you, and give you some hints.

Deep insert with Business Central APIs – Kauffmann @ Dynamics 365 Business Central

Deep insert with Business Central APIs (part 2) – Kauffmann @ Dynamics 365 Business Central

57'th #bcopendiscussion - Deep Insert with API’s?

Best Regards
Gerardo

Was this reply helpful? Yes No
Suggested answer

Ramesh Kumar 7,545 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Hi Samantha,

I would say yes and developer should be able to do it as lot of user what to have Header and line details combined. In your case creating combined table with posted invoice lines and posted credit lines.

query 50000 "Item Sales Quantities"
{
QueryType = API;
APIPublisher = 'yourpublisher';
APIGroup = 'yourgroup';
APIVersion = 'v1.0';
EntityName = 'itemSalesQuantity';
EntitySetName = 'itemSalesQuantities';

dataitem(SalesInvoiceLine; "Sales Invoice Line")
{
DataItemLink = "Item No." = field("Item No.");
sum("Quantity");
}

dataitem(SalesCreditMemoLine; "Sales Credit Memo Line")
{
DataItemLink = "Item No." = field("Item No.");
sum("Quantity");
}
}

https://learn.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/devenv-query-links-joins?

Thanks

Ramesh

If this was helpful, please check the "Does this answer your question?" box and mark it as verified.

Was this reply helpful? Yes No
Suggested answer

YUN ZHU 97,931 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Hi, hope the following helps.

Dynamics 365 Business Central: API query type (Develop a custom API using Query)

https://yzhums.com/57475/

Thanks.

ZHU

Was this reply helpful? Yes No
Vahid Ghafarpour 12,216 Super User 2026 Season 1 on at

Like (1)

Report
Copy link

Link copied!

If any of the responses helped resolve your issue, please take a moment to mark the best answer. This helps others in the community quickly find solutions to similar problems.

To do this, simply click the "Does this answer your question?" button on the most helpful response and like the helpful posts. If your issue is still unresolved, feel free to provide more details so the community can assist further!

Thanks for being an active part of the Dynamics 365 Community!

Was this reply helpful? Yes No