Entering journal entries manually takes time, particularly when the information already exists in a spreadsheet or another application.
Integration Manager provides a way to bring that information into Dynamics GP. A repeatable import can reduce re-entry, but the source file, transaction grouping, and field mapping need to be configured correctly.
Start with a small test file. Confirm the results in GP before using the integration for routine accounting work.
Key Takeaways
- Prepare consistent source data and define how rows belong to each journal entry.
- Configure header and detail queries to match the selected destination.
- Review imported entries and reconcile totals before posting.
Before You Build the Integration
Confirm your GP version, Integration Manager installation, available destination adapters, and permissions with your administrator.
The steps and mapping options can differ by adapter and configuration. This overview follows a text-file approach; have your GP partner confirm the settings for your environment.
Before proceeding:
- Use a test company with appropriate data and configuration.
- Confirm that a current backup and recovery process are available.
- Assign responsibility for reviewing the imported entries.
- Document the expected transaction count and debit and credit totals.
Review Dynamics GP user types, licensing, and access if the person configuring or running the integration cannot access the required functions.
1. Prepare Your Source File
Use consistent column headings, date formats, account formats, and amount conventions.
A basic journal-entry file needs information that identifies each transaction and its distribution lines.
| Information | Purpose |
|---|---|
| Source entry identifier | Distinguishes one journal entry from another |
| Transaction date | Identifies the date to map into GP |
| Reference | Describes the entry |
| Account number | Identifies the distribution account |
| Debit and credit amounts | Defines the amounts for each distribution |
| Line identifier, if needed | Distinguishes individual distribution lines |
Your source identifier does not necessarily become the GP journal-entry number. Confirm how the selected destination handles numbering.
If your file uses one signed amount column instead of separate debit and credit columns, document the sign convention and configure the mapping accordingly. Do not reuse mapping rules from a different file format without testing them.
Check the exported file for blank rows, altered account numbers, and unexpected date or amount formatting.
2. Create a New Integration
Open Integration Manager and create a new integration.
Give it a descriptive name, such as “Monthly GL Journal Import,” and document:
- The source file location.
- The intended GP company.
- The purpose of the entries.
- The file format and amount convention.
- Who owns the integration.
Keep a sample of the approved source format so future users know what the integration expects.
3. Define the Header and Detail Sources
Journal entries contain header information, such as the transaction date and reference, plus distribution lines containing accounts and amounts.
Configure source queries for those levels. Depending on your design, the queries may read the same file or separate files.
For a CSV source, select the appropriate text-source option, choose the comma delimiter, and indicate whether the first row contains column names. Preview the data to confirm that columns and values are interpreted correctly.
Microsoft’s General Journal guidance describes header and detail sources as separate parts of the integration.
4. Configure Grouping and Query Relationships
The header query should return one record for each intended journal entry. The detail query should return the distribution lines belonging to it.
Use a reliable transaction identifier and the necessary related fields. Grouping by date alone can combine entries that should remain separate.
Then configure the relationship between the header and detail queries. Preview both queries and verify that each header connects to the correct lines.
Microsoft’s GL integration example demonstrates grouped header queries and relationships between transaction and distribution data. Its example includes Analytical Accounting, which requires additional mappings.
5. Select the Destination and Map Fields
Choose the appropriate General Journal or GL transaction destination for your installed adapter.
Map the required header and distribution fields, including:
- Batch information.
- Transaction date.
- Reference.
- Account number.
- Debit and credit amounts, or the destination’s equivalent amount field.
Confirm any default values, prompts, numbering rules, and options for creating batches.
If your organization uses Analytical Accounting, include its requirements in the design. Review segments and dimensions in Microsoft Dynamics to prepare for that discussion.
Need Help Configuring Your GP Import?
Bring a sample source file, the expected journal entries, and any error messages. LBMC Technology Solutions can review the setup through its Dynamics GP support services.
6. Run a Small Test
Confirm that you are connected to the intended test company, then run a small, representative file.
Include more than one entry so you can check that grouping keeps transactions separate. Review the integration results and investigate errors before expanding the test.
If a run partly succeeds, identify what was created before rerunning it. Otherwise, a retry may create duplicate entries.
7. Review the Entries Before Posting
Compare the imported results with your approved source:
- Number of journal entries and distribution lines.
- Transaction dates and references.
- Account numbers.
- Debit and credit totals.
- Batch assignment.
- Required reporting or Analytical Accounting values.
Confirm that each entry balances and follows your organization’s review and approval process before posting.
Record the results so the test can be repeated after changes to the file or integration.
Common Integration Manager Import Problems
“Account Number Is a Required Field”
Check whether the account field is mapped and whether every intended distribution contains a value.
Microsoft also identifies blank source rows as a cause of this error. Inspect the query preview as well as the file itself.
Separate Entries Are Combined
Review the header grouping and query relationships. Confirm that your identifying fields distinguish every intended transaction.
Debit or Credit Amounts Are Incorrect
Compare the source convention with the destination rules. A file containing separate debit and credit columns needs a different mapping review from one containing signed amounts.
An Import Partly Completes
Review the results in GP before retrying. Document which transactions succeeded, which failed, and how the remaining records will be processed.
When Should You Consider Another Integration Approach?
Integration Manager may fit a repeatable file-import process. If the requirement involves ongoing application-to-application communication, evaluate the available alternatives.
Dynamics GP Service Based Architecture is one method to discuss with your developer. The right choice depends on the operations required, processing frequency, volume, and maintenance needs.
Integration Manager FAQs
Can the header and detail queries use the same file?
Yes, a design can use one file for both queries. Grouping and relationships must still produce the correct transaction structure.
Can we reuse the integration each month?
A repeatable integration requires a consistent file format, documented settings, and a review process. Retest when source columns, mappings, or configuration change.
Does a successful import mean the entries are ready to post?
No. Review and reconcile the results before following your normal posting approval process.
Ready to Reduce Manual Journal Entry Work?
Tell us where your journal-entry data comes from and how your team processes it today. We can discuss an import approach and the validation needed to support it.
