Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
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 323

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

  • HasibRahman Profile Picture
    HasibRahman 323 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)

  • Suggested answer
    Praveen Kumar RR Profile Picture
    Praveen Kumar RR 1,550 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 323 on at
    RE: Number of Invoice of an customer! what are the table involved

    Sales >>customers

  • Praveen Kumar RR Profile Picture
    Praveen Kumar RR 1,550 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

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

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans