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) / Number of Invoice of a...
Microsoft Dynamics GP (Archived)

Number of Invoice of an customer! what are the table involved

(0) ShareShare
ReportReport
Posted on by HasibRahman 325

I m trying to calculate the total number of invoice of an customer through sql query.

query

select CUSTNMBR , Count(DISTINCT SOPNUMBE ) from SOP30200 where SOPTYPE=3 GROUP BY CUSTNMBR

I get the following results

Untitled111.png

SMART LIST RESULT 

SAMRT-LIST-RESULT.png 

why there is an difference between my dataset and smart list dataset.

*This post is locked for comments

I have the same question (0)
  • Praveen Kumar RR Profile Picture
    Praveen Kumar RR 1,552 on at
    RE: Number of Invoice of an customer! what are the table involved

    Hi,

    please provide the Smartlist Navigation (node you selected)

    Thanks,

    Praveen

  • HasibRahman Profile Picture
    HasibRahman 325 on at
    RE: Number of Invoice of an customer! what are the table involved

    Sales >>customers

  • Suggested answer
    Praveen Kumar RR Profile Picture
    Praveen Kumar RR 1,552 on at
    RE: Number of Invoice of an customer! what are the table involved

    Hasib,

    In addition to SOP30200 table, you need to refer below tables,

    select CUSTNMBR , Count(DISTINCT SOPNUMBE ) from SOP30200 where SOPTYPE=3 GROUP BY CUSTNMBR

    UNION

    select CUSTNMBR , Count(DISTINCT SOPNUMBE ) from SOP10100 where SOPTYPE IN (2,3) GROUP BY CUSTNMBR

    SELECT * FROM RM10101 WHERE CUSTNMBR = 'AARONFIT0001' AND RMDTYPAL = 1

    SELECT * FROM RM20101 WHERE CUSTNMBR = 'AARONFIT0001' AND RMDTYPAL = 1

    SELECT * FROM RM30101 WHERE CUSTNMBR = 'AARONFIT0001' AND RMDTYPAL = 1

    In the snapshop of Smartlist you are showing Total number of Invoice, YTD, which includes information from RM Tables as well.

    Please make use of above tables and check if you can get the exact count as Smartlist.

    If you are still facing difficulty to arrive at the exact result, please feel free to reach me,

    I am happy to help you with SQL Query.

    Thanks,

    Praveen

  • HasibRahman Profile Picture
    HasibRahman 325 on at
    RE: Number of Invoice of an customer! what are the table involved

    hi praveen

    Any of the following table didnt give the required data set .

    smartlist total invoice is :55

    select CUSTNMBR , Count(DISTINCT SOPNUMBE ) from SOP30200 where SOPTYPE=3 GROUP BY CUSTNMBR

    UNION

    select CUSTNMBR , Count(DISTINCT SOPNUMBE ) from SOP10100 where SOPTYPE IN (3) GROUP BY CUSTNMBR

    THE ABOVE QUERY RETURNED   45 (total invoice).

    SELECT CUSTNMBR,COUNT(DOCNUMBR) FROM RM10101  WHERE CUSTNMBR = 'AARONFIT0001' AND RMDTYPAL = 1 GROUP BY CUSTNMBR  (270)(total invoice)

    SELECT CUSTNMBR,COUNT(DOCNUMBR) FROM RM20101 WHERE CUSTNMBR = 'AARONFIT0001' AND RMDTYPAL = 1 GROUP BY CUSTNMBR  (37)(total invoice)

    SELECT CUSTNMBR,COUNT(DOCNUMBR) FROM RM30101  WHERE CUSTNMBR = 'AARONFIT0001' AND RMDTYPAL= 1 GROUP BY CUSTNMBR   (14)(total invoice)

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

Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft

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