Temporary tables in Navision: use them with intelligence :)

<< Telligent Software loves the .NET Communities | Home | Google Talk silent upgrade? >>

Temporary tables in Navision: use them with intelligence :)

For the guys that read my blog and are interested in Navision programming, I want to talk a bit about a great Navision's feature: the temporary tables.

A Temporary Table is created by C/SIDE when a record variable is created by setting its Temporary property to Yes.

It is created with the same exact structure of the real table that is in the subtype property of the record variable. It is, however, completely empty (no data). This type of table can be used as a temporary workspace to insert records, manipulate them and then either put them into a real table or allow them to disappear. No data in a Temporary Table will ever be stored on the server (and to save records that are in a Temporary Table to the server, you must copy them to another record variable that is not tied to a temporary table).

As you can see the figure below, two record that points to the Customer table are created. The first record points to the real Customer table in the database (with all its records), and the second record points to a temporary table (with the same structure as the Customer table but empty).

What it's important to remember is that a temporary table use the system memory to store its data, so it's extremely resource consuming. If you have a table with lots of data, the system memory can become full and the system could start to swap on the disk (with poor performances). When the free disk space become not enough to the swapping system, Navision could show you some strange errors (like "unable to open floppy", unable to write file" etc.).

So remember... temporary tables are extremely useful and with lots of advantages to use, but they must be controlled and used with intelligence (not store too much data on them) or all their advantages can quickly become disadvantages on system's performances.

Print | posted on Wednesday, August 31, 2005 9:35 PM

Comments on this post

# re: Temporary tables in Navision: use them with intelligence :)

Thanks for the tip.
One more question about Navision performance, coz no one of our consultants can help me.
There is a column "Cost Amount (Actual)" in "Item Ledger Entry" table, but it's FlowField.
Question is how to sum values from this field without simple loop through each record.
I can't sum the same field in "Value Entry" table because it's filtered by "Posting Date" and they are not the same in both tables.
Is there a possibility to use something like sql join in Navision?

Please help me because this loop is working longer and longer each day in my report - date filter looks like this: ''..(today).

Left by SeeR on Sep 01, 2005 4:58 AM

# re: Temporary tables in Navision: use them with intelligence :)

The field "Cost Amount (Actual)" in Item Ledger Entry table is defined as:
Sum("Value Entry"."Cost Amount (Actual)" WHERE (Item Ledger Entry No.=FIELD(Entry No.)))
To sum all the "Cost Amount (Actual)" in Item Ledger Entry table I think that the solution is a loop, because you cannot do a flowfield of a flowfield.
This is what I undertand of your explanation above... what type of join do you think to do?

Left by Stefano Demiliani on Sep 01, 2005 5:54 AM

# re: Temporary tables in Navision: use them with intelligence :)

I thought about something like this

SELECT SUM(dbo.[company$Value Entry].[Cost Amount (Actual)]) AS EXPR1
FROM dbo.[company$Item Ledger Entry] INNER JOIN
dbo.[company$Value Entry] ON dbo.[company$Item Ledger Entry].[Entry No_] = dbo.[company$Value Entry].[Entry No_]
WHERE (dbo.[company$Item Ledger Entry].[Location Code] = '01') AND (dbo.[company$Item Ledger Entry].[Posting Date] < CONVERT(DATETIME,
'2005-07-01 00:00:00', 102))

This sql sums all what I need in one second instead of 10 in c\al loop.

Left by SeeR on Sep 01, 2005 11:56 AM

# re: Temporary tables in Navision: use them with intelligence :)

Maybe you can filter the Item Ledger Entry for Location Code and Posting Date (with a SETRANGE and a SETFILTER instruction) and then launch (via code) a CALCFIELD of Cost Amount (Actual).

Left by Stefano Demiliani on Sep 01, 2005 12:08 PM

# re: Temporary tables in Navision: use them with intelligence :)

This is exactly the way I'm doing this, here is my code:

entry - OnPreDataItem()
---------------------------------
itemEntry.RESET;
openBalance := 0;

itemEntry.SETRANGE(itemEntry."Posting Date", 0D, entry.GETRANGEMIN("Posting Date")-1);
itemEntry.SETFILTER(itemEntry."Location Code", entry.GETFILTER(entry."Location Code"));
IF itemEntry.FIND('-') THEN
REPEAT
itemEntry.CALCFIELDS(itemEntry."Cost Amount (Actual)");
openBalance := openBalance + itemEntry."Cost Amount (Actual)";
UNTIL itemEntry.NEXT = 0;

Left by SeeR on Sep 01, 2005 1:14 PM

# re: Temporary tables in Navision: use them with intelligence :)

Is this the loop that you want to avoid?

Left by Stefano Demiliani on Sep 01, 2005 1:22 PM

# re: Temporary tables in Navision: use them with intelligence :)

yes, because it works on the Navision side. Database will do it much more quicker

Left by SeeR on Sep 01, 2005 2:06 PM

# re: Temporary tables in Navision: use them with intelligence :)

Hmm
no response.

I assume that you also don't have resolution for this problem

:-(

Left by SeeR on Sep 02, 2005 11:40 AM

# re: Temporary tables in Navision: use them with intelligence :)

No response because I'll do it exactly like you... Navision works like this, at the moment I've no other ideas...

Left by Stefano Demiliani on Sep 02, 2005 11:51 AM

# re: Temporary tables in Navision: use them with intelligence :)

Thanks anyway :-)

Left by SeeR on Sep 02, 2005 8:17 PM

# re: Temporary tables in Navision: use them with intelligence :)

Thank you for the article. I'm trying to find information on how you would write records from multiple tables to a temp table to be able to sort on a field that would otherwise be in a sub-table (an "indented" table).

Can you give an example of this?

Left by a navision user on Oct 18, 2006 6:50 PM

# re: Temporary tables in Navision: use them with intelligence :)

Can you be more precise and explain what you want to do? Sorry but I've not understood the problem.

Left by Stefano Demiliani on Oct 19, 2006 6:01 AM

# re: Temporary tables in Navision: use them with intelligence :)

hi stefano,

I try to use temp table in my report, using integer data item to loop the temp table, but the problem seems that I can't make it to print my first record, since on predataitem I have to put find('-') then on postdataitem trigger I create a code (next = 0) to detect is it at the end of the record (if yes then break). This way, I could never print the first record, any idea how to solve this ? Because by using temp table, i have to populate all data into this temp table and that means the cursor will stay in the last record.

Left by ayashi on May 25, 2007 6:10 AM

# re: Temporary tables in Navision: use them with intelligence :)

I've not totally understood what is your problem.
Can you post here a piece of your code?

Left by Stefano Demiliani on May 25, 2007 11:34 AM

# re: Temporary tables in Navision: use them with intelligence :)

i have a problems. we use LS retail with navision. what happans after pressing cash the transaction complet but the screen fadded out.
and the button does not work any more.it seems to system hung.but After pressing the mouse left button frequently several time, normal screan appear again. i found that increase of DBMS cache size some times solve it.but some times it does not work for long time.is it related to database size. any one faced this type of problem. can tempory table use can help me?

Left by Hasan on Jul 02, 2007 12:26 PM

# re: Temporary tables in Navision: use them with intelligence :)

How is the free size of your DB (%)? Do you have many flowfield on the mask that you want to open (if any)?

Left by Stefano Demiliani on Jul 02, 2007 12:29 PM

# re: Temporary tables in Navision: use them with intelligence :)

when we use more than one user in one database using client-server (online architecture).what happens LS Retail on Navision (3.7) does not allow more than one transaction at a time, it shows the message "Lock by super/other user “.and automatically after the another transaction completed this transaction also complete but the screen become faded out/discolored/gray. (The main sale window) And the button does not work any more. It seems to system hung. But After pressing the mouse left button frequently several time, normal screen appear again. Any one has a clue about problem please reply.

Left by Hasan on Jul 09, 2007 5:06 AM

# re: Temporary tables in Navision: use them with intelligence :)

Hasan, this is not a standard problem in NAV. Do you have custom code? Have you checked the LOCKTABLE operations?

Left by Stefano Demiliani on Jul 09, 2007 9:37 PM

# re: Temporary tables in Navision: use them with intelligence :)

Make me Learn How to record Variables in building reports....

Left by Jagan Mohan on Aug 08, 2007 11:49 AM

# re: Temporary tables in Navision: use them with intelligence :)

Hi,

Thank you for the article. I am new to navision. I want to know whats the use of Integer table. I find in reports it has been used. But i cant understand how to use it and why its using.

Thanks in advance.

Left by sangeetha on Dec 11, 2007 7:03 AM

# re: Temporary tables in Navision: use them with intelligence :)

Integer table in NAV is mainly used for looping operations, to print temporary tables in reports or to print a certain information exactly n-times.

Left by Stefano Demiliani on Dec 11, 2007 10:29 PM

# re: Temporary tables in Navision: use them with intelligence :)

Also remember that Temp Records will never be cleared. If you do a ClearAll(); All your variables will be cleared, only your Temp Record will stay intact.

This can come in handy when you need to clear most of your variables, but need some others to stay as they are. All you need to do is put these vars in a temp table before you do a clearall.

Grtz
Nix

Left by Nix on Feb 11, 2008 10:00 AM

# re: Temporary tables in Navision: use them with intelligence :)

Hi Stefano,

I'm not sure that you are right with temp tables.....Of my knowledge when you create a temp table Navision creates a temp file in in %TEMP% folder of your profile and use that file instead of RAM.....my testing is based on Navision 4 Sp3 and SQL 2000...it is quite easy to verify, just create a function to fill a temp table with 100000 records and then check the temp folder and the ram while is running...
What do you think Stefano?

Ciao!

Left by Taddeo on Nov 07, 2008 3:27 PM

# re: Temporary tables in Navision: use them with intelligence :)

When using Temporary tables in NAV, all database operations take place only in system memory during the applications run.
Advantage of temporary table is that all interaction with at temporary table takes place at client (this reduces load on network and server).
When you use a temporary table to store a large number of data, NAV obviosuly comes the temporary file in help (it has a max size of 2GB) and I've observed many times that there's a limit on the number of records you can store on a Temp table.
I suggest you this forum entry:
http://dynamicsuser.net/forums/p/18534/85089.aspx

Left by Stefano Demiliani on Nov 07, 2008 4:01 PM

# replica jewelry

I'm not sure that you are right with temp tables.....Of my knowledge when you create a temp table Navision creates a temp file in in %TEMP% folder o

Left by replica jewelry on Apr 29, 2010 5:22 AM

Your comment:

Title:

Name:

Email: (never displayed)

(will show your gravatar)

Website:

Comment: Allowed tags: blockquote, a, strong, em, p, u, strike, super, sub, code

Please add 6 and 4 and type the answer here:

STEFANO DEMILIANI