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!