Skip to main content

Notifications

Announcements

🌸 Community Spring Festival 2025 Challenge 🌸
News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Small and medium business | Business Central, N... / API development - comb...
Small and medium business | Business Central, N...
Suggested answer

API development - combine data from 2 tables

(4) ShareShare
ReportReport
Posted on by Samantha73 2,763
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
  • Suggested answer
    YUN ZHU Profile Picture
    YUN ZHU 80,093 Super User 2025 Season 1 on at
    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)
     
    Thanks.
    ZHU
  • Suggested answer
    Ramesh Kumar Profile Picture
    Ramesh Kumar 2,235 on at
    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");
        }
    }
     
     
     
    Thanks
    Ramesh
     
    If this was helpful, please check the "Does this answer your question?" box and mark it as verified.
     
     
  • gdrenteria Profile Picture
    gdrenteria 16,677 Most Valuable Professional on at
    API development - combine data from 2 tables
  • Suggested answer
    Khushbu Rajvi. Profile Picture
    Khushbu Rajvi. 12,609 Super User 2025 Season 1 on at
    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
  • Suggested answer
    Jainam M. Kothari Profile Picture
    Jainam M. Kothari 3,827 on at
    API development - combine data from 2 tables

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

🌸 Community Spring Festival 2025 Challenge 🌸

Quick Links

🌸 Community Spring Festival 2025 Challenge 🌸

WIN Power Platform Community Conference 2025 tickets!

Jonas ”Jones” Melgaard – Community Spotlight

We are honored to recognize Jonas "Jones" Melgaard as our April 2025…

Kudos to the March Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 293,364 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 232,524 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,158 Moderator

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans