Tip #27 - Using Temp tables in Arrays

I'll be honest, I found this out by accident lately but I want to share it anyway. Since it took me almost 15 years to find, it might not be know to some others.

As many of you know, using temporary tables in Microsoft Dynamics NAV is a very powerfull feature. We can use it to buffer data, to simulate SELECT DISTINCT etc. And they are free.

Using temporary tables becomes even more powerfull when adding an array to it. Let's see this in an example.

We'll create a new codeunit with a variable CustTemp that has two dimensions.

So now we can use CustTemp[1] and CustTemp[2].

Let's write some C/AL Code with this.

We create a new record using CustTemp[1]. We then try a FINDFIRST on CustTemp[2]. Let's see what message we get.

How cool is this! The contents of the CustTemp[2] is the same as that of CustTemp[1].

So what can we do with this. Let's add some more code.

We add a new record to CustTemp[2] and filter on this value using CustTemp[1]. Then we show the value of Name and the number of records in CustTemp[2].

What does this give?

The CustTemp[1] now has the value of the Name we insterted in CustTemp[2]. CustTemp[2] ignores the filter we used in CustTemp[1].

Ok Mark, great stuff but what can I do with this?

Microsoft uses this in at least two places in the standard Navision product.

The fist place is the UpdInvPostingBuffer() function in Codeunits 80 and 90. This temporary buffer variable has two dimensions which are used to combine a select distinct with a total of the values.

The second place is the Inventory Profile Offsetting Codeunit (99000854) in Navision Manufacturing. In this codeunit supply and demand is created in the InventoryProfile table.

 

Cheers,

/Marq

Comment List
  • I like the valuable info you provide in your articles. I will bookmark your weblog and check again here regularly.

    I am quite sure I will learn lots of new stuff right here!

    Good luck for the next!

  • It's definitely not a bug but a great feature. When you work with normal tables you can have many pointers (variables) to the same dataset (table). Using dimensions for temporary tables is the same. You can use it for cycling through temporary dataset and modifying key fields. It is the easiest way to do this.

  • I already knew this for a long time and use this 'bug' a lot.

    I also mentioned it in my how to : www.mibuso.com/howtoinfo.asp

    ---

    The array technique can also be used on temporary record-variables. They work also independently of each other like on a real table. BUT THERE IS ONLY 1 TEMPTABLE! This means that if you create a record with element [1], it will be available in element [2]! An example (This is the very first code on the temptable, so there are no records in it!).

    CLEAR(recRecord[1]);

    recRecord[1]."No." := '1');

    recRecord[1].INSERT(FALSE);

    recRecord[2].RESET;

    recRecord[2].FINDFIRST;

    MESSAGE('%1',recRecord[2]."No."); // this will show the value of the record I

    created in element [1]!

    -----

    But I have to admit I never new it was a bug.....

    When I first found out how this worked, I thought it was correct functioning, because if you insert a record in element 1 of the array on a real table, it is available in element 2 of the array. So it seemed logical that the temptable-array follows this logic.

  • I find it to be a great feature. Just don't mix it with the new optional ShareTable parameter in the Rec.COPY function unless you really hate the next developer looking at your code... :-)

  • Wow, some things will never ever be teached during development lessons ...

    Now I got the point of the "feature": saving different filters on the same record - thanks!!!!

    @Eric

    As I already said, a mere decimal array was not practicable for my issue, but thanks for your try.

    Please keep on blogging things like these :-)

  • Ok, I have the results of the jury.

    I asked Michael Nielsen who worked for Navision since 1987. It is a bug. But, it became a feature when Peter Bang used it in the product.

    They had to copy the bug to the new managed stack.

    Now, isn't that a cool inside story.

    Thanks for the information Michael. :)

  • Thanks for the subtitles Waldo, as always. :)

  • I think the message is somewhat lost in the blog ... .

    You can work with one dataset, but you can apply different filters (in each dimension a different set of filters), right?  In this way, it can be useful indeed.

    @Natalie:

    Don't forget that it's possible to work with multi-dimension-arrays (e.g. 10x4 or 10x4x4 or ...).  I don't know if this could be useful in your solution?

  • Natalie,

    That was my first reaction as wel. Using this for a report and asuming it was a bug.

    Later, going though these codeunits I realised that it is used by Microsoft. Codeunit 80 and 90 are very old and the design of the buffer has never changed much.

    Therefore I assume this is by design.

    However, I will discuss this with the product team. I will send them an email. I will also travel to the MDCC in a few weeks so I can ask them directly. :)

  • Hi Marc,

    for me, this is a bug, not a feature.

    It was only last thursday I accidently had to deal with this issue myself.

    In a report I needed variables to store many decimal values, 4 each record.

    I tried to solve that with temopary record variables with 4 dimensions each, hopefully to save too many variables.

    But as one dimension affects the other, this try was useless :-(

    I still do not see any sense in this ...

    PS: I could not have worked with a decimal array instead due to further reasons - so do not wonder ;-)

Related
Recommended