How are these two tables connected to each other? I don't see any common column between these two tables except for the BACHNUMB.
I want to pick one record from GL10000 and then, based on this record, find all records in RM20101.
*This post is locked for comments
The Gl10000 is the work GL transaction table. You will need to first join it to the GL10001 table to get the corresponding distribution records.
Using the TWO company I entered and posted a transaction with a document number of SALES00000001003. I then ran this query and it returned the results for which you seek. All the distributions records corresponding to this transaction. The records in the GL100001 table match those in the RM20101 table.
SELECT *
FROM GL10000 T1
INNER JOIN GL10001 T2 ON T1.JRNENTRY=T2.JRNENTRY
INNER JOIN RM20101 T3 ON T2.ORDOCNUM=T3.DOCNUMBR
WHERE T2.ORDOCNUM = 'SALES00000001003'
It doesn't resolve my issue but I want to give you credit for your efforts in trying to help. thanks
We do not have too many clients who only post to the GL. I did try it on a few that do and they all returned records. I would set up a test company and enter a receivables invoice and post it. They run this query. I assume what you are trying to accomplish is to match your unposted AR financial batches against open AR documents that were the result of posting AR batches.
Running this query against database does not return any records.
Running it against TWO returns some records.
SELECT *
FROM TWO..GL10001 T1
INNER JOIN TWO..RM10101 T2 ON T1.ORCTRNUM=T2.DOCNUMBR AND T1.SQNCLINE=T2.SEQNUMBR
INNER JOIN TWO..RM20101 T3 ON T2.DOCNUMBR=T3.DOCNUMBR
This will get you one record for each distribution and the RM20101 record will duplicate.
No.
I need to be able to finds records in GL10001 and RM20101
I believe the table for which you seek is the RM10101 not the RM20101. The RM20101 is the RM Open Transaction table. The RM10101 table contains the distributions corresponding to those transactions. If you chose to not post trough the GL when you post your receivables these transactions will then reside in the GL10000 and GL10001 tables. It would be best to them post these ledger transactions and use the Originating Transaction fields to link the GL20000 records to the RM20100 table. Just look for GL20000.OR????? fields.
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,253
Super User 2024 Season 2
#2
Martin Dráb
230,188
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (