Open Forum

1.  Correcting GLTRX Distribution Reference Description

Posted 3 days ago
  |   view attached


I've run into a bit of an issue with a new staff member. We use a third party product to copy & paste multiple journal entries to GP using Excel. This works great for us.

Unfortunately, the new staff member didn't pay close attention to the distribution reference descriptions when importing and posting.

We now have a few entries with incorrect reference descriptions which can cause us huge headaches come audit time.

Is there a way to correct just the reference descriptions without having to void and re-create the entire entry?

My gut says no.

Is there a solution via SQL?

Your thoughts?

I am attaching a factitious Detailed Trial Balance report for visual representation.



------------------------------
Manuel Vasquez
Applications/Programmer Analyst
Kings View Corporation
Fresno CA
------------------------------


2.  RE: Correcting GLTRX Distribution Reference Description

Posted 2 days ago
Hi Manuel,

You should be able to fix this in SQL easily. However, if you're not familiar with doing stuff like this in SQL, please ask for help from someone who is.

You would replace the 'New Distribution Reference' with what you want in the Distribution Reference and replace 123456 with the journal entry number.

Here is the script you can use to fix this:
update GL20000
set DSCRIPTN = 'New Distribution Reference' -- change this
where JRNENTRY = 123456 -- change this to the JE number


------------------------------
Victoria Yudin
Microsoft Dynamics GP MVP 2005-2017
Flexible Solutions, Inc.
http://www.GPReportsViewer.com
http://victoriayudin.com
------------------------------



3.  RE: Correcting GLTRX Distribution Reference Description

Posted 2 days ago
If your distributions only have the one entry like in your report Victoria's suggestion will work great.  However, if there are multiple distributions with the same Journal Entry then if you did a select in the table like this:

select * from GL20000
where JRNENTRY = 46553  -- Change this to the appropriate Journal Entry

You might see something like this(taken from my test company):

In that case select your line and scroll all the way to the right and make note of the DEX_ROW_ID, because if you update based on JRNENTRY then the DSCRIPTN for ALL the lines will be changed and not just the one line.

update GL20000
set DSCRIPTN = 'New Distribution Reference' -- change this
where DEX_ROW_ID = 123456 -- change this to the DEX ROW ID number

------------------------------
Dianna Moore
Development Engineer
APTEAN | Diamond Municipal Solutions product line
------------------------------



4.  RE: Correcting GLTRX Distribution Reference Description

Posted 2 days ago
Ladies,

Thank you both for your contributions. The scripts worked perfectly. I was able to keep my sanity today!

Thank you,

Manuel

------------------------------
Manuel Vasquez
Applications/Programmer Analyst
Kings View Corporation
Fresno CA
------------------------------



5.  RE: Correcting GLTRX Distribution Reference Description

Posted 2 days ago
I have used the following SQL statement to update the distribution reference:

update GL20000 set dscriptn = 'corrected description'

where dex_row_id = 61994

I find the dex row ID based on the JE#/date first.  However, I don't like updating in SQL because of the risk of updating the tables directly.  I generally ask staff to reverse and correct in Dynamics.  Although this is straight forward, I'm not sure it will take less time than correcting in Dynamics.



------------------------------
Selena Breedlove
SVP of Finance/Controller
US Assure
Jacksonville FL
------------------------------