Posted Purchase report

artpingartping Member Posts: 39
I am trying to create a report that will give me information from Table 122, 123. I added the tables as DataItems with the line table at top level and the header table indented. DataItemLink is set to No. and Document No. When I go to design the report I want the bulk of my information coming from the line table (No., Document #, Buy from Vendor #, Description, Direct Unit cost.) I want to add the posting date from the Header table to each transaction line and the ability to filter on posting dates. As soon as I add the Posting date to the Line body it changes the caption to a control. I suspect this is due to not bing able to link the posting date to a field in the line table?

Comments

  • ArhontisArhontis Member Posts: 667
    Yes... But you can modify the caption with the properties of the text box that the caption has has parent... :) To display the Posting date on the line you must place i.e. "Purchase Header"."Posting date" to the Source Expr property of the text box, where "Purchase Header" is the name of your header dataitem... (i don't exactly recall the name of the pusted purch. header...)

    Sorting on each field is by default in Nav, but you can add it in the ReqFilterFields property of a dataitem to make it visible without the user to add it in the list when running the report.

    Tip: To make a dataitem disappear from running the report and making the user unable to place a filter, then empty reqfilterfields property of the dataitem and choose a sorting at that dataitem...
  • artpingartping Member Posts: 39
    That worked great but if I enter dates to filter by posting date and No. it returns all the transactions from that account number and nothing in the posting date. If I filter just on No. it brings back all the transactions but with the posting dates. The intent is to use the report on a montly basis against certain expense accounts.
  • ArhontisArhontis Member Posts: 667
    Have you used indentation with the dataitems and the DataItemLink property of the child dataitem to link those two dataitems via Document No.?

    There is also the report 406 (a little complicated to start from) that you can see to take some ideas about those two tables...
  • adamEthanadamEthan Member Posts: 104
    Just my .02c

    When I first started making custom reports, if I ever tried to put the "line" table first and have the "header" be the indented line, there were usually problems with the filtering, or the information wouldn't match up, ever if the dataitemlink was correct

    i got around this by using flowfields on the main table i wanted to use. i don't know if this was the most efficient, or the most proper way to do things, but that's what i did and it worked #-o

    best of luck
  • ArhontisArhontis Member Posts: 667
         Parent Data Item (header)
           Child DataItem  (Line)
    
    Then the DataItemLink property of LINE dataitem must have something like that: Document No.=FIELD(No.)" (for those tables you use)

    And place in the Header ReqFilterFields property the "No." and in the Line ReqFilterFields property the "No." again.

    The header No. is the document number but the "No." of the line dataitem is the account. That way to get specific date and account you place 2 filters, one at the "No." of the header (first tab of report) to filter the document no. and then a second filter on the "No." of the line (SECOND tab of the report) to filter the accounts you want...

    You might want to add a third filter on the line for Type (to choose account type)...

    I hope it is more clear now... :)
    Practice Makes Perfect... Just experiment and use the help (F1 on the property of the report dataitem) to get additional info on how are used...

    Best of luck to you too...
  • adamEthanadamEthan Member Posts: 104
    Arhontis

    Yes that is the usual setup for some basic reports:

    However if the main filter you want to apply is on the line, for example "Planned Shipment Date" you must put like

    Child - Line
    Parent - Header

    because sometimes cannot get the child tables filtering to work?

    Is bad description of problem that can sometimes happen, I'm sorry.

    Flowfield was nice though
  • ArhontisArhontis Member Posts: 667
    I have never done something like that... don't you get second tab to be able to filter the lines? and place true to the property of the header as PrintOnlyIfDetail so that it will show you only the headers that have at least one line within the filters you have placed on the second tab (line filters)...

    In very special occasions you can also use variables and place them on the request form to be able to apply filters via code in the i.e. OnAfterGetRecord of the header, or OnPreDataItem of the line...
  • adamEthanadamEthan Member Posts: 104
    Arhontis wrote:
    I have never done something like that... don't you get second tab to be able to filter the lines? and place true to the property of the header as PrintOnlyIfDetail so that it will show you only the headers that have at least one line within the filters you have placed on the second tab (line filters)...

    Hrm this is something I have never tried. I will test sometime, although all my reports work now because i added flowfields to tables that have data I needed. thank u

    -adam
  • artpingartping Member Posts: 39
    Thanks for all the suggestions. I seem to be missing report 406? I have linked both tables with No. and Document No. The report pulls the data from the line table with no problem. The problem is that the Posting date doesn't filter the data like No. I would like to filter a create the report to show by account nomber and posting date so that I can show a specific period.
  • ArhontisArhontis Member Posts: 667
    Report 406 is not new, 3.70.A has it...

    About your task, in case you only need the header for filtering the "Posting Date" and only show data from the line sorted by Account no. then you should only have one dataitem, the line. Create a date variable and place it in the request form. Then in the OnAfterGetRecord of the Line dataitem do something like that:
    IF vPostingDate<>0D THEN BEGIN
      vPostedPurchHeader.GET("Document No.");
      IF vPostedPurchHeader."Posting Date"<>vPostingDate THEN
         CurrReport.SKIP;
    END;
    
    So that the report will ignore the lines that their header has a different "Posting Date" than the user entered in the request form...
  • artpingartping Member Posts: 39
    I have 3.70A and I don't see report 406. What is the report name? If I only use the line table will I be able to see the posting date on the report. So that each line has when it was posted?
  • ArhontisArhontis Member Posts: 667
    report 406:
    406 Purchase - Invoice NAVW13.70 21/05/03 12:00:00

    As for displaying the header posting date, then in the OnAfterGetRecord of line do something like that:
    vPostedPurchHeader.GET("Document No.");
    IF vPostingDate<>0D THEN BEGIN
      IF vPostedPurchHeader."Posting Date"<>vPostingDate THEN
         CurrReport.SKIP;
    END;
    
    and on the line body section (where you display your line info) add a text box with SourceExpr vPostedPurchHeader."Posting Date".
    The vPostedPurchHeader is a global record variable of table "Purch. Inv. Header".

    That way you can show a value of a variable on a section of a report...
  • ArhontisArhontis Member Posts: 667
    report 406:
    406 Purchase - Invoice NAVW13.70 21/05/03 12:00:00

    As for displaying the header posting date, then in the OnAfterGetRecord of line do something like that:
    vPostedPurchHeader.GET("Document No.");
    IF vPostingDate<>0D THEN BEGIN
      IF vPostedPurchHeader."Posting Date"<>vPostingDate THEN
         CurrReport.SKIP;
    END;
    
    and on the line body section (where you display your line info) add a text box with SourceExpr vPostedPurchHeader."Posting Date".
    The vPostedPurchHeader is a global record variable of table "Purch. Inv. Header".

    That way you can show a value of a variable on a section of a report...
  • artpingartping Member Posts: 39
    I don't have 406 but have 10121 Purchase Invoice. The only thing that I seem to be missing for the report is the filtering on Posting date. If I leave my request form empty and filter on account number it brings back all the transactions with the correct posting dates. IF I enter a date on the request form it brings back all the line transactions without any filtering taking place.
  • jversusjjversusj Member Posts: 489
    artping wrote:
    I don't have 406 but have 10121 Purchase Invoice. The only thing that I seem to be missing for the report is the filtering on Posting date. If I leave my request form empty and filter on account number it brings back all the transactions with the correct posting dates. IF I enter a date on the request form it brings back all the line transactions without any filtering taking place.

    consider how dataitems are run - a record will be brought up for the parent and if a report section exists, it will be written. then it will step into the child and, again, if a section exists it will be written. this continues for each until no more records are found.
    line --> write report section
    ___header --> write section
    it sounds like your report is using body sections on the line item, which are being created before you even look at the header data item. your filter is happening after you have already created the output.

    like the others said in this post, you can check the posting date within the code of the line data item by calling a variable header record. you can still use the header dataitem if you really want, and even let users enter filters on it - then you would COPYFILTERS to your variable record in the line data item (refer to arhontis' post) and pass those user filters to where you need them used.
    kind of fell into this...
  • ArhontisArhontis Member Posts: 667
    Hi,

    What variable have you placed on the request form? It must be the one you have created... (vPostingDate in the sample code)

    And you should use the sample code in the OnAfterGetRecord of the Line dataitem...

    It is simple, just review your report a little, experiment a little and you will get it... :)
Sign In or Register to comment.