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

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) / Applied payments - joi...
Microsoft Dynamics GP (Archived)

Applied payments - joining SQL tables

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

Using report builder 2.0. GP 2010. 

I've posted a similar question in the past but I am posting a new thread because I wasn't asking my question quite the right way. 

I need the following tables and information, for the cash account, combined all into one SQL query (that does not produce duplicates). I've tried looking at some of Victoria Yudin's and they don't have everything that I need. I'm sure it's possible to build off her queries but I just don't have the know how to accomplish what I need. 

RM30201

APTODCDT

APTODCNM

APPTOAMT

RM00101

CUSTNAME

CUSTCLAS

GL10110

ACTNUMBR_1

ACTNUMBR_2

ACTNUMBR_3

ACTNUMBR_4

ACTNUMBR_5

CRDTAMNT

DEBITAMT

Note: Where there is no debit entry I need to see zero for the APPTOAMT. Where there is a debit I need to see the amount. Same for credit. 

*This post is locked for comments

I have the same question (0)
  • Mike Smith Profile Picture
    Mike Smith 6,840 on at

    What about a scenario where a single cash receipt is applied to multiple documents?

    This scenario is responsible for duplicates but how do you want to account for the multiple apply to documents?

    Should just the "first" record be displayed (obviously, what constitutes "first" would have to be defined)?

    Or should the cash distribution be a representation of the amount applied to any single document?

    Does this report have to tie back to the GL (I'm thinking GL20000/30000 is your best bet, not the GL10110) or can the cash account be pulled from the actual cash receipt transaction distribution?

    There is a lot to take into account when working with the apply process and taking everything into account.

    Having a better idea of what the end report will be used for will also help.

  • painterisaac Profile Picture
    painterisaac 3,015 on at

    I apologize, I thought I had covered all my bases.

    If you think that one GL table would be better than another I'm all for trying it out. However, in looking at the tables I see that GL10110 has what I need (Account Segments, Debits, Credits) and GL20000/GL30000 do not have what I need.

    The cash distribution should be a representation of the amount applied to any single document.

    Below is an illustration of what we need the report to look like in the end...

    I do realize that there might be some duplication with total applied, applied and the doc #; however, it is essential for our needs that the DEBIT and CREDIT amounts do not duplicate. 

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

Introducing the 2026 Season 1 community Super Users

Congratulations to our 2026 Super Stars!

Congratulations to our 2025 Community Spotlights

Thanks to all of our 2025 Community Spotlight stars!

Congratulations to the February 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