Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Finance forum / How to exclude prepaym...
Finance forum
Unanswered

How to exclude prepayment accounts from the vendor account statement report using x++

(1) ShareShare
ReportReport
Posted on by D365FO DEV 386
Hello,
 
for the vendor account statement report, i've added a boolean field "Exclude Prepayments". i want to make it that if this field is true, to exclude all the invoices of type "Prepayment". So what i did first is extended the contract class and added the parameter. then, i extended  the Classes\VendAccountStatementIntDP\initVendTransQuery method to add a range based on this parameter value. but it's still not working till now. the vendor invoice type is "InvoiceType" field in VendInvoiceJour table, and has Enum PurchInvoiceType::VendorAdvance (for the value prepayment).
 
here is my code if anyone can tell me whats wrong, i will appreiciate it.
 
 
[ExtensionOf(classStr(VendAccountStatementIntDP))]
final class VendAccountStatementIntDP_Extension
{
    private NoYes ExclPrepayments;
    public void processReport()
    {
        // Call the base class method
        next processReport();
        // Retrieve the contract object
        VendAccountStatementIntContract vendAccountStatementIntContract = this.parmDataContract();
    
        // Get the value of the exclusion parameter
        ExclPrepayments = vendAccountStatementIntContract.parmExclPrepayment();
    
        // Initialize the query
        Query query = this.initVendTransQuery();
    }
    public Query initVendTransQuery()
    {
        Query query = next initVendTransQuery();
        // Initialize the base query as done in the standard DP class
        QueryBuildDataSource vendTransDataSourceBase = SysQuery::findOrCreateDataSource(query, tableNum(VendTrans), tableNum(VendTable));
       
        // Add VendInvoiceJour as a child data source of VendTrans
        QueryBuildDataSource vendInvoiceJourDataSource = vendTransDataSourceBase.addDataSource(tableNum(VendInvoiceJour));
        vendInvoiceJourDataSource.relations(true);
        vendInvoiceJourDataSource.joinMode(JoinMode::OuterJoin);
        // Define correct linking
        vendInvoiceJourDataSource.addLink(fieldNum(VendInvoiceJour, LedgerVoucher), fieldNum(VendTrans, Voucher));
        vendInvoiceJourDataSource.addLink(fieldNum(VendInvoiceJour, InvoiceAccount), fieldNum(VendTrans, AccountNum));
        // Add filtering condition based on the parameter value
        if (ExclPrepayments == NoYes::Yes)
        {
            vendInvoiceJourDataSource.addRange(fieldNum(VendInvoiceJour, InvoiceType)).value(SysQuery::valueNot(Enum2Str(PurchInvoiceType::VendorAdvance)));
        }
        return query;
    }
}
 
Categories:
Development / Customization / SDK
  • Martin Dráb Profile Picture
    Martin Dráb 228,501 Most Valuable Professional on at
    How to exclude prepayment accounts from the vendor account statement report using x++
    I disagree - your new code still does absolutely nothing to address the problem. You just changed the logic deciding which VendTrans records will be included, but - as discussed before - it doesn't change the number of records returned by the query, because VendTrans is outer-joined to the parent data source. You'd have to change the join mode of VendTrans, not VendInvoiceJour, but it would significantly change the behavior of the report.
     
    Also, I see you've kept or reintroduced almost all the problems that we already discussed. You should read the discussion again and fix them.
  • D365FO DEV Profile Picture
    D365FO DEV 386 on at
    How to exclude prepayment accounts from the vendor account statement report using x++
    I updated now my code based on everything you told about. but its still not working.
     
     
    [ExtensionOf(classStr(VendAccountStatementIntDP))]
    final class VendAccountStatementIntDP_Extension
    {
        private NoYes ExclPrepayments;
        public void processReport()
        {
                 
            VendAccountStatementIntContract vendAccountStatementIntContract = this.parmDataContract();
            ExclPrepayments = vendAccountStatementIntContract.parmExclPrepayment();
            next processReport();
        }
        public Query initVendTransQuery()
        {
            Query query = next initVendTransQuery();
            
           
            QueryBuildDataSource vendTransDataSourceBase = query.dataSourceTable(tableNum(VendTrans));
            
            if (ExclPrepayments == NoYes::Yes)
            {
                
                QueryBuildDataSource vendInvoiceJourDataSource = vendTransDataSourceBase.addDataSource(tableNum(VendInvoiceJour));
                vendInvoiceJourDataSource.relations(true);
                vendInvoiceJourDataSource.joinMode(JoinMode::NoExistsJoin);
                vendInvoiceJourDataSource.addLink(fieldNum(VendTrans, Voucher), fieldNum(VendInvoiceJour, LedgerVoucher));
                vendInvoiceJourDataSource.addLink(fieldNum(VendTrans, AccountNum), fieldNum(VendInvoiceJour, InvoiceAccount));
                vendInvoiceJourDataSource.addLink(fieldNum(VendTrans, TransDate), fieldNum(VendInvoiceJour, InvoiceDate));
                vendInvoiceJourDataSource.addRange(fieldNum(VendInvoiceJour, InvoiceType)).value(SysQuery::valueNot(PurchInvoiceType::VendorAdvance));
            }
            return query;
        }
    }
  • Martin Dráb Profile Picture
    Martin Dráb 228,501 Most Valuable Professional on at
    How to exclude prepayment accounts from the vendor account statement report using x++
    Please read my message once more, you'll see I'm talking about a different join. Your code doesn't address the problem in any way.
     
    It's irrelevant to what type of join you use for your VendInvoiceJour data source; the fact that VendTrans is outer-joined to its parent data source is still true. And therefore your filter may limit what data is loaded for VendTrans fields, but it won't filter out any rows, because an outer join means that all (applicable) records are loaded for the parent data source, regardless of whether there is a related record in the child data source (VendTrans) or not.
     
    By the way, scroll down to the beginning of this discussion, read what I told you about findOrCreateDataSource() and fix it.

    Also, you shouldn't call initVendTransQuery() for the second time. It's already called by standard code.
  • D365FO DEV Profile Picture
    D365FO DEV 386 on at
    How to exclude prepayment accounts from the vendor account statement report using x++
    Hi martin,
     
    i changed from outerjoin to innerjoin , but the records of type "prepayment" are not being filtered. This is my code:
     
    [ExtensionOf(classStr(VendAccountStatementIntDP))]
    final class VendAccountStatementIntDP_Extension
    {
        private NoYes ExclPrepayments;
        public void processReport()
        {
            
           
            VendAccountStatementIntContract vendAccountStatementIntContract = this.parmDataContract();
            ExclPrepayments = vendAccountStatementIntContract.parmExclPrepayment();
            Query query = this.initVendTransQuery();
            next processReport();
        }
        public Query initVendTransQuery()
        {
            Query query = next initVendTransQuery();
            QueryBuildDataSource vendTransDataSourceBase = SysQuery::findOrCreateDataSource(query, tableNum(VendTrans), tableNum(VendTable));
            QueryBuildDataSource vendInvoiceJourDataSource = vendTransDataSourceBase.addDataSource(tableNum(VendInvoiceJour));
            vendInvoiceJourDataSource.relations(true);
            vendInvoiceJourDataSource.joinMode(JoinMode::InnerJoin);
        
            
            if (ExclPrepayments == NoYes::Yes)
            {
                vendInvoiceJourDataSource.addRange(fieldNum(VendInvoiceJour, InvoiceType)).value(SysQuery::valueNot(PurchInvoiceType::VendorAdvance));
            }
            return query;
        }
    }
     
  • Martin Dráb Profile Picture
    Martin Dráb 228,501 Most Valuable Professional on at
    How to exclude prepayment accounts from the vendor account statement report using x++
    I know you said the query was correct, but do you realize that your filter will never reduce the number of records returned by the query, because VendTrans itself is outer-joined to its parent data source? If you expected that some records will be filtered out, the design of your solution is wrong and your testing failed too.
     
    It would help if you explained your intent and your problem, instead of just saying that the query is correct (which is doubtful) and "it's not working" (which might mean anything).
  • D365FO DEV Profile Picture
    D365FO DEV 386 on at
    How to exclude prepayment accounts from the vendor account statement report using x++
    the query is correct, but i  dont know why it's not working on the report when printing.
  • D365FO DEV Profile Picture
    D365FO DEV 386 on at
    How to exclude prepayment accounts from the vendor account statement report using x++
    Hi Martin, 
     
    this is the value of my query: 
     
    {SELECT * FROM VendInvoiceJour(VendInvoiceJour_2) WHERE VendTrans.Voucher = VendInvoiceJour.LedgerVoucher AND VendTrans.AccountNum = VendInvoiceJour.InvoiceAccount AND VendTrans.TransDate = VendInvoiceJour.InvoiceDate AND ((NOT (InvoiceType = 1)))}
 

    i believe its correct, but now i need to know why its not working when printing the report.
  • D365FO DEV Profile Picture
    D365FO DEV 386 on at
    How to exclude prepayment accounts from the vendor account statement report using x++
    hI MARTIN,
     
    the problem is i dont know how to resolve it based on your reply, because i am new to this one. thats why i asked you if please you could update my code based on what you said, so i will be more familiar with it for the future.
  • Martin Dráb Profile Picture
    Martin Dráb 228,501 Most Valuable Professional on at
    How to exclude prepayment accounts from the vendor account statement report using x++
    Now you've reintroduced the bugs mentioned in my first reply. Please pay attention to what I'm saying and do changes for a reason, not randomly.
  • Martin Dráb Profile Picture
    Martin Dráb 228,501 Most Valuable Professional on at
    How to exclude prepayment accounts from the vendor account statement report using x++
    If you agree that you have no intention to add a second VendTrans data source, fix the bug I mentioned in my previous reply. There is no point in testing your code further before you fix the known problem.

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

Quick Links

Anton Venter – Community Spotlight

Kudos to our October Community Star of the month!

Announcing Our 2024 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Dynamics 365 Community Newsletter - September 2024

Check out the latest community news

more Community News

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,532 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 228,501 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans