Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / SQL Question - trying ...
Microsoft Dynamics GP (Archived)

SQL Question - trying to link GL to PM with notes from SY

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

Hi,

I'm new to GP but have been using SQL for the past 12 years - I'm currently trying to learn the table structures in order to write reports etc....mainly using ODBC into Excel.

I've been trying to construct a view that will show all open and historical GL transactions with an outer join to PM (all records from GL but only PM records where the GL line originates from PM) I'd also like to join SY03900 to PM so that I can see PM notes.

I'm trying to join GL30000 to PM30200 like this...

from GL30000
left inner join PM30200 on GL30000.ORTRXSRC = PM30200.TRXSORCE


 ....but I'm getting some strange result - are these the correct tables for doing this??

(I'll assume at this stage that joining SY to PM will be straight forward as I already have this working on it's own.....I'm also assuming that I'd use GL20000 and PM20000 for open transactions).

Thanks,

Darren

*This post is locked for comments

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: SQL Question - trying to link GL to PM with notes from SY

    Hi Mahmoud,

    Thanks for the advice - I managed to find some code on the net that I was able to modify.

    The table structure seems quite complex and not very logical - the links that you posted have been very useful!

    thanks,

    Darren

  • Suggested answer
    Mahmoud Saadi Profile Picture
    Mahmoud Saadi 32,738 on at
    RE: SQL Question - trying to link GL to PM with notes from SY

    Initially, allow me to welcome you in Dynamics GP Community. This is the best place to look for regarding such inquiries and requests

    Linking AP to GL is much more complex that just a join condition, there are multiple factors to be considered. Your 12 years of experience with SQL will definitely give you an edge while checking what others have already done regarding AP-GL, here is a a very neat script written by Siva Kumar, it could be the basis for your requirement SQL Script for AP-GL

    Furthermore; for the table structure, you may want to take a look at the following legendary article by Leslie Vail on Tables Names and Data Flow

    Please never hesitate to share any further inquiries,

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,235 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans