Archive for October, 2008
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.
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:
- Open Smartlist
- Open Sales>>Sales Transactions (10.0)
- Click column
- Choose Add
- Find User to Enter
- Choose OK
You should now be able to see the user ID that entered every order, invoice, quote, or return in SOP.
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.
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′)
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 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:
- Open an integration.
- From the Integration window, choose Properties, and choose the Scripts tab.
- An icon next to a script indicates that the script is attached to this integration.
- Select the script type for the integration.
- Select Add based on when the scripting task should occur (before integration, before each document, etc).
Additional sample scripts are listed below:
· 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
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:
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.
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.
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?