Vendor Ledger entries in last 2 years?

nverma

I have to create a report, that should print a list of all the vendors that do not have any vendor ledger entries in the last two years. Any suggestions How I can do this.

So far, I have a two dataitems: Vendors and Vendor Ledger entries (indented under it).

I am just not sure how to check if a vendor has any ledger entries in the last two years.

DenSter

Think about posting date, and maybe setting a filter

nverma

Right now its showing me all vendor ledger entries that have a posting date between today and two years ago...

How can I make it show the vendor information if there were no vendor ledger entries in the last two years...I am not sure which item to set the filter on...


Vendor Ledger Entry - OnPreDateItem

TodaysDate := TODAY;
CalculateDate := '<CD-2Y>';
TwoYearsAgo := CALCDATE(CalculateDate,TodaysDate);
"Vendor Ledger Entry".SETFILTER("Posting Date",'%1..%2',TwoYearsAgo,TODAY);

DenSter

You really need to start thinking about these things yourself. Go through the steps, analyze the need.

If you need to create a list report that shows the vendors that do not have any vendor ledger entries for the past 2 years, does it make sense to have a dataitem that shows vendor ledger entries? In my opinion no. Why would you have a dataitem for a table from which you are not going to show any data? You have to think about this.

Instead of a dataitem, add some code to the OnAfterGetRecord trigger of the Vendor, and use the CurrReport.SKIP method to skip over vendors that DO have ledger entries. Since you are not showing any details, you don't need to actually retrieve and records, so you can use the ISEMPTY command.

nverma

SOLVED!

// Filter the Vendor Ledger Entries, "Posting Date" to be between today and two years ago.
TodaysDate := TODAY;
CalculateDate := '<CD-2Y>';
TwoYearsAgo := CALCDATE(CalculateDate,TodaysDate);
VendorLedgerEntries.SETFILTER("Posting Date", '%1..%2', TwoYearsAgo,TodaysDate);
//  - ADD End

Vendor - OnAfterGetRecord()
//  - ADD Start
// Filters the Vendor No. that are found in the Vendor Ledger Entries to the Vendor No found in Vendor Table.
// If a match a found, then skip that record.
VendorLedgerEntries.SETRANGE("Vendor No.", Vendor."No.");
IF VendorLedgerEntries.FINDSET THEN
  REPEAT
    CurrReport.SKIP;
  UNTIL VendorLedgerEntries.NEXT=0;
// - ADD End

DenSter

Again, a good start but no....

You are looping where you don't need to loop. All you need to do is know whether records exist, like this:

VendorLedgerEntries.SETRANGE("Vendor No.", Vendor."No.");
IF NOT VendorLedgerEntries.ISEMPTY THEN
  CurrReport.SKIP;

Also, your date filtering can be done better:

VendorLedgerEntries.SETRANGE("Posting Date",CALCDATE('<-2Y>',TODAY),TODAY);

No need for 4 different variables, and it's easier to use SETRANGE. Also, I don't know where 'CD' comes from

David_Singleton

.

nverma

DenSter wrote:

Again, a good start but no....

You are looping where you don't need to loop. All you need to do is know whether records exist, like this:

VendorLedgerEntries.SETRANGE("Vendor No.", Vendor."No.");
IF NOT VendorLedgerEntries.ISEMPTY THEN
  CurrReport.SKIP;

Also, your date filtering can be done better:

VendorLedgerEntries.SETRANGE("Posting Date",CALCDATE('<-2Y>',TODAY),TODAY);

No need for 4 different variables, and it's easier to use SETRANGE. Also, I don't know where 'CD' comes from

CD was ment to be "Current Date". I wasnt sure how to tell the system to calculate the 2 year difference. Thats why I used CD (current day) - 2y.

your way makes a bit more sense. :thumbsup: