When things go wrong - 'Object reference not set to an instance of an object'

This must be one of my most hated error messages. Usually, I see it in Integration Manager, but this time it has shown up in Management Reporter.

Jake Friedl recently  posted a list of things to check when you receive this error. Thank you Jake! This is a great list:

1. Check for missing ISO codes.
2. Start GP and log into any company as 'sa'.
3. Go to Microsoft Dynamics GP -> Tools -> Setup -> System -> Currency.
4. You will need to select every single currency from the Currency ID lookup.
5. Once selected, confirm a code is entered in the ISO Code field.
6. Check for missing functional currencies.
7. You will need to log into every single company in GP to check the following.
8. Go to Microsoft Dynamics GP -> Tools -> Setup -> Financial -> Multicurrency.
9. Confirm a currency is selected in the Functional Currency field.
10. Check for a blank Budget ID.
11. Start SQL Management Studio and log in as a SQL sysadmin.
12. Run the following query against each GP company database: select * from gl00200
13. If you find a blank value in the BUDGETID column, back up the GP database and clear the value with: delete gl00200 where BUDGETID= ''
14. After going through the above, you will need to re-create the data mart integration if you are using the data mart provider:
15. Close all instances of MR and back up the MR database.
16. Start the Configuration Console and click on the integration under ERP Integrations.
17. Click the Disable Integration button and wait a moment for that to take effect.
18. Click the Remove link in the top-right.
19. Stop the MR services at the top level of the Configuration Console and then delete the DDM database from SQL.
20. In the Configuration Console, start the MR services and then click File -> Configure and deploy a new DDM integration.
21. Enable the integration and wait for the initial load to complete.

Until next post!

Leslie

Dexterity training in Dallas – Live or Live on-line

It must be in the air! Come to Dallas, or join us On-Line.

I’m doing another Dexterity Fundamentals class in Dallas, TX, December 9th-13th, 2013. This time we are offering it both on-site in Dallas and Live on-line!

At last, you can go to a fun-filled Dexterity training class without leaving the comfort of your own home or office!

For more information, or to sign up, call
ConexusSG at 469-828-3274 or email training@ConexusSG.com

What will you Learn?

The Dexterity Fundamentals class teaches you everything you need to know to get started developing integrating applications.  During this class, you will learn the Dexterity components and Dynamics GP programming standards. You will complete many hands-on projects including the following:

 

·         How to set up the development environment

·         Create a Maintenance and Lookup window adhering to the Dynamics GP user interface guidelines

·         Create an Item Entry and Item lookup window using techniques that can fast-track your development

·         Use integrated Debugging tools to resolve errors

·         Learn about multiuser processing and how optimistic locking works

·         Create an integrating application that interacts with existing Dynamics GP components

·         Create record notes, browse buttons, shrink/expand buttons, zooms and expansion buttons

·         Create menus to navigate to your application

·         Create and launch reports using Report Writer

·         Work with multiple tables, set ranges and create virtual keys

·         Add items ‘On the Fly’ and create ‘Find’ buttons

·         Call existing Dynamics GP functions

·         Modify a Dynamics GP window thereby creating an Alternate window

·         Use object triggers and techniques for cross-dictionary integration

·         Create SQL tables from Dexterity

·         Package your application and create a .cnk file

·         Learn how to update your application to a new release.

·         Other topics and procedures

How should You prepare?

Review the Quick Start manual that is included in the Dexterity documentation. You can access this manual from the Help menu of Dexterity: Help | Online Manuals | Quick Start

Alternatively, after you install Dexterity, look for the QStart.pdf file in the following folder:

. . .\Microsoft Dexterity\Dex 12.0\Manuals

What do you need?

Each student must provide their own computer with the following software installed:

• Dynamics GP 2013
• Dexterity 2013
• Dynamics GP 2013 SDK  - located on the GP 2013 DVD: \Tools\SDK\Dynamics GP\SDK.exe

Until next post!

Leslie

---

SQL datetime formatting function–convert to string

I found this looking for something else on the Internet, but I thought it would be a great item for your toolbox. The function was originally written for 2005, but it has worked on everything I’ve tried.
Enjoy!
Many thanks to Anubhav Goyal , who provided this information to the SQL community on June 11, 2009  - You rock Anubhav! http://anubhavg.wordpress.com/2009/06/11/how-to-format-datetime-date-in-sql-server-2005/
/*-------------------------------------------------------------------------------------------*/
--SQL Server date formatting function --convert datetime to string
/*--------------------------------------------------------------------------------------------*/
--SQL datetime functions
--SQL Server date formats
--T-SQL convert dates
--Formatting dates sql server
CREATE FUNCTION dbo.fnFormatDate (@Datetime DATETIME, @FormatMask VARCHAR(32))
RETURNS VARCHAR(32)
AS
BEGIN
    DECLARE @StringDate VARCHAR(32)
    SET @StringDate = @FormatMask
    IF (CHARINDEX ('YYYY',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'YYYY',
                         DATENAME(YY, @Datetime))
    IF (CHARINDEX ('YY',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'YY',
                         RIGHT(DATENAME(YY, @Datetime),2))
    IF (CHARINDEX ('Month',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'Month',
                         DATENAME(MM, @Datetime))
    IF (CHARINDEX ('MON',@StringDate COLLATE SQL_Latin1_General_CP1_CS_AS)>0)
       SET @StringDate = REPLACE(@StringDate, 'MON',
                         LEFT(UPPER(DATENAME(MM, @Datetime)),3))
    IF (CHARINDEX ('Mon',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'Mon',
                                     LEFT(DATENAME(MM, @Datetime),3))
    IF (CHARINDEX ('MM',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'MM',
                  RIGHT('0'+CONVERT(VARCHAR,DATEPART(MM, @Datetime)),2))
    IF (CHARINDEX ('M',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'M',
                         CONVERT(VARCHAR,DATEPART(MM, @Datetime)))
    IF (CHARINDEX ('DD',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'DD',
                         RIGHT('0'+DATENAME(DD, @Datetime),2))
    IF (CHARINDEX ('D',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'D',
                                     DATENAME(DD, @Datetime))  
RETURN @StringDate
END
GO
--Microsoft SQL Server date format function test
--MSSQL formatting dates
SELECT dbo.fnFormatDate (getdate(), 'MM/DD/YYYY')           --01/03/2012
SELECT dbo.fnFormatDate (getdate(), 'DD/MM/YYYY')           --03/01/2012
SELECT dbo.fnFormatDate (getdate(), 'M/DD/YYYY')            --1/03/2012
SELECT dbo.fnFormatDate (getdate(), 'M/D/YYYY')             --1/3/2012
SELECT dbo.fnFormatDate (getdate(), 'M/D/YY')               --1/3/12
SELECT dbo.fnFormatDate (getdate(), 'MM/DD/YY')             --01/03/12
SELECT dbo.fnFormatDate (getdate(), 'MON DD, YYYY')         --JAN 03, 2012
SELECT dbo.fnFormatDate (getdate(), 'Mon DD, YYYY')         --Jan 03, 2012
SELECT dbo.fnFormatDate (getdate(), 'Month DD, YYYY')       --January 03, 2012
SELECT dbo.fnFormatDate (getdate(), 'YYYY/MM/DD')           --2012/01/03
SELECT dbo.fnFormatDate (getdate(), 'YYYYMMDD')             --20120103
SELECT dbo.fnFormatDate (getdate(), 'YYYY-MM-DD')           --2012-01-03
--CURRENT_TIMESTAMP returns current system date and time in standard internal format
SELECT dbo.fnFormatDate (CURRENT_TIMESTAMP,'YY.MM.DD')      --12.01.03
GO

Linking a Sales Tax Detail to a Vendor

Follow this method and you can create PM Transactions to the taxing authorities to remit sales tax, no re-keying required.

Recently I responded to a post on the GP Community Forum from a user wanting to tie a vendor to the sales tax details so that he could automatically create a payables document similar to what is done when you pay something with a credit card.

I thought that was a really good idea and set out to find a way to do it. You need Integration Manager to accomplish it, but it’s easy to set up.

Prerequisites:

Tax Details

You must assign a general ledger account to each Tax Detail you use to calculate tax. The GL account must be unique per tax authority payee.  For example, if I pay the city of Dallas for one tax detail, and pay the county tax assessor for another, you must have two different accounts. This is needed because you are going to assign a vendor to the GL account assigned to the Tax Detail. Each account will represent a single tax collector.

You do not need a unique account for each Tax Detail, just a unique account for each vendor.

Account Maintenance

For each liability account to a Tax Detail, record the Vendor ID in one of the User Defined fields on the Account Maintenance window. I used UserDefined1 in my example.

The Join

You are going to create a SQL statement that matches the account on the Tax Details card with the Account on the Account Maintenance screen. You are also going to match UserDefined1 with the Vendor Master. This will marry a vendor to each tax detail. Now, include whatever tables are needed to retrieve the sales tax amount in whatever period you are working with and use the Integration Manager (or eConnect, etc) to create a payables transaction for the resulting amount.

I used the SQL statement below to retrieve the sales tax detail amount on historical SOP Invoices. I hard coded the date range to select documents with an invoice date between the first day of the previous month and the last day of the previous month, you wouldn’t want to do that. I just put it in here to show how it is done. Voided documents were excluded:

/* This query returns the sales tax amount on historical SOP Invoices with an invoice
   date between the first day of the previous month and the last date of the
   previous month. Voided documents are excluded.
  
   It uses the following tables:
  
   SOP30200    Sales Transaction History
   SOP10105    Sales Taxes Work and History
   GL00100    Account Master
   GL00105    Account Index Master
   PM00200    Vendor Master
   TX00201    Sales/Purchases Tax Master  
*/

SELECT    
  CASE SOP30200.SOPTYPE
     WHEN 1 THEN 'Quote'
     WHEN 2 THEN 'Order'
     WHEN 3 THEN 'Fulfillment Order'
     WHEN 4 THEN 'Invoice'
     WHEN 5 THEN 'Return'
  END AS Doc_Type
, SOP10105.SOPNUMBE AS SOP_Number
, SOP30200.DOCDATE AS Invoice_Date
, SOP30200.CUSTNMBR AS Customer_ID
, SOP30200.CUSTNAME AS Customer_Name
, GL00100.USERDEF1 AS User_Defined1
, PM00200.VENDNAME AS Vendor_Name
, GL00105.ACTNUMST AS GL_Account_Number
, SOP10105.TAXDTLID AS Tax_Detail_ID
, SOP10105.STAXAMNT AS Sales_Tax_Amt
, SOP10105.FRTTXAMT AS Tax_on_Freight
, SOP10105.MSCTXAMT AS Tax_on_Misc
, DATEADD (m,-1, DATEADD(d,1-DATEPART(d,GETDATE()),GETDATE())) as FirstDayPrevMo
, DATEADD (d,-DATEPART(d,GETDATE()),GETDATE()) as LastDayPrevMo

FROM

SOP10105 INNER JOIN
        SOP30200 ON SOP10105.SOPTYPE = SOP30200.SOPTYPE
    AND SOP10105.SOPNUMBE = SOP30200.SOPNUMBE
      INNER JOIN GL00100 ON SOP10105.ACTINDX = GL00100.ACTINDX INNER JOIN
                     TX00201 ON SOP10105.TAXDTLID = TX00201.TAXDTLID INNER JOIN
                     PM00200 ON GL00100.USERDEF1 = PM00200.VENDORID INNER JOIN
                     GL00105 ON GL00100.ACTINDX = GL00105.ACTINDX
WHERE    

(SOP30200.SOPTYPE in (3,4)) and  VOIDSTTS = 0 
GO
 
Until next post!

Leslie

First and Last day of Previous Month

In the accounting world I so often need to limit my selection criteria to the first and last day of the previous month. This is certainly not a secret formula, but I now have a place where I can look it up. Sadly, I have not memorized this formula

First Day of Previous Month:

DATEADD (m,-1, DATEADD(d,1-DATEPART(d,GETDATE()),GETDATE()))

Last Day of Previous Month:

DATEADD(d,-DATEPART(d,GETDATE()),GETDATE())

Example:

SELECT * FROM SOP30200
WHERE  

VOIDSTTS = 0 and DOCDATE between
DATEADD (m,-1, DATEADD(d,1-DATEPART(d,GETDATE()),GETDATE())) and
DATEADD(d,-DATEPART(d,GETDATE()),GETDATE())

Until next post!

GP2013 Time to Upgrade!!! Service Pack 2 is out!

Yay!!
Service pack 2 for Dynamics GP 2013 has been released.

For everyone waiting for this momentous event, it is here.  Many folks hold out until Service Pack two on the theory that the fixes that didn’t make the ‘release to market’ version are fixed in Service Pack one, but it isn’t until Service Pack two where the real fixes are included that were discovered by actual users.

I am actually a member of the ‘Service Pack two’ crowd .

For partners, you can find the DVD, full install code here: https://mbs.microsoft.com/partnersource/downloads/releases/MDGP2013_Release_Download

For customers, look here: https://mbs.microsoft.com/customersource/downloads/servicepacks/MDGP2013_Release_Download

For Partners, If all you’re looking for is the Service Pack 2 patch, look here: https://mbs.microsoft.com/partnersource/downloads/servicepack/MDGP2013_PatchReleases

For Customers, If all you’re looking for is the Service Pack 2 patch, look here: https://mbs.microsoft.com/customersource/downloads/servicepacks/MDGP2013_PatchReleases

The official ‘Fix List’ is here: https://mbs2.microsoft.com/fileexchange/?fileID=07a5d226-5811-48f2-b783-667dd58669f2

Enjoy!

Until next post!

Leslie

Bootstrapper Error

Dynamics GP Forum member named ‘BenefitElect’ shared this solution with us. This is a problem that’s otherwise impossible to track down. Thanks to BenefitElect and Microsoft Tech Support, it will be no problem for us.

A big THANK YOU to BenefitElect for sharing!

The Problem

Here’s the scenario, you’re installing Dynamics GP 2013 on a x64 workstation and you are greeted with the following:

"An error occurred while installing system components for Microsoft Dynamics GP 2013 Bootstrapper.  Setup cannot continue until all system components have been successfully installed."

When you click the details button, the following is displayed:

"Setup has detected that the file C:\Users\User\AppData\Local\Temp\VSDC0C4.tmp\Watson\dw20shared.msi' has either changed since it was initially published or may be corrupt."

The Solution

Here’s the solution from Microsoft Tech Support:

1. First, you will need to navigate to the folder where the setup.exe is for GP 2013. I would like you to copy the Watson folder out of the installation media and paste it into the C:\ root directory on the workstation. The Watson folder should contain three different files: dw20shared.msi, dw20sharedamd64.msi, and dw20sharedia64.msi.

2. Next, click Start > type in cmd and then right-click on the Command Prompt icon in the search results and then left-clicked on "Run as Administrator".

3. In the Command Prompt window, perform a change directory to the Watson folder:

cd\

cd C:\Watson

4. Next, you will need to execute the following command to install Dr. Watson for a 64-bit workstation.

msiexec /i dw20sharedamd64.msi APPGUID={91710409-8000-11D3-8CFE-0150048383C9} /qb+

If this runs successfully, you should be able to go back to the setup.exe to finish the bootstrap installation

Why does my computer get stuck?

 

Read this fabulous post from David Musgrave about an annoying problem that, until now, was impossible to figure out. Stop using a ‘fix’, and finally make the problem go away!

Here’s the description straight from David:

What would then happen is my laptop would shutdown and restart and then sit with Please Wait on the screen. If left for a while, the lock screen would appear, but when you clicked to get to the logon screen, it would still say Please Wait.

Give it a read. Another mystery Solved!

Enjoy!

Leslie

Round 5 of the 2013 U.S. Payroll Tax Update is now available for Microsoft Dynamics GP2010 and GP2013

Microsoft just released round 5 of the 2013 Payroll Tax updates. This update is supported only on GP2010 or GP2013, if you are using version 10, or any previous version, do not attempt to install it.

A summary of the changes in this update are as follows:

A summary of all updates for 2013 are as follows:

You can update your system using the following navigation:

Microsoft Dynamics GP | Maintenance | U. S. Payroll Updates | Check for Tax Tables

To find the Last Tax Table Update Date and last Tax Code Update Date, follow this navigation:

Microsoft Dynamics GP | Tools | Setup | System | Payroll Tax

Download addresses if you want to install manually, or read more about this and previous changes go to this web site:

Round 5: https://mbs.microsoft.com/customersource/downloads/taxupdates/tugp2010.htm?printpage=false#download

Enjoy!

Leslie

It’s almost here!!! Dexterity in Dallas!

 

Come one, come all, we are running the Dexterity class in Dallas the week of June 17th. It runs from Monday, June 17th through Friday, June 21st.

The class is definitely a ‘go’, there will be no last minute cancellations. If you have been disappointed before, you won’t be disappointed again.

This is the Dexterity Fundamentals class. No previous experience with Dexterity is required (or advised). Even if you have no intention of writing the first line of code in the ‘real world’, this class will teach you a ton about what’s going on behind the scenes. You can finally understand what those error messages mean, and how to read that crazy DEXSQL.LOG.

Even better, if you engage a firm to do some custom code for you, you’ll be more adept of evaluating their estimate. But more fun, is you’ll learn some cool things that can drive your users crazy (all in a fun way) .

This class starts with the basics, but by the end of the week you will have created your own integrating application that works seamlessly with GP. How cool is that!

For you Modifier aficionados, Modifier is just a scaled down version of the tool used to design the user interface in Dexterity. Everything you learn about user interface design, you can put in your Modifier toolbox.

Speaking of user interface design, any VSTools programmers will learn how to create a Dexterity interface that can take the place of your WinForms interface. That means your application will be able to work with the new Web client – until your application can work with the new Web client, you’re losing opportunities for more sales. No need to rewrite your code in Dexterity, just hook it to the Dex version of the same events you are using now.

If you’ve got the user interface down, but your code is in VBA, then it’s time to  turn your VBA code into Dexterity code. You’ll be surprised at how easy the transition is. Once you’ve transitioned it to Dexterity, your application will also work with the new Web client.

Don’t let this opportunity pass you by. Dallas is the American Airlines hub, so it’s easy to get here. The class is being held in a nice part of town where you can actually afford the hotels, and lunch is provided.

ConexusSG, is hosting the training. To sign up, call or e-mail Jerry Adams at ConexusSG. Phone: 469-828-3274 or Training@ConexusSG.com

You can also contact me directly if you have any questions.

email: leslievail@earthlink.net   phone: 972-814-8550

I hope to see you there!

Leslie

An exception occurred while trying to load or initialize the addin located at Rtc.

Don’t ya love these error messages?

My friend and fellow MVP, Frank Hamelly posted a question on the GP Forum about getting the above error message after loading a fresh install of SQL2012 and GP2013 on his laptop. Frank had previously had GP2012 running, so it seemed odd that it would not run now. (Welcome to my world Frank )

Anyway, after some basic ‘have you tried this’ responses, Jonathan Fear came to the rescue.

As it turned out, Frank Hamelly had recently done the Live Messenger to Skype conversion and Jonathan Fear  suggested that the removal of the Microsoft.Dynamics.GP.Rtc.dll should resolve the problem.

Frank Hamelly reported back and said it did indeed solve the problem!

Other programs Jonathan Fear said they had experienced problems with were Shortel Communicator or something similar to Lync.

Here’s the link to the Forum post

https://community.dynamics.com/gp/f/32/t/105759.aspx

Thank you Jonathan for giving us a solution to one of those pesky error messages!

Until next post!

Leslie Vail

The ‘Other List’ is out and I’m in trouble!

 

Well, the ‘Other List’ was released today, and my name was on it. I fear now that my awesomeness has just taken a critical (if not mortal) hit.

I need help. The clock is ticking. I need to do something awesome enough for Doug Pitcher to overlook the infraction. OR . . . I understand that cash is always an acceptable bribe. Where shall I fax the money?

Fight the good fight Doug, your public awaits your announcement.

Until next post!

Leslie

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