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) / Create Sales by State ...
Microsoft Dynamics GP (Archived)

Create Sales by State (Location) with SQL Statement

(0) ShareShare
ReportReport
Posted on by David Posey

Our site is importing invoices from another system into GP Dynamics so the SOP tables are not being populated. However all customer information is in GP so I would think linking the sales totals to a customer file would be straight forward but I'm having trouble finding where I should be linking this information together.

I would love to be able to tie all customer file info to the corresponding invoice (sales) for aggregate reporting.  I've tried tinkering with the Originating Master info but it is not complete enough to get detailed customer file info like city, ship-to or state.

*This post is locked for comments

I have the same question (0)
  • MattPaulen Profile Picture
    MattPaulen 6,912 on at

    What module are you importing the invoices in to?

  • David Posey Profile Picture
    David Posey on at

    That's a good question.  We are using Smart Connect to import the invoices.  Where would I look to see the destination module?


    If I had to guess it appears this is being imported in the Financial Module.

  • MattPaulen Profile Picture
    MattPaulen 6,912 on at

    In Smart Connect,  it would be the Destination Group and Node Type that would specify the module and transaction type that it is being imported in to.  If it is just Financial, the Originating Master ID is typically what would tie to the customer ID.  Is that being populated by the integration?

  • David Posey Profile Picture
    David Posey on at

    Yes Originating Master ID is the populated item.

  • Verified answer
    MattPaulen Profile Picture
    MattPaulen 6,912 on at

    If the customer info is stored in GP you should then be able to join on ORMSTRID and the CUSTNMBR field from RM00101 (or RM00103 if needing the customer address table).

    Here's a sample query I put together that is just taking in to account credit amounts (obviously this would need to be fleshed out more)

    SELECT TOP 100 cust.STATE,SUM(CRDTAMNT) AS [Sales], PERIODID FROM GL20000 gl

    LEFT JOIN RM00101 cust

    ON gl.ORMSTRID = cust.CUSTNMBR

    WHERE SOURCDOC = 'SJ'

    GROUP BY cust.state,gl.PERIODID

    ORDER BY cust.STATE, PERIODID

  • David Posey Profile Picture
    David Posey on at

    Excellent!  Thanks for your help!

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