How To Use Dexterity To Quickly Add Fields to GP Inquiry Windows

You may not want to become an expert Dexterity programmer, but it could be useful to learn just enough Dexterity to customize your inquiry windows. The objective of this article is to do just that. We have confidence that you can do this. If you do not agree, you can quickly scan through to realize what is possible so you can suggest inquiry enhancements to your Dexterity consultant. And if you are not a neophyte, maybe you can pick up a tip or two.

Advertisements

In this simple Dexterity modification, we add the Purchase Order Number and the first associated POP Receipt Number to the Accounts Payable window.  Also, if the user presses the ZOOM buttons for these fields, they open the associated inquiry windows.

You can use these same techniques to add the vendor/customer ID and document numbers to the General Ledger detail inquiry windows, and also drill down to the original PM/RM transactions.

We will select the PM_Transaction_Inquiry form and then open the main window in Dexterity:
dexPMinq1

This is what this window looks like under the Windows -> Preview menu:
dexPMinq2

We are going to add another column for POP Receipt Number and PO Number.

We want to make the new fields wide as the ORIGINAL AMOUNT field, we click on that field, and select the LAYOUT -> PROPERTIES menu option, and we see the SIZE-WIDTH is 137.  We can drag the fields to the right, and grab the end of lines at the top and bottom of the window and stretch them.  We open the LAYOUT -> TOOLBOX, click on the “A” icon, type “POP Receipt Number” and “PO Number”.  We highlight each of these fields, and under LAYOUT -> PROPERTIES we click ZOOM to true.  This property really only changes the display.
dexPMinq3

The actual zooming script is on the ZOOM BUTTONS, since this is an array field, you drag the next element in the array onto the window.  The VISIBLE property of the ZOOM BUTTONS should be false.
dexPMinq4

Next you double click on the scrolling window and add the POP INVOICE NUMBER and PO NUMBER fields.  You would then resize them and lengthen the lines in the scrolling window.  Since this is an inquiry window, it doesn’t matter whether the fields are editable or not, the whole window is marked as “BROWSE ONLY.”
dexPMinq5

We are done with the user interface, but we need to add two more local boolean fields, because you don’t need to hide Boolean fields.  You do not have to worry about any hyperspace stuff (if you do not know what this means, do not worry about it).  Any data type field will do, we are going to attach a script to these fields:
dexPMinq6

DEXTERITY SCRIPTS THAT POPULATE THE NEW FIELDS

First, we will write the trigger scripts that populate the POP Invoice Number and PO Number fields.  We like to re-use our scripts, so we will first write the subroutines that grabs the data we need.  We will create a new function:

We will forgo the explanation of these scripts, the manual can tell you what these commands mean, but they are fairly self explanatory.  The pragma commands decrease the number of bogus compile messages.

{ procedure GetFirstPOPInvoiceFromHistory }
function returns string strPOPInvoiceNumber;
in string strVendorID;
in string strDocumentNumber;
pragma(disable warning LiteralStringUsed);
clear field strPOPInvoiceNumber;
range clear table POP_ReceiptHist;
‘Vendor ID’ of table POP_ReceiptHist = strVendorID;
‘Vendor Document Number’ of table POP_ReceiptHist = strDocumentNumber;
clear field ‘POP Receipt Number’ of table POP_ReceiptHist;
range start table POP_ReceiptHist by number 2;
‘Vendor ID’ of table POP_ReceiptHist = strVendorID;
‘Vendor Document Number’ of table POP_ReceiptHist = strDocumentNumber;
fill ‘POP Receipt Number’ of table POP_ReceiptHist;
range end table POP_ReceiptHist by number 2;
get first table POP_ReceiptHist by number 2;
if err() = OKAY then
strPOPInvoiceNumber = ‘POP Receipt Number’ of table POP_ReceiptHist;
elseif err() <> MISSING and err() <> EOF then
call ErrorMessage,_script_,”PO_ReceiptHist”,err(),”Finding VendorID/DocNo “+strVendorID+”/”+strDocumentNumber;
end if;
range clear table POP_ReceiptHist;
pragma(enable warning LiteralStringUsed);

{ procedure GetFirstPOFromPOPInvoice }
function returns string strPONumber;
in string strPOPInvoice;
local string strTable;
pragma(disable warning LiteralStringUsed);
strTable = “POP_ReceiptLine”;
range clear table POP_ReceiptLine;
‘POP Receipt Number’ of table POP_ReceiptLine = strPOPInvoice;
clear field ‘Receipt Line Number’ of table POP_ReceiptLine;
range start table POP_ReceiptLine by number 1;
‘POP Receipt Number’ of table POP_ReceiptLine = strPOPInvoice;
fill ‘Receipt Line Number’ of table POP_ReceiptLine;
range end table POP_ReceiptLine by number 1;
get first table POP_ReceiptLine by number 1;
if err() = OKAY then
strPONumber = ‘PO Number’ of table POP_ReceiptLine;
elseif err() = MISSING then
warning “MISSING “+strPOPInvoice+” in table “+strTable;
elseif err() = EOF then
strTable = “POP_ReceiptLineHist”;
range clear table POP_ReceiptLineHist;
‘POP Receipt Number’ of table POP_ReceiptLineHist = strPOPInvoice;
clear field ‘Receipt Line Number’ of table POP_ReceiptLineHist;
range start table POP_ReceiptLineHist by number 1;
‘POP Receipt Number’ of table POP_ReceiptLineHist = strPOPInvoice;
fill ‘Receipt Line Number’ of table POP_ReceiptLineHist;
range end table POP_ReceiptLineHist by number 1;
get first table POP_ReceiptLineHist by number 1;
if err() = OKAY then
strPONumber = ‘PO Number’ of table POP_ReceiptLineHist;
elseif err() = MISSING then
warning “MISSING “+strPOPInvoice+” in table “+strTable;
elseif err() = EOF then
{ no message }
else
call ErrorMessage,_script_,strTable,err(),”Finding “+strPOPInvoice;
end if;
else
call ErrorMessage,_script_,strTable,err(),”Finding “+strPOPInvoice;
end if;
range clear table POP_ReceiptLine;
range clear table POP_ReceiptLineHist;
pragma(enable warning LiteralStringUsed);

Years ago I had copied and pasted the table error codes from the PDF manual into this script, which you may find useful.  Commented out is the script where I have the application email the error messages to support.  We use the newer try/end try construct when we call COM or .NET objects, but we prefer the old style error messages for table errors using this script.  (Yes, I know, the programming “case” structure is sexier and more elegant, but this is one of my first Dexterity scripts, and I do not feel like rewriting it.)

{ procedure ErrorMessage }
in string l_script;
in string l_tablename;
in integer l_error_code;
in string l_msg;
local string l_errmsg,l_displayedmsg;
local text txtMessage;
pragma(disable warning LiteralStringUsed);
if l_error_code = 1 then
set l_errmsg to “Low on memory”;
elseif l_error_code = 2 then
set l_errmsg to “Database manager not initialized”;
elseif l_error_code = 3 then
set l_errmsg to “Database manager not supported”;
elseif l_error_code = 4 then
set l_errmsg to “Too many tables opened”;
elseif l_error_code = 5 then
set l_errmsg to “Record length too long”;
elseif l_error_code = 6 then
set l_errmsg to “Too many keys for database type”;
elseif l_error_code = 7 then
set l_errmsg to “Too many segments”;
elseif l_error_code = 8 then
set l_errmsg to “Table not registered”;
elseif l_error_code = 9 then
set l_errmsg to “Table not found”;
elseif l_error_code = 10 then
set l_errmsg to “Locked record”;
elseif l_error_code = 11 then
set l_errmsg to “Table name error”;
elseif l_error_code = 12 then
set l_errmsg to “Table not open”;
elseif l_error_code = 13 then
set l_errmsg to “Table not opened exclusive”;
elseif l_error_code = 14 then
set l_errmsg to “Invalid command sent to database manager”;
elseif l_error_code = 15 then
set l_errmsg to “Key number doesn’t exist”;
elseif l_error_code = 16 then
set l_errmsg to “End of file”;
elseif l_error_code = 17 then
set l_errmsg to “Duplicate record”;
elseif l_error_code = 18 then
set l_errmsg to “Missing record”;
elseif l_error_code = 19 then
set l_errmsg to “A set is already active”;
elseif l_error_code = 20 then
set l_errmsg to “Transaction in progress”;
elseif l_error_code = 21 then
set l_errmsg to “Not a variable length table”;
elseif l_error_code = 22 then
set l_errmsg to “No table definition could be found”;
elseif l_error_code = 23 then
set l_errmsg to “Attempted to lock two records”;
elseif l_error_code = 24 then
set l_errmsg to “No lock on update”;
elseif l_error_code = 25 then
set l_errmsg to “Table doesn’t match definition”;
elseif l_error_code = 26 then
set l_errmsg to “The disk is full”;
elseif l_error_code = 27 then
set l_errmsg to “Unknown error”;
elseif l_error_code = 28 then
set l_errmsg to “A nonmodifiable key changed”;
elseif l_error_code = 29 then
set l_errmsg to “Not a variable length field”;
elseif l_error_code = 30 then
set l_errmsg to “A record was changed with a passive lock”;
elseif l_error_code = 31 then
set l_errmsg to “Deadlocked”;
elseif l_error_code = 32 then
set l_errmsg to “Path not found”;
elseif l_error_code = 33 then
set l_errmsg to “Buffer error”;
elseif l_error_code = 34 then
set l_errmsg to “Error in creating a Btrieve table”; {this was before SQL!!!}
elseif l_error_code = 35 then
set l_errmsg to “Invalid key definition”;
elseif l_error_code = 36 then
set l_errmsg to “Maximum number of SQL connections reached”;
elseif l_error_code = 37 then
set l_errmsg to “Error accessing SQL data”;
elseif l_error_code = 38 then
set l_errmsg to “Error converting SQL data”;
elseif l_error_code = 39 then
set l_errmsg to “Error generating SQL data”;
else
set l_errmsg to “Unknown Error”;
end if;
l_displayedmsg = l_errmsg+” [Err”+str(l_error_code)+”] / “+l_msg+ ” [TABLE- “+l_tablename+”] [SCRIPT- “+l_script+”]”;
warning l_displayederrmsg;
{txtMessage = l_displayedmsg; COMMENTED OUT:
call SendGPSimpleEMail,’User ID’ of globals,’User Name’ of globals,txtMessage;}
pragma(enable warning LiteralStringUsed);

 

When you open the inquiry window, this trigger script populates the new fields on the window:

{ procedure Trigger_PM_Trx_Inquiry_Scroll_FILL }
local string strVendorID,strDocumentNumber,strPOPInvoiceNumber;
pragma(disable warning LiteralStringUsed);
default form to PM_Transaction_Inquiry;
default window to PM_Transaction_Inquiry_Scroll;
strVendorID = ‘Vendor ID’ of table PM_Inquiry_TEMP;
strDocumentNumber = ‘Document Number’ of table PM_Inquiry_TEMP;
strPOPInvoiceNumber = GetFirstPOPInvoiceFromHistory(strVendorID,strDocumentNumber);
‘POP Invoice Number’ = strPOPInvoiceNumber;
if empty(‘PO Number’) then
‘PO Number’ = GetFirstPOFromPOPInvoice(strPOPInvoiceNumber);
end if;
pragma(enable warning LiteralStringUsed);

 

The Startup script is the magical script that makes all this work.  When Dynamics GP starts up it looks for and runs the Startup script.  All your trigger scripts should either reside in Startup or in a subroutine called by Startup.  There does not appear to be any limitation to the number of trigger scripts, we have dictionaries with hundreds of trigger scripts.

{ Startup }
{ dexterity looks for this script name, expecting to find trigger scripts }
local integer intResult;
pragma(disable warning LiteralStringUsed);
intResult = Trigger_RegisterFocus(anonymous(
window PM_Transaction_Inquiry_Scroll of form PM_Transaction_Inquiry),
TRIGGER_FOCUS_FILL, TRIGGER_BEFORE_ORIGINAL,
script Trigger_PM_Trx_Inquiry_Scroll_FILL);
if intResult <> SY_NOERR then
warning “Trigger registration for Trigger_PMTrxInquiry_Scroll_FILL has failed.”;
end if;
pragma(enable warning LiteralStringUsed);

\DEXTERITY SCRIPTS THAT CALL OTHER INQUIRY WINDOWS

If you want to open an inquiry window or transaction entry window with certain document numbers or master record numbers, look for a ‘Display Record’ or ‘Redisplay Button’ field, if it exists you should first try running that field script, like I do in this script:

{ procedure Do_POP_Document_Inquiry }
in string strVendorID;
in string strPONumber;
close form POP_Document_Inquiry;
open form POP_Document_Inquiry;
default form to POP_Document_Inquiry;
default window to POP_Document_Inquiry;
‘All Or Range2’ = 1;
‘Start Vendor ID’ = strVendorID;
‘End Vendor ID’ = strVendorID;
‘Sort By’ = 1; {DDL}
‘All Or Range’ = 1;
‘Start PO Number’ = strPONumber;
‘End PO Number’ = strPONumber;
‘(L) Open POs CB’ = true;
‘(L) Historical POs CB’ = true;
‘(L) Receipts Received CB’ = true;
run script delayed ‘Redisplay Button’;
run script delayed ‘Scrolling Window Expand Button’;

That approach does not always work, especially in the more quirky POP and SOP modules.  Another approach to try is to run the script for the key field, or run a script log when opening the inquiry window to get a clue as to which script you need to run.  In this next script I couldn’t figure out how to call the inquiry window, so I called Dexterity support:

{ procedure Do_POP_Inquiry_Invoice_Entry }
in string strPOPInvoiceReceipt;
local string strTable;
local integer intOpen4Hist3;
pragma(disable warning LiteralStringUsed);
strTable = “POP_ReceiptHist”;
‘POP Receipt Number’ of table POP_ReceiptHist = strPOPInvoiceReceipt;
get table POP_ReceiptHist by number 1;
if err() = OKAY then
intOpen4Hist3 = 3;
elseif err() = MISSING then
strTable = “POP_Receipt”;
‘POP Receipt Number’ of table POP_Receipt = strPOPInvoiceReceipt;
get table POP_Receipt by number 1;
if err() = OKAY then
intOpen4Hist3 = 4;
else
call ErrorMessage,_script_,strTable,err(),”Finding POP Invoice “+strPOPInvoiceReceipt;
end if;
else
call ErrorMessage,_script_,strTable,err(),”Finding POP Invoice “+strPOPInvoiceReceipt;
end if;
if intOpen4Hist3 <> 0 then
OpenWindow(strPOPInvoiceReceipt,2{POP_BYNOTSET},
intOpen4Hist3,1) of form POP_Inquiry_Invoice_Entry;
run script delayed ‘Scrolling Window Expand Button’ of
window POP_Inquiry_Invoice_Entry of form POP_Inquiry_Invoice_Entry;
end if;
pragma(enable warning LiteralStringUsed);
{ Microsoft Dynamics Incident Number: 9547384 Incident Type: Technical Support
Subject: pop invoice and po inquiry
Originated: 8/27/2009 7:03:00 AM PDT
It looks like POP Receipt Number CHG is used for receipts and invoices.
To open the invoice inquiry is similar to the PO one:
call to OpenWindow of form POP_Inquiry_Invoice_Entry,
passing in the Receipt Number, POP_BYNOTSET which has a value of 2,
Origin integer – 4 if open 3 if hist, and the CurrencyView integer. }

 

We are almost done, we now double click on the fields we placed on the scrolling window above to add the following scripts.

{ procedure PM_Transaction_Inquiry_Scroll l_Run_Do_POP_Document_Inquiry_CHG }
pragma(disable warning LiteralStringUsed);
if empty(‘PO Number’) then
warning “This PM Trx has no purchase order.”;
else
call Do_POP_Document_Inquiry,’Vendor ID’,’PO Number’;
end if;
pragma(enable warning LiteralStringUsed);

{ procedure PM_Transaction_Inquiry_Scroll l_Run_Do_POP_Inquiry_Invoice_Entry_CHG }
pragma(disable warning LiteralStringUsed);
if empty(‘POP Invoice Number’) then
warning “This PM Trx has no POP Invoice Number.”;
else
call Do_POP_Inquiry_Invoice_Entry,’POP Invoice Number’;
end if;
pragma(enable warning LiteralStringUsed);

Two final scripts we need to attach to the main window ZOOM fields that call the above scrolling window scripts, and we are done.

{ PM_Transaction_Inquiry Zoom Buttons[2]_CHG }
run script ‘(L) Run_Do_POP_Inquiry_Invoice_Entry’
of window PM_Transaction_Inquiry_Scroll;

{ PM_Transaction_Inquiry Zoom Buttons[3]_CHG }
run script ‘(L) Run_Do_POP_Document_Inquiry’
of window PM_Transaction_Inquiry_Scroll;

Here is the completed production version of these modifications, showing that the scripts will successfully open the inquiry windows, using the Fabrikam vendor ADVANCE0001 who has lots of purchase orders:
dexPMinq9

POSSIBLE ADDITIONAL MODIFICATIONS

Due to our business practices, we nearly always have a one-to-one correlation between purchase orders and payables invoices, and over ninety-five percent of our purchase orders have only one invoice.  But if that is not the case with your business, this is a possible solution you can implement.  Your logic can go like this: if a payables invoice has more than one purchase order and/or POP Invoice, when the user opens one of these inquiry windows you can open your third party window that has a scrolling window for all the Purchase Orders and POP Invoices for that payables invoice, and you can let the user access the inquiry windows from this scrolling window.

NEXT STEPS

You need to create a chunk file so these changes can be incorporated into Dynamics GP.  When I asked Dr Google about “create chunk file dexterity” he suggested several sites, including this site:
https://support.microsoft.com/en-us/help/894700/how-to-create-a-chunk-file-in-dexterity-in-microsoft-dynamics-gp

Likewise, Dr Google suggested several sites for “dynamics gp alternate forms security,” including:
https://support.microsoft.com/en-us/help/942507/how-to-give-users-access-to-a-modified-form-or-to-a-modified-report-in

We published this blog on how to transfer your Dexterity code to the next version of Dynamics GP:
https://dynamicsgptipsandtraps.wordpress.com/2018/10/23/how-to-upgrade-your-dexterity-code-to-a-new-version-of-dynamics/

How To Upgrade Your Dexterity Code to a New Version of Dynamics

This blog is for your convenience so you don’t have to figure out how to transfer your code each time there is a major Great Plains upgrade, as the Dexterity manuals are a little confusing on this procedure. This blog does not contain any secret information or clever tips, it is just the checklist I compiled for my use and your benefit. If you have any suggestions to improve this checklist, please let me know.

For the past fourteen years I have been working as an in-house Dexterity programmer, and I only needed to transfer my Dexterity code when we upgraded Dynamics GP.  Since years could pass before we upgraded I would have to spend half a day figuring out how to transfer the resources again, so I did what all smart programmers do, I prepared a checklist so I would not have to figure this out afresh each time.

If you need to update your Dexterity code between service packs, you should probably not use Developer Update but the full transfer described here.  Often the service packs include updates or native script changes that require a full transfer.  (David Musgrave says you should never use Developer Update in the newer versions of Dexterity.)

In this checklist I use the generic source code control to generate raw text files that are not in some magical data structures somewhere.  Years ago we experienced corruption on our Dexterity Visual Source Safe control files, so my personal preference will always be to do the jump files with Generic Source Code control.  This could be a redundant backup copy, which is oxymoronic, since by definition there is no such thing as a redundant backup.  If your office stores all the source code in a central server like they should, but they don’t back it up like they ought to, and something happens but you have another copy from a little while ago, YOU WILL BE A HERO.

This useful series of blogs gives you advice on setting up your development environment was published by David Musgrave:
https://winthropdc.wordpress.com/2017/07/12/dexterity-development-environments-series/

The directory listing screen shots were from the shareware Total Commander program, which I highly recommend.  The text editor screen shots were from QEdit.

EXTRACT / BACK UP CHANGES IN EXISTING DEXTERITY DICTIONARY

STEP 1:
First, compile all your custom code and synchronize your development dictionaries, you need to eliminate all compile errors, using these menu options:
File -> Open Development Dictionary, UTILITIES -> SYNCHRONIZE
and EXPLORER -> COMPILE ALL

Next, you need to properly point to the proper development dictionary.  Here I created a DEXTEMP subdirectory.
Go to the menu: Edit -> Options
Make sure the checkbox for Use Source Control is checked,
The ORIGINAL DICTIONARY should be the original unmodified Dynamics dictionary.

dexcheck1What to do if you did not retain this dictionary: You can try with an original dictionary that should be identical.  If you are lucky, you will be able to generate the text files reflecting your changes.
Otherwise, get another unmodified DYNAMICS.DIC dictionary file, make a copy, and start copying and pasting and making your changes in this copy.  You can only use the DYNAMICS.DIC file from the Dynamics program directory ONLY IF you did not install a service pack since you initially created your Dexterity development dictionary.

IMPORTANT: Select the correct PROJECT NAME.

STEP 2: Click the APPLY button, then OK, then VALIDATE CONNECTION, you want to see this message:
dexcheck2

But, if you get this message:
dexcheck3

It means you need to create the subdirectory in the DSCCS\ROOT\ directory.  This is the directory you setup when you installed the Generic Source Code provider.  This is on the install CD in the Tools\Dex\DSCCS\ subdirectory.
dexcheck4

STEP 3: Delete all folders and files in the project folder under the ROOT folder, or move them to another subdirectory as a backup, we want to start from scratch.

NOTE: To change the root folder, click on DEXTERITY SCCS in the Control Panel:
dexcheck5

STEP 4: Execute menu option:
Explorer -> Source Control -> Update SCC State
dexcheck6

The resets the SCC State all your scripts, tables, and third party forms to NEW, and GP resources to Main Product:
dexcheck7

If the SCC State for the third party resources are blank, you did not delete them.

STEP 5: Go to your modified Great Plains forms and “touch” them, by opening the form and clicking OKAY, which makes them NEW:
dexcheck8

How do you know which forms to select?  The best way is to review the chunking macro file.  NOTE: Once you figure out how to chunk your code, record the steps in a macro in Dexterity Utilities.
dexcheck9

IMPORTANT: Double check that you have touched all modified forms.
NOTE: If you click on SCC STATE in header to sort by this field, you can double check your work easier.

STEP 6: Access the menu Explorer -> Source Control -> Check In, and Click INSERT ALL to check in the source files:
dexcheck10

You need to enter a comment, a dot will do, and click CHECK IN:
dexcheck11

Then this window will be blank, so CLOSE it.
dexcheck12

STEP 7: Execute the menu option:
Explorer -> Source Control -> Update Index File
The Index File will be created in the ADMIN folder.  If there is no ADMIN folder, there is no index file.

STEP 8:
Quickly scan the directories to be sure the extraction of the changes is complete:
dexcheck13

For example, the FORMS folder will include the forms that have been edited and/or created.  The form files will also include the scripts for that form.  These are text files, you can view them or search them.  You should avoid editing them directly.
dexcheck14

STEP 9: Uncheck USE SOURCE CONTROL, then press APPLY, and OKAY.
dexcheck15

STEP 10: These text files are a complete backup of all changes, so copy them to a backup subdirectory.

TRANSFER CHANGES TO NEW VERSION OF DEXTERITY DICTIONARY

Now that we have extracted the text files for all our enhancements to Dynamics GP, now we merge these enhancements into a blank dictionary of the next version.

STEP 1: Install Microsoft Dynamics and Dexterity for the new version.

Copy the DYNAMICS.DIC file from Dynamics folder to the Dexterity folder, once as an unmodified dictionary, and also for each third party dictionary whose changes you wish to transfer.  You will also need to copy the DEX.INI file to the DATA subdirectory.

Suggested naming conventions for these copies of DYNAMICS.DIC file:
DYN2016Unmodified.DIC
DYN2016ModName.DIC
Initially they will be identical, when the transfer is complete DYN2016ModName.DIC will contain both the Dynamics encrypted code plus the source code of the third party modifications.

Make sure you can run Dynamics from Debug -> TEST mode using the unmodified Dexterity dictionary.  If it errors out because of a missing DLL, please copy that DLL file from the Dynamics folder to the Dexterity folder.

You need to copy some files from the Dynamics folder to the Dexterity program folder:

(Note: this list is for GP2015)
GPDWIN32.dll
GPIcons.dll

When you compile, if you get an error message saying one of these DLL’s are missing, copy them to get rid of the compile error message:
Microsoft.Dynamics.GP.BusinessIntelligence.Homepage.dll
Microsoft.Dynamics.GP.BusinessIntelligence.Homepage.Framework.dll
Microsoft.Dynamics.GP.BusinessObjects.dll
Microsoft.Dynamics.GP.ManagementReporterServices.dll
Microsoft.Dynamics.GP.Svc.Application.dll

Reference, post by Dave Musgrave:
https://winthropdc.wordpress.com/2015/07/24/developing-using-dexterity-for-microsoft-dynamics-gp-2015/

Also, copy the DEX.INI file from the Dynamics data subdirectory to the Dexterity data subdirectory.  Dave recommends:
Change DexHelpPath to point to the Dexterity folder.
Add ShowResids=TRUE (for pre GP 2015, GP 2015 now shows Resource IDs as default)

STEP 2: File -> Open the copy of the DYNAMICS dictionary.
Under Edit -> Options menu, setup the SOURCE CONTROL options.

IMPORTANT: Here the Original Development Dictionary will be a copy of the unmodified Dynamics.DIC file.  It is good practice to copy this file to something like Dyn2016Unmodified.DIC.  The process will load the changes into this dictionary from the text files created in the previous step.

IMPORTANT: Select the correct PROJECT NAME.

Click on VALIDATE CONNECTION:
dexcheck17

STEP 3: Set the other Dexterity options:
GENERAL: Check Allow Long Physical Table Names
You may wish to change the script text font size
dexcheck18

STEP 4: Review the SCC States, you should not need to reset them.
The SCC State should either be MAIN PRODUCT or NOT SET.
Only vanilla Great Plains resources should be listed because this is an unmodified dictionary.
dexcheck19

STEP 5: Now we are ready to import the changes into the update version of Great Plains.
On the menu, select: Explorer -> Source Control -> Update
Use these settings from Chapter 51 of the Dexterity PGRV1.pdf documentation file:
dexcheck20

IMPORTANT: You must USE INDEX FILE for a successful UPDATE.
You will want to save both reports to a text file:
dexcheck21

You will see this window, quickly review the source files so you have a feel they are all there, especially the modified forms, and click OKAY:
dexcheck22

STEP 6: Review these reports, the SCC report in particular.  If you have many third party changes, rarely will you see a pristine report.  Review each error and warning.  If you have dozens or hundreds of error messages, you will need to go back to the previous dictionary and begin again.

STEP 7: Synchronize and compile the new dictionary, you want no errors in this step.  If you get a bunch of missing DLL warning messages, ensure that the files listed in STEP 1 were copied to the proper subdirectory.

STEP 8: Open a form and on the menu select LAYOUT -> SHOW FIELD NAMES:
dexcheck23

STEP 9: THE MOST IMPORTANT STEP OF ALL!
Immediately access Great Plains in TEST mode and see if you can access data, like doing a GL Account lookup, to ensure that the new modified dictionary is valid.

STEP 10: Review each modified form for cosmetic changes.  If Great Plains added any push buttons, you may need to move your push buttons around.  And you may need to move around any other fields that Great Plains has added to these forms.

Test in particular those modified windows where you are interacting with Great Plains scripts and/or objects.

 

Developing with Dexterity/.Net, and GP Power Tools

My objective in this blog is to lay out my professional opinion on the various approaches to customizing GP-Great Plains. My experience for the past fourteen years is with a wholesale company with an unlimited Dexterity support plan for some years, and during that time I submitted an unlimited number of support requests to learn as much as I can from the excellent Dexterity support.

When scanning the developer listings for contracts and positions on the various recruiter websites, I get the impression that some people out there may have some unrealistic expectations regarding Microsoft Dynamics GP customizations.

My objective in this blog is to lay out my professional opinion on the various approaches to customizing GP-Great Plains.  My experience for the past fourteen years is with a wholesale company with an unlimited Dexterity support plan for some years, and during that time I submitted an unlimited number of support requests to learn as much as I can from the excellent Dexterity support.

Please, feel free to comment on your thoughts on GP Development in the comments, particularly if your experience is on the partner side of the fence.  This blog expresses my personal programming preferences, which will evolve as I get back into the consulting world.

PROGRAMMING WITH DEXTERITY

Dynamics GP itself was written using the Dexterity programming environment, both Dynamics GP and Dexterity were developed simultaneously.  Dexterity was designed so any Dexterity modifications could appear to be part of Great Plains, without touching source code, while interacting with the internal Dynamics code and windows, and scrolling windows.  Upgrading to the next version of Great Plains is painless, usually the developer only needs to do a Developer Update to rebuild the chunk file.  Rarely does the developer need to update their enhancement code for an upgrade.  (I plan to publish my Developer Update checklist in a future blog.)

Dexterity has a full complement of functions that can trigger off Dexterity form, window, field, and table events.  When updating the POP and SOP windows in particular, you need to use the pseudo-object scripts to interact the native Great Plains objects, fields, and data, which can be challenging.

Dexterity programs can write to the GP SQL databases using either the Dexterity table functions, or pass-through SQL commands, or by calling stored procedures.  My preference is to create all third party SQL tables through the Dynamics GP menus so they are visible to Dexterity.  Dexterity can also see SQL views as read-only tables.

Dexterity is usually the best tool to use when modifying Dynamics GP functionality.  This is especially true when you need to modify Dynamics windows, and most especially true when you are modifying scrolling windows, or the Dexterity flavor of grids.  You can add tables and fields and menus to the existing Dynamics forms, you can also hide or rearrange the existing fields and prompts.

When you program your enhancements in Dexterity, the Modifier and Extender products are mostly redundant.  However, some developers use VBA to customize fields added to windows using the Modifier.

PROGRAMMING WITH VISUAL STUDIO TOOLS AND DEXTERITY

Dexterity is missing some fundamental functionality that can be added by the Visual Studio Tools, which enables you to write functions that trigger off events in Dynamics.  We use the Visual Studio Tools to read the system environment variables, specifically the Citrix server name to display in error messages, and also to return a directory listing useful in EDI applications.

Dynamics GP still has many years of life left, the number of Great Plains GPUG members outnumber the total of all the other users at the user group conference, but the number of Dynamics AX new installs are greater than Dynamics GP new installs.  So if you are developing involved applications such as EFT or EDI applications, it makes sense to write these as .NET objects that are called by Visual Studio Tools C# codes that are called from a Dexterity/Dynamics GP user interface, and could later be called from a Dynamics AX user interface.  This keeps your options open.

The Dexterity manual suggests that you can manipulate scrolling windows with the Visual Studio Tools functions, but to the best of my knowledge and experience, this functionality is not dependable.  Developers who specialize in .Net programming have not been able to get this to work properly, and this was confirmed by a support case I submitted to Great Plains and some GPUG online discussions.  There might be some funky secret knowledge that is need to access the scrolling window table buffers properly, if so, I am unenlightened.

Visual Studio Tools can also run any Dexterity script in any dictionary, in addition to triggering off any Dexterity script.  This gives you more flexibility if you need to write code that interacts with other third party Dexterity applications.  You can also create .Net forms and grids that can be opened in Dynamics GP, but my preference is to create these in Dexterity.

If you wish to write a .Net program that independently reads and writes to the Great Plains tables, you do not need to use the Visual Studio Tools.  A really good example of this type of program is Salespad.

IMPORTING EXTERNAL TRANSACTIONS

The best practice for writing transactions to Dynamics SQL tables is the same whatever tool you choose to use.  You simply enter a transaction in vanilla Great Plains that matches the custom transaction as closely as possible, then generate a transaction using the method you choose, then compare the data in each table field by field to make sure the data pattern is identical.  Usually, you do not need to write to the note index fields, but once I had to generate a note index field for an inventory item table.

In addition, when creating transactions with inventory items, and if an external application is creating and/or updating those inventory items, or any other master records, you need to ensure that inventory items and/or other master records in the transactions contain valid data.  We often write a check routine verifying that the master records referenced in the transactions are valid before the application will post the transaction batch.

Over the years, I have developed Dexterity functions to write to the major transaction tables that includes rollback capability when an error is encountered.  Also, I have written Dexterity routines that read data directly from Excel spreadsheets.  If a client has a license for eConnect or its more capable cousin, SmartConnect, that is a good solution also, particularly since many developers are familiar with these tools.

Some consultants have reported difficulties on the GPUG discussion board using the eConnect and SmartConnect tools in unique situations.  Microsoft has developed the Service Based Architecture product to integrate external data using .Net functions, which they hope will replace eConnect and SmartConnect.  Although the Service Based Architecture has not yet gained traction in the marketplace, technically it is very appealing.  A hurdle to learning SBA is it will only run on Windows Server, it will not run on Windows 10.  These tools are useful to stream web based transactions data into Great Plains.

GP POWER TOOLS

David Musgrave did an early morning remote presentation for our local GPUG chapter, since early morning in Florida is like midnight in Australia, and we were absolutely blown away.  Everyone clapped.  Truly, IMHO, whether you are a user, or a system administrator, or a developer, GP Power Tools needs to be part of your Great Plains installation.  I mean that literally, GP Power Tools is not merely an add-on, it indeed becomes part of Great Plains.

Without a doubt, David Musgrave is a first rate Dexterity developer.  After Microsoft hired him he helped write enhancements for both Dynamics and Great Plains.  He wrote the field level security module, provided input to some trigger functionality that dramatically improved the Report Writer, and wrote a remote debugging module that grew into the current GP Power Tools, which Microsoft allows him to market independently today, for everyone’s benefit.

Please, review his listing of the chief benefits of the GP Power Tools:
https://winthropdc.wordpress.com/2016/03/02/gppt-top-benefits-of-gp-power-tools/

You may not pick up on the true power of GP Power Tools by just reading this list, so let me paraphrase what I find most appealing about these tools.

David Musgrave was a high-level escalation support person for Microsoft, and had to troubleshoot an intermittent mission critical anomaly for a large VIP client.  He was quite frustrated, so he wrote a quick application to capture the various SQL dexlog and Dexterity script logs while Great Plains was running to help him troubleshoot this problem successfully.  This tool evolved into the GP Support Debugging Tool to benefit other users, which evolved into today’s GP Power Tools.

While at Microsoft, David also help troubleshoot funky problems encountered by large installs during version update conversions.  With this knowledge, he added some routines to GP Power Tools to check the GP SQL databases for problems that could possibly cause problems during updates.  If you run these routines before you upgrade to a new version they will also help control your blood pressure and help alleviate other stress related disorders and perhaps even prevent temporary baldness, which sometime occurs when you start pulling out your hair.

David probably has the deepest knowledge of Great Plains security on anyone outside of Great Plains, especially since he was involved in developing the pessimistic security model, so the GP Power Tools includes both additional security functions and improved reporting on which users can do what in GP.

GP Power Tools will also help you find your way in Great Plains, so you will never forget where you are.  You can know whether you are in your peaceful production copy of Great Plains, or whether you are in the light blue test environment of Great Plains, or whether you are in the yellow development version of Great Plains, or whatever color scheme you care to setup.  And, you don’t have to worry about being in limbo in an invisible session, GP Power Tools can pull GP back into the visible desktop.

FUTURE BLOGS

In the first versions of Dynamics Report Writer you could change the position of the fields on the report and add fields from the current tables to the report, but you were not able add fields from additional tables to the report.  You can circumvent these limitations using triggers and also by using the Dexterity Report Writer trigger functions that our hero David Musgrave helped develop while he was at Microsoft.  We will write a blog on these advanced Report Writer topics.  We will also write a blog on modifying other third party dictionary products using triggers and cross-dictionary development strategies.

SUMMATION OF LONG TERM STRATEGY

Although the headhunters tell me that though there still numerous developer positions for Dynamics GP, the developer positions for Dynamics AX outnumber those for Dynamics GP by about five to one.  However, at the user group conferences the Great Plains GPUG members outnumber the total all the other user groups combined.  Demand for third party enhancements for Dynamics GP remains strong.

We have every indication that Microsoft will formally support Dynamics GP for the next decade or longer, the recent release of their Service Based Architecture integration software confirms this.  However, the fact that Microsoft has laid off their top marketing and support staff, including David Musgrave, shows a certain ambivalence towards the product.  Developers who know how to program for both the Dynamics GP and AX platforms usually prefer to develop in Dexterity for Dynamics GP.  Since Dynamics GP stores its data in SQL tables, there is nothing internally that will prevent Dynamics GP from running on any future hardware or software platforms.  Since Dynamics GP was originally developed first for BTrieve and CTree tables, adding support for MS SQL in version 3X, it is missing some SQL-specific data elements like foreign keys, but this does not cause any upgrade or performance headaches.  In summary, Great Plains has many years of life left.   Long live Great Plains!  Or at least until the day when I am singing with Elvis.

Finishing an Aborted GL Closing

Closing the general ledger in Dynamics GP can take a long time, if you have millions of GL records it can take many, many hours.  You punch the button, and you see, NOTHING, but something is happening in the background as it transfers the years activity from the open file to the history file.  Let the process run overnight, or over the weekend if you have a very large database.

For the most recent years our GL closings have gone well, but we did have several years glclose1some time ago when the GL close failed.  One year there was an automated process that rebooted the server, another year the Citrix session that was doing the close timed out and aborted.  Since the GL closing process is a background procedure, Citrix thought GP wasn’t doing anything.

Call Great Plains support, tell them your GL closing failed, their answer is simple, restore from backup and try again.  But… we couldn’t restore, the backup was bad.

Fortunately, the GL file structure is pretty simple, so we were able to write the SQL queries to resolve the close.  My recollection was one year we rolled the data back so the user could restart the close, another year we finished the closing process.  I won’t provide you with cookbook SQL queries to just run because that is just not practical, you need to understand where the closing process stopped to recover from it, but I will provide the closing logic used by GP.

BE PATIENT, DO NOT RUSH, AND PREPARE

You do not need to be in a rush to close the year, you can have two open years in Great Plains.  The closing process creates the beginning balances for the new year’s balance sheet accounts, which means the balance sheet for the new year will not make sense until you close the old year.  There is no accounting advantage to closing the GL before the conclusion of your first reporting period.

Of course, do a SQL backup.  After our experiences, we open a remote session that will not time out directly on the server to close the year we do not close our large GL on a Citrix workstation that times out after a period of inactivity.  Also, suspend any jobs that will reboot the server in the middle of the night.  (You are luckier if your server aborts during a version upgrade.  GP remembers its progress and will actually restart the upgrade conversion.)

We always make a copy of the general ledger file, with all of the debit and credit amounts, in a separate SQL table for the auditors.  This table is refreshed just before the year-end close, so if the closing process is aborted we know for sure the total of the debits and credits for the year.

Also, make sure that all GL Accounts are in their proper Balance Sheet versus Income Statement category.  You can do this either with a SQL query or with a SmartList.

If you miss an account that has not been properly identified as a balance sheet or income statement account, you can do a pseudo-reversal of the close to fix the closing entry and the beginning balances.  I say pseudo because it doesn’t actually reverse the physical closing, but it will move balances between the balance sheet and the income statement after you have fixed the GL account type flag.  This blog explains how to do this:
https://community.dynamics.com/gp/b/kuntzconsulting/archive/2018/02/06/reverse-fiscal-year-end-close

You should always review the official GL closing checklist, it never hurts:
http://support.microsoft.com/kb/888003/

This is a really good article on GL Closing issues that many users encounter.  It is a bit old, but it still applies:
http://community.dynamics.com/product/gp/gptechnical/b/dynamicsgp/archive/2012/12/11/draft-microsoft-dynamics-gp-year-end-release-2012-general-ledger-year-end-close.aspx

WHAT HAPPENS DURING GL CLOSING?

Caveats: You are on your own here, Microsoft will not support you, they want you to restore from backup.  ALSO- these experiences are from GP Versions 8 and 9, but should glclose2be applicable today.  And there may be complications if you are multi-currency.

The good news is the GL files are pretty simple, we have encountered no obscure files that obfuscate the closing process, so a good SQL guy can recover from an aborted closing.  These are the files involved:

GL00100 and GL00105: GL Account and GL Account Index
GL10000/GL10001: Unposted GL Transactions.

GL20000: Posted current year GL Transactions
GL30000: Closed GL Transactions
These two tables are basically the same structure, except the SQL year field in GL20000 is YEAR1 and the year field in GL30000 is HSTYEAR.

GL10110: Summary GL Transactions for current year GL Transactions
GL10111: Summary GL Transactions for historical year GL Transactions

SY40100/SY40101: Period setup files, these tables contain flags denoting whether a period is open or closed.

NOTE: You can always regenerate the summary totals from the detail using the GL Reconcile process.  Often this is needed when recovering from an aborted GL closing.

During closing, the following happens, based on my recollections and my notes from prior years.
1. The closing entry closing the income and expense accounts is created in GL20000 table with dexterity code.
2. The closed year GL lines are created in the GL30000 historical GL table.  This is probably done by one or more SQL queries since there is no progress bar.
3. The closed year GL lines are deleted from the GL20000 current year table.  This data was deleted row by row in GP9 with Dexterity code rather than with a SQL query.  Which makes sense, SQL queries that delete large data sets can cause the SQL transaction logs to overflow, killing the process.
4. The BBF and P/L journal entries are created for the new current year in the GL20000 table, so the balance sheet can be printed for the new year.
5. The summary GL10110/GL10111 GL tables are created from the detail (when this exactly happens I do not know).  You should do this using the GP Utility menu option.
6. The closing flags in the fiscal period tables, SY40100/SY40101.

SCENARIO ONE: The closing process aborted before the closing entries were finished.

Here there will be no general ledger records for the closed year in the GL30000 file.

The GL for the year being closed is out of balance.  This script will tell you which AJEs are out of balance:

declare @GLSummary TABLE (AJE int, debit money, credit money )

insert into @GLSummary ( AJE,debit,credit )
select trx.jrnentry,Debit=sum(trx.debitamt),Credit=sum(trx.crdtamnt)
from [AGINC].[dbo].[GL20000] trx
inner join aginc.dbo.gl00100 gl
on trx.actindx = gl.actindx
WHERE gl.accttype = ‘1’
group by trx.jrnentry

— gl.accttype = 1 excludes unit accounts

SELECT aje,debit,credit,net=debit-credit from @GLSummary WHERE debit <> credit

NOTE: GL Entries to unit accounts NEVER balance, so these are excluded in the above query.

The out-of-balance journal entries should be the closing entry that is incomplete.  If so, delete this  journal entry and you should be able to re-start the posting process.

SCENARIO TWO: The closing process aborted when it was nearly completed.

We did a SQL query and discovered the total of the debits and credits for the backup glclose3closed GL file for the closed year equaled the debits and credits in the historical GL30000 table.  But, the GL20000 current year file had transactions for the closed year that were out of balance.  We simply wrote SQL queries to delete the closed year transactions that were stranded in the GL20000 table.

You need to double-check to see if you need to manually create the BBF and P/L beginning balance amounts.  We also had to delete the GL Summary balances in the open summary table for the closed GL year when this happened to us, per my notes.

SCENARIO THREE: The closing process aborted when creating the historical year GL30000 entries.

This we have not experienced, but you should be able to simply manually write the queries copying the missing records from the GL20000 current year file to the GL30000 historical year.  And then follow the steps in SCENARIO TWO.

There may be other scenarios, so just review the data to see where the process aborted, and either finish it or roll it back.

Disclaimer: I take no responsibility for any problems you encounter during this process.  These notes are a starting point in recovering from an aborted GL closing.  You are on your own.  I can only say that I have recovered from aborted GL closings in the past.  And you do need some good SQL expertise and patience to successfully recover from an aborted GL Close.  These are recollections from my notes and memories from several years ago.

Furthermore, Agent Phelps, as always, should you or any of your Force be caught or killed, the Secretary will disavow any knowledge of your actions. This tape will self-destruct in ten seconds. Good luck.

Simple Users Tips and Traps

We know that Mark Polino has some wonderful fifty tips in fifty minutes on all sorts of Great Plains knowledge, but sometimes he forgets the quick tips that the true newbie Great Plains users do not know, so here are some tips that this bear of very little brain would like to share.

My first quick tip is: Do not forget that Dr Google and the GPUG discussion board are tips2wallywilling and eager to help you by scanning the internet for the answers to your questions.  Quite often, if you type in a piece of a funky error message, you will get a direct hit.  Many questions in the GPUG discussion board have been answered dozens of times already, so always search before you ask.

Dr. Google was able to find this version of Mark Polino’s tips, maybe there are more out there:
https://blogs.msdn.microsoft.com/gp/2009/03/16/popular-convergence-session-50-tips-in-50-minutes/

SHORTCUT FOR ENTERING DATES

When you are entering dates, you only need to change what you need to change.  To Tips2DateEntrychange the day, just type the day; to change the month and the day, just type the month and the day.  Save a keystroke here, save a keystroke there, and you get more time to drink coffee, which is always a good thing, unlike whiskey, which is not always a good thing, but whiskey, unlike coffee, has a country song.
https://www.youtube.com/watch?v=vogS-Fp2QVI

SEARCHING LONG REPORTS ELECTRONICALLY

When you are trying to balance an account, you can print the report to screen and Tips2FindTextOnReportquickly search the hundreds of pages electronically using the FIND menu option.

Keep in mind this is a TEXT search, not a numeric search, so you have to include the commas in any large numbers.  Or you can enter a portion of the number, or any text that appears on the report.

Sometimes I do this to search for the string “Error” for error messages.

 

MODIFYING YOUR CURRENT REPORT

When you print a report to screen, you can click on MODIFY to modify the contents of tips2modifyreportthat report.  This is helpful as sometimes it is difficult to find the exact report, and since a report selection may call several reports based on the report options.  For example, detail and summary options usually call different reports.

Don’t fall into the trap of modifying a report when other people are using the system.  If someone tries to run a report that you are modifying, the reports dictionary could become corrupted.  Which is not good.

SOMETHING TO TRY WHEN YOU ENCOUNTER A FUNKY PROBLEM

In the early versions of Dynamics, it seemed that if you called support for anything, their first response was, Try rebuilding the reports dictionary!  We rarely hear this advice anymore, Dynamics is now very mature, but if you have a hard to diagnose funky problem, you can always try this.  You need to do this when upgrading from one version of Dynamics to another.  Rebuilding the reports dictionary is very safe, I have never encountered any problems doing this.

To rebuild the reports dictionary:
Everyone needs to exit Great Plains.
In Customization Maintenance window, select all customized reports, and click EXPORT (see menu below).
Rename the REPORTS.DIC file to maybe REPORTS123118.DIC.
Open Great Plains, access the Report Writer, then close both, this generates a blank REPORTS.DIC file.
In Customization Maintenance window, there should be no customized reports, click IMPORT and select your exported package file (see menu below).
Tips2CustomMaint

PM MINIMUM CHECK AND OTHER PROFESSIONAL TOOLS

In our prior blog we discussed the RM TANSACTION UNAPPLY function in the Professional Tools, another popular tool is the PM MINIMUM CHECK function.

When this has been activated the minimum check amount is stored in the DEX.INI file:

MinPMCheck=2.00
; MinPMCheck used by Professional Tools, it is a setup option
; It is best practice to document DEX.INI settings like this one.

If the minimum check is not displayed in the SELECT PAYABLES CHECKS window, you Tips2PMMinCheckneed to add these entries to the other applicable Citrix metaframe servers or application servers.

The other interesting function we have activated is the DOC DATE VERIFY option, so your users don’t enter dates for the year 2525, which is a good song, but not a very good date to enter:
https://www.youtube.com/watch?v=izQB2-Kmiic

tips2proftools

This blogger describes the DOC DATE VERIFY function with a YouTube video:
https://community.dynamics.com/gp/b/calbusinesssolutionsbloggptips/archive/2015/07/15/dynamics-gp-video-tip-professional-services-tools-library-how-to-enable-doc-date-verify

And this blog has a quick explanation for all the Professional Tools functions:
https://www.erpsoftwareblog.com/2015/07/37-free-but-dangerously-powerful-pstl-tools-for-microsoft-dynamics-gp/

 

 

 

 

 

Basic Balancing and Accounting Tips

If you balance every day and you can find the exceptions much quicker.
Topics:
Balancing Tips
GL Allow Account Entry flag
Tips, Balancing POP RNI to GL
Better way to enter PM/RM AJE’s
RM Apply and Unapply Tips
Disaster Recovery

Early in our implementation cycle, the salesperson from the dealer who was selling us Great Plains was selling Accounting on the idea that since Great Plains was the most dependable accounting system on the planet that they would no longer have to worry about whether their payables and receivables balanced to the general ledger.  What was my response when Accounting told me about this assurance?  My response was that I definitely agreed, and  since Great Plains was the most dependable accounting system on the planet, that meant that we needed to balance all our control accounts to the general ledger EVERY DAY.  If there are any differences, if you balance every day, you will find any differences right away, but if you wait until month-end, you will never find the differences in the morass of data.  Often out-of-balance amounts are caused by timing and/or discount differences, or some sort of environmental problem.  Never have I found a difference caused by a bug in the Great Plains code.  However, we will never rid the planet of the damage caused by sunspots and gamma rays that seem to cause so many random system problems.

It is important to unmark the ALLOW ACCOUNT ENTRY box for control accounts (cash, TipsAllowAcctEntryA/R, A/P, etc.) on the Account Maintenance window.  For instance, this keeps users form entering the cash account number as the GL Distribution for expenses, thinking that the expense needs to come out of cash.

Another quick tip when you do not balance is to review the GL report for a transaction with a GL or an odd SOURCE DOCUMENT number on the GL detail inquiry window or GL Detailed Trial Balance.

We have other blogs on the fine points of balancing your receivables and payables to the general ledger:
https://dynamicsgptipsandtraps.wordpress.com/2018/07/19/my-gl-payables-receivables-total-do-not-balance-to-my-pm-rm-trial-balance-what-to-do/
https://dynamicsgptipsandtraps.wordpress.com/2018/07/23/fixing-the-pm-rm-historical-trial-balance/

BALANCING ACCRUED EXPENSES, POP RNI REPORT

We balance our Received/Not Invoiced report monthly.  We are able to balance this report to the Accrued Expenses account in the general ledger to the penny.  When Tips1RNIbalancing, keep in mind that an accrued expense will drop off this report when it is INVOICED, not when it has been POSTED.  Which means that posting as many invoice batches as you can will make  this account easier to balance, and that the POP Invoice unposted batch reports will be a reconciling item.

If you need to write off a POP Receiving amount that represents inventory that was not actually received and that will never be invoiced, you can enter a POP Return for that item.

We are planning a future blog on balancing your general ledger to the POP RNI Report.  This is the MS Dynamics GP support discussion of this topic:
https://support.microsoft.com/en-ph/help/951236/information-about-why-the-values-on-the-received-not-invoiced-report-m

TRAP WHEN CLOSING PURCHASE ORDERS

If you close a purchase order where the inventory shipments received have been partially invoiced, a journal entry will be generated for the un-invoiced items.  You Tips1ClosePOshould review the journal entry; the item cost may not be what you expect.

If you close a purchase order where none of the inventory shipments have been received, NO JOURNAL ENTRY will be created by Great Plains.  Great Plains says this is an intentional design decision.

ENTERING RM/PM ADJUSTING ENTRIES

When you discover a miscoding of account numbers for an expense or sales transaction, Tips1ZeroRMPMAJEyou should consider entering them through a zero dollar transaction rather than a general ledger entry.  That way the corrected account numbers will be assigned to the proper vendor or customer in your BI reports, and the auditors will have fewer general ledger entries to critically review.  For the document number you could enter the original document number plus “-JE”.  You can enter zero transactions to adjust RM, PM, and POP transactions.

ROLLING DOWN WRITEOFF AND OTHER CHANGES TO DEFAULT CLASS

Even if your business does not want to have a minimum receivable write-off, you should Tips1CusClassSUalways have at least a minimal write-off amount, even if it is a dollar, so your cashiers can write-off any hanging pennies when entering cash receipts.  Otherwise, you will have lots of penny trash cluttering up your trial balance that you then have to write-off.  Be sure to check all the history boxes on the default class also.

Note that you can check the DEFAULT checkbox for one default customer class.  When Tips1CusClassRolldownyou make a change in any class   Great Plains asks you if you want to roll down to all customers, or vendors, or inventory items, in this class.  This means it will ONLY roll down the changes, NOT all the items entered in the class setup.

PRIMARY DIRECTIVES FOR ACCOUNTING SYSTEMS

There are a few rules for Great Plains and all other accounting systems, like the first rule we already discussed, BALANCE DAILY.

Another simple rule is always understand the journal entry each transaction is generating, do not view Great Plains like it is somehow magical.

And, you never have to worry about making yet another redundant backup, because there is no such thing as a redundant backup.

Another rule for the timid, whenever a Great Plains process asks you if you want a REPORT ONLY, or if you want to both process and print the report, ALWAYS print the REPORT ONLY, and then decide if you want to continue and PROCESS.  Which brings us to the next tip:

REAPPLYING CASH AND CREDIT MEMO AMOUNTS

When you apply cash and credit memos to your sales transactions, these matched transactions remain on the customer statements until you run PAID TRANSACTION Tips1PSTLREMOVAL, which transfers the matched sales and cash-credit memo transactions from the open tables to the history tables.  If the customer complains that the cash or credit memos were applied to the wrong invoice, then you need to run the RM Transaction Apply function in the Professional Services Tools Library window.  This tool is now free, but you need to manually add it to your menus.

When you click the RM TRANSACTION UNAPPLY radio button, Great Plains lets you Tips1Unapplyselect the credit memos and/or cash payments you wish to unapply, then you click OKAY.

We need to follow the PRIME DIRECTIVES above, and when Great Plains asks us if we want to first jump off the cliff before we know where we are going to land, we need to respond, REPORT ONLY.  You might ask, do we want to UNAPPLY ALL, or only ONE PAYMENT?  The answer is, pick the best one, since are first going to Tips1UnapplyPromptrun the REPORT ONLY, you will get to see what each choice actually does before you do it.

When you run the Unapply Report, you only want to UNAPPPLY only if the invoices matches the payments/credit memos on the report.  If these do not match, and you hit UNAPPLY then your RM Trial Balance will no longer match your general ledger.  Some years ago when one of our users did this it took me several hours to puzzle through the writing of a SQL query to fix the applied data.  So please be kind to your IT department, run REPORT ONLY first.

Tips1UnapplyReport

PLANNING FOR DISASTER RECOVERY

disasterrecoveryplan

Fixing an Out-Of-Balance PM/RM Historical Trial Balance

Human error is also the primary cause of HATB discrepancies.  Like the regular AR trial balance, if you do not click to exclude UNPOSTED APPLIED CREDIT DOCUMENTS, this guarantees that you will not balance to the GL.  Knowledge Base Article 866570 also advises that you check to exclude zero balance, no activity, and probably fully paid items also.  And also since the HATB report uses the document dates and apply-to dates to back into the AR/AP HATB balances, transactions with next year’s date will totally drop off the report.

dt130107
Dilbert website

On rare occasion you may find some hanging ancient transactions that appear on the AR/AP HATB report that need to be cleared.  If you submit a technical support case with Great Plains, they will send you some thorough diagnostic SQL scripts so they can suggest steps to take to resolve it.

If you are moderately brave with SQL you can fix the files, just grab the values in the files before you try the fix.  You will likely only be modifying history files, which means minimal risk.  The HATB uses the trial balance files as a starting point, excluding recent transactions after the HATB date, and adding back relevant transactions from the history file and the historical apply-to file.

Recently we had to fix two transactions hung on our AR HATB file.  One was out of balance by the discount amount, the apply to file had a discount amount double the discount in the history record.  The discount was under ten dollars, we tried to match the discount amounts, that didn’t work, so then we just zeroed out the discount amount in the historical payables tables and the historical apply-to table, and the bogus HATB transaction disappeared.

HATB_OOB
This was an ancient invoice that was stranded on our HATB report. Notice how the inquiry screen does not foot. We found a credit memo in history whose amount was equal to the difference between the document amount and the applied amounts. This credit memo was missing its apply-to record in table PM30300, once we added that, the apply-to document was in balance.

We looked at the inquiry screen for the other bogus AR HATB transaction, and we discovered that the apply to inquiry screen was out of balance.  We looked through the transactions nearby for that vendor, and there was a credit memo for the exact out of balance amount, and we discovered that this transaction was missing the historical apply-to record.  So we added the record using SQL, using a neighboring apply-to record to figure out what to enter in each of the fields.  Once we INSERTed the missing apply-to record, that bogus AR transaction dropped off the AR HATB report.

A consultant friend of mine had a difference in his client’s AR HATB file a few periods back that was also equivalent to a credit amount, so he simply tinkered with the apply-to date in the apply-to file, and that fixed that HATB report.

If tinkering with the files with SQL queries is not a realistic option for you, you can fix most HATB errors simply by voiding the problem payment/receipt and re-entering it as a manual payment/receipt and posting it again.