No one really likes to do data entry, or at least not for long periods at a time and on a continual basis. Many of our clients have the need to move large amounts of data from a separate system into Microsoft Dynamics SL and will often use of three ways to accomplish this task. (Well, four if you include manual data entry.) However, there are three basic methods of automating the entry that can save time, avoid data entry errors, and allow for a more thorough review of the data.
The first method is one I am sure many of you do already and have likely been doing for years. This is the old, reliable “copy and paste” of data from Excel into certain data entry screens that are laid out like a spreadsheet, or grid, and include the following:
- GL – Journal Transaction screen
- AP – Detail account distribution for an AP Voucher and Adjustments Entry
- AR – Detail account distribution for an AR Invoice and Memo entry
- PR – Time Entry or Time and Dollar in the payroll entry screens
- IN – Inventory Receipts, Issues, Adjustments and Transfers entry screens
- IN – Detail inventory components for a Kit entry
- IN – Detail inventory components for a Bill of Materials entry
- PO – Detail lines for a Purchase Orders entry
- PC – Project Charge Entry screen
- PC – Project Budget Maintenance screen
- BI – Flexible Billings detail entry for the Invoice and Adjustments Maintenance
- AL – Project Allocator rate changes in the entry screens (Standard or Multi-Level)
This list is not all inclusive, but these are the most common screens used to copy and paste data. There are a few things to keep in mind when using Excel as the source:
- Data must be in the same columns as the entry screen, even if the column is blank.
- Certain data must be formatted correctly in order to paste without an error.
- Keep in mind that Excel will drop a preceding 0 unless the field is formatted as TEXT.
The second method uses the Transaction Import (TI) functionality and is probably used most often by our clients since it is easy to set up and will allow you to import data into just about all screens that accept input. Even though the system imports the data, it will still need to pass all required validations, such as an active and open account, subaccount, customer ID, etc. It also allows you to import data that has more than one level to it. For instance, you may want to import customer invoices into the AR Invoice and Memo entry screen. There are three levels to this screen: (1) the batch, (2) the document, and (3) the detail transactions for each document. TI can handle all three levels and as many of each as you need.
This tool uses a “control” file that maps the various levels and fields from the Dynamics SL screen to the data within your comma delimited file. The control file is generated by the system and is unique to your settings in the SL screen, so it will know about any hidden or added fields. Once the control file is set up and your data is ready, it is an easy matter of matching these up in the TI screen and letting the system do your work! If there are any records that contain an error, it will log these for your further review. One client uses TI to import several hundreds of budget records while preparing this each year. With each iteration of the budget, it is easy to re-import the entire budget to display it using current financial reporting.
The third method of importing data is through a custom programmed integration. Boyer & Associates is often called on to write additional code that seamlessly integrates an outside system with the Dynamics SL system. The real-time integration can work two ways, writing data into SL and certain data back out to the other system, something a TI process can’t achieve. There are also many third party products built to integrate nicely into the Dynamics SL system. Many of the payroll processing companies have pre-built GL interfaces that can help book the payroll entries for you as well.
Whichever method you are using, or would like to use, Boyer consultants can assist you with getting the most out of your Dynamics SL investment. Leave the data entry to the system and let us help you user you time for more value-added analysis.
If you have any questions regarding these alternatives, please contact us at any time.
For additional tips and tricks regarding Microsoft Dynamics SL and ERP products, subscribe to our blog.
