Importing MDA with SmartConnect/eConnect

I have been using SmartConnect a lot recently, and its an incredibly powerful tool – there is nothing you can’t do with it. So gear up for many posts about it.

Recently we had the requirement to setup automated import of GL Transactions with MDA into GP, where the customer was using Wennsoft Equipment Management Series. As eConnect does not support addition of MDA lines with GL transactions, neither does SmartConnect which is built on top of eConnect. Additionally, Wennsoft has its own MDA tables which were required to be populated, and linked to the GL transactions.

Also, as our main aim was setting up automated integrations, and Integration Manager isn’t really built for automating imports (although possible using Macros – see How to schedule Dynamics GP to automatically log in and run an Integration Manager integration and here Weekly Dynamic: Automating Integrations) – we decided to go for SmartConnect.  

SmartConnect allows execution of SQL commands on both document and map inserts, which solved the problem. We wrote SQL code to insert the MDA lines with each GL transaction, and also make the associated imports into the relevant Wennsoft tables. The resulting solution is so simple – that you’d be surprised.

Below are the steps we followed, and the associated SQL scripts (built with the help of Rubal). If you are using eConnect, you would want to refer to the stored procedure below, and use in the GL Insert Post processing routine.

First created a GL Import Integration in Smart Connect, and mapped the required fields.

 

image

Add the MDA Code in the Description field with a distribution line

image

Create a new task in Smart Connect that runs if the document insert succeeds. From here call the Stored procedure that would do the inserts related to MDA. The two stored procedures used are listed below.

 

image

Finally with a couple of clicks setup the schedule for the import!

image

Please use these stored procedures only for your reference, and test them in your test environment. 
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--sp_Insert_Equipment_MDA_for JE 3447, 'EQUIP'

CREATE PROC dbo.sp_Insert_Equipment_MDA_for_JE
@jrnentry INT ,
@mdaGroup NVARCHAR(15)
AS
DECLARE @actindex INT
DECLARE @DTA_Index NVARCHAR(25)
DECLARE @amount NUMERIC(19, 5)
DECLARE @date DATETIME
DECLARE @mdaCode NVARCHAR(15)


DECLARE @CurrentSeqNum AS INT ,
@NextSeqNumber AS INT ,
@fake AS BIT


SET @CurrentSeqNum = 0
SET @fake = 1
SET @CurrentSeqNum = 0
SELECT TOP 1
@NextSeqNumber = ( SQNCLINE )
FROM GL10001
WHERE JRNENTRY = @jrnentry
AND SQNCLINE > @CurrentSeqNum
ORDER BY SQNCLINE ASC
SET @CurrentSeqNum = @NextSeqNumber

--select @NextSeqNumber, '1'
IF ( @NextSeqNumber IS NOT NULL )
BEGIN
WHILE ( @fake = 1 )
BEGIN
-- Check if mda codes exist and account has mda group assigned
SELECT @actindex = actindx ,
@mdaCode = RTRIM(DSCRIPTN)
FROM GL10001
WHERE JRNENTRY = @jrnentry
AND SQNCLINE = @CurrentSeqNum
--select @actindex as acc, @mdaCode as code
IF EXISTS ( SELECT GROUPID
FROM DTA00300
WHERE ACTINDX = @actindex
AND GROUPID = @mdaGroup )
AND EXISTS ( SELECT *
FROM DTA00200
WHERE CODEID = @mdaCode )
BEGIN
--If yes,get values and insert MDA
SELECT @DTA_Index = REPLACE(DTA_Index, '.', '')
+ 'N' ,
@date = TRXDATE
FROM GL10000
WHERE JRNENTRY = @jrnentry
SELECT @amount = ( DEBITAMT - CRDTAMNT )
FROM GL10001
WHERE JRNENTRY = @jrnentry
AND SQNCLINE = @CurrentSeqNum
--select @DTA_Index as dt, @amount am

EXECUTE sp_InsertEquipmentMDALines @jrnentry,
@actindex, @DTA_Index, @CurrentSeqNum,
@mdaGroup, @amount, @date, @mdaCode

END

--Get next line
SET @NextSeqNumber = NULL
SELECT TOP 1
@NextSeqNumber = ( SQNCLINE )
FROM GL10001
WHERE JRNENTRY = @jrnentry
AND SQNCLINE > @CurrentSeqNum
GROUP BY SQNCLINE
ORDER BY SQNCLINE ASC
--select @NextSeqNumber, 'next'
--if end of transaction, Stop processing
IF @NextSeqNumber IS NULL
BEGIN
--select 'break'
BREAK
END
ELSE
BEGIN
SET @CurrentSeqNum = @NextSeqNumber
SET @actindex = 0
SET @DTA_Index = ''

SET @amount = 0
SET @mdaCode = ''
END
END
END
 
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC dbo.sp_InsertEquipmentMDALines
@jrnentry INT ,
@actindex INT ,
@DTA_Index NVARCHAR(25) ,
@SEQNUMBR INT ,
@mdaGroup NVARCHAR(15) ,
@amount NUMERIC(19, 5) ,
@date DATETIME ,
@mdaCode NVARCHAR(15)

AS
INSERT INTO dbo.DTA10100
( DTASERIES ,
DTAREF ,
ACTINDX ,
SEQNUMBR ,
GROUPID ,
DTA_GL_Reference ,
DOCNUMBR ,
RMDTYPAL ,
GROUPAMT ,
JRNENTRY ,
TRXDATE ,
PSTGSTUS
)
VALUES ( 2 ,
@DTA_Index ,
@actindex ,
@SEQNUMBR ,
@mdaGroup ,
'' ,
'' ,
0 ,
@amount ,
@jrnentry ,
@date ,
1
)



INSERT INTO dbo.DTA10200
( DTASERIES ,
DTAREF ,
ACTINDX ,
SEQNUMBR ,
GROUPID ,
CODEID ,
DOCNUMBR ,
RMDTYPAL ,
POSTDESC ,
DTAQNTY ,
CODEAMT ,
TRXDATE
)
VALUES ( 2 ,
@DTA_Index ,
@actindex ,
@SEQNUMBR ,
@mdaGroup ,
@mdaCode ,
'' ,
0 ,
'' ,
0 ,
@amount ,
@date
)

-- This part is only required if you are using Wennsoft
INSERT INTO dbo.EQSB1010
( DTASERIES ,
DTAREF ,
ACTINDX ,
SEQNUMBR ,
SEQUENCE1 ,
Equipment_MDA_Code ,
Secondary_MDA_Group ,
Secondary_MDA_Code ,
EQS_Cost_Code ,
POSTDESC ,
DTA_GL_Reference ,
CODEAMT ,
DTAQNTY ,
Specification_Index

)
VALUES ( 2 , -- DTASERIES - smallint
@DTA_Index , -- DTAREF - char(25)
@actindex , -- ACTINDX - int
@SEQNUMBR , -- SEQNUMBR - int
1.00000 , -- SEQUENCE1 - numeric
@mdaCode , -- Equipment_MDA_Code - char(15)
'' , -- Secondary_MDA_Group - char(15)
'' , -- Secondary_MDA_Code - char(15)
'' , -- EQS_Cost_Code - char(15)
'' , -- POSTDESC - char(51)
'' , -- DTA_GL_Reference - char(25)
@amount , -- CODEAMT - numeric
0 , -- DTAQNTY - numeric
0 -- Specification_Index - int

)
 

Visit site:
Importing MDA with SmartConnect/eConnect

June 11, 2012 · Jivtesh Singh · No Comments
Tags: , , , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 767

Steps to disable third-party products or temporarily disable additional products in Dynamics GP

In the last couple of weeks I have seen a couple of GP consultants / advanced users try and disable products the long way, manually editing the dynamics.set file – Steps to disable third-party products or temporarily disable additional products in the Dynamics.set file in Microsoft Dynamics GP

Although this works – there is a much easy way to do this! Use the Dictionary control window in Support debugging tool. I personally think, it takes letter time to install Support debugging tool, then to do this manually – plus you don’t have to worry about making mistakes. SDT also takes a backup of the Dynamics.set file automatically. 

The Dictionary Control window, allows dictionaries to have their triggers and alternate or modified windows to be disabled temporarily or until re-enabled.

 

image

image

This can be used for troubleshooting issues without having to manually edit the Dynamics.set launch file and restart the application. Here is an example from David Musgrave’s blog.

Check out a lot more posts about the support debugging tool here – GPWindow.com – Dynamics GP- TOOLS FOR GP > SUPPORT DEBUGGING TOOL

More:
Steps to disable third-party products or temporarily disable additional products in Dynamics GP

June 10, 2012 · Jivtesh Singh · No Comments
Tags: , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 321

Canadian Payroll Midyear Tax Update has released.

The Canadian Payroll 2012 Midyear tax update has released for Microsoft Dynamics GP 10 and 2010.

The changes in this update include Ontario and Nova Scotia.

Once the update is installed the Last Tax Update field in the Payroll Control Setup – Canada window (Microsoft Dynamics GP menu >> Tools >> Setup >> Payroll – Canada >> Control) should be 06/15/2012.

Links to site:

GP 10
https://mbs.microsoft.com/customersource/downloads/taxupdates/cagpye10.htm?printpage=false

GP 2010
https://mbs.microsoft.com/customersource/downloads/taxupdates/cagptuye2010.htm?printpage=false

Thanks
Terry Heley

View original post here:
Canadian Payroll Midyear Tax Update has released.

June 5, 2012 · Terry Heley MSFT · No Comments
Tags: , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 279

Dynamics GP SOP line items with serial numbers and comments

Based on some reader requests, this new view is a combination of  my All SOP Line Items view and the recently posted script showing how to get a concatenated list of serial numbers in SQL. This new view will return all posted (history) or unposted (open) Sales Order Processing line items with the associated serial numbers (or lot numbers). The serial/lot numbers for each line will be concatenated into one field. The results will also include the header and line item comments.

For more Dynamics GP SOP scripts please visit my SOP SQL Views page. Or take a look at my GP Reports page for additional reporting links. SOP table information can be found on this page.

CREATE VIEW view_SOP_Line_Items_Serial
AS

-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
-- view_SOP_Line_Items_Serial
-- Created on June 4, 2012 by Victoria Yudin - Flexible Solutions, Inc.
-- For updates please see http://victoriayudin.com/gp-reports/
-- All line items for posted and unposted SOP transactions with serial and/or lot numbers
-- Returns Functional amounts only
-- Component Items are not taken into consideration
-- Fields that can have different values on the document header and
-- line item are both returned prefaced by 'Header' or 'Line'
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~

SELECT S.*,
coalesce(C.CMMTTEXT,'') Line_Item_Comment,
coalesce(SN.Serial_Lot_Numbers,'') Serial_Lot_Numbers,
coalesce(HC.CMMTTEXT,'') Header_Comment

FROM  --sop lines
(SELECT Doc_Status = 'Open',
H.SOPNUMBE SOP_Number, H.SOPTYPE SOP_Type,
H.DOCDATE Document_Date, H.GLPOSTDT GL_Posting_Date,
H.ORDRDATE Order_Date, H.DUEDATE Due_Date,
H.MSTRNUMB Master_Number,
CASE H.PSTGSTUS
  WHEN 0 THEN 'Unposted'
  WHEN 2 THEN 'Posted'
  ELSE 'Error'
  END Posting_Status,
H.CUSTNMBR Customer_ID, H.CUSTNAME Customer_Name,
H.CSTPONBR Customer_PO, H.BACHNUMB Batch_Number,
H.LOCNCODE Header_Site_ID, L.LOCNCODE Line_Site_ID,
CASE H.VOIDSTTS
  WHEN 0 THEN 'Not Voided'
  WHEN 1 THEN 'Voided'
  ELSE ''
  END Void_Status,
H.SLPRSNID Header_Salesperson, L.SLPRSNID Line_Salesperson,
H.SALSTERR Header_Territory, L.SALSTERR Line_Territory,
H.PYMTRMID Payment_Terms_ID,
H.SHIPMTHD Header_Shipping_Method, L.SHIPMTHD Line_Shipping_Method,
H.PRBTADCD Bill_To_Address_ID,
H.PRSTADCD Header_Ship_To_Address_ID, H.ShipToName Header_Ship_To_Name,
H.ADDRESS1 Header_Address_1, H.ADDRESS2 Header_Address_2,
H.ADDRESS3 Header_Address_3, H.CITY Header_City,
H.STATE Header_State, H.ZIPCODE Header_Zip_Code,
H.COUNTRY Header_Country,
L.PRSTADCD Line_Ship_To_Address_ID, L.ShipToName Line_Ship_To_Name,
L.ADDRESS1 Line_Address_1, L.ADDRESS2 Line_Address_2,
L.ADDRESS3 Line_Address_3, L.CITY Line_City,
L.STATE Line_State, L.ZIPCODE Line_Zip_Code,
L.COUNTRY Line_Country,
H.DOCAMNT Total_Document_Amount, H.MRKDNAMT Total_Markdown_Amount,
H.SUBTOTAL Document_Subtotal,
H.FRTAMNT Freight_Amount, H.MISCAMNT Misc_Amount,
H.TAXAMNT Tax_Amount, H.CURNCYID Currency_ID,
H.ReqShipDate Header_ReqShipDate, L.ReqShipDate Line_ReqShipDate,
H.USER2ENT User_to_Enter,
H.COMMNTID Header_Comment_ID, L.COMMNTID Line_Comment_ID,
L.LNITMSEQ Line_Item_Sequence, L.CMPNTSEQ Component_Sequence,
CASE L.NONINVEN
  WHEN 0 THEN 'Inventory'
  WHEN 1 THEN 'Non-Inventory'
  ELSE ''
  END Item_Type,
L.ITEMNMBR Item_Number, L.ITEMDESC Item_Description,
L.QUANTITY Quantity, L.UOFM U_of_M, L.QTYBSUOM Qty_in_Base_U_of_M,
L.QTYREMAI Qty_Remaining, L.UNITPRCE Unit_Price,
L.XTNDPRCE Extended_Price,
CASE L.MRKDNTYP
  WHEN 0 THEN 'Percentage'
  WHEN 1 THEN 'Amount'
  ELSE ''
  END Markdown_Type,
L.MRKDNAMT Markdown_Amount, L.MRKDNPCT/100 Markdown_Percentage,
L.TRDISAMT Trade_Discount_Amount,
L.UNITCOST Unit_Cost, L.EXTDCOST Extended_Cost

FROM SOP10100 H
INNER JOIN SOP10200 L
	ON H.SOPTYPE = L.SOPTYPE AND H.SOPNUMBE = L.SOPNUMBE

UNION

SELECT Doc_Status = 'History',
H.SOPNUMBE SOP_Number, H.SOPTYPE SOP_Type,
H.DOCDATE Document_Date, H.GLPOSTDT GL_Posting_Date,
H.ORDRDATE Order_Date, H.DUEDATE Due_Date,
H.MSTRNUMB Master_Number,
CASE H.PSTGSTUS
  WHEN 0 THEN 'Unposted'
  WHEN 2 THEN 'Posted'
  ELSE 'Error'
  END Posting_Status,
H.CUSTNMBR Customer_ID, H.CUSTNAME Customer_Name,
H.CSTPONBR Customer_PO, H.BACHNUMB Batch_Number,
H.LOCNCODE Header_Site_ID, L.LOCNCODE Line_Site_ID,
CASE H.VOIDSTTS
  WHEN 0 THEN 'Not Voided'
  WHEN 1 THEN 'Voided'
  ELSE ''
  END Void_Status,
H.SLPRSNID Header_Salesperson, L.SLPRSNID Line_Salesperson,
H.SALSTERR Header_Territory, L.SALSTERR Line_Territory,
H.PYMTRMID Payment_Terms_ID,
H.SHIPMTHD Header_Shipping_Method, L.SHIPMTHD Line_Shipping_Method,
H.PRBTADCD Bill_To_Address_ID,
H.PRSTADCD Header_Ship_To_Address_ID, H.ShipToName Header_Ship_To_Name,
H.ADDRESS1 Header_Address_1, H.ADDRESS2 Header_Address_2,
H.ADDRESS3 Header_Address_3, H.CITY Header_City,
H.STATE Header_State, H.ZIPCODE Header_Zip_Code,
H.COUNTRY Header_Country,
L.PRSTADCD Line_Ship_To_Address_ID, L.ShipToName Line_Ship_To_Name,
L.ADDRESS1 Line_Address_1, L.ADDRESS2 Line_Address_2,
L.ADDRESS3 Line_Address_3, L.CITY Line_City,
L.STATE Line_State, L.ZIPCODE Line_Zip_Code,
L.COUNTRY Line_Country,
H.DOCAMNT Total_Document_Amount, H.MRKDNAMT Total_Markdown_Amount,
H.SUBTOTAL Document_Subtotal,
H.FRTAMNT Freight_Amount, H.MISCAMNT Misc_Amount,
H.TAXAMNT Tax_Amount, H.CURNCYID Currency_ID,
H.ReqShipDate Header_ReqShipDate, L.ReqShipDate Line_ReqShipDate,
H.USER2ENT User_to_Enter,
H.COMMNTID Header_Comment_ID, L.COMMNTID Line_Comment_ID,
L.LNITMSEQ Line_Item_Sequence, L.CMPNTSEQ Component_Sequence,
CASE L.NONINVEN
  WHEN 0 THEN 'Inventory'
  WHEN 1 THEN 'Non-Inventory'
  ELSE ''
  END Item_Type,
L.ITEMNMBR Item_Number, L.ITEMDESC Item_Description,
L.QUANTITY Quantity, L.UOFM U_of_M, L.QTYBSUOM Qty_in_Base_U_of_M,
L.QTYREMAI Qty_Remaining, L.UNITPRCE Unit_Price,
L.XTNDPRCE Extended_Price,
CASE L.MRKDNTYP
  WHEN 0 THEN 'Percentage'
  WHEN 1 THEN 'Amount'
  ELSE ''
  END Markdown_Type,
L.MRKDNAMT Markdown_Amount, L.MRKDNPCT/100 Markdown_Percentage,
L.TRDISAMT Trade_Discount_Amount,
L.UNITCOST Unit_Cost, L.EXTDCOST Extended_Cost

FROM SOP30200 H
INNER JOIN SOP30300 L
	ON H.SOPTYPE = L.SOPTYPE AND H.SOPNUMBE = L.SOPNUMBE) S --sop lines

LEFT OUTER JOIN
(SELECT p.SOPNUMBE, p.SOPTYPE, p.LNITMSEQ,
LEFT(p.serial_numbers, len(p.serial_numbers)-1) Serial_Lot_Numbers
FROM
 (SELECT p1.SOPNUMBE, p1.SOPTYPE, p1.ITEMNMBR, p1.LNITMSEQ,
  (SELECT coalesce(rtrim(SERLTNUM) + ', ','')
   FROM SOP10201 p2
   WHERE p1.SOPNUMBE = p2.SOPNUMBE and p1.SOPTYPE = p2.SOPTYPE
      and p1.ITEMNMBR = p2.ITEMNMBR and p1.LNITMSEQ = p2.LNITMSEQ
   ORDER BY SOPNUMBE
   FOR XML PATH('')) serial_numbers
  FROM SOP10201 p1
 GROUP BY SOPNUMBE, SOPTYPE, ITEMNMBR, LNITMSEQ) p) SN --serial/lot numbers
ON SN.SOPNUMBE = S.SOP_Number AND SN.SOPTYPE = S.SOP_Type AND SN.LNITMSEQ = S.Line_Item_Sequence

LEFT OUTER JOIN SOP10202 C --line item comments
ON C.SOPNUMBE = S.SOP_Number AND C.SOPTYPE = S.SOP_Type AND C.LNITMSEQ = S.Line_Item_Sequence

LEFT OUTER JOIN SOP10106 HC --header comments
ON HC.SOPNUMBE = S.SOP_Number AND HC.SOPTYPE = S.SOP_Type

--add permissions
GO
GRANT SELECT ON view_SOP_Line_Items_Serial TO DYNGRP

Disclaimer: I tested this on limited data, if you find an issue or have a suggestion for improvement, please let me know and I will post the update here for everyone.

Filed under: Dynamics GP, GP Reports code, GP SQL scripts, SOP SQL code Tagged: featured, GP Reports code, GP SQL view, Sales Order Processing, SQL code

See the original post:
Dynamics GP SOP line items with serial numbers and comments

June 4, 2012 · Victoria Yudin · No Comments
Tags: , , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 732

Table Links in Extender

There are many times we see that a user removes a record from Microsoft Dynamics GP, but the Extender data is left in the database.  If a new record is creating that happens to have the same Key on it, the data then links to that new record.  Using the Table Links, it is possible to have the Extender data removed when the Microsoft Dynamics GP data it is linked to is removed as well.

Linking an Extender Window to a table ensures that the Extender Window’s data is deleted when the corresponding Microsoft Dynamics GP table record is removed.   A window can be linked to more than one table. When linked to more than one table, the Extender Window data will only be deleted when there is not a corresponding record in any of the linked tables.

If you link an Extender Window to a work table and not the corresponding history table, then the Extender data will be deleted when the record is posted because the data is removed from the work table during posting.  We recommend that you actually just link to the history table if all of your data is moved to history eventually.  This way the Extender data will only be removed if you remove history on that record.
 

To link a window to a table:
1. Open the window you want to link to in Extender.
2. Select Options – Table Links to open the Table Links window.
3. Click the Add button above the Table Links list.

4. Select the Product, Series and Table that you want to link to.
5. Match the Key Fields on the window to fields in the Table.

6. Click Save.
7. Relaunch Microsoft Dynamics GP to enable the Table Links.

Read more here:
Table Links in Extender

May 31, 2012 · Nicole Albertson · No Comments
Tags: , , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 339

SafePay Transactions Upload window does not populate in Microsoft Dynamics GP

SafePay allows the customer to generate a list of checks and voids (from Payables Management and Payroll) that they can upload to the bank.  When a check is presented to the bank to be cashed, the bank will compare the amount and information on the check to the uploaded SafePay file, as a security and fraud prevention measure.

In Microsoft Dynamics GP, the user can generate the upload file to send to the bank, if they have purchased SafePay.  However, we have received several support incidents where the SafePay Transactions Upload window is blank and does not populate in Microsoft Dynamics GP.  If it’s the user’s first time using SafePay, we often find they are not registered for the Bank Reconciliation module.   However, if the user has been using SafePay for some time, we usually find that the check has already been uploaded and it will not upload again. The check can only be uploaded once to the bank.

Here is a running list of potential reasons why the SafePay Transactions Upload window is completely blank:

  • The checkbook ID that the check was generated in is not linked to the Bank Upload ID in SafePay.
  • The check date does not fall within the Last Upload Date and Upload Cutoff Date range specified in the SafePay-Transactions Upload window.
  • The checks have already been uploaded. Review the SafePay Transactions History window for the check number.
  • The Customer is not registered for the Bank Reconciliation module. Note: The CM20200 table will be empty if they are not registered for Bank Reconciliation.
  • The default ‘DD’ prefix was removed from the Next Earnings Statement Number (ie. Direct Deposit number) in Payroll, so the system treats all checks with the same beginning digit as direct deposits and does not include them in the SafePay upload.
  • A third party product may be interfering.  Disable any 3rd party products and test again. 

For more information or steps on how to review the above causes, please refer to Knowledge Base article # 2708664

Thank you,

Cheryl Waswick | Technical Support Engineer | Microsoft Dynamics GP Support -Financials

Visit link:
SafePay Transactions Upload window does not populate in Microsoft Dynamics GP

May 29, 2012 · Cheryl W. MSFT · No Comments
Tags: , , , , , , ,  · Posted in: Blogs I Follow Total Views: 593

Error Exporting to Excel from Business Portal 5.1 for Microsoft Dynamics GP 2010 in Internet Explorer 9

If you’re using Business Portal 5.1 for Microsoft Dynamics GP 2010 along with Internet Explorer 9 and attempt to export the results of a query to Excel you may have received the following “Results not copied to Excel” error:

 

This generally happens when the result set you are exporting is large (~1000 records or greater).

When exporting a Business Portal query to Excel we utilize a java script file in your Business Portal installation folder.  We have worked with our development team to come up with a fix for this issue.

This workaround involves modifying the DataCenter.js file that is found in the C:Program FilesMicrosoft DynamicsBusinessPortalPortalDatacenter directory on your Business Portal server.  The steps are as follows:

1)      First, make a backup of DataCenter.js

2)      Then, open DataCenter.js in Notepad

3)      Search this file for “//Size it”

4)      Edit the file as follows to add the alert section:

Before Edit:

                }

                                }

                                //Size it

                                if(excel.MajorVersion < 10)

                                                excel.ActiveSheet.UsedRange.AutoFitColumns();

                                                excel.ActiveSheet.UsedRange.AutoFitRows();

                               

 

After Edit:

 

           }

                                  alert(“Click to export”);

                                 }

                                 //Size it

                                if(excel.MajorVersion < 10)

                                                excel.ActiveSheet.UsedRange.AutoFitColumns();

                                                excel.ActiveSheet.UsedRange.AutoFitRows();

                               

 

NOTE: You can change the text in the alert() line to whatever you’d like.

5) Save your changes to this file

6) Reload Business Portal and export a query to Excel.  You should now receive the alert prompt, which will give the site the short amount of extra time it needs to successfully export to Excel.

NOTE: You may find that you need to run IISRESET before this change will take effect.

Implementing this quick fix should allow you to successfully export your Business Portal query data to Excel after you have upgraded to IE9.

Enjoy!

Lucas

Continue Reading:
Error Exporting to Excel from Business Portal 5.1 for Microsoft Dynamics GP 2010 in Internet Explorer 9

May 24, 2012 · Lucas Miller · No Comments
Tags: , , , , , , ,  · Posted in: Blogs I Follow Total Views: 452