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

(3) Share

Report

Posted on by D365FO DEV

388

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

All responses (21)

Answers (0)

Martin Dráb 230,370 Most Valuable Professional on at

Like (0)

Report

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 388 on at

Like (0)

Report

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 230,370 Most Valuable Professional on at

Like (0)

Report

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 388 on at

Like (0)

Report

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 230,370 Most Valuable Professional on at

Like (1)

Report

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 388 on at

Like (0)

Report

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 388 on at

Like (0)

Report
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 388 on at

Like (0)

Report

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 230,370 Most Valuable Professional on at

Like (1)

Report

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 230,370 Most Valuable Professional on at

Like (1)

Report

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.