Finance | Project Operations, Human Resources, ...

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

(1) Share

Report

Posted on by Dineshkarlekar

1,495

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

All responses (11)

Answers (0)

Dineshkarlekar 1,495 on at

Like (0)

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

hi ,

Martin ,

I am trying with new query , please let me know if i have to make any other change .
SELECT VENDPACKINGSLIPTRANS.DeliveryDate,PurchLine.PURCHID,PurchLine.ITEMID,PurchLine.INVENTTRANSID, VENDPACKINGSLIPTRANS.INVENTTRANSID, VENDPACKINGSLIPTRANS.INVENTREFTRANSID,* FROM PurchLine JOIN PurchTable ON PurchLine.PurchId = PurchTable.PurchId JOIN VENDPACKINGSLIPTRANS ON PurchLine.INVENTTRANSID = VENDPACKINGSLIPTRANS.INVENTTRANSID WHERE ((IsDeleted = 0)) AND (( VENDPACKINGSLIPTRANS.DeliveryDate>={ts '2024-01-01 00:00:00.000'} AND VENDPACKINGSLIPTRANS.DeliveryDate<={ts '2024-02-28 00:00:00.000'}))

thanks,

Regards,

Dinesh
Dineshkarlekar 1,495 on at

Like (0)

Report

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

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
Martin Dráb 233,675 Most Valuable Professional on at

Like (1)

Report

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

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 1,495 on at

Like (0)

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

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);
Bharani Preetham Pe... 3,634 Moderator on at

Like (0)

Report

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

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.
Martin Dráb 233,675 Most Valuable Professional on at

Like (0)

Report

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

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.

Dineshkarlekar

1,495 on at

Like (0)

Report

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

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();
        }
    }

}

Suggested answer

Bharani Preetham Pe... 3,634 Moderator on at

Like (1)

Report

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

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?
Martin Dráb 233,675 Most Valuable Professional on at

Like (1)

Report

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

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.
Martin Dráb 233,675 Most Valuable Professional on at

Like (1)

Report

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

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.

Community site session details

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

Helpful resources

News and Announcements

Quick Links

Announcing the Engage with the Community forum!

🌸 Community Spring Festival 2025 Challenge Winners! 🌸

Adis Hodzic – Community Spotlight

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

Featured topics

Product updates

Community site session details

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

Helpful resources

News and Announcements

Quick Links

Announcing the Engage with the Community forum!

🌸 Community Spring Festival 2025 Challenge Winners! 🌸

Adis Hodzic – Community Spotlight

Subscribe to this forum!

Select categories

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

Featured topics

Product updates