mibuso.com
Home› NAV/Navision Classic Client

List of Order

prashantiprashanti Member Posts: 96
edited 2012-12-10 in NAV/Navision Classic Client
Hi Experts!!

In my setup I have defined 2 global dimensions and 3 shortcut dimensions.

So when I created the order I have assigned all the dimensions attached to the order for one customer.

I want to view the list of orders with this dimensions. As your'll all know that in sales header only global dimension is saved and rest dimensions are attached to the sales line. I want to view all the order with their names and differenct line amounts with the five dimensions records attached to it in one short.

How to do it.

Regards
Prashanti

Comments

  • prashantiprashanti Member Posts: 96
    Somebody please help me out in my problem.
  • David_SingletonDavid_Singleton Member Posts: 5,479
    What have you tried so far? What are the errors? What issues are you having?

    Or are you asking for some member here to do all the work for you?
    David Singleton
  • DivyaDivya Member Posts: 125
    prashanti wrote:
    Hi Experts!!

    In my setup I have defined 2 global dimensions and 3 shortcut dimensions.

    So when I created the order I have assigned all the dimensions attached to the order for one customer.

    I want to view the list of orders with this dimensions. As your'll all know that in sales header only global dimension is saved and rest dimensions are attached to the sales line. I want to view all the order with their names and differenct line amounts with the five dimensions records attached to it in one short.

    How to do it.

    Regards
    Prashanti

    It is just a report.. Do you know how to design a report..........
    Victory is sweetest when you've known defeat
  • prashantiprashanti Member Posts: 96
    Ya know how to design the report. An I have designed the same with the two tables but when I preview the same I get to view all the records and its not getting filtered properly.
  • rsaritzkyrsaritzky Member Posts: 469
    prashanti wrote:
    Hi Experts!!

    In my setup I have defined 2 global dimensions and 3 shortcut dimensions.

    So when I created the order I have assigned all the dimensions attached to the order for one customer.

    I want to view the list of orders with this dimensions. As your'll all know that in sales header only global dimension is saved and rest dimensions are attached to the sales line. I want to view all the order with their names and differenct line amounts with the five dimensions records attached to it in one short.

    All the dimension values for each order and each order line are stored in the "Document Dimension" table - Table 357.

    In your OnAfterGetRecord trigger for "Sales Header", you can write code something like this:

    DocumentDimension.SETRANGE("Table ID", 36); // Sales Header is table 36
    DocumentDimension.SETRANGE("Document Type", DocumentDimension."Document Type"::Order);
    DocumentDimension.SETRANGE("Document No.", "Sales Header"."No.");
    DocumentDimension.SETRANGE("Dimension Code", <Name of your first dimension, e.g. DEPARTMENT>);
    IF DocumentDimension.FINDFIRST THEN
    GlobalVariableDim1Value := DocumentDimension."Dimension Value Code");

    DocumentDimension.SETRANGE("Dimension Code", <Name of your second dimension, e.g. PROJECT>);
    IF DocumentDimension.FINDFIRST THEN
    GlobalVariableDim2Value := DocumentDimension."Dimension Value Code");

    DocumentDimension.SETRANGE("Dimension Code", <Name of your third dimension, e.g. AREA>);
    IF DocumentDimension.FINDFIRST THEN
    GlobalVariableDim3Value := DocumentDimension."Dimension Value Code");

    DocumentDimension.SETRANGE("Dimension Code", <Name of your fourth dimension, e.g. BUSINESSGROUP>);
    IF DocumentDimension.FINDFIRST THEN
    GlobalVariableDim4Value := DocumentDimension."Dimension Value Code");

    DocumentDimension.SETRANGE("Dimension Code", <Name of your fifth dimension, e.g. BUSINESSGROUP>);
    IF DocumentDimension.FINDFIRST THEN
    GlobalVariableDim5Value := DocumentDimension."Dimension Value Code");


    After this code is executed, you have have your 5 global variables GlobalVariableDim1Value, GlobalVariableDim2Value, etc. and you can display them on the report.


    Also, If the dimensions on the individual lines can be different than those on the header (depends on how you use the application), you can do the same thing in the OnAfterGetRecord trigger of the Sales Line table:

    DocumentDimension.SETRANGE("Table ID", 37); // Sales Line is table 37
    DocumentDimension.SETRANGE("Document Type", DocumentDimension."Document Type"::Order);
    DocumentDimension.SETRANGE("Document No.", "Sales Line"."Document No.");
    DocumentDimension.SETRANGE("Dimension Code", <Name of your first dimension, e.g. DEPARTMENT>);
    IF DocumentDimension.FINDFIRST THEN
    GlobalVariableDim1LineValue := DocumentDimension."Dimension Value Code");

    DocumentDimension.SETRANGE("Dimension Code", <Name of your second dimension, e.g. PROJECT>);
    IF DocumentDimension.FINDFIRST THEN
    GlobalVariableDim2LineValue := DocumentDimension."Dimension Value Code");

    DocumentDimension.SETRANGE("Dimension Code", <Name of your third dimension, e.g. AREA>);
    IF DocumentDimension.FINDFIRST THEN
    GlobalVariableDim3LineValue := DocumentDimension."Dimension Value Code");

    DocumentDimension.SETRANGE("Dimension Code", <Name of your fourth dimension, e.g. BUSINESSGROUP>);
    IF DocumentDimension.FINDFIRST THEN
    GlobalVariableDim4LineValue := DocumentDimension."Dimension Value Code");

    DocumentDimension.SETRANGE("Dimension Code", <Name of your fifth dimension, e.g. BUSINESSGROUP>);
    IF DocumentDimension.FINDFIRST THEN
    GlobalVariableDim5LineValue := DocumentDimension."Dimension Value Code");


    Does this get you started?

    Ron
    Ron
  • murtazakhanmurtazakhan Member Posts: 11
    Hi Ron,
    I saw your post regarding the subject matter for specific dimensions. Can you please tell me what changes would I have to make to the script to get all sales orders, invoiced and outstanding, between two dates.

    ](*,) I have been banging my head with the wall and could find anything.
    Best regards,

    Murtaza.
  • rsaritzkyrsaritzky Member Posts: 469
    murtazakhan wrote:
    I saw your post regarding the subject matter for specific dimensions. Can you please tell me what changes would I have to make to the script to get all sales orders, invoiced and outstanding, between two dates

    Murtaza,

    Outstanding Sales orders are in the Sales Header table (36) with “Document Type” = “Order”
    Invoiced Sales Orders are in the Sales Invoice Table (Table 112).

    The most straightforward way to do this type of report is to have 2 dataitems, treated separately. If this is OK, then set a filter on the dataitem. You can set the filter in the Request Form if you want it to be user specified. The request form should display “Document Date” (or whatever date you wish to filter on). You can ask the user to enter the date filter twice (once for Sales Header and once for Sales Invoice Header), or in your code, copy the filter from one to the other.
    For this example, assume you have 2 dataitems:

    1. Sales Header
    2. Sales Invoice Header

    In the properties for Sales Header, set the ReqFilterFields property to include “Document Date” (or whatever date field you wish to filter on.
    In the properties for Sales Invoice Header, set the DataItemTableView property to include a key – that way, the request form will NOT display the Sales Invoice Header dataitem.
    Then, in the “OnPreDataItem” trigger, copy the filter from the Sales Header to the Sales Invoice Header:
    “Sales Header”.COPYFILTER(”Document Date”, “Sales Invoice Header”.“Document Date”);

    This way, both dataitems will be filtered the same. If you have multiple filters (e.g. Document Date and Customer No.), you will need to copy each of the filters.

    Copy the code from the first dataitem to the second once you have it worked out (e.g. retrieving the dimensions) so that you have the same fields of information.

    Create two report sections that have the same fields. You could look at Report 10408 “Bank Reconciliation” – that report has 4 sections for Checks, Deposits, Outstanding Checks and Outstanding Deposits that are copies of each other.

    If you want the open and outstanding invoices sorted/grouped together (e.g. all invoices open and outstanding for each customer), then you would first have to loop through the Sales Header and copy the records to a temporary table, then loop through the Sales Invoice Header records and copy them to the same table. These first 2 dataitems would not print anything. Then, you can sort the temporary table by Customer and print the records from that dataitem.

    Note that these techniques are based on the Classic Report Designer. When using RDLC reports in NAV2009, there are additional things that you may need to do. Also, some additional filters may have to be set, e.g. if you want to separate documents originally entered as type "invoice" vs. type "order". Also, Sales Credit Memos are in a third table.

    I hope this helps.
    Ron
  • murtazakhanmurtazakhan Member Posts: 11
    Thanks Ron, I got it.
    I do not know much report designing in Navision but I got the data from the tables you mentioned into Excel and it gave me exactly what I wanted after some tweaks.

    One more thing about the Sales Orders again. There is an input field in Sales Order form called "Sales Type". I have tried all my efforts to find out in which table does the data of "Sales Type" in the Header of a Sales Order go but have failed. I checked its properties and it gives sourceexpr as "ShortcutDimCode[3]" then I tried any possible table I could think of but could not find this column in any table.

    Can you please help me with this.

    Thanks

    Murtaza
  • rsaritzkyrsaritzky Member Posts: 469
    murtazakhan wrote:
    One more thing about the Sales Orders again. There is an input field in Sales Order form called "Sales Type". I have tried all my efforts to find out in which table does the data of "Sales Type" in the Header of a Sales Order go but have failed. I checked its properties and it gives sourceexpr as "ShortcutDimCode[3]" then I tried any possible table I could think of but could not find this column in any table.
    Hi Murtaza,

    If the field is "ShortcutDimCode3", then this is one of your Dimensions - dimension 3. You can verify this by going into Dimensions (G/L menu,Setup,Dimensions) and you should see "Sales Type" as one of the lines.

    Dimensions 1 and 2 are stored on the Sales Document (e.g. Invoice, Order) record. Dimensions 3-8 are stored in the "Document Dimension" table. The link between the Sales Header (or Sales Invoice Header or any other table) and the Document Dimension table is by Table Number, Document Type and Document Number. For example, Sales Header is Table 36. So for Sales Header document number 12345, document type "Order", the link is Table ID = 36, Document Type = Order Document No. = 12345, Line No. = 0 (header records use line number 0 always, line records use the line number), and Dimension Code "SALES TYPE".

    I hope this will give you a start on this.
    Ron
Sign In or Register to comment.