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

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / table info request
Microsoft Dynamics GP (Archived)

table info request

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

HI folks

im being lazy today and hoping a nice forum poster will just know this

my user wants a report that among other things has the journal entry number and the a/p batch ID on it.

JE number is going to come from GL20000.  Where is that a/p batch id hiding?  If in a/p what is my link in crystal from gl20000?

thanks much!

ian

*This post is locked for comments

I have the same question (0)
  • Richard Wheeler Profile Picture
    Richard Wheeler 75,848 Moderator on at
    RE: table info request

    You would have to use the ORDOCNUM link batch to the payables document and then the batch ID would be on the payables document record.

    select * from two..gl20000 GL

    INNER JOIN TWO..PM30200 PM ON GL.ORDOCNUM = PM.DOCNUMBR

    where sourcdoc ='PMTRX'

    This should get you started.

  • Verified answer
    Victoria Yudin Profile Picture
    Victoria Yudin 22,768 on at
    RE: table info request

    Ian,

    I would probably do this in SQL rather than directly in Crystal because you need to union data from 2 payables tables. Here is the SQL for this:

    select pm.BACHNUMB [PM Batch Number], gl.*

    from GL20000 gl

    inner join

    (select VCHRNMBR, DOCTYPE, BACHNUMB

    from PM30200

    union

    select VCHRNMBR, DOCTYPE, BACHNUMB

    from PM20000) pm

    on gl.ORCTRNUM = pm.VCHRNMBR and gl.ORTRXTYP = pm.DOCTYPE

    Note that this will only include transactions that were entered in the PM (Payables Management) module.  

  • Ian Richardson Profile Picture
    Ian Richardson 4,150 on at
    RE: table info request

    our VAR's third party product includes a nifty way to publish Crystal reports to GP so users can run them from within GP hence the desire to use Crystal.  I had thought that i'm going to need to use two a/p tables for paid and unpaid.  I was even wondering if  I may need to use two G/L tables for open year historical year.

  • Verified answer
    Victoria Yudin Profile Picture
    Victoria Yudin 22,768 on at
    RE: table info request

    Ian,

    I am a big fan of Crystal myself, however for complicated table joins and unions I prefer to create a SQL view or stored procedure first, then point Crystal to the view or stored procedure.

    Getting a union of the GL20000 and GL30000 data is probably a good idea, as well. :-)

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

News and Announcements

Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft

Quick Links

Responsible AI policies

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

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans