Migrating reports

Here are instructions to move modified reports from one version of GP (or recreate reports.dic):

1. Log into GP on the new SQL server and choose any company.

2. Go to Tools>Customize>Report Writer. This will create a new reports.dic file. Insert one report from the left to make sure this is created. E.g. 1099 misc.

3. Log into the old SQL server and choose any company.

4. Go to Tools>Customize>Customization Maintenance and select all the contents.

5. Click on “Export” and save the package to a temporary location.

6. On the new SQL server, log into GP and go to Tools>Customize>Customization Maintenance and click on “Import”. Find the Package file saved from the old SQL server and import it into the new SQL server.

All users must be off the system to import into the new reports.dic.

You should also be aware that several table changes have been made from 8.0 to 10.0 so expect some errors. You should get some of your reports to come over and some will require re-modification or tweaking.

Another way to do the reports.dic is to go to Tools>>customize>>report writer. Open the report writer screen and click Import. Browse out to the old reports.dic file then highlight all the reports on the left hand side. Click insert then import. You don’t need everyone out of the system to do this. I’ve found sometimes it’s easier to get the reports over this way than using the import from customization maintenance.

Good luck.

User who entered SOP Doc

Someone called today asking how to find who entered a sales order in Dynamics. This can simply be done by adding the User to Enter column on the Sales Transaction Smartlist.

Instructions as follows:

1. Open Smartlist
2. Open Sales>>Sales Transactions (10.0)
3. Click column
4. Choose Add
5. Find User to Enter
6. Choose OK

You should now be able to see the user ID that entered every order, invoice, quote, or return in SOP.

Happy Searching.

Convergence 2009

Just saw dates have been confirmed for Convergence 2009. March 10th-13th, 2009 in New Orleans, LA. Mark your calendars for this great event. Here is the link to the Convergence event page.

http://www.microsoft.com/dynamics/convergence/default.aspx

GL transaction export script

I often have the request to get all the journal entries for a particular period of time. Usually this request comes from auditors. You can export the GL detail report but formatting is usually an issue. You can export all the transactions from a smartlist (account transactions) but that usually takes a long time if you want the data for a long date range, say 6 months to a year.

Here is a script I've used in the past to export the data straight from SQL. It includes the user who posted the transaction and the actual date it was posted on. You will have to change the bolded red date and year for your particular needs. You will also need to add GL00100.ACTNUMBR_X to include however many account segments you have in your GL account.

When running the query in SQL have the output results set to file.

Note this is for open year transactions. If you want data from a historical year, find and replace the table from GL20000 to GL30000 and OPENYEAR to HSTYEAR.

Open Year Query

SELECT GL20000.JRNENTRY AS Journal_Entry_Number, GL20000.SOURCDOC AS Module, GL20000.TRXDATE AS Date,
GL00100.ACTNUMBR_1 AS Account_Segment1, GL00100.ACTNUMBR_2 AS Account_Segment2, GL00100.ACTNUMBR_3 AS Account_Segment3, GL20000.OPENYEAR AS Year,
GL00100.ACTDESCR AS Acct_Description, GL20000.DEBITAMT AS Debit, GL20000.CRDTAMNT AS Credit,
GL20000.TIME1 AS Time, GL20000.USWHPSTD AS User_Who_posted
FROM GL20000 INNER JOIN
GL00100 ON GL20000.ACTINDX = GL00100.ACTINDX
WHERE (GL20000.OPENYEAR = 'XXX') AND (GL20000.TRXDATE => '11 / 30 / 2008') AND (GL20000.TRXDATE <= '12 / 31 / 2009')

Historical Year Query

SELECT gl30000.JRNENTRY AS Journal_Entry_Number, gl30000.SOURCDOC AS Module, gl30000.TRXDATE AS Date,
GL00100.ACTNUMBR_1 AS Account_Segment1, GL00100.ACTNUMBR_2 AS Account_Segment2, GL00100.ACTNUMBR_3 AS Account_Segment3, gl30000.HSTYEAR AS Year,
GL00100.ACTDESCR AS Acct_Description, gl30000.DEBITAMT AS Debit, gl30000.CRDTAMNT AS Credit,
gl30000.TIME1 AS Time, gl30000.USWHPSTD AS User_Who_posted
FROM gl30000 INNER JOIN
GL00100 ON gl30000.ACTINDX = GL00100.ACTINDX
WHERE (gl30000.HSTYEAR = 2004) AND (gl30000.TRXDATE => '01 / 01 / 2004') AND (gl30000.TRXDATE <= '12 / 31 / 2004')

Happy Exporting.

Autocomplete Errors

Autocomplete has been in place for some time to help speed up your data entry, but typos may add unnecessary items to your lookup fields. In order to clean those you can right click them and select "Remove From List" to get rid of the mistakes in your autocomplete lookup.

In the example below I remove AARONFRITZ from my customer id autocomplete list:

Warn User When Logging Into TEST Company

GP has a simple hidden trick that allows, upon logging into a test company, a message to come up to warn the user that they are accessing a Test copy of your Production company.

All you have to do is change the company name to include '<TEST>' (no quotes) at the end. (Microsoft Dynamics GP Menu >> Tools >> Setup >> Company >> Company)

Integration Manager Advanced Techniques - Scripting in VBScript

In case you were not aware, Integration Manager has a slew of sample VBScripts that you can copy and paste into your integration to accomplish such useful tasks as:

• Autocreate Alphanumeric Vendor or Customer ID codes (e.g. for vendor "Acme Company" becomes ACME0001)
• Prompting the user for input (e.g. automate requesting a batch id or document date from user)
• Removing non numeric characters from phone numbers
• Concatenating multiple fields from source file into one GP destination field
• Deleting a source file after successful integration
• Prompting User to define source file location
• Sending an outlook email that notifies receipent of errors or warning messages
• etc etc

Adding a sample script is simple:

1. Open an integration.
2. From the Integration window, choose Properties, and choose the Scripts tab.
3. An icon next to a script indicates that the script is attached to this integration.
4. Select the script type for the integration.
5. Select Add based on when the scripting task should occur (before integration, before each document, etc).

Additional sample scripts are listed below:

GP Specific Sample Scripts
· Assigning an Alphanumeric Vendor ID
· Assigning a Sequential Vendor ID
· Setting the Batch Date
· Prompting the User for a Batch ID
· Concatenating Two or More Source Fields
· Prompting the User for a Document Date
· Setting the Document Type in Payables or Receivables based on the Document Amount
· Setting a GL Account in SOP based on Item and Customer
· Adding Leading Zeros to a Check Number in Bank Reconcile Integrations
· Removing Leading Zeros from a Check Number in Bank Reconcile Integrations
· Automatically Set the Reversing Date for a GL Transaction to the First Day of the Next Month
· Integrating Multi-column Budget Information
· Integrating Multi-column GL History
· Removing Specific Characters from a Phone Number
· Removing All Non-numeric Characters from a Phone Number
· Setting the Posting Type for a GL Account
· Updating the Default Purchasing U of M for an Item
· Setting a source field that is blank to the default value
· Setting the Typical Balance for a GL Account based on number scheme

General Purpose Sample Scripts
· Looking Up a Value in a Database Table
· Deleting a Source File After a Successful Integration
· Executing a Stored Procedure in SQL
· Filtering ODBC/Text Source Data based on User Input at Runtime
· Validating Foreign Keys
· Prompting the User for the Path to the Source Files
· Marking Source Records as Integrated
· Displaying a message box with the error and warning messages
· Sending an Outlook E-mail that Notifies the Recipient of Document Errors
· Protecting an Integration with a Password
· Renaming a Source File that Uses Today's Date in its Name
· Validating Data based on a List of Values

Eliminating the Print Dialog Box

When you print a document in Great Plains, a follow on print dialog box is displayed. If you want to stop this dialog box from printing each time you select a print routine, change the DEX.INI file to the following:

NoPrintDialogs=TRUE

With this change, the print dialog box will no longer be displayed. This change must be made on every workstation where you want the print dialog box not to be shown.

Using GP Macros to Import Data

1. Determine what information is needed to complete the record in Great Plains. Create an Excel spreadsheet of that information. i.e. Vendors that you want to inactivate. Your spreadsheet would be vendors #'s that you want to inactivate.

2. Open the window in Great Plains you want to update. (Vendor Maintenance)

3. You need to record the macro you want to run. Tools->Macro -> Record. It will prompt you to name/save the macro (vendor.mac) Proceed with entering a samples transaction (Put in the vendor #, inactivate the vendor
and save) Go to Tools->Macro->Stop

4. Open your macro file using Microsoft Word. It will open the document and look similar to the attached file named bom.mac.

5. Select Tools -> MailMerge function within Word and follow the wizard.
a. .Main Document select Create ->Form Letters -> Active Window
b. Get Data - Go - Open Data and select your file you created in step 1. remember that it is an Excel file. You will get a message that no fields match - Select Edit Main Document
c. Your original .mac file will be highlighted in red. Select the fields within the single quotes i.e. ' "Vendor" ' and choose the field you want to populate the data with under Insert Merge Field button.
d. After all the fields are populated Select Merge
e. Merge to new document and select enter
f. Save your file as .txt and name it filename.mac (it needs to have the .mac extension) see example attached.

6. Open up window in Great Plains you want to update (vendor Maintenance) Put the cursor on the field that you started recording your macro on and Select Tools->Macro->Run Select the file you created in step 5f and go.

7. The macro will run automatically until finished. You can't use the computer until it finishes or it will error out. If the macro errors out it will stop on the record you will need to delete the rows prior to the error in order to continue.

New location

After setting up a new location for a company I had to input several settings in Dynamics. Thought I'd list them off below so I don't forget what needs to be done in the future.

1. Copy GL accounts to include new location. Used Mass Modify under Cards>>financial>>mass mass modify
2. Add Customer Class ID for new location
3. Add new site and linked new location to inventory account
4. Setup new tax detail and tax schedule
5. Add location to tree in FRx
6. Add new logo to invoice and PO for new location
7. Add items to site for SOP

Anything I forget to do?

Add “OTHER” Distribution Entries to Change PO Receiving Transaction "PURCH" Account(s)

If you want to change the purchases account on a PO receiving transaction but don’t want to go back to each line item to change the account, you can add additional distribution lines with the Type = OTHER. You would enter a credit to the default PURCH account(s) and then enter a debit to the new/correct account(s). This feature can also be used if additional accounting entries related to the transaction are needed (to record use tax, for example). As long as you use the OTHER distribution type and have the total debits = total credits for the additional entries, there won’t be any distribution error messages.

Changing Purchase Accounts on PO Receipts

If you want to change the purchases account on a PO receiving transaction but don’t want to go back to each line item to change the account, you can add additional distribution lines with the Type = OTHER. You would enter a credit to the default PURCH account(s) and then enter a debit to the new/correct account(s). This feature can also be used if additional accounting entries related to the transaction are needed (to record use tax, for example). As long as you use the OTHER distribution type and have the total debits = total credits for the additional entries, there won’t be any distribution error messages.

Report Formatting Wrong with FRx 6.7 SP 10

If you have FRx reports that have the currency symbol ($) on the first and last rows and percentage columns as well as amount columns, I recommend that FRx 6.7 SP10 not be installed. With this service pack, the CS Print Control on a row in a row format will over ride the formatting in the column layouts. Therefore, the percentages will be shown as amounts on the last row (or any other row with CS in the Print Control field). Also, any row with CS will show the amounts with 2 decimals, even if the report is set to round on the catalog. One fix is to take the CS off of the row format and put a special format mask on the amount columns in the column layout. However, this will cause the $ to be on every row of the report.

Microsoft has identified this as bug 212217. Their response to me regarding this problem if you have installed SP 10 is as follows:
Workarounds 1 and 2 that I have included below apply to this bug.

Workaround 1:
You can export the report to Excel and manually edit the numbers.

Workaround 2:
You can uninstall FRx using the attached 'How to Uninstall FRx.doc' and Reinstall FRx 6.7 SP 9 using the attached 'FRx67GPInstall.pdf'.

You can download the FRx 6.7 with SP 9 installer (file name: FRx674GP10FullInstall.zip) at the link below:

https://mbs.microsoft.com/fileexchange/?fileID=9360d805-b22b-4e28-907a-35c4a7c23ca0

I would advise you to make a backup of your SysData folder before this process as well as Export .TDB's of each of your production Specifications Sets.

Report lists in Dynamics 10.0

Started using the new reporting lists navigation pane in Dynamics 10.0. It's a nice way to customize the system to a users needs. You can create custom lists and put any window that relates to those lists in the Action, Modify, New, Restriction (can filter data), Reports or Go To windows. Thus a user can access all that they need relating to specific transactions in one view.

Good job Microsoft on this one. I think it almost makes up for taking away the palette's in GP 8.0 and 9.0.

Does Consistency Matter?

This morning I received a call from a longtime client of ours. Over the years I have provided consulting when required; which really hasn’t been much. Today the request was something like this, “Remember when you did the integration with UPS and Great Plains? Can you do that again?”

This client felt comfortable calling directly to the person who had helped her before, and the solution was delivered quickly and easily.

Yes, consistency matters. Work with people who know what they’re doing, and who have been doing it for a long time.

Using FA Import Utility to perform mass update of general FA fields

Many users think that the Fixed Asset Import Utility can be used only for importing new assets. But this utility can also be used to update many assets with new General (NOT Book) information, if you have the data in an Excel file. The import file must be in the layout defined by Microsoft Dynamics GP for importing general asset information (which can be found in the online help under Fixed Assets Import - Field mappings). Although all of the columns must be present in the worksheet file, you need to fill in only the Book ID (which will be “GENERAL” for all assets), Asset ID, Suffix ID, and the field(s) you want to update. For example, if you need to populate the Structure ID for many existing assets, your Excel file needs to have all the required columns (in the exact/required order) but only the Book ID, Asset ID, Suffix ID, and the Structure ID columns would be populated. You then delete the header row and save the file as a text (tab delimited) file. Perform the import on the Asset Import/Export window (Tools>Utilities>Fixed Assets>Asset Import Export) as follows:
1. Mark the “Import” box
2. Enter “asset.mac” in the Macro Location field
3. Mark the “Run Macro after creation” box
4. Enter the path to your text/tab delimited file
5. Set the Delimiter Character to “Tab”
6. Click the Continue button
***NOTE: Because this is a mass update, perform the import in a test company first or make a backup of the company database before running the import.

Indentifying security tasks that include access to a particular window

Users who have assigned v10 security using the security tasks provided by Microsoft may find it difficult to remove access to certain windows. For example, if you are trying to remove access to the RM Cash Receipts Entry window from a user’s role but can’t seem to find the task assigned to that role that includes access to the Cash Receipts Entry window. Below are steps Microsoft Dynamics Tech Support provided to find the security tasks that include access to a particular window. Since the process involves using the Clear Data window, I recommend that you make a backup of the Dynamics and company databases beforehand and be very careful to run the Clear Data process on ONLY the table specified (i.e. Security Resource Descriptions).

a. Go to Microsoft Dynamics GP Maintenance Clear Data. From this window, click the Display tab and select ‘Physical’

b. Select the series where you can find the window. On this specific case, select Sales.

c. Then scroll down on the tables list until you see Security Resource Descriptions, add that to the right by clicking insert. Then click on the OK.

d. Now the SY09400 table will be populated and you can run the SQL query below. You can now see the security ID of the window on the first column.

select SECURITYID, RESTECHNAME, * from Dynamics..SY09400 where DSPLNAME = 'Cash Receipts Entry'

More Cubes

Just found another good use for the Analysis Cubes function in GP. We have a client that wants to easily see PO quantities for identical items across three company databases. Not available in standard GP reporting; would require some work with SQL Server Reporting Services. But with Analysis Cubes; voila, it's right there: