web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

Introducing our New Video Series: Community Spotlight
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / To add one more table...
Finance | Project Operations, Human Resources, ...
Suggested Answer

To add one more table in custom query in data provider class

(1) ShareShare
ReportReport
Posted on by Dineshkarlekar 1,836
Hi , 
Everyone ,
i have created the report which contains controller and dp class and the dp class was using query , 
now i have to add the contract class to it for from date and to date filter  i have made required changes in dp class but i am not getting final query in debugger , can anybody help me out with this , please suggest me what changes i have to make in my dp class to get required output between dates . i am trying to do something like this .
my code is below.
 
SELECT VendPackingSlipJour.DELIVERYDATE,* FROM PurchLine 
JOIN  PurchTable ON PurchLine.PurchId = PurchTable.PurchId
join VendPackingSlipJour  ON PurchTable.PurchId = VendPackingSlipJour.PurchId
WHERE ((IsDeleted = 0))
and VendPackingSlipJour.DELIVERYDATE >= '2019-03-27'
and VendPackingSlipJour.DELIVERYDATE <= '2019-05-14'
 
thanks,
regards,
Dinesh
I have the same question (0)
  • Dineshkarlekar Profile Picture
    Dineshkarlekar 1,836 on at
    my dp class code , 
    [
   SRSReportQueryAttribute(queryStr(DTRecivedNotInvoicedQue)),
   SRSReportParameterAttribute(classstr(DTRecievedButNotInvoicedContract))
]
class DTRecivedButNotInvoicedDp extends SRSReportDataProviderBase
{
    DTRcvdNotInvoicedTmp                DTRcvdNotInvoicedTmp;
    Purchline                           purchline,purchlineLoc;
    LedgerJournalTrans                  ledgerJournalTrans;
    VendPackingSlipTrans                vendPackingSlipTrans;
    VendInvoiceJour                     vendInvoiceJour;
    VendInvoiceTrans                    vendInvoiceTrans;
    PurchTable                          purchTable;
    VendAccount                         vendAccount;
    str                                 name;
    ExchRate                            rate;
    AmountCur                           invamt,notinvamt;
    PurchQty                            qty,notinvqty,cancelqty,lineQty;

    [SrsReportDataSetAttribute(tableStr('DTRcvdNotInvoicedTmp'))]
    public DTRcvdNotInvoicedTmp GetDTRcvdNotInvoicedTmp()
    {
        select * from DTRcvdNotInvoicedTmp;
        return		  DTRcvdNotInvoicedTmp;
    }

    Public void processReport()
    {
        Query							 q;
        QueryRun					     qr;
        QueryBuildDataSource		     qbdsVendPackingSlipJour;
        QueryBuildDataSource			 qbdsobjDirPartyTable;
        QueryBuildDataSource			 qbdsobjLogisticsPostalAddress;
        QueryBuildRange					 qbr;
        TransDate                        fromDate;
        TransDate                        toDate;


        DTRecievedButNotInvoicedContract contract =
            this.parmDataContract() as DTRecievedButNotInvoicedContract;

        fromDate = contract.parmFromDate();
        toDate   = contract.parmToDate();

        DTRcvdNotInvoicedTmp.clear();

        q = this.parmQuery();
        q.dataSourceName(tableStr(PurchLine)).addRange(fieldNum(PurchLine,IsDeleted)).value(queryValue(0));

        if(fromDate && toDate)
        {  
            qbdsVendPackingSlipJour = q.addDataSource(tableNum(VendPackingSlipJour));
            qbdsVendPackingSlipJour.relations(false);
            qbdsVendPackingSlipJour.addLink(fieldNum(PurchLine, PurchId),fieldNum(VendPackingSlipJour, PurchId));
            q.dataSourceName(tableStr(VendPackingSlipJour)).addRange(fieldNum(VendPackingSlipJour, DeliveryDate)).value(queryRange(fromDate, toDate));
        }

        qr = new QueryRun(q);

        while(qr.next())
        {
            purchTable = qr.get(tableNum(PurchTable));
            purchline = qr.get(tableNum(Purchline));

            DTRcvdNotInvoicedTmp.clear();
            DTRcvdNotInvoicedTmp.PurchId     = purchTable.PurchId;
            DTRcvdNotInvoicedTmp.PurchName   = purchTable.PurchName;
            DTRcvdNotInvoicedTmp.LineNumber  = purchline.LineNumber;
            DTRcvdNotInvoicedTmp.ItemId      = purchline.ItemId;
            DTRcvdNotInvoicedTmp.ItemName    = purchline.itemName();
            DTRcvdNotInvoicedTmp.PurchQty    = purchline.PurchQty;

            select sum(LineAmount)from purchlineLoc
                where purchlineLoc.PurchId == purchTable.PurchId;

            DTRcvdNotInvoicedTmp.POTotalAmount = purchlineLoc.LineAmount;

            select sum(Qty),sum(ValueMST) from vendPackingSlipTrans
                    where vendPackingSlipTrans.ItemId ==  purchline.ItemId
                     && vendPackingSlipTrans.InventTransId == purchline.InventTransId;

            rate =   ExchangeRateHelper::getExchangeRate1_Static(Ledger::current(),purchTable.CurrencyCode,today())/100;

            DTRcvdNotInvoicedTmp.RecivedQty   = vendPackingSlipTrans.Qty;
            DTRcvdNotInvoicedTmp.RecivedAmount = vendPackingSlipTrans.ValueMST/rate;
            qty = 0;
            invamt = 0;

            while select * from vendInvoiceTrans
                join vendInvoiceJour
                    where vendInvoiceTrans.InvoiceId == vendInvoiceJour.InvoiceId
                    &&  vendInvoiceTrans.InvoiceDate == vendInvoiceJour.InvoiceDate
                    &&  vendInvoiceTrans.numberSequenceGroup == vendInvoiceJour.numberSequenceGroup
                    &&  vendInvoiceTrans.InternalInvoiceId == vendInvoiceJour.InternalInvoiceId
                    &&  vendInvoiceTrans.OrigPurchId == purchTable.PurchId
                    && vendInvoiceTrans.PurchaseLineLineNumber == purchline.LineNumber
                    &&  vendInvoiceTrans.ItemId == purchline.ItemId
            {   
                qty   += vendInvoiceTrans.Qty;
                invamt += vendInvoiceTrans.lineAmountInclTax();
            }
            DTRcvdNotInvoicedTmp.InvoicedQty   = qty;
            DTRcvdNotInvoicedTmp.InvoiceAmount = invamt;
            notinvqty = 0;
            notinvamt = 0;
            cancelqty = 0;
            lineQty = 0;
            lineQty = purchline.PurchQty;

            if(qty <= lineQty)
            {
              cancelqty = purchline.PurchQty - vendPackingSlipTrans.Qty;
            }
            else
            {
                cancelqty = 0;
            }

            if(cancelqty != 0
                && purchline.RemainPurchPhysical == 0 )
            {
                DTRcvdNotInvoicedTmp.CancelQty =  cancelqty;
            }
            notinvqty = purchline.RemainPurchFinancial + purchline.RemainPurchPhysical;
            notinvamt = notinvqty * purchline.PurchPrice;
            DTRcvdNotInvoicedTmp.NotInvoicedQty = notinvqty ;
            DTRcvdNotInvoicedTmp.NotInvoiceAmount =  notinvamt;

            DTRcvdNotInvoicedTmp.insert();
        }
    }

}
     
  • Martin Dráb Profile Picture
    Martin Dráb 238,580 Most Valuable Professional on at
    Please look at what query you've generated by your code and share it with us.
     
    I can say for sure that your code doesn't match your SQL code above, because there you join VendPackingSlipJour with PurchTable, while you join it with PurchLine in X++.
     
    You can simplify your code by adding VendPackingSlipJour to the query instead of doing it at runtime.
     
    Next time, please remove code irrelevant to the problem. Most of code below is not related to the query and therefore it just make the problem harder to understand.
  • Martin Dráb Profile Picture
    Martin Dráb 238,580 Most Valuable Professional on at
    Another problem is that even your SQL code is wrong, because it ignores partial and summary postings. The correct way of joining purchase orders and packing slips is over VendPackingSlipPurchLink table.
  • Suggested answer
    Bharani Preetham Peraka Profile Picture
    Bharani Preetham Pe... 3,634 Moderator on at
    We are not exactly aware of the query which you created and using in the report as contract. Please share the aot query schema for more understanding.
     
    Also, when adding datasource to the query, ideally it should be added to the datasource not directly to the query object. And as Martin suggested the join you made is different from the thing which you showed in the question level.
     
    Are you getting query object from this.parmquery() method? 
  • Dineshkarlekar Profile Picture
    Dineshkarlekar 1,836 on at
    hi , every one 
     i have made changes  to the code please let me know if i am doing anything wrong.below query i am getting 
    SELECT * FROM PurchLine(PurchLine) WHERE ((IsDeleted = 0)) 
JOIN * FROM PurchTable(PurchTable)
 ON PurchLine.PurchId = PurchTable.PurchId 
JOIN * FROM VendPackingSlipJour(VendPackingSlipJour_1)
 ON PurchTable.PurchId = VendPackingSlipJour.PurchId 
AND ((DeliveryDate>={ts '2024-01-01 00:00:00.000'} AND DeliveryDate<={ts '2024-02-29 00:00:00.000'}))}
     
    [
   SRSReportQueryAttribute(queryStr(DTRecivedNotInvoicedQue)),
   SRSReportParameterAttribute(classstr(DTRecievedButNotInvoicedContract))
]
class DTRecivedButNotInvoicedDp extends SRSReportDataProviderBase
{
    DTRcvdNotInvoicedTmp                DTRcvdNotInvoicedTmp;
    Purchline                           purchline,purchlineLoc;
    LedgerJournalTrans                  ledgerJournalTrans;
    VendPackingSlipTrans                vendPackingSlipTrans;
    VendInvoiceJour                     vendInvoiceJour;
    VendInvoiceTrans                    vendInvoiceTrans;
    PurchTable                          purchTable;
    VendAccount                         vendAccount;
    str                                 name;
    ExchRate                            rate;
    AmountCur                           invamt,notinvamt;
    PurchQty                            qty,notinvqty,cancelqty,lineQty;

    [SrsReportDataSetAttribute(tableStr('DTRcvdNotInvoicedTmp'))]
    public DTRcvdNotInvoicedTmp GetDTRcvdNotInvoicedTmp()
    {
        select * from DTRcvdNotInvoicedTmp;
        return		  DTRcvdNotInvoicedTmp;
    }

    Public void processReport()
    {
        Query							 q;
        Query							 qnew = new Query();
        QueryRun					     qr;
        QueryBuildDataSource		     qbdsVendPackingSlipJour;
        QueryBuildDataSource			 qbdsobjDirPartyTable;
        QueryBuildDataSource			 qbdsobjLogisticsPostalAddress;
        QueryBuildRange					 qbr;
        TransDate                        fromDate;
        TransDate                        toDate;


        DTRecievedButNotInvoicedContract contract =
            this.parmDataContract() as DTRecievedButNotInvoicedContract;

        fromDate = contract.parmFromDate();
        toDate   = contract.parmToDate();

        DTRcvdNotInvoicedTmp.clear();

        q = this.parmQuery();
        q.dataSourceName(tableStr(PurchLine)).addRange(fieldNum(PurchLine,IsDeleted)).value(queryValue(0));

        if(fromDate && toDate)
        {  
            qnew = q;
            qbdsVendPackingSlipJour = qnew.dataSourceTable(tablenum(PurchTable)).addDataSource(tablenum(VendPackingSlipJour));
            qbdsVendPackingSlipJour.relations(false);
            qbdsVendPackingSlipJour.joinMode(JoinMode::InnerJoin);
            qbdsVendPackingSlipJour.addLink(fieldNum(VendPackingSlipJour, PurchId),fieldNum(PurchTable, PurchId));
            qbdsVendPackingSlipJour.orderMode(OrderMode::GroupBy);
            qbdsVendPackingSlipJour.addGroupByField(fieldNum(PurchLine, ItemId));
            qbdsVendPackingSlipJour.addRange(fieldNum(VendPackingSlipJour, DeliveryDate)).value(queryRange(fromDate, toDate));
            qr = new QueryRun(qnew);
        }
         else
        {
            qr = new QueryRun(q);
        }

        while(qr.next())
        {
            purchTable = qr.get(tableNum(PurchTable));
            purchline = qr.get(tableNum(Purchline));

            DTRcvdNotInvoicedTmp.clear();
            DTRcvdNotInvoicedTmp.PurchId     = purchTable.PurchId;
            DTRcvdNotInvoicedTmp.PurchName   = purchTable.PurchName;
            DTRcvdNotInvoicedTmp.LineNumber  = purchline.LineNumber;
            DTRcvdNotInvoicedTmp.ItemId      = purchline.ItemId;
            DTRcvdNotInvoicedTmp.ItemName    = purchline.itemName();
            DTRcvdNotInvoicedTmp.PurchQty    = purchline.PurchQty;

            select sum(LineAmount)from purchlineLoc
                where purchlineLoc.PurchId == purchTable.PurchId;

            DTRcvdNotInvoicedTmp.POTotalAmount = purchlineLoc.LineAmount;

            select sum(Qty),sum(ValueMST) from vendPackingSlipTrans
                    where vendPackingSlipTrans.ItemId ==  purchline.ItemId
                     && vendPackingSlipTrans.InventTransId == purchline.InventTransId;

            rate =   ExchangeRateHelper::getExchangeRate1_Static(Ledger::current(),purchTable.CurrencyCode,today())/100;

            DTRcvdNotInvoicedTmp.RecivedQty   = vendPackingSlipTrans.Qty;
            DTRcvdNotInvoicedTmp.RecivedAmount = vendPackingSlipTrans.ValueMST/rate;
            qty = 0;
            invamt = 0;

            while select * from vendInvoiceTrans
                join vendInvoiceJour
                    where vendInvoiceTrans.InvoiceId == vendInvoiceJour.InvoiceId
                    &&  vendInvoiceTrans.InvoiceDate == vendInvoiceJour.InvoiceDate
                    &&  vendInvoiceTrans.numberSequenceGroup == vendInvoiceJour.numberSequenceGroup
                    &&  vendInvoiceTrans.InternalInvoiceId == vendInvoiceJour.InternalInvoiceId
                    &&  vendInvoiceTrans.OrigPurchId == purchTable.PurchId
                    && vendInvoiceTrans.PurchaseLineLineNumber == purchline.LineNumber
                    &&  vendInvoiceTrans.ItemId == purchline.ItemId
            {   
                qty   += vendInvoiceTrans.Qty;
                invamt += vendInvoiceTrans.lineAmountInclTax();
            }
            DTRcvdNotInvoicedTmp.InvoicedQty   = qty;
            DTRcvdNotInvoicedTmp.InvoiceAmount = invamt;
            notinvqty = 0;
            notinvamt = 0;
            cancelqty = 0;
            lineQty = 0;
            lineQty = purchline.PurchQty;

            if(qty <= lineQty)
            {
              cancelqty = purchline.PurchQty - vendPackingSlipTrans.Qty;
            }
            else
            {
                cancelqty = 0;
            }

            if(cancelqty != 0
                && purchline.RemainPurchPhysical == 0 )
            {
                DTRcvdNotInvoicedTmp.CancelQty =  cancelqty;
            }
            notinvqty = purchline.RemainPurchFinancial + purchline.RemainPurchPhysical;
            notinvamt = notinvqty * purchline.PurchPrice;
            DTRcvdNotInvoicedTmp.NotInvoicedQty = notinvqty ;
            DTRcvdNotInvoicedTmp.NotInvoiceAmount =  notinvamt;
            DTRcvdNotInvoicedTmp.NotInvoiceAmountSupplier = vendPackingSlipTrans.ValueMST/rate -invamt ;

            DTRcvdNotInvoicedTmp.insert();
        }
    }

}
     
  • Martin Dráb Profile Picture
    Martin Dráb 238,580 Most Valuable Professional on at
    I see you love posting all the code unrelated to your question...
     
    Anyway, so what problem do you have with your query? It seems to look like you wanted. It's difficult to give you an answer if you don't describe the problem.
  • Bharani Preetham Peraka Profile Picture
    Bharani Preetham Pe... 3,634 Moderator on at
    Code change is fine. But before making changes please answer our questions so that we can resolve problem a bit faster. 
     
    After making changes did you try to check if it is working? As Martin suggested you need to give your full problem statement. 
  • Dineshkarlekar Profile Picture
    Dineshkarlekar 1,836 on at
    hi every on report is working but i am getting lines repeated in report in some cases , if i want to group by in query the reports reports is not getting open , but if i remove group by reports runs but with repeated records . is anything wrong with my group by query .
     
                qnew = q;
            qbdsVendPackingSlipJour = qnew.dataSourceTable(tablenum(PurchTable)).addDataSource(tablenum(VendPackingSlipJour));
            qbdsVendPackingSlipJour.relations(false);
            qbdsVendPackingSlipJour.joinMode(JoinMode::InnerJoin);
            qbdsVendPackingSlipJour.addLink(fieldNum(VendPackingSlipJour, PurchId),fieldNum(PurchTable, PurchId));
            qbdsVendPackingSlipJour.orderMode(OrderMode::GroupBy);
            qbdsVendPackingSlipJour.addGroupByField(fieldNum(PurchLine, ItemId));
            qbdsVendPackingSlipJour.addRange(fieldNum(VendPackingSlipJour, DeliveryDate)).value(queryRange(fromDate, toDate));
            qr = new QueryRun(qnew);
  • Martin Dráb Profile Picture
    Martin Dráb 238,580 Most Valuable Professional on at
    I already mentioned one bug - that you join PurchTable and VendPackingSlipJour. The reason why you some records twice may be related - maybe these are orders included in more then a single packing slip.
     
    If you add group by ItemId, you'll get a single record for each item ID and no other fields will be populated, therefore you won't get any information about orders or packing slips. That's clearly not what you want. By the way, you would have immediately seen the problem if you test your query. You really need to learn how to debug code; it's a crucial skill for a developer.
  • Dineshkarlekar Profile Picture
    Dineshkarlekar 1,836 on at
    hi , martin 
    thanks for reply , 
    can you please suggest how can i resolve this error , should i join vendpackingsliptrans with sales line using inventtransid . or any other table i have to use .
     
    thanks,
    Regards,
    Dinesh
     
     

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

Introducing our New Video Series: Community Spotlight

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Congratulations to our 2025 Community Spotlights

Thanks to all of our 2025 Community Spotlight stars!

Congratulations to the December Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 679 Super User 2026 Season 1

#2
Abhilash Warrier Profile Picture

Abhilash Warrier 332 Super User 2026 Season 1

#3
Martin Dráb Profile Picture

Martin Dráb 238 Most Valuable Professional

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans