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
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. :-)
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.
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.
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.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,280
Super User 2024 Season 2
#2
Martin Dráb
230,235
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (