Illegal address for field ‘FICA/Med Add Tax Rate in script ‘941_Generate’

This error has been coming up lately in version GP2010. The solution has been to rename the Forms.dic. So, you need to export any modified forms to a package file. Rename your Forms dictionary and then import the package file, thereby creating a new Forms dictionary.
While I have only seen the forms dictionary needing to be renamed, start with renaming both the forms and reports dictionaries and then add them back one at a time. Or, just export all of your modifications, rename all of the forms and reports dictionaries and then import everything back in.

Update!  Robert said that he ran into similar issue when applying the GP2010 year end tax update. His error message was:

Unhandled script exception: Illegal address for field 'Medicare Wages and Tips Additional in script '941_Generate'. Script terminated.

He said this solution solved that problem too!
Microsoft has acknowledged this is an issue, but has not determined the exact reason.


Until next post!
Leslie

A Get/Change Operation on Table gpAppEnabled cannot find the table

 

This error came up after the ‘Technical Service Tools’, also known as the ‘Professional Services Tools Library’ was installed at a site upgrading from release 10 to release 2010 of Dynamics GP.

Once each of the 16 companies were opened by the ‘sa’ user, the problem went away. I suppose opening the company using the ‘sa’ user created the table that the system was looking for.

Until next post!

Leslie

David is on Page 4, vote for the best!

Hi guys,

Let’s put on a push this year to put the Top Most Influential Person in Microsoft Dynamics 2013 on the list.

You all know that I think this list is genius as a marketing tool (I wish I had thought of it), but if it’s going to be good marketing then it needs to be credible. Let’s put David Musgrave on the list and give it some credibility!

Vote for David here:  on Page 4.

Until next post!

Leslie

The MOST Awesome - Doug Pitcher

Doug,

Snarky doesn’t even begin to describe it! I love it! This ‘Other List’ thing has taken on a life of it’s own. I’m honored that a group of colleagues have decided that they can do better than anyone else on page 9! Boy, and they really have. I’ve gone from obscurity to the ‘man’ in first.

However, their efforts aside, I feel much more Awesome by having a high position on “Doug Pitcher’s OFFICIAL 100 most famous, awesome and totally influential Dynamics people for 2013” than anywhere on the ‘Other List’ (they snubbed me last year if my memory serves).

But Doug, there is no voting site for “Doug Pitcher’s OFFICIAL 100 most famous, awesome and totally influential Dynamics people for 2013”. I have mentioned to my team that cash would probably be as good as a vote, but I don’t think my ‘people’ have come to that yet. I’m thinking their loyalty stops at the whole wallet thing. OK, I know their loyalty stops at the whole wallet thing.

I would like to officially thank you for one hundred votes! How cool is that?, to be voted for by the owner of the REAL list of 100 most famous, awesome and totally influential Dynamics people for 2013. It just doesn’t get any better than this .

Page 9 is here: http://www.dynamicsworld.co.uk/the-top-100-most-influential-people-in-microsoft-dynamics-list-for-2013-page-9/

The Most Awesome list is here: Doug’s Most Awesome List.

The world watches and waits!

Until next post,

Leslie Vail

Updating the ‘Display in Lookups’ selections on the Account Maintenance window

 

You may have noticed that Integration Manager does not let you choose the individual selections for the ‘Display in Lookups’ field on the Account Maintenance window. It’s either all on, or all off. I used to use Table import to set the flags, but there is a much better way.

My thanks goes out to Tim Hickey of ConexusSG for showing me how to update this field using SQL. So much easier than table import.

Since the field GL00100.DSPLKUPS is a binary field, Tim converted the values for the multi-select list box to binary. He then used the corresponding value in a SQL UPDATE statement. Now, all of his clients (and you) can easily take advantage of this power feature in General Ledger.

For example, if you wanted to set the Payroll accounts to display only in the payroll module lookups (option 9), you would write something like this:

UPDATE GL00100
SET DSPLKUPS = 0X8
WHERE ACCATNUM in (39,36)

Take another look at your chart of accounts, and use this feature to help speed up account selection. The table below has every option you can choose for the ‘Display in Lookups’ field, along with the correct SQL value.

Enjoy!

Option	Image	SQL	Binary
1		0X0	0000
2		0X1	1000
3		0X2	0100
4		0X3	1100
5		0X4	0010
6		0X5	1010
7		0X6	0110
8		0X7	1110
9		0X8	0001
10		0X9	1001
11		0XA	0101
12		0XB	1101
13		0XC	0011
14		0XD	1011
15		0XE	0111
16		0XF	1111

General Ledger User-Defined Functions for Report Writer

 

We all know there is some really great things you can do with the Report Writer User-defined functions. Unfortunately they are not well documented.

What will follow is a series of posts where I will list all of the KB articles I could find include information about one of the RW functions in Report Writer.

The posts will be separated by module. First up, General Ledger.

Enjoy!

GENERAL LEDGER

1. How to use Report Writer in Microsoft Dynamics GP to add the purchase order number from the Payables Transaction Entry window to the Detailed Trial Balance report in the general ledger
   (863689 - modified: 2012-07-13)
   This article describes how to add the purchase order number from the Payables Transaction Entry window to the Detailed Trial Balance report in the general ledger in Microsoft Dynamics GP and in Microsoft Business Solutions - Great Plains.
2. How to print a General Posting Edit List that includes distribution accounts for variable and fixed allocation accounts in Microsoft Dynamics GP
   (931452 - modified: 2007-08-21)
   Explains how to print the General Posting Edit List with distribution accounts for variable and fixed allocation accounts in Microsoft Dynamics GP 9.0 and in Microsoft Business Solutions - Great Plains 8.0.

...

Payables Management User-Defined Functions for Report Writer

 

We all know there is some really great things you can do with the Report Writer User-defined functions. Unfortunately they are not well documented.

What will follow is a series of posts where I will list all of the KB articles I could find include information about one of the RW functions in Report Writer.

The posts will be separated by module. This post is dedicated to Payables Management.

Enjoy!

PAYABLES MANAGEMENT

1. How to use Report Writer to replace the Vendor Check Name field with the vendor note for a Payables Management check in Microsoft Dynamics GP
   (857745 - modified: 2012-07-13)
   Explains how to use Report Writer to replace the Vendor Check Name field with the vendor note in Microsoft Dynamics GP and in Microsoft Business Solutions - Great Plains.
2. How to round a calculated field in Report Writer in Microsoft Dynamics GP
   (872275 - modified: 2012-08-27)
   Describes how to create a calculated field to round to the nearest two decimal places or the nearest whole dollar by using Report Writer in Microsoft Dynamics GP.
3. How to add buckets to the Payables Management "Aged Trial Balance" report for the summary print option in Report Writer in Microsoft Dynamics GP
   (948664 - modified: 2012-07-14)
   Describes how to add buckets to the Payables Management Aged Trial Balance report for the summary print option in Report Writer in Microsoft Dynamics GP.
4. How to modify the aged trial balance reports in Report Writer so that the printed reports show the number of days outstanding in Payables Management in Microsoft Dynamics GP 9.0 and in Microsoft Great Plains 8.0
   (910699 - modified: 2012-07-14)
   Describes how to add the total outstanding days to the Payables Management aged trial balance reports in Microsoft Dynamics GP and in Microsoft Great Plains. You need Report Writer to follow the steps. You may also need the system password.
5. How to add buckets to the Payables Management "Aged Trial Balance" report for the detail print option in Report Writer in Microsoft Dynamics GP
   (948599 - modified: 2012-07-14)
   Describes how to add buckets to the Payables Management Aged Trial Balance report for the detail print option in Report Writer in Microsoft Dynamics GP.
6. How to print the Check Date fields in the Canadian date format of MMDDYYYY in Payables Management reports in Microsoft Dynamics GP
   (970114 - modified: 2012-07-13)
   Describes how to print the Check Date fields in the Canadian date format of MMDDYYYY in Payables Management reports in Microsoft Dynamics GP
7. How to add the "Distribution Reference" field to the PM Transaction Posting Journal report in Microsoft Dynamics GP
   (865470 - modified: 2011-07-20)
   Describes how to add the "Distribution Reference" field to the PM Transaction Posting Journal report in Microsoft Dynamics GP.
8. How to suppress the "Address 1" field, the "Address 2" field and the "Address 3" field on any check in Payables Management in Microsoft Dynamics GP
   (874218 - modified: 2011-07-25)
   Describes how to suppress the "Address Line 3" field on any check in Payables Management in Microsoft Dynamics GP.
9. How to add a new bucket to the "Aged Trial Balance-Detail with Options" report in Payables Management in Microsoft Dynamics GP
   (864847 - modified: 2011-07-20)
   Describes how to add a new bucket to the "Aged Trial Balance-Detail with Options" report in Payables Management in Microsoft Dynamics GP and in Microsoft Business Solutions - Great Plains.

Receivables Management User-Defined Functions for Report Writer

 

We all know there is some really great things you can do with the Report Writer User-defined functions. Unfortunately they are not well documented.

What will follow is a series of posts where I will list all of the KB articles I could find include information about one of the RW functions in Report Writer.

The posts will be separated by module. This post is dedicated to Receivables Management.

Enjoy!

RECEIVABLES MANAGEMENT

1. How to add the customer purchase order number to the Receivables Management Accounts Due Report in Microsoft Dynamics GP
   (856461 - modified: 2012-07-13)
   Describes how to add the customer purchase order number to the Receivables Management Accounts Due Report in Report Writer in Microsoft Dynamics GP and in Microsoft Business Solutions – Great Plains 8.0.
2. How to suppress the Address 2 field and the Address 3 field in a report in Receivables Management for Microsoft Dynamics GP
   (853430 - modified: 2011-07-11)
   Describes how to suppress empty address line fields so that the City, State, and Zip fields print under the last line of the address.
3. How to use Report Writer to print all seven aging periods on the RM Aged Trial Balance report in Microsoft Dynamics GP 9.0
   (933643 - modified: 2011-08-01)
   Describes how to print all seven aging periods on the RM Aged Trial Balance report in Microsoft Dynamics GP 9.0 and Microsoft Business Solutions - Great Plains 8.0.
4. How to use the aging date to calculate the "Days Past Due" field on the RM Detail Historical Aged Trial Balance report in Microsoft Dynamics GP
   (856016 - modified: 2007-11-13)
   Describes how to use the aging date to calculate the "Days Past Due" field on the RM Detail Historical Aged Trial Balance report in Microsoft Dynamics GP.

SOP User-Defined Functions for Report Writer

 

We all know there is some really great things you can do with the Report Writer User-defined functions. Unfortunately they are not well documented.

What will follow is a series of posts where I will list all of the KB articles I could find include information about one of the RW functions in Report Writer.

The posts will be separated by module. This post is dedicated to Sales Order Processing.

Enjoy!

SALES ORDER PROCESSING

1. How to modify the SOP Blank Invoice Form to display the total amount of the invoice in words in Microsoft Dynamics GP and in Microsoft Great Plains
   (917473 - modified: 2012-07-14)
   Describes how to modify the SOP Blank Invoice Form in Microsoft Dynamics GP and in Microsoft Great Plains to display the total amount of the invoice in words.
2. How to change the date format on a Sales Order Processing report in Microsoft Dynamics GP
   (852554 - modified: 2012-06-27)
   Describes how to change the date Format on the Sales Order Processing reports in Microsoft Dynamics GP and in Microsoft Business Solutions - Great Plains 8.0.
3. How to add the "Customer Item Number" field to a quote, order, or invoice form in Sales Order Processing in Microsoft Dynamics GP
   (918943 - modified: 2012-07-14)
   Describes steps that let you print both the "Inventory Item Number" field and the "Customer Item Number" field. You must have access to Report Writer, and you may need the system password to grant security.
4. How to print the difference between the list price and the unit price for items on Sales Order Processing order forms in Microsoft Dynamics GP
   (871675 - modified: 2012-07-14)
   Describes how to print the difference between the list price and the unit price for items on Sales Order Processing order forms in Microsoft Dynamics GP.
5. How to create a Line Number field for the lines items on the SOP Blank Quote Form, on the SOP Blank Order Form, on the SOP Blank Invoice Form, on the SOP Blank Return Form, and on the SOP Blank Back Order Form reports in Microsoft Dynamics GP
   (857285 - modified: 2012-07-13)
   Describes how to create a Line Number field for the lines items on various reports in Microsoft Dynamics GP.
6. How to add the customer e-mail address to the SOP Blank Order Form report or to the SOP Blank Invoice Form report in Microsoft Dynamics GP
   (874101 - modified: 2012-07-14)
   Describes how to add the customer e-mail address to the SOP Blank Order Form report or to the SOP Blank Invoice Form report in Microsoft Dynamics GP and in Microsoft Business Solutions - Great Plains 8.0.
7. How to add Internet information for an item to the "SOP Blank Invoice" form in Microsoft Dynamics GP
   (924538 - modified: 2012-07-14)
   Describes how to use the Report Writer module to modify the SOP Blank Invoice Form.
8. How to use Report Writer to add the functional currency list price to the SOP Blank Invoice Form in Microsoft Dynamics GP
   (852962 - modified: 2012-06-27)
   Describes how to use the rw_ivGetFuncListPrice function script in Report Writer to add the list price of an item to the SOP Blank Invoice Form or other non-options forms in Sales Order Processing.

POP User-Defined Functions for Report Writer

 

We all know there is some really great things you can do with the Report Writer User-defined functions. Unfortunately they are not well documented.

What will follow is a series of posts where I will list all of the KB articles I could find include information about one of the RW functions in Report Writer.

The posts will be separated by module. This post is dedicated to Purchase Order Processing.

Enjoy!

PURCHASE ORDER PROCESSING

1. How to add the vendor ship-to address from the Purchasing Vendor Detail Entry window to the POP Purchase Order Blank Form in Microsoft Dynamics GP
   (927699 - modified: 2012-07-14)
   Describes how to use Report Writer to add the vendor ship-to address from the Purchasing Vendor Detail Entry window to the POP Purchase Order Blank Form in Microsoft Dynamics GP.
2. How to add the vendor e-mail address to POP Purchase Order Blank Form report in Microsoft Dynamics GP
   (874102 - modified: 2012-07-14)
   Describes how to add the vendor e-mail address to the POP Purchase Order Blank Form report in Microsoft Dynamics GP.
3. How to add purchase order notes to the POP Purchase Order Blank Form in Report Writer in Microsoft Dynamics GP
   (858264 - modified: 2012-07-13)
   Discusses how to use Report Writer to add purchase order notes to the POP Purchase Order Blank Form in Microsoft Dynamics GP.
4. How to add line item comments to graphical purchase order reports without creating additional white space in Microsoft Dynamics GP
   (906709 - modified: 2012-07-14)
   Describes how to add line item comments to graphical purchase order reports without creating additional white space in Microsoft Dynamics GP. You must have access to Report Writer, and you may require the system password to complete these steps.
5. How to modify the POP Purchase Order Blank Form report to print the "purchase order line item quantity ordered" note if you use Project Accounting in Microsoft Dynamics GP
   (944890 - modified: 2012-07-14)
   Describes how to modify the POP Purchase Order Blank Form report to print the "purchase order line item quantity ordered" note if you use Project Accounting in Microsoft Dynamics GP.
6. How to add the Purchases account or the Inventory account to the alternate "POP Purchase Order Blank Form" in Project Accounting In Microsoft Dynamics GP
   (918944 - modified: 2012-07-14)
   Describes how to add these accounts to the alternate POP Purchase Order Blank Form. By default, this report contains the Project Number and the Cost Category. You must be an administrator to complete these steps.

Inventory User-Defined Functions for Report Writer

 

We all know there is some really great things you can do with the Report Writer User-defined functions. Unfortunately they are not well documented.

What will follow is a series of posts where I will list all of the KB articles I could find include information about one of the RW functions in Report Writer.

The posts will be separated by module. This post is dedicated to Inventory

Enjoy!

INVENTORY

1. How to add the "Customer Item Number" field to a quote, order, or invoice form in Sales Order Processing in Microsoft Dynamics GP
   (918943 - modified: 2012-07-14)
   Describes steps that let you print both the "Inventory Item Number" field and the "Customer Item Number" field. You must have access to Report Writer, and you may need the system password to grant security.
2. How to print the difference between the list price and the unit price for items on Sales Order Processing order forms in Microsoft Dynamics GP
   (871675 - modified: 2012-07-14)
   Describes how to print the difference between the list price and the unit price for items on Sales Order Processing order forms in Microsoft Dynamics GP.

Bank Rec Report User-Defined Functions for Report Writer

 

We all know there is some really great things you can do with the Report Writer User-defined functions. Unfortunately they are not well documented.

What will follow is a series of posts where I will list all of the KB articles I could find include information about one of the RW functions in Report Writer.

The posts will be separated by module. This post is dedicated to Bank Reconciliation

Enjoy!

BANK RECONCILIATION

1. Error message when you try to view the "dist total" additional footer in Report Writer in Microsoft Dynamics GP: "Cannot break on the current field. Select new break field"
(905638 - modified: 2012-07-14)
Describes a problem that occurs because a break field is not selected. Explains how to resolve the problem.

Project Acct User-Defined Functions for Report Writer

 

We all know there is some really great things you can do with the Report Writer User-defined functions. Unfortunately they are not well documented.

What will follow is a series of posts where I will list all of the KB articles I could find include information about one of the RW functions in Report Writer.

The posts will be separated by module. This post is dedicated to Project Accounting

Enjoy!

PROJECT ACCOUNTING

1. How to modify an invoice in Project Accounting so that numbers are converted into words and then displayed at the bottom of the "PA Invoice Format Project Detail Page 1" report
   (920825 - modified: 2012-07-14)
   Describes how to modify an invoice in Microsoft Dynamics GP so that numbers are converted into words and then displayed at the bottom of the "PA Invoice Format Project Detail Page 1" report.
2. How to modify the POP Purchase Order Blank Form report to print the "purchase order line item quantity ordered" note if you use Project Accounting in Microsoft Dynamics GP
   (944890 - modified: 2012-07-14)
   Describes how to modify the POP Purchase Order Blank Form report to print the "purchase order line item quantity ordered" note if you use Project Accounting in Microsoft Dynamics GP.
3. How to add the Purchases account or the Inventory account to the alternate "POP Purchase Order Blank Form" in Project Accounting In Microsoft Dynamics GP
   (918944 - modified: 2012-07-14)
   Describes how to add these accounts to the alternate POP Purchase Order Blank Form. By default, this report contains the Project Number and the Cost Category. You must be an administrator to complete these steps.

Payroll & HR User-Defined Functions for Report Writer

 

We all know there is some really great things you can do with the Report Writer User-defined functions. Unfortunately they are not well documented.

What will follow is a series of posts where I will list all of the KB articles I could find include information about one of the RW functions in Report Writer.

The posts will be separated by module. This post is dedicated to Payroll & HR

Enjoy!

PAYROLL AND HUMAN RESOURCES

1. How to use Report Writer in Microsoft Dynamics GP 9.0 to create a calculated field to mask the social security number on the Employee Checks report and on the "Direct Deposit Statement of Earnings" report
(935619 - modified: 2011-08-02)
Describes how to block out the social security number on an employee checks report or the "Direct Deposit Statement of Earnings" report in Microsoft Dynamics GP. You must use Report Writer.

Dexterity User-Defined Functions for Report Writer

 

You don’t think I’d forget about Dexterity, did you? Here are the articles I could find to help Dexterity programmers develop user-defined functions.

Enjoy!

DEXTERITY

1. How to improve the performance of user-defined Report Writer functions in Microsoft Dynamics GP 9.0 or in Microsoft Great Plains
   (920830 - modified: 2012-07-14)
   Describes how to improve the performance of a user-defined Report Writer function by setting the table to remain open when a report is being generated.
2. How to use Customization Maintenance packages to enable Great Plains Report Writer functions from a 3rd party report dictionary
   (862665 - modified: 2012-07-13)
   This article discusses how to use Customization Maintenance packages to enable Great Plains Report Writer functions from a 3rd party report dictionary.
3. Error message when you print a Sales Order Processing invoice from a customization that you created in Dexterity in Microsoft Dynamics GP: "User Defined Function failed in execution"
   (943947 - modified: 2012-07-14)
   Describes an issue that occurs because of the parameters that are passed to the Report Writer function. Provides a resolution.
4. Optional parameters should not be used in Report Writer functions
   (856206 - modified: 2012-07-13)
   Calculated field need to supply all arguments when calling RW functions
5. RW:Out of Memory Error Message
   (866943 - modified: 2012-07-14)
   Using incorrect Runtime
6. Useful functions for developers to use instead of creating alternate reports in Microsoft Dynamics GP
   (888884 - modified: 2011-07-25)

GP 2013 error voiding checks

 

The Problem:

When you try to void a check in GP2013, you get the following error:

Batch batchname failed to complete posting. Use the Batch Recovery window to complete the posting process. When you click on the More Info button you see the following:

A save operation on table ‘ME_Void_Checks’ cannot find the table.

[Microsoft'][SQL Server Native Client 10.0][SQL Server]Invalid object name  ‘company database name.dbo.ME123506’.

Of course, the Batch Recovery window is no help.

The error occurs whenever you void a check, no matter which window you choose to void it from (PM or Bank Rec). This error is caused by the Safe Pay product. There is an old KB article explains the problem and shows you how to fix it, but it doesn’t fix it (it’s an old KB article).

ME_Void_Checks Failed Accessing SQL Data ... - Microsoft Support

After reading the KB article, I found out that the culprit is the Safe Pay product. Still, I went through the steps suggested by the KB article.

First, I tried Resolution 2 to fix it (because it was easier). I ran the Grant.sql script against the system and company databases, but it did not fix the problem.

Next I tried Resolution 1. “Run the installation routines again from the shortcut bar.” I did not have any such thing to run from my shortcut bar. I hunted around for a window that I could add to my shortcut bar, but none of them looked promising. I abandoned that effort.

The error message actually does tell us what’s wrong, the database is missing the ME123506 table.

The Fix:

Since the system couldn’t find the table, go to SQL Maintenance, select the Safe Pay product, and created the table. Viola! Problem solved.

Until next post!

Leslie

Problem with dual install of 2010 and 2013

Both GP 2010 and GP2013 are installed on my local computer. That machine is also the SQL server.

The Problem:

I created a new user  (USER1) in GP2013 and gave him access to the Fabrikam company, which was database TWO01. The system database was named DYN131.

• USER1 was granted access to Fabrikam.
• USER1 was a power user

Whenever I tried to log in as USER1, I was greeted with this:

I was definitely typing in the correct user name and password. Finally, I went directly to the SQL server and blanked out the password. The blank password didn'’t work either.

The Solution:

I checked the SQL Server to make sure USER1 was listed as a Login to the server and was mapped to the both the TWO1 database and the DYN131database. No problems there. I then checked the DYN131 and TWO1 databases to confirm USER1 was a member of the DYNGRP database role for both databases.No problem there either.

Since I needed to change something, I selected USER1 and mapped him to the DYNGRP of the TWO database. The TWO database is the sample company for the GP2010 installation. That’s all I did; USER1 was not listed as a user for the TWO database.

Solved. Now, USER1 could log in to the sample company created in the GP2013 installation.

Go figure.

Until next post!

I want to be on Doug Pitcher’s list! Vote for me!

I know the fervor of the DynamicsWorld “top” 100 list is everywhere, but there is a far more prestigious list to aspire to. I am of course talking about the 2013 version of Doug Pitcher’s OFFICIAL 100 most famous, awesome and totally influential Dynamics people for 2012.

The list is so funny and honest, I don’t know how he comes up with this stuff, remarkable!. You will laugh out loud reading it, it’s that good.

I’ve been proud to be on it, and I hope I can cut the muster again this year.  And, dah, David Musgrave is on the list! That tells me for sure that Doug’s list is the best!

I don’t think Doug has an official ‘voting’ poll, but should you have the chance to talk, e-mail, text, twitter, Facebook, Linked in . . .  him, put in a good word for me

Keep up the good work Doug!

Until next post!

Leslie

Vote for David Musgrave–2013

I am in full agreement with Jivtesh. Though I think this whole 'list' thing is nothing more than a brilliant marketing idea of Dynamics World, still, to not have David Musgrave on the list should only CONFIRM IT IS A FRAUD.

C'mon people, we know who the superstars are. We do not need some web site determining  the best of the best. I think we all know that it's David Musgrave, and it has been for years.

But, if it's a vote that they need, vote for David and vote often. Even if it is a marketing scheme, I'll buy in just to get the right man at the top of the list.

Until next post!

Leslie

Review of Developing Microsoft GP Business Applications

Wow!

I am honored that my book, Developing Microsoft GP Business Applications is being reviewed in detail by Ian Grieve. I just learned of this today, he has 5 parts already reviewed. The reviews start here: http://www.azurecurve.co.uk/2013/02/review-of-developing-microsoft-dynamics-gp-business-applications-part-1/

Ian is completely independent and I will definitely be reading his reviews and be able to make the book better because of them. I've only read the 5th article, and am so very impressed by his thoroughness and honesty.

He talks about what's good, and what could be improved, I am elated to learn that someone so exceptional is taking this much time reviewing my book!

Thank you Ian!

Until next post,

Leslie

Dynamics GP 2013 Service Pack error

The Problem
Here’s the scenario. You have just installed a service pack. When you launch GP 2013, you are told you need to run Utilities. You launch Utilities, but oddly, it doesn’t find an existing instance of GP 2013. So, you just hit next and it moves along. Suddenly, the error pop’s up telling you:

“A system database name was not supplied. Run a repair of the Microsoft Dynamics GP installation”

The fact that it was trying to create a new system database makes a little more sense now. Next, you dutifully launch Control Panel and begin to run the Repair routine. Right at the beginning of the Repair process you get another error:

           “Object reference not set to an instance of an object”

Fabulous, you’ve seen this error before and it’s never pretty. We used to see it using Integration Manager and it was sometimes hard to fix. You’ll be delighted to know that this is a known error with an easy fix.

This situation comes up if you install the same service pack twice. The first time you install it, everything works perfectly, when you re-install, it you have the above problems.

The Solution
The fix for this is explained by Sara Purdy on the Microsoft Dynamics GP Support and Services Blog. Always go there first if you have any problems in Dynamics GP, there you will find some really exceptional advice.

The actual posting is called (deep breath here) Microsoft Dynamics GP 2013 .MSP Patch Files Removing Information from Dex.ini and Registry.
All of the meaty solution information below was lifted directly from Sara’s  post. I’ve added some editorial comments for those of us (like me) who may need just a little more information to pull this off.

The culprit, as identified by Sara Purdy.

We have identified an issue with Microsoft Dynamics GP 2013 where the .msp files are removing registry entries and part of the Pathname= line in the Dex.ini when you RE-APPLY a patch. The first install of the patch works fine. The issue comes into play if you reapply the patch again on either a single instance or multi-instance. The Country and System Database Name get blanked out in the registry. The Pathname= line in the Dex.ini gets modified.

Here's the registry path:

HKLM\SOFTWARE\Wow6432Node\Microsoft\Business Solutions\Great Plains\v 12.0\1033\DEFAULT\SETUP

Bad: The broken Registry looks like this:

Good: The Fixed Registry looks like this:


Bad: The Dex.ini file looks like this:

Good: The fixed Dex.ini file looks like this:

Of course, you will use the name of your system database, whatever it is. Yours will look like this: Pathname=your_system_database/dbo/

After you fix the registry and your Dex.ini file, you will need to re-apply the service pack. This, you have to do from the command line (what fun!).

The Second part of the Solution:
These are the instructions from Sarah Purdy’s post:

The current workaround when re-applying the .msp to a specific instance of GP 2013 is to do the following:

1. Click Start, click Run, type regedit, and then click OK.

2. Locate and then click the following registry subkey:
Microsoft Dynamics GP 10.0 and later versions

32-bit environment
    HKEY_LOCAL_MACHINE\Software\Microsoft\Business Solutions\Great Plains\V12.0\1033\your_instance_name\SETUP

64-bit environment
   HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\
Business Solutions\Great Plains\V12.0\1033\your_instance_name\SETUP

3. Expand the folder for the instance of Microsoft Dynamics GP you want to patch.

Note The DEFAULT folder is for the default installation. Replace V12.0 with your version of GP. The Inst01 folder is for the second instance of Microsoft Dynamics GP. For example, if you want to patch the second installation, expand the Inst01 folder.

4. Click the SETUP folder, double-click the Product Code, and then copy the Value data field’s value.

5. Exit Registry Editor.

6. Open up a Command Prompt by right-clicking on it and selecting Run As Administrator.

7. To install the .MSP file, type the following, and then hit Enter.

---

C:\updatename.msp /n {E979C594-95F9-4E3A-985D-A1DFDF403227}

---

(You can ‘Paste’ in the command line screen by right clicking next to where you want the data, and then choose ‘Paste’)

Note: Replace "C:\updatename.msp" with the path to the .MSP file and the name of the .MSP patch file. Replace "{E979C594-95F9-4E3A-985D-A1DFDF403227}" with the ProductCode value that you copied in step 4.

The registry will look like this for GP2013:

The current workaround when re-applying the .msp to all instances of GP 2013 on a machine is to do the following:

1. Open up a Command Prompt by right-clicking on it and selecting Run As Administrator, type in the following, and then hit Enter.

---

    msiexec.exe /p C:\updatename.msp

---

Note: Replace C:\updatename.msp with the path to your .MSP file and the name of the .MSP patch file.

Example:
msiexec.exe /p C:\ MicrosoftDynamicsgp12-kb2799678-ENU.msp


2. Click OK.

Until next post!

Leslie

DynUtils.exe is Missing

This is a first for me.

I had a client call wanting to set up a new company that had the same structure as an existing company. They had not set up a new database before so we were going to walk through it over the phone.

The first step, as you know, is to launch Dynamics Utilities. Surprise! Dynamics Utilities was gone and had left no forwarding address. Specifically, the DynUtils.exe file was missing. These folks were serious about not wanting anybody to run this program.

Out of curiosity, I deleted the DynUtils.exe from my workstation, and sure enough, Dynamics GP launched without a problem.

The moral of this story is to show you another clever way to put a little more security at the workstation. I would go farther though and take away the DynUtils.set file too! I wouldn’t bother with the ‘DU’ dictionaries, that’s only because I’m a bit disinclined to go to through the effort.

Special thanks to Mark Polino for the ‘left no forwarding address phrase’.

Until next time!

Leslie

T-SQL 2012: CHOOSE and IIF

 

We’ve been needing these two features for a long time. This post includes a couple of easy examples showing how these functions work.

CHOOSE

I’ll start with CHOOSE. By using CHOOSE you can do away with many tedious CASE statements. CASE statement opportunities are everywhere in Dynamics GP. For instance, how many times have you needed to display the document type in your report and want a word, not a number. Prior to SQL 2012, we would have written a CASE statement that would look a lot like this:

Case SOPTYPE
    when 1 then 'Quote'
    when 2 then 'Order'
    when 3 then 'Invoice'
    when 4 then 'Return'
    when 5 then 'Back Order'
    when 6 then 'Fulfillment Order'
    ELSE 'Undefined Document Type'
End as ' Doc Type'

CHOOSE will pick the value in your list according to the value of your data. It returns the expression at a specific index. So, instead of writing a case statement, you can simply write this:

CHOOSE (SOPTYPE, 'Quote', 'Order', 'Invoice', 'Return',
'Back Order', 'Fulfillment Order') AS DocType

If the value of your index is greater than the number choices, it will return NULL. It will also return NULL if the value is less than 1.

IIF

Next is IIF. Most of you probably already know how to use this and have wondered why you couldn’t do it in T-SQL. Flavors of this are in Excel formulas, Access calculations and even VBA, to name a few. It works with a Boolean expression and returns a value based on whether the expression is true or false. You set this up with three parameters. The first parameter is the expression you’re evaluating, the second parameter is the value if the expression is true, and the third parameter is the value if the expression is false. Yep, just like Excel.

So here’s what it looks like:

IIF(boolean_expression), value if true, value if false)

So much easier than CASE.

Until next post!

Leslie

T-SQL 2012: LAG & LEAD

I’ve just started playing with SQL 2012 and wanted to show you the new LAG and LEAD features.

I’ll use the report below as an example. Suppose you needed to generate a report that showed the last three years of sales. The result set would need to look something like this:

LEAD looks ahead the specified number of rows from the current row, and LAG (you guessed it!) looks behind the specified number of rows from the current row. By using the OVER clause you can specify your grouping (partitioning) and sort order.

Using SQL 2012 you can easily accomplish this using the following SELECT statement:

SELECT
   year(docdate)  AS ‘Order Year’
  ,SUM(SubTotal) AS ‘Current Yr Sales’
  ,LAG(SUM(SubTotal), 1, 0) OVER (ORDER BY YEAR(docdate)) AS ‘Last Year Sales’
  ,LAG(SUM(SubTotal), 2, 0) OVER (ORDER BY YEAR(docdate)) AS ‘Year Before Last’
  ,LAG(SUM(SubTotal), 3, 0) OVER (ORDER BY YEAR(docdate)) AS ‘Three Years Ago’
  ,LEAD(SUM(SubTotal), 1, 0) OVER (ORDER BY YEAR(docdate)) AS ‘Next Year’
FROM SOP30200
GROUP BY YEAR(DOCDATE)
ORDER BY YEAR(DOCDATE);

Have fun with it!

Until next post!

Leslie

Dexterity Training in Dallas (June 17 – 21, 2013)

 

Looking for Dexterity training? Come to Dallas!

We’re running this class if only a single person signs up, no cancellations here in Dallas .

I will be teaching the Dexterity Basics class in Dallas, Texas from June 17^th to June 21^st, providing the basic foundations any Dexterity programmer must have.

This is an opportunity for all of you VSTools programmers to learn how to build your user interface in Dexterity instead of WinForms so that your application will run with the new GP 2013 Web Client. Your existing code will hook right up to the Dexterity events, you may even like the Dexterity forms designer better than VS Tools!

Dexterity is easy to learn and easy to maintain.

For you VBA enhancers, you will learn how to write Dexterity code to deliver your solution instead of VBA. While VBA works marvelously with the rich client, it will not work for the new GP 2013 Web Client. Come to Dallas and learn how easy Dexterity really is.

Even you Technical Consultants, taking the basic Dex class with provide you with the tools you need to understand what all of those error messages really mean. You can become better consultants by learning just how this engine works! (and it’s easy).

For more information, send an email to training@conexussg.com or call 469-828-3274.

Hope to see you in Dallas!

The basics class covers the following topics:

Dexterity I – Product Fundamentals

Synopsis:
Dexterity Product Fundamentals will teach you about the development environment used to build the Microsoft Dynamics GP family of products. This course will provide you with the foundation you need to build integrated, database-independent applications. In addition, you will learn how to provide powerful application functionality for Microsoft Dynamics GP.
By actively participating in these classes, you should learn:

Basic programming techniques used by Microsoft Dynamics GP to develop their core modules.

• How to modify existing Dynamics GP forms and reports in order to make customizations for customers.
• How to develop add-on applications that will interact with the Dynamics GP products to meet specific requests by customers.
• How to package and ship your dictionary allowing Microsoft Dynamics GP to make updates to Dynamics GP without affecting the developer’s dictionary.
• How to register your product with Microsoft Dynamics GP using a unique Product ID to ensure no duplication of add-on applications with the same name.
• How to develop add-on applications that will “look and feel” like Dynamics GP and provide seamless interface between the applications to ensure a professional finished product.
• How to use object triggers, which are scripts in your applications that are invoked by events in Dynamics GP, allowing several developers to deliver enhancements to the same Dynamics GP form.