Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Smartlist Account tran...
Microsoft Dynamics GP (Archived)

Smartlist Account transactions Query

(0) ShareShare
ReportReport
Posted on by Rijo Mathachan 65

Hello All,

I am pretty much new to GP.  I am trying to convert Some of smartlist to SSRS. I started with Account Transactions. I created a query for Account transactions based on the Constant SMARTLIST_OBJECTTYPE_ACCOUNTTRANSACTIONS.

Constant SMARTLIST_OBJECTTYPE_ACCOUNTTRANSACTIONS

Tables
GL10001 - GL_TRX_LINE_WORK
GL20000 - GL_YTD_TRX_OPEN
GL30000 - GL_Account_TRX_HIST
MC40200 - MC_Currency_SETP
GL00100 - GL_Account_MSTR
GL10000 - GL_TRX_HDR_WORK

Joins
GL10001 left-outer join to MC40200
GL10001 left-outer join to GL00100
GL10001 left-outer join to GL10000
GL20000 left-outer join to GL00100
GL20000 left-outer join to GL10000
GL30000 left-outer join to GL00100

The Query i created mentioned below

SELECT     *

FROM         GL20000 LEFT OUTER JOIN

                      GL10000 ON GL20000.JRNENTRY = GL10000.JRNENTRY LEFT OUTER JOIN

                      GL30000 LEFT OUTER JOIN

                      GL00100 ON GL30000.ACTINDX = GL00100.ACTINDX ON GL20000.ACTINDX = GL00100.ACTINDX RIGHT OUTER JOIN

                      GL10001 ON GL10000.JRNENTRY = GL10001.JRNENTRY AND GL10000.BACHNUMB = GL10001.BACHNUMB AND

                      GL00100.ACTINDX = GL10001.ACTINDX

 

 

Is this correct (joins)? Do I need to do anything else?  

please  give me some helpful notes to add more parameters to this query?

Thanks and regards

Rijo

*This post is locked for comments

  • Rijo Mathachan Profile Picture
    Rijo Mathachan 65 on at
    Re: Smartlist Account transactions Query

    Thank you Mike. You are right , I found Some useful views.

    Thank you so much

  • Verified answer
    Mike Smith Profile Picture
    Mike Smith 6,840 on at
    Re: Smartlist Account transactions Query

    You may want to start with the existing SQL Views that are in the system as this will save considerable amounts of time over recreating what is already there.

    If you look at the Views under each company database, you will see View names that correspond to the related SmartList in GP.

  • Rijo Mathachan Profile Picture
    Rijo Mathachan 65 on at
    Re: Smartlist Account transactions Query

    Thank you Richard.

  • Verified answer
    Richard Whaley Profile Picture
    Richard Whaley 25,195 on at
    Re: Smartlist Account transactions Query

    I dont think Joins are what you want between the 10000, 20000, and 30000 tables.  You want to create a Union between these three tables to provide one all inclusive table.

    Try:

    Create View All_GL_Transactions

    Select.......from GL10000

    Union All

    Select.....from GL20000

    Union All

    Select....from GL30000

    Go

    Then write your SSRS report against that view.  The 10000 has unposted GL transactions,m 20000 has posted current year gl trx, while 30000 has posted prior year transactions.

    Note that the list of fields in each select statement above (....) MUST be identical.  You cannot have fields from one table that are not matched in other tables.

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