Load Temporary Table with Data From Three Tables

Business Central/NAV Developers Forum
1

NAV2009 Clasic

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.

The user will select Inventory Posting Group and Posting Date using the ReqFilterFields on the Value Entry.

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;

What I need to do with this Temp table is to group Invoiced Quantity and Sales Amount (Actual) by Region and Model for the report output.

Rather new to this so any help would be appreciated.

Thanks

2

if you want to create a sales report check first the standard sales report: 204, 205, 206

maybe in your case you could create a dataitem structure starting with sales header.

dataitems

  • sales header
    – customer
    – sales line
    — item
    — value entry

something like that depending on your concrete needs and which fields you want to display and how (grouping, …).

3

Jonathan, thanks for the reply. Customer Dimension 1, Model and Series from the Item card are not part of any keys (Custom fields) and report has to be grouped by these fields. I will also be able to use this temp table for future reporting by adding fields, keys etc. Just need a little help loading up the temp table.

Thanks

4

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

5

I think you’ve nailed it so far as the report grouping requirement goes; you’ve created an appropriate key on your temporary table.

So far as your other objective that would allow you to use this same temporary table for other reports, you have a couple of choices. The brute-force method would have you creating new fields and keys in this buffer table every time you needed to support another report, allowing for any overlaps that might conveniently present themselves (maybe you need Model in more than one report?). A more polished approach might be to abstract all of your field definitions in your buffer table so that they would work equally well for all reports. So, instead of having fields named Region, Model, Series, you would make fields named Code01, Code02, etc, Date01, Date02, Decimal01, and so on. Then you’d create keys based on these fields, using the sort sequences your various reports would need. Then, for each special report, you’d only need to populate the right fields in the report buffer to establish your PK and your report data, then ensure you’ve defined and selected the right key for that report.

I’ve used the second approach with some success. There’s a bit of overhead that the approach imposes on the developer, but once you’ve got it down, adding new reports is pretty easy.

6

George, thanks for the reply and info, very usefull. I don’t think my code for loading the temp table is correct. When I run I get Temp Sales Entry No ‘0’ Already Exists. Do I need some sort of record counter or something? Also in the Integer DataItem, in the OnPreDataItem code, can I user the CurrReport.CREATETOTALS function to summarize my data?

Thanks for the help.

7

George, thanks for reply and info, very usefull. I do 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 summarize my data?

Thanks for the help. Almost there.

8

On your error related to PK violation (… already exists), I’ll take a wild guess at it. Looking at your code, if you have a global variable named ValueEntry that is not the same record as the data item “Value Entry” (meaning that you didn’t name your data item ValueEntry as opposed to the default value “Value Entry”), then it’s likely that your global variable named ValueEntry is not initialized at the point of assignment for the TempSales.“Entry No.” field. If that’s the case, the in “Value Entry”.OnAfterGetRecord, you’ll need to GET a copy of that record into ValueEntry before you can assign values from that record into TempSales.

I hope that makes sense.

9

Thanks George, I’ll have a look.