Temporary Table for Reporting

midnight

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

tinoruijs

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.

midnight

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

midnight

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

lubost

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_Brummel

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.

midnight

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.