SmartList Shows Duplicate Year End Journal Entry Distributions

David Meego - Click for blog homepageI have had two support cases with this issue recently.  So I thought I would post an explanation of the problem along with a script to fix it. The good news is that the underlying cause of the issue has been fixed in Microsoft Dynamics GP 2010 (v11.00).

The Issue

After running the end of fiscal/financial year process and posting the year end General Ledger journals.  You look at the "Balance Brought Forward" journal in SmartList and it appears to have the same amounts repeated a number of times for a particular account. Also some of the amounts for the same account are missing entirely.

For example:

Account Number Debit Credit
123456-00 $0.00 $5,000.00
123456-00 $0.00 $5,000.00
123456-00 $0.00 $5,000.00

If using the Journal Entry Inquiry window to look at the same journal you will see the different amounts as expected.

For example:

Account Number Debit Credit
123456-00 $0.00 $5,000.00
123456-00 $1,000.00 $0.00
123456-00 $500.00 $0.00

What is actually happening is that the amounts from the first line for an account number is being display for each of the distribution lines using the same account number.

Because the inquiry window is correct, it tells us that the actual data is not damaged and the issue is a display problem with SmartList.

The Cause

The issue is caused by the way SmartList works.  When you select a SmartList favorite and select your Search criteria, SmartList runs a SQL query to find all the records that match your criteria.  It then gets the key fields for that record and looks up the data for the fields to be displayed from the necessary table(s). Once it has the data, it adds a line to the right hand pane and then gets the key fields from the next record from the SQL query and looks up the data for the fields and so on.

This usually works fine for General Ledger Journal Entry transactions, except for the "Balance Brought Forward" transactions on a multi-currency system. The reason is that code that creates the BBF transaction was not populating the Sequence Number field.  On a non multi-currency system there is only ever one distribution in the BBF journal for each account number, so each line can be uniquely identified.

On a multi-currency system, you can have multiple distributions for different currencies against the same account number.  As the Sequence Number has not been populated, it will have a value of zero.  Now when SmartList looks up the data, it uses a Sequence Number value of 0 for each of the lines, which always returns the first of the distribution lines for that account number.  Hence we see what appears to be duplicated lines and the other lines are missing.

The Fix

The underlying cause of the issue is not a SmartList bug, but a problem with the Year End Closing code not populating the Sequence Number field for the transaction.  So if we run the script below after changing XXXX to the correct Journal Entry Number, we can renumber the Sequence Number for the Journal and resolve the issue.  We are using the increment of 16384 as that is the default increment value for line item sequence numbers in Dynamics GP.

Transact SQL Script

 DECLARE @x int
SELECT @x=0
UPDATE GL20000 set SEQNUMBR=@x,@x=@x+16384
WHERE JRNENTRY=XXXX -- Where XXXX is the Journal Entry

As mentioned earlier, this issue has been fixed for Dynamics GP 2010.  The "Balance Brought Forward" journal will now have the Sequence Number field incremented to create a unique key for each distribution.

Hope you find this useful.

David

// Copyright © Microsoft Corporation. All Rights Reserved.
// This code released under the terms of the
// Microsoft Public License (MS-PL, https://opensource.org/licenses/ms-pl.html.)