Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Integration Manager Ge...
Microsoft Dynamics GP (Archived)

Integration Manager General Journal, source has credit & debit account on same row

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

Hi - I'm trying to write this integration, and I know our situation isn't unique, but I can't seem to find an answer elsewhere. Our import file has debit account, credit account, and amount on each row. The integration manager seems to expect debit account and amount on one row and offsetting credit account(s) and amount(s) on one or more separate rows. I believe I have to somehow split the input row into two journal entry detail rows in the IM. Anyone have an idea how that can be done?

Additionally, it would be good to group the input rows into subtotals by date, debit account, & credit account before posting so all the detail transactions don't end up in the GL system. I think that could be done by sorting and grouping in the source definitions, but please let me know if I'm wrong about that. 

Thanks for any assistance.

Mike Sedita

*This post is locked for comments

  • Verified answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Integration Manager General Journal, source has credit & debit account on same row

    Thanks. The data is not in excel but I can do the same with a sql query outside of IM and import it as text.

  • Verified answer
    L Vail Profile Picture
    L Vail 65,271 on at
    RE: Integration Manager General Journal, source has credit & debit account on same row

    Hi Mike,

    If your source data in Excel is similar to this (only with extra columns for date, etc.):

    JE DrAcctNumber CrAcctNumber DrAMT CrAMT
    1 000-1200-00 000-1101-00 350.00 350.00
    2 000-2000-00 000-1000-00 600.00 600.00

    I would do an Advanced ODBC Source and create a Query something like this

    Select
    JESources.JE, JESources.DRAcctNumber as Acct, JESources.DRAmt as Amt From JESources
    UNION
    Select JESources.JE, JESources.CRAcctNumber, -1* JESources.CRAmt from JESources

    Then, I would use the positive/negative number option. You can get the 'Header' source by building it off of this same Advanced ODBC Source. For subtotaling, you could also handle that with a Group in your Advanced ODBC Source.

    Kind regards,

    Leslie

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,269 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,198 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans