Prevent report being run depending on BOOLEAN/Posting Dates

shogan@mila.ie

Hey everyone,

I have a report where the data for certain customers before a certain date is redundant.

I would like to prevent it from being run on or before Posting Date/s (usually in a range) 30/06/2009 for customers where a boolean field called 'NI Customer' is set to Yes.

I have a warning message to inform users, but I'd like to be certain...

I am a n00b so please, be gentle

garak

could you explain it please with other words?
What do you want to do.....

shogan@mila.ie

OK, I hope this will be clearer...

I have a report that includes the Customer and Customer Ledger Entry tables, and these are also tabs on the report which I can filter on.

I want the report to terminate with a message when the following combinations of filters arise:

( Customer.'NI Customer' = TRUE ) AND ( 'Customer Ledger Entry'.'Posting Date' includes dates <= 30/06/2009 )

Basically, I do not want the report to execute/run where there is a possibility of including NI Customers with Posting Dates <= 30/06/2009.

Regards,
Stephen

DaveT

Hi Stephen,

In the Cust. Ledger Entry dataitem you can put

If Customer."NI Customer" and ( "Cust. Ledger Entry","posting date" <=  MyDate then
   error( 'My error message' );

Where MyDate is your cut-off date - assuming that this will be entered in a request form

This should terminate the report and give you the error message.

DenSter

shogan@mila.ie wrote:

Basically, I do not want the report to execute/run where there is a possibility of including NI Customers with Posting Dates <= 30/06/2009.

So why not filter where the boolean = TRUE and posting date > 30/06/2009? That way you never have to terminate anything, and the report only runs for customers within the filter. Following your logic, if the first customer would cause an error, the entire report would abort, even if there are other customers who don't have those entries. It's better to set filter for only those that are included.

shogan@mila.ie

DaveT: The Posting Date is actually a filter on the Cust. Item Ledger Entry tab of the report, and not entered in a request form - there is no request form on this report.

As a filter, we typically run the report for a month, so for July we would have

Posting Date  010709..310709

Where I need to prevent the report from running AT ALL is where customers are being selected where 1/any/all of them have NI Customer = TRUE AND Posting Date = date range where the start of the range begins with a date BEFORE 010709.


DenSter: Not entirely sure if I understand you, but I hope the above explanation helps.

1. If a posting date range that begins on or after 010709..t then I should be able to run the report where NI Customer = TRUE|FALSE.
2. I should be able to run the report for any Posting Date range where NI Customer = FALSE.
3. However, I don't want the report to run AT ALL if ANY of the customers have their NI Customer =TRUE and a Posting Date range starts with a date BEFORE 010709.

So you are right in what you say - the report is to terminate even if I have 100 customers and only 1 is an NI Customer = TRUE and Posting Date start date <= 300609.

I have a feeling that there will be some pre-processing involved, where I need to (a) determine that the start date of the Posting Date range <= 300609 AND (b) that from the customers being selected if any of them have NI Customer = TRUE.

Really appreciate your help here guys.

DaveT

Hi Stephen,

The code I gave will work for you with a little mod. Create a variable Mydate and in the OnPreReport Trigger add

if "Cust. Ledger Entry".GETFILTER( "Posting Date" ) <> '' then
   MyDate := "Cust. Ledger Entry".GETRANGEMIN( "Posting Date" );

In the OnAfterGetRecord of the "Cust. Ledger Entry" put

If Customer."NI Customer" and ( "Cust. Ledger Entry","posting date" < MyDate ) then
   error( 'My error message' );

shogan@mila.ie

Hi Dave,

I follow your logic for getting the min of the Posting Date Range, so that we have a starting date and we'll use this to check and see if we are running the report from a date before 01/07/2009...

However, in the OnAfterGetRecord of the Cust. Ledger Entry, I am slightly confused.

What I expected was to set a constant (say CutOffDate = "01/07/2009") that acts as a cut-off date to prevent the report running at all when there are customers with Customer."NI Customer" BEFORE that date. BTW I am unsure as to how to set a constant...

Then, something like:

If Customer."NI Customer" AND (MyDate < CutOffDate)
THEN
   ERROR('My error message');
// actually here, I'd like to quit the report
// CurrReport.QUIT; ?????

Would this not be more accurate?

Sorry, I just fail to see where in your code that the 30/06/2009 or 01/07/09 is mentioned as a cut off...

Thanks again man. Appreciate all this as even though I am working on Navision attain 3.01A, I am studying NAV 5.0 SP1

DaveT

Hi Stephen,

I should have given a better explaination sorry :oops:

The code in the OnPreReport is getting the minimum date of the filter set on the Cust. Ledger Entry dataitem. If no filter is set then the date is set as 0D - i.e. a blank date. I was working under the assumption that it is dates prior to the range you want to exclude so for example with 01/01/08..31/08/09 you would want an error for NI Customers with posting date on or before 31/07/09 i.e. the cutoff date

The code in the OnAfterGetRecord in the Cust. Ledger Entry is to test the posting date against the date calculated in the OnPreReport trigger. So in the example I am checking if the posting date is less that 01/01/08 i.e. on or before 31/07/09

Note to set a date use :

CutoffDate := 300609D;

Hope this helps