Applying filter from current report to other report

stoocake

I am trying to run a report modally, that gets a customer number, runs another report and uses the current customer number as the "Sell-To Customer No." filter in the other report. How do I get it to automatically fill in the filter field of the 2nd report that is called?

Marije_Brummel

What is the dataitem that the second report uses? You should define a variable record of that type, apply the filter and give this as a parameter to the second report.


PS. I've created a sample for you email report which is available at the download section.

http://www.mibuso.com/dlinfo.asp?FileID=519

stoocake

Ok thanks. How do I pass the parameter to the 2nd report? So far I have:


SalesHeader.SETFILTER("No.",'',"No.");

Is this the correct way to declare the filter in the 1st report? Sorry to ask such simple questions but I've not been doing this long and I'm trying my hardest to learn quickly.

I am using the customer table for the first report, and the Sales Header for the 2nd.

Thanks for the download. I'll give that a try later. How should I change it to work with my current Sales Order summary report?

Marije_Brummel

The report I've made only works for customer-based reports.

You will have to modify it to make it fit for Sales Header reports.

In order to do this, define a sales header variable like my Cust.

Set the filters like

SalesHdr.Setrange("Sell-to Customer No.", "No.");

And replace the Cust parameter in the saveashtml by the SalesHdr.

This will now make a html file of all Sales Headers related to the customer and send this by email.

stoocake

Thank you. I'll try that just now. I edited my last message to give more information. How do I pass the filter from 1 table to the 2nd?

stoocake

I ran the report you sent but after a few tries, all it did was say 0 of 692 emails sent.

Any ideas?
I still can find how to push a filter set in 1 report onto the next one?

Marije_Brummel

First, a little warning. If the mail would succeed, there would be on their way, almost 700 test emails to you clients. Be carefull with that!

Be sure to test it on a fake email addres!

If you mail me your customised report, I will have a look at it.

You can also try to send a small testemail using the mail codeunit to find out if the problem is in the mail codeunit or in the report.

stoocake

I made sure to change the email address to my own before running it. I have managed to use the mail codeunit to send mail so I'm sure it's working.
The problem I'm still having is that it's sending the full Sales order summary report (all customers details) to just 1 customer. I will email you the fobs if you send me your email address.
Thank you.

Marije_Brummel

Try to change this please

Filename := ('c:\expedite\') + ("No.") + ('.htm');

SH.SETRANGE("Sell-to Customer No.","No.");

REPORT.RUNMODAL(50100, TRUE, FALSE, SH);

//Order.SETTABLEVIEW("Sales Header");
//Order.SAVEASHTML(Filename);

Mail.NewMessage('stewart.smyth@drondickson.co.uk','','Testing AER','Your Order Status',Filename,TRUE);

Still, you need to change the report.run to the HTML thing and add if/then to the mail statement, but you can copy that from my report.

Succes.

stoocake

Thank you very much. That seems to have fixed it. Now how do I tell the 2nd report not to output to a printer?
I wish I could return the favour - perhaps when I've been doing this for 8 years, like yourself, I'll be able to lend a hand.

Marije_Brummel

If you don't want to print you should replace the

Report.Runmodal

By

Report.SAVEASHTML

Like in my report.

And in 8 years, I am affraid, Navision will exist only in the History books. So everyone gets a clean start with Dynamics, and we will see who is helping who then

stoocake

If I change it to report.SAVEASHTML it runs it as one big file again, rather than break it up into seperate customers. Is there a way around that?

Marije_Brummel

Just do the same thing as to the Report.RUN

REPORT.SAVEASHTML(50100, FILENAME, FALSE, SH); 

Add the parameter, please.

stoocake

I have done that but it is generating an html file with over 700 pages.
I thought only the RunModal thing could divide it up?
Stewart

Marije_Brummel

It should work this way, realy. :?

Please repost you code with the SAVEASHTML.

stoocake

Indeed it does work now. I must have missed something lastnight but it's going now. Just going to sort out the formatting of the html and that'll be the report ready to run.
One more quick question though, if the "Outstanding Qty" is 0, can I tell it not to produce a report? Would a simple filter do this?

JK

Use IF ... = 0 THEN ..

CurrReport.BREAK

OR

CurrReport.SKIP

OR

CurrReport.QUIT

Depends on what you are going to do.

stoocake

That would work but the trouble is, by the time it has checked the "Outstanding Quntity" it has already generated the header and generates an html file with just the customer name and a title on it.

I've tried using:-
Lines := "Sales Line".COUNTAPPROX
If Lines = 0 THEN
CurrReport.QUIT;

..but then the report produces nothing, presumably because it can't count the lines from the header section of the report.
I've tried applying a filter in the properties of the data item, and also using the SETTABLE function, but I get the same problem; a header with a title on it.

Sorry to ramble on, but I wanted to explain more clearly.
Thank you

Marije_Brummel

If I understand you right, you don't want the report beeing generated under conditions.

Maybe better is to make a fuction in the mail report which returns false if the report should not be generated.

I hope you understand this, if not, just ask. :?

stoocake

I will give this a try and if I get nowhere I'll ask again. I'd rather try and work it out for myself if possible. Thank you for your generosity with your help.
I'll give the function thing a bash and get back to you if/when I get stuck.
Ta,
Stoo

stoocake

I have named the function NoReport and the boolean variable is called DoRun.
So far I have:

Customer - OnAfterGetRecord()
SH.SETRANGE("Sell-to Customer No.","No.");
Filename := ('c:\expedite\') + ("No.") + ('.htm');
Subject := "No.";
NoReport;



IF DoRun THEN
REPORT.SAVEASHTML(50100, Filename, FALSE, SH);
Mail.NewMessage('stewart.smyth@drondickson.co.uk','',Subject,'Attached is a list of outstanding orders',Filename,TRUE);



NoReport()
SL.GET(SH."No.",SL."Document No.");
NoLines := SL.COUNT;
IF NoLines <> 0 THEN
DoRun := TRUE
ELSE DoRun := FALSE;


But it still isn't working - same as before. Where am I going wrong?

Marije_Brummel

You can try

NoReport() 
SL.SETRANGE("Document No.", SH."No."); 
NoLines := SL.COUNT; 
IF NoLines <> 0 THEN 
DoRun := TRUE 
ELSE DoRun := FALSE; 

(The setrange instead of get)

stoocake

That didn't seem to make much difference I'm afraid. I can't get my head round this one?
Stewart

Marije_Brummel

Do you want to skip the sales documents without lines, or do you want to skip the ones where Outstanding Qty = 0?

If so, you should add this to you filter on the SL variable.

stoocake

I can filter it ok by lines with 0 Qty, or where Qty = Qty Shipped.
The problem now lies that we have orders that are cancelled but still show open. If an order is cancelled, the Qty is set to 0. If I filter by this then I still get the header. Usually when an order is cancelled the user types one of the following into the box:
Order Cancelled
order cancelled
Cancelled
cancelled
(note, it is case-sensitive)
I am having trouble filtering by the word cancelled. I have tried using the wildcard eg: *Cancelled*|*cancelled* but it doesn't seem to do anything.
How would I apply such a filter?

Marije_Brummel

I cannot quickly think of a way this filter can be applied.

You can however also add a line

If strpos(UPPERCASE(Field), 'CANCEL') <> 0 then
  MakeReport = FALSE;