Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / smart list crossing da...
Microsoft Dynamics GP (Archived)

smart list crossing databases

(0) ShareShare
ReportReport
Posted on by Ian Richardson 4,150

hi folks

I want to make a smart list that will cross databases and pull all JE's for account ranges.

Each database obviously has a different set of account indexes.  I'm using account master and transaction amounts work.  I'm finding that smart list is missing transactions / accounts.  I am thinking that is because of the differing indices.  If I say I want all entries for all companies where account = 1000.  Each dbase will have a different account index for what 1000 means on the data table.

Is such a smart list possible, and if so which tables and how to I link them?

thanks!

*This post is locked for comments

  • Suggested answer
    Richard Whaley Profile Picture
    Richard Whaley 25,195 on at
    RE: smart list crossing databases

    If you need to sort by accounts, use the GL00105 or the Account String table.  Other tables might sort by the account index et cetera.

  • Mahmoud Saadi Profile Picture
    Mahmoud Saadi 32,738 on at
    RE: smart list crossing databases

    Ian,

    For me, I would stick to the view suggestion as I have explained above. Just change the "Two" company name with your company name for each part, the union will ensure to retrieve the same data set from several company database. One added value above the builder is that you can get the company name, so that you can further search at.

    I have already created several views across several GP Companies using the method included above.

    Please never hesitate to share any of your further inquiries.

  • Ian Richardson Profile Picture
    Ian Richardson 4,150 on at
    RE: smart list crossing databases

    thanks to you both.  I have tried builder and it did not work.  As originally posted I think because I included account master and linked on index.  I need a way to pull particular accounts, or ideally particular segments in the account string.

    What tables would be suggested via builder?

    thanks!

  • Mahmoud Saadi Profile Picture
    Mahmoud Saadi 32,738 on at
    RE: smart list crossing databases

    I absolutely agree with Mr. Richard suggestion, Smart list builder could absolutely consider Multi sources.

    You now have plenty of options to choose from

  • Richard Whaley Profile Picture
    Richard Whaley 25,195 on at
    RE: smart list crossing databases

    You can use SmartList Builder and create a Multi-Company Smartlist without creating views.  Views, while great, often need to be maintained during upgrades.

    If you need help with SLB, we have a great book on that product on our web site.

  • Ian Richardson Profile Picture
    Ian Richardson 4,150 on at
    RE: smart list crossing databases

    an excellent answer as usual.

    We are GP2010 so its builder.

    We have 25 companies (dbases) so its going to be quite the sql view.  I assume the logic is the same just more tedious for my poor IT guy that has to build the query against each dbase.

    thanks for the assistance

  • Verified answer
    Mahmoud Saadi Profile Picture
    Mahmoud Saadi 32,738 on at
    RE: smart list crossing databases

    To simplify the task, build up a view on the database level includes primarily two parts; each of the parts should include the company id, company name, account index, account number, journal entry number (work), debit and credit. To retrieve one set for both databases, use the union function.

    The tables used could be;

    • GL00100
    • GL00105
    • GL10001
    • CM00100 (a company table from which you could retrieve the company id)
    • Dynamics..SY01500 (Company Master table)


    SELECT E.CMPANYID,E.CMPNYNAM, A.ACTINDX,A.JRNENTRY, B.ACTNUMST, A.DEBITAMT, A.CRDTAMNT FROM GL10001 AS A
    LEFT OUTER JOIN TWO..GL00105 AS B
    ON A.ACTINDX = B.ACTINDX
    LEFT OUTER JOIN TWO..GL00100 AS C
    ON B.ACTINDX = C.ACTINDX
    CROSS JOIN ( SELECT DISTINCT CMPANYID FROM TWO..CM00100 ) AS D
    LEFT OUTER JOIN DYNAMICS..SY01500 AS E
    ON D.CMPANYID = E.CMPANYID

    Once the view is created, you can use smart list builder to add it as a smart list into your Dynamics GP. Or, if you are on GP2013 SP2, you could use smart list designer.

    Hope this helps,

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,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans