NAV Inv Posting Buffer 2016 vs 2017- 2 ways to group data

As you already might know, in the purchase/sales posting routines from NAV an additional temporary table Invoice Posting Buffer is used to group invoice lines by the fields specified in the Primary Key of Invoice Posting Buffer table (TAB 49) and then Post only 1 G/L Entry for each group of purchase/sales lines.

In NAV 2016 and previous versions it is used an array with 2 dimensions of temporary table 49 variable to group the records. See also here a question regarding this.

This summer I helped organize a NAV internship and among other things I “tried” to explain to the students the Sales Post process. You can see in the below image how I tried to make a summary of the whole process. 🙂

I noticed that in NAV 2017 the way the sales lines are grouped  was changed compared to 2016 version: in the latest version it is not used anymore a 2 dimensional array.

In this post I will explain how both versions work, starting with the NAV 2016 Version (2 dimensions array).

To be honest I struggled a little bit to understand this, because a temporary table is acting “strange” when used with an array so I searched on the internet and found an interesting post by Mark Brummel about this and I encourage you to read it too.

To demonstrate how amounts can be grouped using this method, I created 2 tables 50008 “Customer Test” and 50009 “Customer Test Buffer”.

In the “Customer Test” table I have 3 fields: Customer, Line, Amount. The Primary key is composed Customer + Line.

In the “Customer Test Buffer” I have 2 fields: Customer and Amount and the Primary Key is Customer. We will group Amounts per Customer using this buffer table.

In my test I want to loop through all records from “Customer Test” record, copy the data to “Customer Test Buffer” variable and if the value from Primary Key (Customer) of the Buffer already exists, add the amount from “Customer Test” to the existing amount from “Customer Test Buffer” (Modify). If it does not exist, Insert a new line in the temporary “Cust Test Buffer” table.. The “Find” function “Finds a record in a table based on the values stored in keys.”, so it helps us to achieve this.

Then I want to loop through the newly created records from the temporary buffer and display a message with the total amounts for each Customer.

In my 50008 table “Customer Test” I entered the following test data:

I created also Codeunit 50008 “Summarize Amounts” and added the following code in the OnRun trigger:

OnRun()
//How Inv Posting Buffer works in NAV 2016 and older versions
CustReal.FINDSET;
REPEAT
  CustTemp[1].PrepareData(CustReal);
  CustTemp[2] := CustTemp[1];
  IF CustTemp[2].FIND THEN BEGIN
    CustTemp[2].Amount := CustTemp[2].Amount + CustTemp[1].Amount;
    CustTemp[2].MODIFY;
  END ELSE
    CustTemp[1].INSERT;
UNTIL CustReal.NEXT = 0;


IF CustTemp[1].FIND('+') THEN
  REPEAT
    MESSAGE('Cust No: %1;Cust AMount: %2',CustTemp[1].Customer, CustTemp[1].Amount);
  UNTIL CustTemp[1].NEXT(-1) = 0;

Variables:

CustReal: TAB 50008 “Customer Test”

CustTemp: TAB 50009 “Customer Test Buffer” + Property Temporary = Yes.

The “PrepareData” function from “Customer Test Buffer” simply copies Customer and Amount from “Customer Test” table:

PrepareData(CustReal : Record "Customer Test")
CLEAR(Rec);
Customer := CustReal.Customer;
Amount := CustReal.Amount;

When using a Temporary table with 2 dimensions the content of Variable[1] and Variable[2] is the same, so when I do “CustTemp[2].Amount = x”, the value x will be available also in “CustTemp[1].Amount”, that is why when I do the Repeat Until on “CustTemp[1]” I get the correct grouped amounts  per customer:

Now, in NAV 2017 Version Microsoft changed a lot the code from Codeunit 80. Well, they only changed it to be more readable and easy to understand, from a functional point of view it does the same things.

For example, I applied the 2017 logic for grouping the amounts using the Inv Posting Buffer table to our “Customer Test” grouping example: now I loop through the records from “Customer Test”, I copy the data from the current record to a “CustBuffer” variable (it can be temporary or not, it does not matter because we don’t insert in this one), then I copy the “CustBuffer” to another “CustTempBuffer” (this one has to be temporary in order to not insert into the actual database table, but only in memory) and if I FIND existing records with the same Primary Key fields values in this temporary table, I only add the amount to the existing line and modify the line, if I don’t find existing records, I Insert the record to the temporary table. This way of coding does the same thing as in the previous version, but it does not seem confusing anymore !

In my Test exercise, I created Codeunit 50009 “Summarize Amounts 2017” to demonstrate this:

OnRun()
//How Inv Posting Buffer works in NAV 2017 Version
CustReal.FINDSET;
REPEAT
  CustBuffer.PrepareData(CustReal);
  CustTempBuffer := CustBuffer;
  IF CustTempBuffer.FIND THEN BEGIN
    CustTempBuffer.Amount += CustBuffer.Amount;
    CustTempBuffer.MODIFY;
  END ELSE 
    CustTempBuffer.INSERT;
UNTIL CustReal.NEXT = 0;

IF CustTempBuffer.FIND('+') THEN
  REPEAT
    MESSAGE('Cust No: %1;Cust AMount: %2',CustTempBuffer.Customer, CustTempBuffer.Amount);
  UNTIL CustTempBuffer.NEXT(-1) = 0;

Besides this logic of grouping invoice lines, you should know the following things about the Invoice Posting Buffer (Table 49):

• Whenever you want to add a new field in the Sales/Purchase Line and have it copied to the G/L Entry in the Posting process, you have to add the field to the Invoice Posting Buffer table. From here, after the grouping was done, you will add code to copy the field to the General Journal Line, and from the General Jnl Line you will add code in CU 12 to copy it to the G/L entry. Don’t forget to add the field also to the Purchase/Sales Invoice Line (for the posted sales/purchase documents): here the fields are copied using TRANSFERFIELDS function, so you don’t have to add code, but only to make sure the field has the same ID in all objects.
• If you want also to have the G/L Entries grouped by the values of the newly added field as well (create different G/L Entry lines if those values are different), you will need to add the field to the Primary Key of Invoice Posting Buffer as well !

If you find this interesting and want to play with my test objects you can download them from THIS LINK. (they are created in NAV 2017). I also added a little logic to add some test data if the “Customer Test” table is empty, so you only have to run Codeunit 50008 or Codeunit 50009 and first time the table will be populated with data from my above example. Have fun ! 🙂