Skip to main content

Notifications

Announcements

Check out the New Agent Creator Community
Announcing new navigation menus and the Engage with the Community forum!
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

  • David Posey Profile Picture
    David Posey on at
    RE: Create Sales by State (Location) with SQL Statement

    Excellent!  Thanks for your help!

  • Verified answer
    MattPaulen Profile Picture
    MattPaulen 6,912 on at
    RE: Create Sales by State (Location) with SQL Statement

    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
    RE: Create Sales by State (Location) with SQL Statement

    Yes Originating Master ID is the populated item.

  • MattPaulen Profile Picture
    MattPaulen 6,912 on at
    RE: Create Sales by State (Location) with SQL Statement

    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
    RE: Create Sales by State (Location) with SQL Statement

    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
    RE: Create Sales by State (Location) with SQL Statement

    What module are you importing the invoices in to?

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

News and Announcements

Check out the New Agent Creator Community
Announcing new navigation menus and the Engage with the Community forum!

Quick Links

Jainam Kothari – Community Spotlight

We are honored to recognize Jainam Kothari as our June 2025 Community…

Congratulations to the May Top 10 Community Leaders!

These are the community rock stars!

Announcing the Engage with the Community forum!

This forum is your space to connect, share, and grow!

Leaderboard >

Last month Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans