As I worked through one of our client’s Dynamics GP integrations this afternoon, through the use of Microsoft’s tool called Integration Manager, I was quickly reminded of how much easier of a time I have been having recently in bringing our customers data into my “new system of choice to implement”, Acumatica’s ERP application.
Don’t get me wrong, as I can build integrations via Integration Manager somewhat trouble-free in Dynamics GP (if our client has been fortunate enough to purchase the application after the expiration time for a new GP license had expired), but not nearly as simple as the “out of the box” data integration possibilities that Acumatica has to offer.
No more multi-level source text files (or text files at all…use EXCEL if you so choose) when attempting to bring in either maintenance windows or transactional data….one file is all it takes per integration…. without the need to create a query to join said source files during the import process.
No more crashing of integrations during the process of importing data after a certain amount of errors, needing to go back to the original source file and changing/re-uploading files through our integration manager mappings, as Acumatica gives one the capability to make “in-line” changes to a poor piece of data directly within a grid itself, allowing one to save and continue without the need to STOP EVERYTHING and correct problems within our source data.
And finally, and perhaps the biggest advantage I have found with Acumatica’s integration capabilities as compared to many other ERP systems I have implemented, MIGRATION MODE within both the A/R & A/P modules, allowing implementers like myself to bring in OPEN & HISTORICAL LINE ITEM DETAILED sales transactions, having no affect on opening inventory “on hand” quantities, as well as also leaving our General Ledger numbers as is, as desired…an absolute MUST with any pre-GO LIVE processing for a new implementation in any new system for a client.
Moving on, one could look-up the “how tos” from instructions on Acumatica’s portal to learn the steps to import data into the system…..or…..check out my abridged version, whereas I did not really need to learn the insides and outs of the course material, nor will you if you have had any type of ERP experience in the past, as figuring out the available tools Acumatica had to offer WITHIN the application itself was incredibly easy to work with, and intuitive to use.
1. Create a New Data Provider
CREATE A NEW DATA PROVIDER as per below, which is really just giving a recognizable name to the record/provider, telling the system what type of file or template you plan to use (in this case I chose Excel), and then finally browsing to a well-formatted file on one’s computer and uploading it to the provider you just created….
…and then to complete the DATA PROVIDER process, simply move to the SCHEMA tab after you’ve uploaded your file, activating the checkbox in the left window pane of the excel sheet which contains your data…..and then clicking on the FILL SCHEMA FIELDS button on the right, which will auto-populated every field from your SOURCE FILE….where you can uncheck the “active” column on the right for any unnecessary data points you’d rather not even consider in your field mapping steps that follow.
DATA PROVIDER is basically just a fancy word for SOURCE FILE…..very simply put.
2. Create a New Import Scenario
CREATE A NEW IMPORT SCENARIO, where you will first give an intuitive name for the data you plan on importing, then look up the window within Acumatica, as I have done below, to which you plan on importing your file to (so in Dynamics GP terms this would be called your DESTINATION area essentially)…
…with the final step at the header-level of your import scenario encompassing the picking of the DATA PROVIDER we previously created in STEP 1 of this process.
3. Build Your Mappings
BUILD YOUR MAPPINGS in the lower half of your import scenario window, where on the MAPPINGS tab itself you will be given TARGET CHOICES to add based on the “screen name” you mapped to in step 2, where in my case I am importing in all of my client’s VENDORS from their old ERP system, so all of the choices seen below are vendor-related and point to certain areas of the Acumatica vendor windows and fields.
****the mapping of data is of course the most time-consuming in any import scenario of any ERP system, so I like to advise my co-worker and/or clients taking on this responsibility to keep both their import scenario open, as well as the window they plan on moving their source file into…….moving TAB by TAB within the Acumatica window, accounting for pertinent data along the way, just as if one were entering in data manually into the window itself.
By doing so, the mappings will become much simpler, especially in Acumatica’s case, where they do a very nice job of identifying “sections” within “tabs” for all their windows, making it easier to pick your Target Object in the end.
Ex. – on the GENERAL INFO tab of the vendor card below, there are fields of important data you will want to import within the MAIN CONTACT, MAIN ADDRESS, & FINANCIAL settings sections for almost all of your vendors.
And if we were to check out our choices in the Target Object drop down columns, we would easily be able to choose this concept of TAB-SECTION in the window of Acumatica, which would then offer us the fields within that section to map to, as per below.
That is basic the process of creating any IMPORT SCENARIO in Acumatica. First, choosing the target object on the left, and then the “source column” from your data provider file on the right. The system also offers you the ability to hard-code certain values into your “target object fields”. See example below. With this feature, one can by-pass the drop-down choices offered from your source file by hitting the pencil icon and stating that the same value should be imported in for every record on the file into certain fields. In this example, I chose to hard-code all of the new vendor records to have a CLASS ID of “APTRADE”.
Then, using that same pencil icon once again to give a value of “TRUE” to state that all of our remittance information should be exactly the same as my vendor’s MAIN address id in this case.
One last thing to point out with the Acumatica mappings within an import scenario is the all important SAVE function as seen below. If one were to think of adding a new record in ANY system window, how would you get out of the window and move on to your next transaction or maintenance routine?
You would be prompted to SAVE your work is the obvious answer, so the same goes for any import scenario you might build. Tell the system to SAVE the entry, then move onto the next on your source file. Simple, but an easy to forget line to include.
4. Run Your Import Scenario
RUN YOUR IMPORT SCENARIO created in step 3 as per below. I advise everyone & anyone whom I work with to first go into the scenario itself, and before bringing in every single line from one’s source file, go to the second tab of your scenario and filter your source file to try an import first of ONLY ONE RECORD. In our case I want to bring in the vendor “047656”, validate that the data is correct for the one record in ALL PERTINENT FIELDS, before releasing my filter and bringing in every record of what could be a very large file.
From my filter I then go through the process of importing in the record, which requires one to first choose the scenario name, click the PREPARE button, which populates the grid on the PREPARED DATA tab with the record we have filtered. in this case being “1” in total that are ready to be imported, then the IMPORT button, kicking off the routine.
After running in my one record, checking it carefully against the original excel file to make sure fields were mapped as expected, I then go back to the import scenario and remove the filter as per below. Now, the balance of my vendors can be imported into Acumatica.
Now when I go back to the IMPORT BY SCENARIO screen, click the PREPARE button once more. Now, my grid fills in with over a thousand records, which should align exactly to the amount of records expected to be imported into Acumatica from the vendor file your customer has supplied you with.
5. Dealing With Data Errors
As mentioned earlier in this article, dealing with data errors is “scary easy” during the Acumatica import process, where you can program your scenario to stop when an error occurs. One will know that an error has stopped the integration when a RED X appears at the top right of your Import by Scenario screen.
In my case I normally let an integration run, minimize my Acumatica application and get some other work done (from time to time checking back in on the integration). If I got an error, I would click on the CLEAR ACTIVATION UNTIL ERROR field, which would save us time by having the system ignore records of data which have already been imported once the integration starts up again.
At that point I would normally filter my ERROR column as per below, taking me to the record that stopped the integration from proceeding (allowing us to reveal the error more clearly as per second screen shot below).
In the case below, I had a record where the TERMS CODE on my source file did not exist in Acumatica, so the INLINE EDITING feature of the integration (tremendous tool!!!) allowed me to go into that one record, change the terms code to one which existed, or remove the code altogether, SAVE the import scenario, then move forward with the importing process with only the Active/Unprocessed records being tended to…basically continuing the imports where we left off without even needing to alter my original source file a smidge!
At the end of my importing process we check out our vendor records to make sure the number equals the number expected to be imported in based on my source file…which is equal in this case…. perfect!
The ease at which Acumatica’s import tool works is tremendous, but then of course one should still abide by your own set of due diligence/rules-set which makes you a successful implementer for your firm:
- Take the time to check out your clients file thoroughly, understanding the functionality of any column in their present system completely, to give you the proper direction of where this data needs to go in the new ERP system you are implementing.
- In cases where your client would like to keep their old system’s nomenclature in the new system you are helping them transfer to, make sure to create all necessary maintenance/system set-ups so your imports won’t fail miserably during any of our steps covered above. For example, if client would prefer a “1” to represent payment terms of Net 30 Days, set-up this “1” properly in the system, as well as any item/vendor/customer classes, shipping methods, tax codes, item uoms, etc.
- And lastly, in order to give the client a CLEAN working start from your imports, I prefer to go through columns on the spreadsheets that our client has offered us from their legacy systems to look for any obvious errors on them caused by either human error on end users parts, or perhaps field value limitations of characters. Meaning if you see a ZIP CODE in an email address column, a long company name that has been split up into 2 different areas of their present maintenance program, a contact name in a State field, etc., take the time to fix these obvious mistakes before importing in the data. By doing this not only will you keep the new system clean from a data perspective, but you also can head-off any functionality or reports-related issues due to a poor data conditions in a maintenance field.
Need more Acumatica help? We are a leading Acumatica partner. Contact us today!
Additional Acumatica Resources
More Usable than Ever: How Acumatica Delivers a Better Experience in 2019 R1
