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

Notifications

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) / 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

I have the same question (0)
  • Verified answer
    Richard Whaley Profile Picture
    Richard Whaley 25,195 on at

    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.

  • Rijo Mathachan Profile Picture
    Rijo Mathachan 65 on at

    Thank you Richard.

  • Verified answer
    Mike Smith Profile Picture
    Mike Smith 6,840 on at

    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

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

    Thank you so much

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

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

#1
mtabor Profile Picture

mtabor 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans