Integration Manager: The Basics

I’m sure there have been numerous articles written about Integration Manager for Dynamics GP. Most of them are written for developers and contain advanced features, tips, and tricks for making it work in unique circumstances or using complicated data sources.

I find that most end users have a difficult time understanding the fundamentals of IM, so I want to outline a very simple integration design for those of you who are new to building integrations and feel a bit overwhelmed by the the tool.

The simplest way to approach a data import using IM is to first manually key in a transaction from the recordset you wish to import, whether it be a journal entry, a payables invoice, or a bank transaction.  This is best done in a test environment. Once you have the transaction entered, you can then open integration manager and better understand how it works because IM is like having another data entry person in your system. That’s right, folks: integration manager enters the data just like you did when you hand-keyed it.

Let’s have a look at a standard journal entry:

There are not a lot of fields on this window, but it is a good example of a transaction entry window in Dynamics GP:

• It has a header section and a detail (line) section or grid
• It has required fields
• It has various options based on user input

Every transaction entry window has a header and a detail. In most cases, the header contains the information outside of the grid, or lines.

In this case, the header fields are:

• Journal Entry number
• Note
• Intercompany checkbox
• Batch ID
• Transaction Type (standard or reversing)
• Transaction Date
• Reversing Date (only if you select the reversing transaction type)
• Source Document
• Reference
• Currency ID

The detail/line/grid fields are:

• Co. ID
• Account
• Debit
• Credit
• Description
• Exchange Rate
• Distribution Reference
• Corresp Co. ID

Required fields are displayed in bold and red. This feature can be activated under user preferences and is very useful for identifying missing data values when importing records.

In terms of user-input options, it is actually surprising how many different parameters there on on this simple window.

• If you mark the intercompany checkbox, for example, the Co. ID and Corresp Co. ID in the grid will be activated
• If you select the Reversing type transaction, the Reversing date field will be activated
• If you select an account in the grid that is linked to an analytical analysis group, the A next to the Account field will be activated, allowing entry of analysis information for this account

All of these options are also operational in Integration Manager and can be activated if needed.  Let’s compare the data entry window to the integration setup:

They certainly don’t look at all alike, do they?  But let’s take a closer look at IM and its components.

Under Sources, we have a header and a detail object, which correlate to the header and detail windows in the transaction entry window.

Query relationships allow us to link the header information to the detail information. More about that in a moment.

The Destination is the transaction entry window in Dynamics GP where the information will appear.

And Destination Mapping is where we will go to map each field from our source file(s) to the destination window.

Sources

Each source in an integration is linked to a data set that the user wishes to import into Dynamics GP.  The data can be in virtually any location and format, from a comma-delimited flat file to an Oracle database.  If using multiple sources (header and detail) there must be a column/field that establishes the link between the two files.  In our example, this is the DocNum field.  Alternatively, IM has the flexibility to allow use of a single datasource for both header and line information.  More about that below.

For our example, we’re going to use a sample integration that comes with Integration Manager.  It consists of two tab-delimited text files: one for the header and one for the detail. You will find these sample files by default in this location: C:\Program Files\Microsoft Dynamics\Integration Manager 10\Samples.

As you can see, the required information for a journal entry is all available in these two files.  The header information contains the Date and Reference values.  The line information contains the distribution accounts and amounts.  Both files contain the DocNum, which is a column used to join the files together and match the appropriate journal entry detail to each header.  We don’t need to provide a journal entry number, as we are going to allow Dynamics GP to assign this at the time of import.

Double-click on the Source object GL Trans Header to open the properties for this source:

You will notice the path to the source file on this window.  Use the elipse (3 dots) to navigate to your desired source file.  The remaining options are self-explanatory.  If you file is comma-delimited, choose that option, if tab-delimited, choose that option, etc.  If the first row of your source file contains field names, mark the checkbox to indicate that as well.

Click the Columns tab to continue and click the refresh button to display the columns from your source file.

Each time you change the source file, you MUST refresh the columns by clicking this button.  This forces IM to “read” the new source file and detect any additional columns or changes you have made to existing columns.

The additional tabs: Filter, Sorting, Scripts contain additional features that permit you to restrict the data to be imported, sort it prior to importing, and add logic to validate or manipulate the data prior to importing.  With scripts you can also enhance an integration to write back to the source and confirm importing was successful.  This is an advanced topic that I won’t address here, but examples are available if you need this functionality.

Repeat this process for each Source in your integration.  In this case, we would go through the same steps for the GL Line Detail source.

After you have clicked Apply and OK to close this window, you should right-click on each Source and choose to Preview.   This should show you the source data that will be imported and allow you to verify that the source files are pointed to the right place.  If the Preview does not look right or you receive an error message, you will need to correct the source data before proceeding.

Query Relationships

Once each source has been pointed to the appropriate source file, columns refreshed, and any additional options selected, we need to define the relationships between the various sources.  Double-click the Query Relationships object to display the sources.

To link the sources, simply click on the field name/column on the header source and drag your cursor to the same field name/column on the detail source.  This will create the arrow displayed.  If it is not set properly, right-click on the arrow and choose Remove.  For more advanced links, right-click on the arrow and choose Properties to open the Select Relationship Type window.  Remember that the Master file is the origin of the arrow.  The child file is where the head of the arrow is pointing.

Once you’ve completed the query relationships, close that window.

Destination

For pre-defined integrations, such as our sample one, the destination is already defined.  If you were building a new integration, you would right-click on the Destination folder object and choose Add a Destination, then select from a list of objects.  In this case, our destination is already set to General Journal.

Destination Mapping

The mapping of your source file to the Dynamics GP destination is the heart of integration manager.  You will notice when you double-click on Destination Mapping that all of the folders displayed pertain to a component of the destination window in Dynamics GP.

In this General Journal example, the top level is our header information.  Click on General Journal to view all of the fields available on the journal entry window in Dynamics GP: Journal Entry, Intercompany, Batch ID, etc.  They are all present and can be mapped here.  The real power of IM comes into play by using the Rules, however.

Notice that the Rule column is set to Default for almost every field.  This tells IM to allow Dynamics GP to automatically take care of the information in that field according to how it would occur if you were manually entering it into the transaction window in the system.  If you were keying a journal entry, Dynamics would provide you with the next journal entry number, the intercompany checkbox would be unmarked, you would need to enter a batch ID, etc.

In our example, we have made the Batch ID a constant value.  Other options are to Use a Source Field, Use Input, or Use Script.  Use a Source Field will allow you to map the field from your source file.  You would need to have a field/column in the header file that contains the Batch ID.  Use Input will prompt you at run time to enter a batch id.  Use Script will allow you to program how you want the batch id to be defined based on additional information you provide in a script.

For each field on the transaction entry window, you need to decide how you want the information populated.  The Rule dropdown may contain different options depending on which field you select.  In addition, if you select Use Constant, the Source column will be populated with the specific options pertaining to that field in Dynamics GP.  For example, if you select Use Constant for the Transaction Type field, you will be presented with the two options available in Dynamics: Standard or Reversing.  By selecting Reversing, you are ensuring that every record imported will be created as a Reversing Transaction, but you must also ensure that your source file header contains a reversing date field/column in order to map that information as well.

The Reference field is a required field, and I will use it  to demonstrate various options that are available in IM at the field/column level.  When you click on the Reference field and choose Use Source, you will notice the ellipsis (3 dots) is activated and you can use that to select the appropriate field to map from your source file.  The Source Object window will open, and the dropdown will contain all of the sources available for this integration. In our case, it will show the header and detail options.  Because we are mapping a field on the header, we want to select from the header source.  We would highlight the Reference field and click Select.

With your cursor still on the Reference field in the mapping window, you will notice there are additional options available in the lower left-hand area of the screen.  These features allow IM to handle various conditions in your source data in order for IM to import it properly into Dynamics GP.  For example, if your source file contains both Standard and Reversing transactions and you have a field in the header that indicates this using S or R, you would want to use the Translation option, which will allow you to tell Dynamics if this column contains an “S” that means Standard, if it contains an “R” that means Reversing, etc.

If you were mapping the batch ID from your source file, but it is in upper and lowercase format, Dynamics GP won’t import it.  The integration will fail because the Batch ID field must be in uppercase characters.  In the integration, you can specify that you want to convert the case at runtime.  The other options are for leading spaces, trailing spaces, how to handle a data string that is too long, and what to do if your source field is null/empty.  By default Dynamics will cancel a document if a required field is null, but for optional fields, you can choose to make it a blank value or use a default value from the Dynamics setup.

Once you have set the options for each field on this window, click the Options tab to select how you want to handle additional elements of the integration.  For example, if the Batch ID you have mapped or entered as a constant value does not exist in Dynamics GP, you can opt to have it created at runtime as shown below:

Once we’ve mapped the header (General Journal object), we need to proceed with the Entries object.  In addition, if we wanted to import Analytical Accounting distributions or Tax details, we would need to map each of those objects as well.  In our case, we will continue with Entries.

As you can see, we are using the source file for the account, debit, and credit amounts and allowing Dynamics GP to handle the rest of the distribution grid in the transaction entry window.  One very useful feature of IM that is available for distribution fields is the Rule to Use Positive Source Field or Negative Source Field.  This allows you to import data from a single field in the source file, as in our sample file where Amount is negative for credits and positive for debits.  IM allows us to use this same field and will designate the debit or credit status of the distribution based on this logic.

The options tab for Entries lets us specify the source object for this particular object: Line Detail.  The Rule column here also contains options such as Default (if you have default distribution accounts set up for a vendor for example) or Empty (to bring in zero amounts) or Default Non-Imported (which is helpful if you only want to map the debit side of a payable and allow Dynamics to assign the A/P account from the Vendor or Posting Setup window.  The Rules will vary depending on the Dynamics GP window destination and the type of transaction you are importing.

___________________________________________________________

After you have mapped all sources to their appropriate destination object, you are ready to run the integration.  The progress window will advise you of any issues with your import, and you can also view the log from that window after the integration has run.  To access a log after you have exited the progress window, simply double-click the top level of the integration (GL Transaction) and click on the Logs tab:

I hope this helps as an introduction to using this very powerful and flexible tool.  We have barely scratched the surface here, but I hope I have answered some fundamental questions about Integration Manager.