Temporary Table for Reporting

midnightmidnight Member Posts: 74
Need to create a temporary table with data from two or three tables, create my own fields in the temp table and key for sorting. I have read about creating temp tables, but based on existing tables. Can someone point me in the right direction.

Thanks

Comments

  • tinoruijstinoruijs Member Posts: 1,226
    Check out this post; http://mibuso.com/forum/viewtopic.php?f=32&t=62634

    With development license you can create tables with all fields and keys you want.

    Tino Ruijs
    Microsoft Dynamics NAV specialist
  • midnightmidnight Member Posts: 74
    Thanks for the reply. I have created a new table (Temp Sales) in the 50,000 range with the fields and keys that are required to be used as a temporary tabel. The data will be coming from three tables. Customer, Value Entry and Item. Now I just have to load my Temporary table with the records and do the report and having some difficulties. Here is what I have so far:

    Data Item:

    Customer ReqFilterFields Customer Posting Group
    Value Entry ReqFilterFields Inventory Posting Group,Posting Date
    Integer For reading Temp Table and producing report (not sure about indenting etc)

    C/AL Globals:

    Item Record Item
    ValueEntry Record Value Entry
    TempSales Record Temp Sales (Temporary set to yes)

    This is where I'm not sure about loading the records into the Temp table to be used in the Integer data item for producing the report. I'm sure this is incorrect:

    Value Entry - OnAfterGetRecord()

    // Get Item Record

    Item.GET("Item No.");

    // Load Temporary Table for Report

    TempSales.INIT;
    TempSales."Entry No." := ValueEntry."Entry No.";
    TempSales.Region := Customer."Customer Dimension 1";
    TempSales.Model := Item.Model;
    TempSales.Series := Item.Series;
    TempSales."Item No." := "Item No.";
    TempSales."Cust No." := Customer."No.";
    TempSales."Inventory Posting Group" := ValueEntry."Inventory Posting Group";
    TempSales."Invoiced Quantity" := ValueEntry."Invoiced Quantity";
    TempSales."Sales Amount (Actual)" := ValueEntry."Sales Amount (Actual)";
    TempSales."Cost Amount (Actual)" := ValueEntry."Cost Amount (Actual)";
    TempSales."Discount Amount" := ValueEntry."Discount Amount";
    TempSales.INSERT;

    Rather new to this so any help would be appreciated.

    Thanks
  • midnightmidnight Member Posts: 74
    Basically what I'm trying to do here is data is filtered from the Cusomer, Value Entry tables by by the ReqFilterFields. Records are then loaded into the Temporary table. I then read the Temporary table and total the Invoiced Quantity, Sales Amount (Actual), Cost Amount Actual by Region/Model. One of my keys on the Temp table is Region,Model. Something Like:

    Region Model Invoiced Qty Sales Amount Cost amount

    EU 5000 100 3000 1000

    EU 6000 200 4000 2000

    NA 5000 50 1000 500

    NA 6000 150 1500 800

    Almost there, just need a little help loading up the temp table.

    Thanks
  • lubostlubost Member Posts: 611
    Hi midnight,

    the best solution:
    1. create new dataitem with no intendation with source table Integer. Set the TableView with filter Number=FILTER(1..)
    2. OnPreDataItem code:

    <your temptable>.RESET;
    <your temptable>.SETCURRENTKEY(Region, Model);
    IF NOT <your temptable>.FINDFIRST THEN
    CURRREPORT.BREAK;

    3. OnAfterGetRecord code:

    IF Number = 1 THEN
    <your temptable>.FINDFIRST
    ELSE
    IF <your temptable>.NEXT = 0 THEN
    CURRREPORT.BREAK;
    (your code here)
  • Marije_BrummelMarije_Brummel Member, Moderators Design Patterns Posts: 4,262
    Hi evenyone,

    There is a design pattern for this and a How Do I video:

    https://www.youtube.com/watch?v=QHn5oEO ... _w&index=8

    If you have any more questions, you can also use the design pattern section on the forum.
  • midnightmidnight Member Posts: 74
    lubost;

    Thanks for the info much appreciated. I will use that for reading the temp table. I think my code above is incorrect for loading the records into the temp table. When I run I get Temp Sales Entry No '0' Already Exists. Do I need some sort of counter for the records? Also in the Integer dataitem in the OnPreDataItem code, can I use the CurrReport.CREATETOTALS function to create my summary report?

    Thanks for you help, alomost there.
Sign In or Register to comment.