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

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Help joining tables (n...
Microsoft Dynamics GP (Archived)

Help joining tables (no duplicates)

(0) ShareShare
ReportReport
Posted on by painterisaac 3,015

I need help writing a SQL join that does not produce duplicates. I need to combine these three tables. Your help will be greatly appreciated seeing as I can use your example as a future reference of learning.

View: AccountTransactions

View: customers

View: ReceivablesTransactions

I am using report builder 2.0. GP 2010.

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    Richard Whaley Profile Picture
    Richard Whaley 25,195 on at

    What type of Account Transactions?  GL transactions?  and do you want to sort by customer or transaction?

    here is one script keying off of the customer:

    SELECT DISTINCT * FROM RM00101 C LEFT OUTER JOIN RM20101 T ON C.CUSTNUM=T.CUSTNUM

    If by account transactions you mean the GL transactions for the receivable, then do another Left Outer Join  on the RM distributions table, joining on the transaction IDs.

  • Verified answer
    Mahmoud Saadi Profile Picture
    Mahmoud Saadi 32,738 on at

    Referring to Ms. Victoria's Blog, there is a script for all receivable transactions with associated definition for all the fields. Each transaction will have at least two of the following distribution types depending on the transaction type and associated parameters

    RM Distribution Types;

    • Cash
    • Terms Taken
    • Accounts Receivable
    • Writeoffs
    • Terms Available
    • GST
    • PPS
    • Other
    • Sales
    • Trade
    • Frieght
    • Miscellaneous
    • Taxes
    • COGS
    • Inventory
    • Finance Charges
    • Returns
    • Debit Memo
    • Credit Memo
    • Service
    • Warranty Expense
    • Warranty Sales
    • Commissions Expense
    • Commissions Payable
    • Unit Account
    • Rounding
    • Realized Gain
    • Realized Loss
    • Unrealized Gain
    • Unrealized Loss

    SQL view with all GL distributions for AR transactions

    I do believe that you need to get all Receivable transactions details for a specific distribution type (Account Receivable). To add a filter to the script above;

    SELECT * FROM 

    (

    SCRIPT

    ) AS RCVTRX

    WHERE RCVTRX.Distribution_Type = 'Accounts Receivable'

    Just to note that joining more than one distribution record for the receivable document will result with duplicating the header details as previously illustrated in another post of yours;

    Add debit and credit from RM10101

    Hope this helps, 

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…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

#1
mtabor Profile Picture

mtabor 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans