Applying filter from current report to other report

stoocake
stoocake Member Posts: 32
edited 2005-10-04 in NAV/Navision Classic Client
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?

Comments

  • Marije_Brummel
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    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
    Marije Brummel | Freelance Business Central Specialist
  • stoocake
    stoocake Member Posts: 32
    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
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    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.
    Marije Brummel | Freelance Business Central Specialist
  • stoocake
    stoocake Member Posts: 32
    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
    stoocake Member Posts: 32
    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
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    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.
    Marije Brummel | Freelance Business Central Specialist
  • stoocake
    stoocake Member Posts: 32
    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
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    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. :D
    Marije Brummel | Freelance Business Central Specialist
  • stoocake
    stoocake Member Posts: 32
    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
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    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 :D
    Marije Brummel | Freelance Business Central Specialist
  • stoocake
    stoocake Member Posts: 32
    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
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    Just do the same thing as to the Report.RUN
    REPORT.SAVEASHTML(50100, FILENAME, FALSE, SH);

    Add the parameter, please.
    Marije Brummel | Freelance Business Central Specialist
  • stoocake
    stoocake Member Posts: 32
    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
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    It should work this way, realy. :?

    Please repost you code with the SAVEASHTML.
    Marije Brummel | Freelance Business Central Specialist
  • stoocake
    stoocake Member Posts: 32
    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
    JK Member Posts: 21
    Use IF ... = 0 THEN ..

    CurrReport.BREAK

    OR

    CurrReport.SKIP

    OR

    CurrReport.QUIT

    Depends on what you are going to do. :)
  • stoocake
    stoocake Member Posts: 32
    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
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    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. :?
    Marije Brummel | Freelance Business Central Specialist
  • stoocake
    stoocake Member Posts: 32
    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
    stoocake Member Posts: 32
    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
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    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)
    Marije Brummel | Freelance Business Central Specialist
  • stoocake
    stoocake Member Posts: 32
    That didn't seem to make much difference I'm afraid. I can't get my head round this one?
    Stewart
  • Marije_Brummel
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    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.
    Marije Brummel | Freelance Business Central Specialist
  • stoocake
    stoocake Member Posts: 32
    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
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    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;
    Marije Brummel | Freelance Business Central Specialist

Categories