Skip to content
+1-888-319-3663

HELP ARTICLE

Creating General Ledger Transaction with Analytical Account (AA)


One of the most confusing pieces of data to integrate into Dynamics GP is anything with Analytical Accounting. The main confusion comes from the fact that the field naming conventions don’t necessarily match the main module. For example, the Document Number field on the AA distribution is the Journal Entry number. There are few other considerations that need to be considered when creating Payables Integrations with Analytical Accounting and really, any transaction in Dynamics GP with Analytical Accounting. When working with General Ledger transactions the speed at which processing occurs will be the largest hurdle to overcome.

There are a few different scenarios that require different mapping options within the Analytics Distribution that we will cover in this article.

Scenario 1 – One Analytical Distribution per Distribution and no duplicate accounts.

This is by far the easiest scenario to integrate as it’s a straight mapping to the fields in the Journal Entry Header, Distributions, and Analytical Distributions.

You can download the maps and sample files here.

Data Source

The first step is to set up or validate the data source. I will use some simple values for AA Dimension and Dimension Code are on each Distribution line within the data source as shown below. The Dimension is in the DepartmentCode column and the Dimension Code is in the ProgramCode column. We are only going to have the Analytical information on the debit distribution and will leave it blank for the credit distribution so those credit distributions will be restricted out later in the mapping.

NOTE: If you are using a text file such as a CSV or TAB delimited, I would recommend using the Create Schema option to set the Dimension and Dimension Codes to char. This will allow SmartConnect to handle any type of value without requiring a calculated field to translate a number to a string if there are a mixture of data types.

In this example, we are using a CSV file with the Bulk Data and Text File data source types. The Data Source Key Field will be the Date column to create a new Journal Entry per date in the file.

 

Mapping

I won’t go through the standard distribution and header mappings in this example but will focus on the Analytics Distribution. The most important note is the Analytics Distribution is used for other document transactions as well as General Journals so the field names might not match to the same values that you find with each different transaction. If we don’t map the right values, Microsoft eConnect will not be able to find the distribution record for proper validation and return an error.

In the case of General Journals, the Document Number would be mapped to the General Journal number. The Document Type is always a 0 when working with General Ledger transactions so we can hard code that value. This is explained in the Microsoft eConnect documentation. In our simple scenario, the Analytical Amount will match our debit distribution amount (this example does not have AA dimensions on the credit amounts) so we wouldn’t have to map the amount field but we will typically do the mapping for consistency.

Mapping Restriction

Not every distribution in my data source will have a dimension code assigned, I need to create a restriction to prevent the Analytics Node from being processed for those distributions.

With the Analytics Distribution Mapping open, click on the Restrictions button. The VB.NET code displayed here will check whether the Dimension or Dimension code is blank and restrict this distribution.

 

Scenario 2 – One Analytical Distribution per Distribution and duplicate accounts.

The next easiest scenario to integrate is when we have the same account number on multiple distributions and each distribution has only one Analytical Distribution. For this scenario, we need to add the distribution sequence to our mapping. Depending on your data source, the value can come from the source or calculated using a global variable.

Data Source

We will use the same data source as scenario 1 with the addition of a sequence number for the distribution. We need to make sure that every row has a sequence number. If you are unable to add the distribution sequence to your source document, you can use this article as an example to calculate the sequence number within the map itself.

Mapping

We need to make one addition to our Add Distribution node by adding the distribution sequence.

Our Analytical Distribution mapping includes the addition of the distribution sequence. Everything else within this node remains the same.

Mapping Restriction

This will be the same restriction as used in scenario 1.

 

Scenario 3 – Multiple Analytical Distribution per Distribution and duplicate accounts.

The next easiest scenario to integrate is when we have the same account number on multiple distributions and there can be multiple Analytical Distributions for each payables distribution. With this scenario, we need to add the distribution sequence to our mapping and an analytical sequence. Depending on your data source, the value can come from a source column or calculated column using global variables as described in scenario 2.

Data Source

Here is our data source with the additional Analytical Distribution sequence number. I also separated the amounts into debit and credit columns to reduce the number of calculations needed for summing amounts on the distribution node.

 

Mapping

In the Add Distribution, we group on the document number and distribution sequence to create one distribution for our account or sequence. We will also sum the debit and credit amount to ensure the correct distribution amount aggregate value.

 

In the Analytics Distribution, we map the AASequence to the subledger assign lookup ID.

 

Mapping Restriction

This will be the same restriction as used in scenario 1.

 

That’s it. All three scenarios should successfully import if you have the right data available and the Analytical codes are set up correctly.

 

Troubleshooting

We will admit that troubleshooting issues regarding integrating AA transactions can be difficult. If something is not mapped correctly on the analytics distribution node, the most common error you will receive is “An Item with the same key has already been added”. Unfortunately, this error is being returned to SmartConnect from the Microsoft eConnect runtime and means eConnect has generated the same error multiple times. It usually means the Microsoft eConnect taProcessAnalytics Stored Procedure cannot validate the Analytics Distribution to the matching Payables Distribution. The way we troubleshoot this issue is to use SQL Profiler to capture the text of the calls being made to the eConnect procedures and executing those procedures within SQL Server Management Studio and execute them within SQL Management Studio. 

Another possible method to validate the mapping is to change the map destination to Dynamics GP – File and review the XML that is generated to verify the calculations and mappings are correct.

Here is a list of items to verify when looking at the generated XML.

  • Make sure the Document Number and Document Type are correct for the Analytics Distribution Node
  • Make sure the Analytics Distribution Node is after the Add distribution node and before the Create transaction node
  • Make sure the Distribution Sequence is correct for the Distribution node and the Analytics Distribution node.

     

Additional Considerations

A common question is whether we can provide the Analytical Distribution Alias and have eConnect pull the right values for the Analytical data. Unfortunately, this is not handled within eConnect so we can only provide the actual Analytical data with the integration.

Another scenario is where the Dimension Codes are all contained on the same line on the source document. If the data is in this format, it will require pivoting the rows so each Dimension Code is on its own line. If the Dimension Codes are not on their own line, we cannot process the file with SmartConnect.

RECENT POSTS


Use the REST Connector to parse nested Value Arrays
SmartConnect email task fails with error: An invalid character was found in the email header
Does SmartConnect import into Avalara Avatax software
Could not initialize a collection when launching SmartConnect
ERROR [HY000] [Microsoft][ODBC Excel Driver]General error Unable to open registry key when you open a SmartConnect map.

POPULAR POSTS


SSL Security error using OLEDB Connection
This implementation is not part of the Windows Platform FIPS validated cryptographic algorithms
Removed Part: /xl/vbaProject.bin part. (Visual Basic for Applications (VBA))
The server process could not be started because the configured identity is incorrect.
Using WINSCP and a task to upload or download files from an SFTP site

CATEGORIES

TAGS

Business Central CRM D365 Business Central Dynamics 365 dynamics crm Dynamics GP Dynamics NAV Econnect Employee Spotlight eone eOne News error Error Message Events Excel Excel Report Builder Extender Flexicoder GP integration Map Meet the Team Microsoft dynamics crm Microsoft Dynamics GP Navigation List Builder Office Relationships partners Popdock release Reporting SalesForce SalesForce.com SmartConnect SmartConnect.com SmartConnect Bootcamp SmartConnect Maps SmartConnect Office Hours SmartList SmartList Builder SmartPost SmartView SQL Tech Tuesday Templates training

Integrate & Automate without Any Code.

SmartList Data has Never Been Faster.

The Easiest Way to Report on GP Data.