Steps to set up a GL Transaction Integration with Analytical Accounting in Microsoft Dynamics GP
This document will walk you through the necessary steps to set up a GL Transaction integration that includes Analytical Accounting mappings using Integration Manager for Microsoft Dynamics GP. This article will cover two common setup scenarios for importing Analytical Accounting information with a GL Transaction.
Applies to: Microsoft Dynamics GP
Original KB number: 2687958
Business scenario
Many companies use the Analytical Accounting module to help track their financial information. Many users also need to import transaction information into Dynamics GP which will include the AA information. Previous to Dynamics GP and Integration Manager 10.0, this was not possible as Integration Manager did not support AA. Those users would have to manually add the AA information after the transactions were imported, a process which could be very time consuming (and outlined in KB 897742).
How to move from Multidimensional Analysis to Analytical Accounting
Background information
Starting with Microsoft Dynamics GP/Integration Manager 10.0, Integration Manager now utilizes eConnect for select destination adapters (formerly the 'SQL Optimized' destination adapters). When using the eConnect destination adapters, it is now possible to import the following transaction types with Analytical Accounting information:
- GL Transactions
- Sales Transactions
- Inventory Transactions
- POP Receivings Transactions
In this article, we will look at two common scenarios for integrating GL Transactions that include AA information.
Activate Analytical Accounting
- In Dynamics GP, click on Tools under Microsoft Dynamics GP, point to Setup, point to Company, point to Analytical Accounting and click Setup. This will open the Analytical Accounting Setup Wizard window.
- Ensure that Create Default Record is checked and click Next, then click Finish.
- Go back to the Analytical Accounting Setup Wizard window again. (See step 1.) The Create Default Record option should now be grayed out.
- Now check Activate Analytical Accounting option and click Next, then Finish.
- Click Yes to proceed with the activation.
- Click OK when it is finished.
Set up sample AA Dimensions and Dimension Codes
First, create three new test accounts that we will use for AA:
In Microsoft Dynamcis GP, click on Cards, point to Financial and click Account.
Create the following accounts:
Account Description Category 999-9999-01 AA Test Account 1 Cash 999-9999-02 AA Test Account 2 Cash 999-9999-03 AA Test Account 3 Cash Accept all other defaults.
Create the AA Transactions Dimension:
In Microsoft Dynamcis GP, click on Cards, point to Financial, point to Analytical Accounting and click Transaction Dimension.
Create the Trx Dimension by entering ELECTRONICS for the Trx Dimension.
Select for the Data Type Alphanumeric.
Enter Electronics for the Description 1 field.
Click Save and close the Transaction Dimension Maintenance window.
Create the AA Transaction Dimension Codes:
In Microsoft Dynamics GP, click on Cards, point to Financial, point to Analytical Accounting, and click Transaction Dimension.
Choose the Dimension ELECTRONICS.
Enter XBOX for the Trx Dimension Code.
Enter XBOX Hardware/Accessories for the Description.
Select the Lookup and choose ELECTRONICS for the Linked to Node.
Click Save.
Keep the window open, now enter TV for the Trx Dimension Code.
Enter Televisions for Description 1.
Select the Lookup and choose ELECTRONICS for the Linked to Node.
Click Save.
Enter STEREO for the Trx Dimension Code.
Enter Stereo Equipment for Description 1.
Select the Lookup and choose ELECTRONICS for the Linked to Node.
Now grant access to the Dimension Codes:
In Microsoft Dynamics GP, click on Tools, point to Setup, point to Company, point to Analytical Accounting and click User Access.
For Trx Dimension, choose the Dimension ELECTRONICS.
Select the Trx Dimension Code XBOX, then click the Distribute and Adjust check boxes at the bottom of the window to enable the code for all users.
Click Save.
Repeat steps b and c for the STEREO and TV" Dimension Codes.
Create the Accounting Class to link our test accounts we created to the AA Dimensions:
In Microsoft Dynamics GP, click on Cards, point to Financial, point to Analytical Accounting and click Accounting Class.
Enter the Class ID ELECTR_CLASS.
Enter Electronics Account Class for the Description 1 field.
In the Grid, you should see the ELECTRONICS Trx Dimension. Change the Analysis Type to Optional.
Click the Accounts in the bottom right corner.
You may be prompted to save changes to the Accounting class. Click Save.
Scroll down in the list of accounts and find the three accounts you created in step 1.
Mark the Link checkbox next to each account 999-9999-01, 999-9999-02 and 999-9999-03.
- You may be prompted with a message saying "This change will affect un-posted transactions and adjustments, if any. Do you want to continue?"
- Click Continue.
Close the Account Class Link window to return back to the Accounting Class Maintenance window.
Click Save and close the window.
Build the Integrations - GL Transaction (with Sequence required)
Example scenario:
One of the Analytical Accounting enabled accounts appears more than once in the distributions. Also, the distribution amount is being split out to multiple Dimension Codes. Because the same distribution account appears more than once, we must map the Line Item Sequence value on the GL Distributions so the AA information can be linked to the correct distribution line/amount.
Furthermore, since the distribution amount for a single line is being split out into multiple Dimension Codes, we must also map the "Assign ID" field for the Analytics Dimension.
To support these requirements, create a Tab Delimited text file for the header and distributions source queries with the following data (include the column headings as shown):
JournalNum TrxDate Account Amount Sequence 10000 4/12/2017 000-1100-00 500 500 10000 4/12/2017 000-1200-00 -500 1000 10001 4/12/2017 999-9999-01 16200 500 10001 4/12/2017 000-1100-00 -16200 1000 10001 4/12/2017 999-9999-01 2000 1500 10001 4/12/2017 000-1200-00 -2000 2000 Save the file as a Tab Delimited text file (*.TXT). For this example, name the file "AA GLTrx-with Seq.txt".
Create a second Tab Delimited source file that contains the AA Distribution information (include the column headings as shown):
JournalNum Account Amount Dimension DimensionCode CodeAmount AAAsignID Sequence 10001 999-9999-01 16200 ELECTRONICS XBOX 8000 1 500 10001 999-9999-01 16200 ELECTRONICS TV 7000 2 500 10001 999-9999-01 16200 ELECTRONICS STEREO 1200 3 500 10001 999-9999-01 2000 ELECTRONICS XBOX 1100 1 1500 10001 999-9999-01 2000 ELECTRONICS TV 600 2 1500 10001 999-9999-01 2000 ELECTRONICS STEREO 300 3 1500 Note
The AAssignID value will be used since we are splitting the distribution amount across multiple Dimension codes. The Sequence value is required because the distribution account 999-9999-01 appears on more than one distribution line, therefore the Sequence value is required to link the AA information to the correct distribution line.
Save the file as a Tab Delimited text file (*.TXT). For this example, name the file AA GLTrx-with Seq AA Dimension.txt.
Open Integration Manager and click on New Integration. Give it the name GL Transaction - AA with Sequence".
Create the Header Source Query
Click on Add Source, click on Text, then click on Define New Text.
Give the source query the name GL Header and browse out to the first file you created named AA GLTrx-with Seq.txt.
Choose Tab as the delimiter and check the box First Row Contains Column Names.
Since we are using a single source file for the Header and Distribution lines, we need to set up the Grouping on the header fields. Click on the Sorting tab.
Add JournalNum and TrxDate to the Group By fields list, then click OK to save the query. Click OK again on Warning stating there are fields named as Show that is not in the Group By list. This is expected.
Right-click on the GL Header query and make sure you can preview the data as follows:
Create the Distribution Source Query
Click on Add Source, click on Text, then click on Define New Text.
Give the source query the name GL Distributions and again browse out to the first file you created named AA GLTrx-with Seq.txt.
Choose Tab as the delimiter and check the box First Row Contains Column Names.
Click OK to save the query. You will receive a message stating that you must set up Query Relationship. Click OK to dismiss the dialog. We will set that up later once all the queries are created.
Right-click on the GL Distributions query and make sure you can preview the data as follows:
Create the AA Dimension Source Query
Click on Add Source, click on Text, then click on Define New Text.
Give the source query the name GL AA Dimension" and browse out to the second file you created for the AA information named AA GLTrx-with Seq AA Dimension.txt.
Choose Tab as the delimiter and check the box First Row Contains Column Names.
Click OK to save the query.
Right-click on the GL AA Dimension query and make sure you can preview the data as follows:
Create the AA Dimension Code Source Query
Click on Add Source, click on Text, then click on Define New Text.
Give the source query the name GL AA Dimension Code and browse out to the second file you created for the AA information named AA GLTrx-with Seq AA Dimension.txt.
Choose Tab as the delimiter and check the box First Row Contains Column Names.
Click OK to save the query.
Right-click on the GL AA Dimension Code query and make sure you can preview the data as follows:
Set up the Query Relationships
Double-click on Query Relationships.
Since the Journal Number is the key field that differentiates one transaction from the next, this key field should go all the way through the Query relationship chain.
To link the AA information, since the AA Dimension information is linked per distribution line, next we need to establish a link between the GL Distributions query and the "L AA Dimension query. As stated in the overview, a given GL account may appear more than once in a single transaction, there we must use the Sequence field to link the AA Dimension to the correct distribution line.
Continuing down the relationship, the AA Dimension Codes are linked back to a given AA Dimension. In this example, we need to continue linking with the Sequence field and also add the DimensionCode field. At this point, all the appropriate relationships have been established so your Query Relationship window should look like the following:
Add the Destination Adapter
Click on Add Dest, expand Microsoft Dynamics GP eConnect, click on Financial and select GL Transaction.
In the Server Name field, enter in the name of your SQL Server instance (not a DSN name) and the company database name.
Define the field mappings
Double-click on Destination Mapping to open the field mapping window.
For GL Transactions with AA information, the Journal Entry # is REQUIRED. This field cannot be defaulted in! Some advanced users will use a VBScript on the Journal Entry# field to get the next number, but it does have to be supplied in the mapping otherwise the AA information cannot be linked to the transaction correctly.
Provide a constant for the Batch ID and Reference fields.
Click on the Entries folder. Map the Account Number, Debit/Credit Amount and Sequence Line to the fields from the GL Distributions query.
Note
The Sequence Line field is required in this case since the GL Account may appear more than once within the same GL Transaction. Without this field mapped, the AA information cannot be linked to the correct distribution.
Click on the Analytics folder. Map the Assign ID and Amount field from the GL AA Dimension source query as follows:
Note
The Assign ID field is required in this case since we are splitting out the distribution amount to more than one AA Dimension Code.
Optional: If the percentages are known instead of the Amounts, you can map the Assigned Percent field instead of the Amount field. You do not need to map both!
Click on the Dimensions folder. In this example, we are only using Alphanumeric Dimension Codes, so we will map the Transaction Dimension and Transaction Dimension AlphaNumeric" from the GL AA Dimension Code source query.
Map the Transaction Dimension to the Dimension field from the source query and Transaction Dimension AlphaNumeric to the DimensionCode field from the source query:
The field mappings are now complete! Save the Integration.
Go ahead and Run the integration now to bring in the two Journal Entries.
Verify the Transaction Integrated as expected.
The first Journal Entry does not have any AA information, so we will look at the second transaction, Journal Number 10001.
To inspect the AA distributions, click on the first distribution line, then click the Analytical Accounting button. We should see the distribution amount of $16,200 has been split across 3 different AA Dimension Codes as follows:
You can use the VCR buttons at the bottom of the window to navigate to the second Analytics distribution where you will see that the distribution amount of $2000 has also been split out across the 3 AA Dimension Codes.
Build the Integrations - GL Transaction (No Sequence required)
Example scenario:
The Analytical Accounting enabled accounts do NOT appear more than once in the distributions. The AA information represents a one-to-one relationship in the source data. This means that a single distribution line links to only one AA Dimension which has only one AA Dimension Code. Because it's one-to-one, we do not need a Sequence Number or the Assign ID fields. For this example scenario, we can use a single source file.
To support these requirements, create a Tab Delimited text file (*.TXT) with the following data:
JournalNum TrxDate Account Amount Dimension DimensionCode 10003 4/12/2017 000-1100-00 500 10003 4/12/2017 000-1200-00 -500 10004 4/12/2017 999-9999-01 8000 ELECTRONICS XBOX 10004 4/12/2017 999-9999-02 7000 ELECTRONICS TV 10004 4/12/2017 999-9999-03 7000 ELECTRONICS STEREO 10004 4/12/2017 000-1100-00 -16200 10005 4/12/2017 999-9999-01 1100 ELECTRONICS XBOX 10005 4/12/2017 999-9999-02 600 ELECTRONICS TV 10005 4/12/2017 999-9999-03 300 ELECTRONICS STEREO 10005 4/12/2017 000-1100-00 -2000 Save the file as a Tab Delimited text file (*.TXT). For this example, name the file "AA GLTrx-No Seq.txt".
Open Integration Manager and click on New Integration. Give it the name "GL Transaction - AA without Sequence".
Create the Header Source Query.
Click on Add Source, click on Text, then click on Define New Text.
Give the source query the name GL Header - No Seq and browse out to the source file you created named AA GLTrx-No Seq.txt.
Choose Tab as the delimiter and check the box First Row Contains Column Names.
Since we are using a single source file, we need to setup the Grouping on the header fields. Click on the Sorting tab.
Add JournalNum and TrxDate to the Group By fields list, then click OK to save the query. Click OK again on Warning stating there are fields named as Show that is not in the Group By list. This is expected.
Right-click on the GL Header - No Seq query and make sure you can preview the data as follows:
Create the Distribution Source Query.
Click on Add Source, click on Text, then click on Define New Text.
Give the source query the name GL Distributions - No Seq and again browse out to the source file you created named AA GLTrx-No Seq.txt.
Choose Tab as the delimiter and check the box First Row Contains Column Names.
Click OK to save the query. You will receive a message stating that you must setup Query Relationship. Click OK to dismiss the dialog. We will set that up later once all the queries are created.
Right-click on the GL Distributions - No Seq query and make sure you can preview the data as follows:
Create the AA Dimension Source Query
Click on Add Source, click on Text, then click on Define New Text.
Give the source query the name GL AA Dimension - No Seq and browse out to the source file you created named AA GLTrx-No Seq.txt.
Choose Tab as the delimiter and check the box First Row Contains Column Names.
Click on the Filter tab and add a restriction with the following settings:
- Column: Dimension
- Operator: <>
- Value: 0
Click And Into Criteria.
Click OK to save the query.
Right-click on the GL AA Dimension - No Seq query and make sure you can preview the data as follows:
Create the AA Dimension Code Source Query
This query will be setup identical to the Dimension query we just created since this example is a one-to-one sample for the distribution/dimension/dimension code relationship. Click on Add Source, click on Text, then click on Define New Text.
Give the source query the name GL AA Dimension Code - No Seq and browse out to the source file you created named AA GLTrx-No Seq.txt.
Choose Tab as the delimiter and check the box First Row Contains Column Names.
Click on the Filter tab and add a restriction with the following settings:
- Column: Dimension
- Operator: <>
- Value: 0
Click And Into Criteria.
Click OK to save the query.
Right-click on the GL AA Dimension Code - No Seq query and make sure you can preview the data as follows:
Set up the Query Relationships.
- Double-click on Query Relationships.
Since the Journal Number is the key field that differentiates one transaction from the next, this key field should go all the way through the Query relationship chain.
To link the AA information, since this example demonstrates a one-to-one relationship between the Distribution/Dimension/Dimension Code, the relationship setup is fairly straightforward. All we need to add is a link to the Account field since the AA information pertains to a particular account number.
Continuing down the relationship, the AA Dimension Codes are linked back to a given AA Dimension. In this example, we need to continue linking with the Sequence field and also add the DimensionCode field. At this point, all the appropriate relationships have been established so your Query Relationship window should look like the following:
- Double-click on Query Relationships.
Add the Destination Adapter.
Click on Add Dest., expandMicrosoft Dynamics GP eConnect, click on Financial and select GL Transaction.
In the Server Name field, enter in the name of your SQL Server instance (not a DSN name) and the company database name.
Define the field mappings.
Double-click on Destination Mappings to open the field mapping window.
For GL Transactions with AA information, the Journal Entry# is REQUIRED. This field cannot be defaulted in! Some advanced users with use a VBScript on the Journal Entry# field to get the next number, but it does have to be supplied in the mapping otherwise the AA information cannot be linked to the transaction correctly.
Provide a constant for the Batch ID and Reference fields.
Click on the Entries folder. Map the Account Number and Debit/Credit Amount to the fields from the GL Distributions - No Seq query.
Click on the Analytics folder. Map the Amount field from the GL AA Dimension - No Seq source query as follows:
Optional: If the percentages are known instead of the Amounts, you can map the Assigned Percent field instead of the Amount field. You do not need to map both!
Click on the Dimensions folder. In this example, we are only using Alphanumeric Dimension Codes, so we will map the Transaction Dimension and Transaction Dimension AlphaNumeric from the GL AA Dimension Code - No Seq source query.
Map the Transaction Dimension to the Dimension field from the source query and Transaction Dimension AlphaNumeric to the DimensionCode field from the source query:
The field mappings are now complete! Save the Integration.
Go ahead and run the integration now to bring in the two Journal Entries.
Define the field mappings.
The first Journal Entry does not have any AA information, so we will look at the second transaction, Journal Number 10004.
To inspect the AA distributions, click on the first distribution line, then click the Analytical Accounting button. We should see the distribution amount of $8000 assigned to the XBOX dimension code as follows:
You can use the VCR buttons at the bottom of the window to navigate to the second Analytics distribution where you will see that the distribution amount of $7000 has been assigned to the TV Dimension Code.
Lastly, the 3rd AA distribution has the amount of $1200 assigned to the STEREO dimension code.
You can also view Journal Entry 10005 in the same way to verify that the distributions have been assigned to the AA Dimension Codes as expected.