Posts Tagged ‘Import’
As you may know my posts originate from being in the front lines working support and aiding fellow coworkers in need, my friend Chastidy presented me an issue with Integration Manager, the customer is trying to record data into the Internet Information window specifically to the “Additional Information” field. When they verified the field properties it says it holds up to 32,000 characters, if they copy and paste the contents being imported it saves just fine, but Integration Manager is truncating to 255 chars, but the field option is set to cancel the document, not to truncate at max.
First thing on my mind was “Challenge Accepted”, the solution its simple, I went to the data source properties and verified the columns available, I noticed that the field data type chosen was “String”, if I recall correctly from my IM self training class Strings are limited to 255 characters, and if you would like to import more than 255 characters the other data type that will allow this into a field like this one is “Long VarChar”, once I changed the data type in the source properties voila!!! information uploaded correctly.
Then the next thing on my mind was “Oh Yeah!!” and pictured me with dark sunglasses.
This small change has a huge advantage and saves a lot of headaches.
I hope this works for you and keep the comments flowing.
Until the next post!!
Francisco G. Hillyer
Mike wrote this great blog article awhile ago: http://gp.rosebizincblogs.com/2008/10/using-gp-macros-to-import-data-2.html
The reason I reference it now is that I have worked with GP for over 12 years and had never used this method for data import. But we are currently working with a new customer that is migrating to GP. They have about 600 BOM’s and though we were able to import the BOM’s fairly easily, importing the routers was just not going to work.
So I thought I would try the macro method. It worked beautifully. The source file I used, had very simple router information, and the macro ran just fine.
Two things to consider:
- It’s not going to work the first time (what macro ever does?), so expect that minor set back.
- Be very careful not to interrupt the process by continuing to work on your machine. In my case, I was accessing GP through a remote desk top, so this wasn’t a problem. I just minimized the RDP window, and went about my day as the macro ran on the remote machine.
If you haven’t tried this out yet, you should give it a try.
Dynamics GP has a very easy to use budget import and export function that allows you to create budgets in Excel and update budgets in GP, based on amounts you have in Excel.
All this functionality is available from the Budget Selection screen. Navigate to: Cards >> Financial >> Budgets
I created this short video to show this functionality in action.
One very important caveat: Make sure that you validate the information in GP after importing or updating budget information from Excel. There is no error reporting on the import function if the import does not properly import the data.
I usually create a quick FRx report for this purpose. But you could also simply export the information from GP, to a new spreadsheet (make sure not to overwrite your original spreadsheet).
Microsoft Dynamics Great Plains uses a reporting tool called Report Writer. I’ve given it somewhat of a bad rap in the past. I hope I haven’t hurt anyone’s feelings with my disparaging words.
One of the most common errors I see people referring to is the “Unable to open customizations dictionary” error when trying to import a new report into the reports.dic file. The resolution to this error is talked about in tech doc 919440 and 869323. Nine times out of Ten the resolution includes getting everyone out of GP then trying to import the file again. (Tools>>customize>>customization maintenance).
I suppose if you don’t have much to do, or have more than a few users and those users don’t have that much to do, it’s not really a problem to kick everyone out of the system. But often I’ll hear “Oh fiddle sticks, I’ll have to wait till tonight to do this” as there’s no way to kick everyone out of the system during the day.
When I have the patience and energy I use the following steps to bring in the new reports so that users can still be in the system and the import is still successful.
- Make a backup of the Reports.dic file
- Save your package file where you can browse to it from Dynamics
- Change the path to the reports.dic file in the dynamics.set file to a new location. (By default the Dynamics.set file is in the GP folder where Dynamics is installed. Open with notepad and the reports.dic file is the 3rd line down where the paths start to all the products.) Change the path to a new location or simply change the reports.dic file path to end with reports2.dic instead of reports.dic
- Launch Dynamics and open Customization Maintenance (Tools>>customize>>Customization Maintenance)
- Import the new report from the package file to the new reports2.dic file
- Exit out of Dynamics
- Change the reports2.dic path back to what it started with in step 3, in our case ending with reports.dic
- Launch Dynamics and open report writer (Tools>>Customize>>Report Writer)
- Choose Import on the right
- Browse to the Reports2.dic dictionary file, highlight the report and press insert (You may be asked to overwrite the current report if you already have that report in the system), then press “Import” at the bottom to bring in the report.
Not the easiest but works without getting everyone out of the system.
The Fixed Assets module has been a standard part of BRL licensing since its inception, and we still have many clients that don’t even use it. This amazes me because when I was a Controller, the fixed-assets-in-a-spreadsheet thing drove me crazy. Invariably the spreadsheets would break, and they wouldn’t total properly, or the calculations for depreciation expense wouldn’t extend gracefully into a new year.
These are the benefits I generally see in using the Fixed Assets module:
- Well integrated into the core financial modules; no duplicate entry required.
- Decent reporting, in that it easily produces lists of assets for insurance listings, tax reporting, audit requests, etc.
- All data is stored in SQL so it will be regularly backed up.
- All calculations are done for you.
- Great audit trail for depreciation and other asset related transactions.
- There are transaction types for typical fixed asset events, and therefore creates a solid audit trail.
- It’s easy to make changes to individual depreciation attributes if needed, and the system will make appropriate transactions to reconcile changes.
The Fixed Assets module is easy to setup and use, and there’s an easy import function built into the module.
The setup may take some planning time to make sure that the various transactions are coded to the proper accounts, and to ease data entry. But once you’ve planned it out, it’s easy to implement.
The import function included in the module is easier and quicker than the Integration Manager approach, so use the Fixed Assets import utility.
You propbably already own the Fixed Assets module, so if you’re not using, go ahead and set it up, import the data, and start using it. You’ll be happy you made the effort.
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.