Blog

Temporary tables in NAV are a great thing, and are frequently used, but there are some misconceptions about them. I see developers do the same mistakes time after time and again. In this post I’ll address some common misconceptions and give some tips and hints that you can use in practice.

There may be a lot of basics for you here, in which case just skip to the end: there I give you a nice tip about how to prevent accidental changes to physical tables when you expect that a table is temporary, and in fact it is not.

A temporary table is a record variable on which you have set the Temporary property to Yes. When you do this, the record variable has the same structure as the physical table, but it is only stored in memory. When you create a temporary record variable, the temporary table is empty at first, and then you can insert, modify, or delete data in it at will, and it does not affect the physical table. I’m quite sure you knew this much, but I repeated it here just to show you how smart I am

Temporary Tables in Function Parameters

One thing that is frequently done is passing around temporary tables to functions as parameters. What often goes wrong here is that people tend to think that inside the function the table is temporary because the variable passed to it is temporary. In fact – it’s not. Or at least not always.

First thing to know about record variables is that a record variable does not represent the whole table. It only represents as much as it says on the tin: the record – or to be as precise as it gets – a single record (row) in a table. The Temporary property is simply telling you where the runtime will store the changes you make if you call any of the data modification functions, or where it will look for more records if you iterate through data or run any of the FIND functions.

Another piece of theory is about function parameters. A parameter can be by value or by reference. By value means that the runtime establishes a new in-memory space for the parameter and when you call the function the value of the variable (or constant, or expression) passed to the function is copied into this freshly allocated memory space – literally you get a copy of the original value. By reference means that the runtime literally takes the variable into the function and the parameter is merely another name tag attached to the same variable that was passed to the function.

Last piece of theory here is about record type as a function parameter. When declaring a parameter of type record, you can set its Temporary property to Yes or No, as with any other record variable.

If you combine the last two theory bits, about parameters, and about record type, you may believe that you have these four combinations:

• Record, by value
• Temporary record, by value
• Record, by reference
• Temporary record, by reference

In fact, you don’t. There are only three combinations:

• Record by value
• Temporary record by value
• By reference

The first two are simple: if the parameter is by value, then the Temporary property on the parameter determines whether within the function the table will be temporary, or physical.

Check this out:

Within the first two functions, the actual state of the Cust record is determined by the Temporary property set on the parameter. If you pass a physical record variable to DoSomething_Temporary, the field values of that physical record will get copied to the Cust record, but the Cust will continue to be temporary.

Just as if you did this: Cust := Customer_Physical;

The same applies if you call DoSomething_Physical and pass the Customer_Temporary to it. The field values are copied, but Cust refers to the physical table.

Furthermore, when you have Temporary set to Yes on a by-value parameter, that temporary table is always empty when the function is called. Just as if you declared a local temporary record variable. That’s because the runtime first establishes a new memory space for the by-value parameter.

So, this covers the first two cases. Now for the third case.

If the record parameter is by reference, then the Temporary property makes absolutely no difference. These two are essentially equal:

When you pass a variable by reference, you don’t allocate new memory space: you simply pass the whole variable. The Temporary property is then taken from the variable, regardless of what you set as the Temporary property on the parameter itself. If you pass a physical table to a temporary by-reference parameter, the parameter record within the function is physical. Also, if you pass a temporary table to a non-temporary by-reference parameter, the parameter record within the function is temporary.

Not understanding this may have devastating effects on your data. Imagine this:

This deletes all of your customers from the physical table (those in the filter, at least). The Temporary property had no effect – you have passed the whole variable including all of its properties, including Temporary.

Therefore, setting Temporary on a by-reference parameter makes absolutely no difference. Never forget this.

Temporary Records and Transactions

Another misconception about temporary records is that they are managed as transactions. They are not. When you call COMMIT, it does not affect your temporary tables. Also, if you call ERROR, it won’t rollback any inserts, changes, or deletions from your temporary tables.

There is not much rocket science around this, really.

SourceTableTemporary

The SourceTableTemporary property on pages (or forms in 5.0 and 2009) allows you to run pages that only work over temporary data. When you run them, they are empty, and then you need to populate them with data. You do that by simply filling in the data in the Rec variable.

One typical problem I’ve frequently seen with pages (or forms) over temporary data, is that the Rec variable is passed by reference, and then iterated over. That’s not the smartest thing in the world. In certain versions of clients, this may cause phantom inserts – if you are positioned on the new row, and then call a function which iterates over a range of rows to do something, and then leaves the row positioned on an actual row.

For example:

Let’s set the phantom inserts aside for a while, and just apply a simple trick here that works – and should always be done anyway – with physical tables as well: when entering a function where a record variable is passed by reference, store the position of the current record, and when exiting the function, restore the record to its original position:

By Reference – How to Know Temporary from Physical?

And finally, for the tip: when in a function that receives a record variable by reference, how do you know if the record is temporary or physical? You may “know” that your function will always be called with temporary record (or you may set its Temporary property to Yes to “ensure” it) but in fact for by-reference whether it’s temporary or not is determined by the actual variable. So, don’t do something crazy like this and delete your G/L:

You know by now that the GLEntryTemp record may – or may not!! – be temporary. Instead of populating the physical G/L Entries into a temporary table, you may end up deleting all of the entries from the actual, physical G/L Entry table. Probably not something you’d want to do on your regular Thursday.

To avoid headache, just do a simple sanity check:

You simply create a record reference over the parameter, and then check its ISTEMPORARY property before you do anything crazy with those physical tables.

So – I hope this was useful and helpful. If not, just feel free to boo me publicly here.