Management Reporter Error: GLTrans with transdate value far outside a normal range.
Error: GLTrans with transdate value far outside a normal range. http://go.microsoft.com/fwlink/?LinkID=397215
This post is a follow-up to a previous post on Management Reporter data validation errors, which focused on a different data validation error with Management Reporter. The good news is that the same tools we used to correct other errors will likely be used here.
In this case, we’re looking for records where the trandate field is “outside normal range”. To solve this issue, you’re going to need the MR4.zip diagnostics package - or the code snippet below. I do recommend grabbing the MR4.zip diagnostics package as it has solutions for other Management Reporter validation problems. You will need to be able to run SQL statements against your Dynamics SL database server.
-- Step 5 - Look for GLTrans for invalid date/periods
-- Records like this can cause the following errors in MR:
-- Error text: Year, Month, and Day parameters describe an un-representable DateTime.
-- Error text: Object reference not set to an instance of an object.
-- GLTrans with blank FiscYr
select top 100 'Step5a',CpnyID, LedgerID, FiscYr, Acct, Sub, perpost, perent, trandate,Posted, Rlsed, BatNbr, Module, *
from GLTran with (nolock) where FiscYr='' and (Rlsed=1 or Posted='P')
--To fix: Consider updating fiscyr based upon the perpost value
-- GLTrans with perpost value far outside a normal range
select top 100 'Step5b',CpnyID, LedgerID, FiscYr, Acct, Sub, perpost, perent, trandate,Posted, Rlsed, BatNbr, Module, *
from GLTran with (nolock) where (PerPost>='204812' or PerPost<='195001') and (Rlsed=1 or Posted='P')
--To fix: These will all likely have a future period to post. You can bring these batches up in GL>Journal Transactions while in Initialization mode.
--Delete them or change the period to post or process as appropriate.
-- GLTrans with transdate value far outside a normal range
select top 100 'Step5c',CpnyID, LedgerID, FiscYr, Acct, Sub, perpost, perent, trandate,Posted, Rlsed, BatNbr, Module, *
from GLTran with (nolock) where (trandate>='01/01/2048' or trandate<='01/01/1950') and (Rlsed=1 or Posted='P') and TranDate<>'1/1/1900'
--To fix: Update trandate to crtd_datetime. This script shows how.
--update gltran set trandate=crtd_datetime where (trandate>='01/01/2048' or trandate<='01/01/1950') and (Rlsed=1 or Posted='P') and TranDate<>'1/1/1900'
-- AcctHist records with FiscYr far outside a normal range
select top 100 'Step5d',a.CpnyID, a.LedgerID, a.FiscYr, a.Acct, a.Sub, a.CuryId, *
from AcctHist a with (nolock) where a.FiscYr>'2048' or a.FiscYr<'1950' or len(a.FiscYr) < 4
--To fix: These are probably unwanted records. In manipulating data historically, we might have set the fiscal year to a future year
-- just to keep the records for later potential review.
You’ll start by validating that there are indeed GLTran entries with odd dates - in this case, it was March 31st, 1930 - by observing rows in the dataset marked Step5c.
An image
An image
Our second and final step is to update the records manually in the database that have a problematic date. The canned snippet grabs any GLTran records that have been released and posted with a trandate that is either before 1950 or after 2048. By running that SQL update statement, the trandate is updated to the created date for the transaction record, which is presumably in modern times.
It’s a great idea to re-run the SELECT statement(s) to double check your work!