web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
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

I have the same question (0)
  • Verified answer
    Mahmoud Saadi Profile Picture
    Mahmoud Saadi 32,738 on at

    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,

  • Ian Richardson Profile Picture
    Ian Richardson 4,150 on at

    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

  • Richard Whaley Profile Picture
    Richard Whaley 25,195 on at

    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.

  • Mahmoud Saadi Profile Picture
    Mahmoud Saadi 32,738 on at

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

    You now have plenty of options to choose from

  • Ian Richardson Profile Picture
    Ian Richardson 4,150 on at

    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

    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.

  • Suggested answer
    Richard Whaley Profile Picture
    Richard Whaley 25,195 on at

    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.

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

Introducing the 2026 Season 1 community Super Users

Congratulations to our 2026 Super Stars!

Meet the Microsoft Dynamics 365 Contact Center Champions

We are thrilled to have these Champions in our Community!

Congratulations to the April Top 10 Community Leaders

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

#1
Shravan Attelli Profile Picture

Shravan Attelli 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans