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) / GL10001 Relationship w...
Microsoft Dynamics GP (Archived)

GL10001 Relationship with RM20101

(0) ShareShare
ReportReport
Posted on by Community Member

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

I have the same question (0)
  • Richard Wheeler Profile Picture
    Richard Wheeler 75,848 Moderator on at
    RE: GL10001 Relationship with 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.

  • Community Member Profile Picture
    Community Member on at
    RE: GL10001 Relationship with RM20101

    No.

    I need to be able to finds records in GL10001 and RM20101

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,848 Moderator on at
    RE: GL10001 Relationship with RM20101

    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.

  • Community Member Profile Picture
    Community Member on at
    RE: GL10001 Relationship with RM20101

    Running this query against database does not return any records.

    Running it against TWO returns some records.

  • Verified answer
    Richard Wheeler Profile Picture
    Richard Wheeler 75,848 Moderator on at
    RE: GL10001 Relationship with RM20101

    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.

  • Community Member Profile Picture
    Community Member on at
    RE: GL10001 Relationship with RM20101

    It doesn't resolve my issue but I want to give you credit for your efforts in trying to help.  thanks

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,848 Moderator on at
    RE: GL10001 Relationship with RM20101

    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'

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…

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