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

API development - combine data from 2 tables

(2) Share

Report

Posted on by Samantha73

2,963

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

All responses (6)

Answers (0)

Vahid Ghafarpour 11,855 Super User 2025 Season 1 on at

Like (1)

Report

API development - combine data from 2 tables

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!
Suggested answer

YUN ZHU 85,670 Super User 2025 Season 1 on at

Like (0)

Report

API development - combine data from 2 tables

Hi, hope the following helps.

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

https://yzhums.com/57475/

Thanks.

ZHU
Suggested answer

Ramesh Kumar 5,458 on at

Like (0)

Report

API development - combine data from 2 tables

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.
Gerardo Rentería Ga... 20,775 Most Valuable Professional on at

Like (1)

Report

API development - combine data from 2 tables

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
Suggested answer

Khushbu Rajvi. 17,504 Super User 2025 Season 1 on at

Like (1)

Report

API development - combine data from 2 tables

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/
Suggested answer

Jainam M. Kothari 9,695 on at

Like (1)

Report

API development - combine data from 2 tables

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/