Balancing the POP RNI Report to the GL

The POP RNI (Received Not Invoiced) balance should be balanced to the General Ledger Accrued Expenses balance, for it is truly a legal liability that must be tracked and reconciled.

Advertisements

In our old blog on balancing GP we promised that we would cover balancing the RNI in more depth, and since then our not so old dog has learned some new tricks.  Please review our old blog for generic advice that affects balancing all subledger in all modules to the GL, and correcting the posted GL subledger AJEs (Adjusting Journal Entries):

https://dynamicsgptipsandtraps.wordpress.com/2018/08/28/basic-balancing-and-accounting-tips/

This is the MS Dynamics GP support discussion of balancing the RNI report to the GL, please review it in case Microsoft adds any suggestions we do not cover here.
https://support.microsoft.com/en-ph/help/951236/information-about-why-the-values-on-the-received-not-invoiced-report-m

CAVEATS: If you use Landed Cost or Multi-currency, you may need to take these adjustments into account.

By definition, drop-shipment transactions never appear on the RNI report because it is your customer who receives the inventory.  For direct shipments your customer has the RNI liability, not you.

PRINTING THE POP RNI REPORT

The RNI, or Received Not Invoiced report, is a PURCHASING ANALYSIS report.  You should probably use the date you began using the POP module as your report beginning date.

RNI Report

We balance our Received Not Invoiced report monthly, but in hind-sight we recommend that you try to balance this at least on a weekly basis, at least until you successfully tame your RNI beast, and keep him tamed.  You should be able to improve your business processes so this report balances to the Accrued Expenses account in the general ledger to the penny.  This is how the general ledger entries flow for the RNI, or Accrued Expenses GL Account:

RNI T Accounts

When balancing, 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.  You can build a SmartList for the unposted RNI balances you need to add back, or you can run this SQL query:

USE [TWO18]  — use your own company database
SELECT [POPRCTNM],[POPTYPE],[VNDDOCNM],[receiptdate],[BACHNUMB],[VENDORID],[VENDNAME], [SUBTOTAL],sum([SUBTOTAL]) OVER() as GrandTotal
FROM [dbo].[POP10300]

TIPS AND TRAPS 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 should 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.

RNI Closing PO

The preferable method to write off an RNI amount that will never be invoiced is to enter a Returns Transaction Entry.  This method always generates a GL adjusting journal entry and provides a better audit trail.  When you enter a RETURN, access the help screen for the window and review the FIELD documentation.  The type affects the allowable return quantity, the unit cost of the return items, and for non-US companies, the value-added Tax Schedule selected.

RNI POP Refunds

For RNI writeoffs, my preference is to select the RETURN type.  The RETURN type uses the POP inventory receiving cost, aka the original shipment receipt cost.

OTHER REASONS WHY VALID POP INVOICES DO NOT APPEAR ON THE RNI REPORT

Why would valid vendor POP Invoices not appear on the RNI Report?  The most common reason is the PO has been cancelled in error.  Unfortunately, Great Plains does not permit you to un-cancel a purchase order that has been cancelled in error.  You can only uncancel a purchase order with a SQL query.  Note that Status 4 means is the RECEIVING status, and that you will need to bring the cancelled subtotal or CANCSUB to zero, and reinstate the outstanding SUBTOTAL amount.

USE TWO18  — use your own company database
declare @PONumber varchar(17)
select @PONumber = ‘PO123456’
update dbo.pop10100
set postatus = ‘4’,statgrp = ‘1’,subtotal = cancsub where PONUMBER = @PONumber
update dbo.pop10100
set cancsub = 0 where PONUMBER = @PONumber
update dbo.pop10110
set polnesta = ‘4’ where PONUMBER = @PONumber

Over the past decade we have had several instances where the POP Invoice amount does not appear on the RNI report, but Great Plains will not let us AUTO-INVOICE the PO because it has already been invoiced!  How do you discover which POP Invoice is hiding?  You simply run a SQL query looking for any unposted POP Invoice lines that are hiding:

USE [TWO18]  — use your own company database
SELECT [POPRCTNM],[PONUMBER],[ITEMNMBR],[ITEMDESC],[UNITCOST],[EXTDCOST]
FROM [dbo].[POP10310] WHERE [POPRCTNM] NOT IN
( SELECT [POPRCTNM] FROM [dbo].[POP10300] )

This SQL query simply looks for unposted POP Invoice or POP Receiving lines that have inventory detail but no header record saved.  This can happen because the underlying Dexterity engine saves each scrolling window line immediately, but does not immediately save the header information.

If this query returns some transactions, how do you fix them?  By far the simplest solution is to enter the hiding transaction in either the POP Invoice Entry or the POP Receiving Entry form, enter the hiding POP Receipt Number.  You will see the lines with inventory items but no vendor information.  My strong advice is to simply delete the invoicing or receiving transaction and re-process the transaction.  If you delete and start over, you know your link file data will be valid.

You will often see POP10300 orphaned header records with a zero POPTYPE flag and no details.  You can delete them if you wish, they have never caused us problems.

PARTIALLY POSTED BATCHES AND OTHER POP DATA ISSUES

If the posting for your POP Invoice or Receiving batch is interrupted, usually some or all of the POP records for one transaction will be in both the work and history files.  Print the batch report to screen, enter another batch number for the problem transaction with duplicates, and post the remaining valid unposted transactions.

We always puzzled out the SQL queries needed to fix the data in the PO tables (POP10100/110), POP Receipt tables (POP10300/310/POP30300/310) and the link tables (POP10500/600), if yours is a more complex situation there may be other tables.  Our situation is we had multiple custom tables with data that was hard to duplicate.  Alternately, you can delete the unposted transaction, and delete with a SQL query any leftover records in the history and link tables.

The following rogue event happened to us: twice in the past two years, but never in the decade previous, in the middle of the day, during data entry, not during posting, all the line inventory details were deleted for SOME but not ALL unposted POP Invoice and POP Receivings.  Our sister custom detail tables were never touched, so it was unlikely that our custom Dexterity code was at fault.  Dr Google told us that several other reputable consultants have had clients that experienced this problem.  However, Great Plains support says that this is a rare event, that they do have a record of anyone opening a support reporting this issue in recent years (I know I did not).  Great Plains support told me to encourage everyone who experiences this problem to contact them.

The very best way to recover from this event is to delete the problem POP Invoices and POP Receivings that have no lines and re-enter them.  BUT, if the user tries to POST these batches with headers and no lines, they will post without warning and without lines, which will mean that the RNI Report for these transactions will no longer be accurate, until someone fixes the link files with SQL queries as best they can.

USEFULNESS OF POP CHECK LINKS

IMHO, running Check Links for the POP module is not only a waste of time, it is also taking an unnecessary risk of data corruption.  Not to mention that the POP check link procedures are glacially slow.  We ran check links on a test backup of our production data (IMHO, you should never run Check Links first on production data, though these functions are much safer now than in the earlier years of Dynamics GP).  Check Links for the POP modules verifies mostly the PO files, but not so much the POP Receipt and POP Link files, which are the real accounting transactions in GP.  We came to this conclusion after exhaustively analyzing the text file output of all our POP check link procedures for several years of work and historical.  If other consultants have differing opinions or experiences, we would be happy to hear from them.

Extending Dynamics GP Report Writer Capabilities with rw_Dexterity functions

You can greatly extend the capabilities of the GP Report Writer with the use of the special rw_TRIGGER functions that enable you to access data from any third party table using Dexterity, or any data source using .NET with Dexterity if you wish.

You are limited in how you can modify existing Report Writer reports in Great Plains, you can add fields from the existing tables attached to the report, and you can remove the fields you do not need on the report, and rearrange the existing fields on the report.  But you can’t add new tables to the report, or include data from non-Great Plains SQL tables.

Most of these limitations were eliminated in Version 9 of Dexterity and Dynamics GP.  When he was working for Microsoft, David Musgrave lobbied to add some Report Writers trigger functions to the Dynamics code base that would allow developers to modify existing reports to include information from any table in the system, or any other data that could be accessed by Dexterity, SQL, or .NET.

David Musgrave describes these capabilities are described in his blog:

https://winthropdc.wordpress.com/2017/03/01/rw-using-the-rw_tablelinestring-and-rw_tablelinecurrency-report-writer-functions/

This blog references a Microsoft knowledgebase article on these enhancements, which has inexplicably been pulled from the Microsoft support site and can also be accessed from David Musgrave’s site:

https://winthropdc.files.wordpress.com/2017/03/kb_mbs888884_usefulfunctionsforreports.pdf

My blog will provide a simple but very useful example on how to use these functions in a custom Report Writer report that references our custom Dexterity code.

These are the six magical Report Writer trigger functions added to Dynamics:

rw_ReportStart()
rw_ReportEnd()
rw_TableHeaderString()
rw_TableHeaderCurrency()
rw_TableLineString()
rw_TableLineCurrency()

The knowledge base article has this cryptic statement: “The parameters of these functions have predefined uses.  However, the developer may also use these parameters for other uses.”

What this really means is that these functions are just shell functions in the Dynamics dictionary, they have no logic attached to them whatsoever, they are there so you can have your Dexterity trigger functions run when they are called.

We are going to trigger off the rw_TableHeaderString function:
function returns string sData; { returns a field that can be placed on the report }
in integer dict_id;             { integer, Dictionary ID }
in string report_name;  { ANY string value }
in string sNumber;          { ANY string value }
in integer sType;              { ANY integer }
in integer iControl;          { ANY integer }

The names of these functions are misleading, all of these six functions can be used ANYWHERE in the report.  To demonstrate this, we will use the rw_TableHeaderString function in the body or line portion of a report in this blog.

PRINTING OUR PM-POP INQUIRY WINDOW DATA

In a previous blog we showed how you can use Dexterity to add the PO Number and POP Invoice Numbers to the PM Inquiry windows, in this blog we will show how to add these fields to the inquiry report using the Report Writer trigger functions.

After we print the PM Transaction Inquiry report to screen and click on the MODIFY button we open the Report Writer to the REPORT DEFINITION:

We look through the table listing in Dexterity and find that this temporary table combines the data from the work, open, and history tables, with the expected multiple keys:

The fields from this temporary table are in the BODY of the LAYOUT of the report:

OBJECTIVE: We want to add the POP Invoice Number and PO Number to this report.  To do this, we will follow the logic of our function in our previous blog: Trigger_PM_Trx_Inquiry_Scroll_FILL.

{ procedure Trigger_rw_POPInvoiceInquiry }
function returns string strOUT;

in integer intDictID;
in string strVendorID;
in string strDocumentNumber;
in integer intDocumentType;
in integer intControl;
local string strPOPInvoiceNumber,strPONumber;

pragma(disable warning LiteralStringUsed);
if intDictID <> Runtime_GetCurrentProductID() then
abort script;
end if;
if intDocumentType > 1 then
abort script; { or maybe not, according to your business rules }
end if;
if intControl = 1 or intControl = 2 then { POP Invoice Inquiry }
strPOPInvoiceNumber = GetFirstPOPInvoiceFromHistory(strVendorID,strDocumentNumber);
if intControl = 1 then
strOUT = strPOPInvoiceNumber;
else
strPONumber = GetFirstPOFromPOPInvoice(strPOPInvoiceNumber);
strOUT = strPONumber;
end if;
else
{ for other report – field combinations, use other intControl numbers }
end if;
pragma(enable warning LiteralStringUsed);

Now we add the trigger script in our Startup script that we started in our previous blog.

{ Startup }
{ Dexterity looks for this script name when you startup Dynamics. }
{ This script typically only holds references to trigger scripts }

local integer intResult;

pragma(disable warning LiteralStringUsed);
intResult = Trigger_RegisterFunction(
function rw_TableHeaderString,TRIGGER_AFTER_ORIGINAL,
function Trigger_rw_POPInvoiceInquiry);
if intResult <> SY_NOERR then
warning “Trigger_rw_POPInvoiceInquiry is not registered.”;
end if;
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);

One last touch, we will add a dummy script so when you COMPILE ALL scripts, you will always get a message that there are now errors, and at least one warning message.

{ StatusBarWillShowNoErrors }
{ so status bar will always show no errors }

Now we can SYNCHRONIZE the dictionary, and COMPILE ALL scripts.  You will need to also create a chunk file for the application so we can reference these scripts when we print our Report Writer reports.

BUILDING REPORT WRITER REPORT REFERENCING UNCHUNKED DEXTERITY APPLICATION

We need to add these magical Report Writer fields to the report.  We will create a POPRctNumber CALCULATED FIELD, which will be USER-DEFINED, and it will return a RESULT TYPE of string.  We select the function that our Dexterity code will trigger after, rw_TableHeaderString:

I could have named this field POP Invoice Number, but IMHO it is best practice to assign a different name so the next programmer who has to support this code is not unduly confused.

We start adding the field parameters the rw_TableHeaderString function expects:

We will add these field parameters defined in our trigger script above, Trigger_rw_POPInvoiceInquiry:

rw_TableHeaderString function:
function returns string sData;  { returns a field that can be placed on the report }
in integer dict_id;             { CONSTANT: enter 2525 }
in string report_name;  { FIELD: PM_Inquiry_TEMP.Vendor ID }
in string sNumber;          { FIELD: PM_Inquiry_TEMP.Document Number }
in integer sType;              { FIELD: PM_Inquiry_TEMP.Document Type }
in integer iControl;          { CONSTANT: Enter 1 for POP Receipt Number, 2 for PO Number }

We move the existing fields to make room for the new fields, and tinker with the headers, and this is our finished report:

We have to set security so the modified report is assigned to the users who will print this report.  Previously we had assigned the modified window to the user, now we assign the modified report to the user for the MICROSOFT DYNAMICS GP Product dictionary:

Once we have properly assigned the security, we can now print the modified report for ADVANCED OFFICE SYSTEMS, who is the first Fabrikam vendors with lots of purchase orders:

OTHER CONSIDERATIONS ON FIELD AND RESULT TYPES

In his blog David Musgrave reminds you that you need to return the proper RESULT TYPE.  For some tables the line sequence numbers are long, some are currency, this is his screen shot that converts the long sequence number to a currency type that the report writer function expects:

Another issue is none of these magical report writer functions accept dates as parameters.  When modifying reports you need to be aware of the USER-DEFINED functions, included is the important RW_DateToString that will enable you to pass a date field as a string data type parameter to the report writer functions.

Most of the useful Report Writer functions are documented in the RM_Func.doc file in the software development kit.  This blog shows how you can install the SDK:

http://www.azurecurve.co.uk/2013/05/how-to-install-microsoft-dynamics-gp-2013-software-development-kit/

David Musgrave has also written a blog on how to use these special report writer functions:

https://blogs.msdn.microsoft.com/developingfordynamicsgp/2008/09/01/using-the-built-in-report-writer-functions/

 

 

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.

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/