Temporary Tables in Business Central

Temporary Tables in Business Central

A temporary table is a temporary variable that holds a table. A temporary table is used as a buffer or intermediate storage for table data.

You can use a temporary table just like you use a database table. The differences between a temporary table and a database table are as follows:

• A temporary table of data isn't stored in the database. It's only held in memory until the table is closed.

• The write transaction principle that applies to a database table doesn't apply to a temporary table.

Today one of my colleagues addressed me with an issue: all the records are deleted in the sales price table while applying the original prices. After Analyzing the Code, I found that the developer had missed setting the Temporary property to Yes while declaring the variable, due to which the table was considered as a Physical table and deleted the data from the database. In this post, I’ll address some common misconceptions and give some tips and hints that you can use in practice.

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 and it does not affect the physical table. I’m pretty sure you knew this much, but I repeated it here just to show you how smart I am 😁

Passing Temporary Table 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 developers 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.

A parameter can be passed by value or passed 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.

Based on the parameter and record type we can find three combinations.

1. Pass by Value

1. Pass by Temporary Value 

1. Pass by Reference (same as Pass by Temporary Reference)

Let us discuss in brief with an example for each.

The first two combinations are simple if the parameter is passed by value then the Temporary property defined on the function definition will define whether the table is temporary or physical within the function.

Refer this one

In the first two functions from the above screenshot, the vendor variable's state is defined by the temporary property set on the parameter. On calling Function_Temporarily() by passing the physical record(Vend_Physical), the values will get copied to the vend variable, but the table remains to be temporary. 

Similarly, If you call the Function_Physical() even by passing the temporary vend record, the vend record within the function will be a physical table.

Furthermore, when you have Temporary set to Yes on a pass 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.

On the other hand, if the record parameter is by reference, then the Temporary property makes absolutely no difference.

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.

By Reference – How to Know Temporary from Physical?

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 the 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 headaches, 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.