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: 282

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: 476

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: 387

Concatenating strings in SQL Server

This SQL Server tip comes from the April 2012 edition of the GP Reports Viewer newsletter.

A request that we have seen many times when creating SOP invoices for Dynamics GP is to concatenate serial numbers.  So if there is an item with serial numbers, instead of showing a list with one serial number per line under the item, which would look a little unwieldy with long list of serial numbers:

we want to show all the serial numbers on one line, separated by a comma. Below is the SQL code that will do this in SQL 2005 and 2008:

SELECT
p.SOPNUMBE SOP_Number, p.SOPTYPE SOP_Type, p.ITEMNMBR Item, p.LNITMSEQ Line_Item_Sequence,
LEFT(p.serial_numbers, len(p.serial_numbers)-1) Serial_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

Using this code, the line item and serial numbers shown above can look like this:

This code can also be changed slightly for other uses, for example, to concatenate a list of invoices paid by a check in payables. Here is the code to do that using my Payables Apply Detail view:

SELECT
p.Vendor_ID, p.Payment_Voucher_Number, p.Payment_Date, p.Payment_Type,
p.Payment_Document_Number, LEFT(p.apply_docs, len(p.apply_docs)-1) Applied_To_Docs
FROM
 (SELECT p1.Vendor_ID, p1.Payment_Voucher_Number, p1.Payment_Date, p1.Payment_Type,
  p1.Payment_Document_Number,
  (SELECT coalesce(rtrim(Apply_To_Doc_Number) + ', ','')
   FROM view_Payables_Apply_detail p2
   WHERE p1.Vendor_ID = p2.Vendor_ID
     and p1.Payment_Voucher_Number = p2.Payment_Voucher_Number
   ORDER BY Payment_Voucher_Number
   FOR XML PATH('')) apply_docs
  FROM view_Payables_Apply_detail p1
  GROUP BY Vendor_ID, Payment_Voucher_Number, Payment_Date, Payment_Type,
   Payment_Document_Number) p

For more tips like this, take a look at my SQL Server Coding Tips page. To receive new tips as soon as they are published, sign up for the GP Reports Viewer newsletter.

Filed under: GP Reports code, GP Reports Viewer, SQL coding, SQL Server Tagged: featured, GP Reports code, GP Reports Viewer, SQL code

See more here:
Concatenating strings in SQL Server

May 21, 2012 · Victoria Yudin · No Comments
Tags: , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 548

Microsoft Dynamics GP and the Concur Import

Concur Standard, Professional, and Premier are Web-based solutions that automate each step of the expense reporting process. Concur is integrated with the Microsoft Dynamics GP financial management solution through the use of Concur Expense Integrator.

A common question that we receive in regards to Microsoft Dynamics GP and the Concur Import is the following:  Will you help me with the Concur setup window as I cannot get the import to work correctly?

Oftentimes, we find that the customer is using Concur Standard.  Concur Standard is a little bit different than the Professional and Premier offerings, as this gives you the ability to create your own file extract definition.  The Standard Edition only has 25 possible columns, but we need to end up with a 77 column comma separated import file, which the Concur Expense Integrator tool for Microsoft Dynamics GP is expecting.  To do this, you will end up having a lot of blank fields for columns that are not used in the source file.  Another requirement is that the file extension must end with .cgl (Company Check) or .igl (Individual Bill/Company Pay). If you are attempting to set this up, you will want to utilize the APGL File Layout (in Appendix A of the Concur Expense.pdf) which is the file definition that you will need to match with the extract definition that was created.  This is the file format that the Concur Expense Integrator Tool is designed to consume.

If you are having trouble mapping the remaining segments of your accounts you need to know where these are in your source file, if you look at the setup window for mapping the segments there are 6 possibilities (Employee Info 1, Employee Info 2, Employee Info 3, Custom 1, Custom 2, and Cost Center) which translates to columns 17,18,19, 64, 65, and 22 respectively. 

I hope that this helps you to successfully integrate your Concur Expense data with Microsoft Dynamics GP.

Enjoy!
Sarah

Visit site:
Microsoft Dynamics GP and the Concur Import

May 17, 2012 · Sarah Purdy · No Comments
Tags: , , , , , , ,  · Posted in: Blogs I Follow Total Views: 573

Mismatched Inventory Quantities

 

Sometime you face difficulties in your inventories where your quantities does not match between applications forms, for example the on hand quantity does not match the historical stock status report or the item stock inquiry, and this is really annoying as the inventory reconciliation utility does not fix this and the resolution for this is a nightmare.

Reason behind such an issue might be a packets loss during the transaction posting where the system inserted the transactions in some tables and did not insert into the other, for instance a normal inventory transaction posting affect IV00102 table to adjust quantity, insert records into IV30300 for history and affect IV10200 and IV10201, all these tables must have the same quantity in normal cases, but in some cases you might have differences.

To fix this issue you will need to follow how Dynamics GP works, as a start we know that the reconciliation utility for the inventory items reconciles the quantity of IV00102 based on the difference between Quantity Received and Quantity Sold of IV10200 table, therefore after performing “Reconcile” for your inventories you can be sure that IV00102 is matched to IV10200.

Now the challenge is how to reconcile IV30300 to IV10200, first of all you will need to identify differences, I have created the below script that generate differences in quantity for all your items per each site between IV10200 and IV30300:

SELECT ITEMNMBR                                       AS ITEMNUMBER, 
LOCNCODE AS LOCATIONCODE,
Sum(TRXQTY * QTYBSUOM) AS QTYIV30300,
(
SELECT Sum(QTYRECVD - QTYSOLD) FROM IV10200 WHERE IV10200.ITEMNMBR = IV30300MODIFIED.ITEMNMBR AND TRXLOCTN = IV30300MODIFIED.LOCNCODE) AS QTYIV10200,
Sum(TRXQTY * QTYBSUOM) - (SELECT Sum(QTYRECVD - QTYSOLD) FROM IV10200 WHERE IV10200.ITEMNMBR = IV30300MODIFIED.ITEMNMBR AND TRXLOCTN = IV30300MODIFIED.LOCNCODE) AS DIFF

FROM (
--Correct IV30300 To Include Transfers
SELECT TRXSORCE, DOCTYPE, DOCNUMBR, DOCDATE, HSTMODUL, CUSTNMBR, ITEMNMBR, UOFM, TRXQTY, UNITCOST, EXTDCOST,
TRXLOCTN AS LOCNCODE, QTYBSUOM, TRFQTYTY, TRTQTYTY
FROM dbo.IV30300 WHERE (DOCTYPE <> 3)
UNION ALL
SELECT TRXSORCE, DOCTYPE, DOCNUMBR, DOCDATE, HSTMODUL, CUSTNMBR, ITEMNMBR, UOFM, TRXQTY * - 1 AS Expr1, UNITCOST, EXTDCOST,
TRXLOCTN AS LOCNCODE, QTYBSUOM, TRFQTYTY, TRTQTYTY
FROM dbo.IV30300 WHERE (DOCTYPE = 3)
UNION ALL
SELECT TRXSORCE, DOCTYPE, DOCNUMBR, DOCDATE, HSTMODUL, CUSTNMBR, ITEMNMBR, UOFM, TRXQTY, UNITCOST, EXTDCOST,
TRNSTLOC AS LOCNCODE, QTYBSUOM, TRFQTYTY, TRTQTYTY
FROM dbo.IV30300 WHERE (DOCTYPE = 3)
)
AS IV30300MODIFIED
WHERE IV30300MODIFIED.ITEMNMBR IN

--Select Inventory Items with Type "Sales Invetory" Only
(SELECT ITEMNMBR FROM IV00101 WHERE (ITEMTYPE = 1) )

GROUP BY ITEMNMBR,
LOCNCODE

HAVING
Sum(TRXQTY * QTYBSUOM) <> (SELECT Sum(QTYRECVD - QTYSOLD) FROM IV10200 WHERE IV10200.ITEMNMBR = IV30300MODIFIED.ITEMNMBR AND TRXLOCTN = IV30300MODIFIED.LOCNCODE)

For the time being you had a list of variances, you will need to identify what figures are really matches your physical, if it was the IV30300, then you will need to create an adjustment using Inventory Transaction Entry to correct IV10200 to match IV30300 and then delete the record from the IV30300, by this the IV10200 will be affected and IV30300 will not. Otherwise if the IV10200 is correct and the issue is in IV30300 then you will need to create a database adjustment in IV30300 to correct the difference and match your IV30300 to the IV10200.

I know that this is not an easy process to be done but I can assure you that above steps can save you several weeks of researches specially if your inventories are loaded with items.

Hope that this helps.


Regards,

Mohammad R. Daoud MVP – MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85
me@mohdaoud.com
www.mohdaoud.com

More:
Mismatched Inventory Quantities

May 12, 2012 ·  · No Comments
Tags: , , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 373

Connection to the MAPI server not available in Dynamics GP

I posted about Dynamics GP and 64 bit support last year. As per the technology requirements for Dynamics GP – for the e-Email feature –

E-Mail Functionality Feature

Item

Requirements

Microsoft Office

Microsoft Office 2007 32-bit
Microsoft Office 2010 32-bit

 

Documentation

Click HERE to review the SystemSetup.pdf manual for Microsoft Dynamics GP 2010.  Refer to Chapter 16 for information regarding e-mail setup.

E-Mail

Extended MAPI compliant e-mail application

 

As you can see 32 bit Office is required. So if you are getting the “connection to the mapi server not available” error, and Outlook is working fine, you need to check your office version. If you have the 64 bit version install, you need to uninstall it, and re-install 32 bit Office. 

See more here:
Connection to the MAPI server not available in Dynamics GP

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