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) / Total items sold
Microsoft Dynamics GP (Archived)

Total items sold

(0) ShareShare
ReportReport
Posted on by manelson05 660

I am being tasked to lookup specific items in GP and am not able to locate via SQL or a smartlist.

I am looking for finished assemblies that are made up of  seperate parts. I am needing to see the total sold over a three month period. Naturally I began looking through BOM and SOP.

Could somebody illustrate the most efficient manner in which I can do this? I have a total of 22 items to lookup.

 

Thank you kindly

 

*This post is locked for comments

I have the same question (0)
  • Dan Liebl Profile Picture
    Dan Liebl 7,320 on at
    Re: Total items sold

    Are you looking for just the assemblies?   Or the components that make up the assemblies?   If the final assemblies, they should show up in SOP as they would be relieved via sales trx.   If looking for the components, then you need to go through IV trx history tables.    To see the list of tables, from inside of GP, you can go to Microsoft Dynamics GP>Tools>Resource Descriptions>Tables.   Select the product and series you are looking for to see a list of tables.  You can change the display to physical name or display name to be most efficient.   Physical is the table name in SQL.

    Dan Liebl, CMA CPIM | Senior Consultant | OTT,Inc | DLiebl@OTT-inc.com

  • manelson05 Profile Picture
    manelson05 660 on at
    Re: Total items sold

    The assemblies right now are what I am after as I will use a multiplier to break out the total quantity for piece parts.

  • manelson05 Profile Picture
    manelson05 660 on at
    Re: Total items sold

    I am able to select SOP30200 but I do not see any date range.

    Do you ahve an exmaple of this type of query? IO am thinign I need to perform some sort of a join between this tablea nd anotehr, would you be able to assist me? Additionally I do not see this capability via smarlist, is that correct?

  • Dan Liebl Profile Picture
    Dan Liebl 7,320 on at
    Re: Total items sold

    That is the Sales Trx History or the header info.   The SOP30300 is where you would want to grab the line details.    You would need to join the 2 tables.   I am not a strong script writer so I cannot help out there.   The sales line items Smartlist should give you item, dates, etc, so it should get you what you need.   You can then get it to excel to do any slicing and dicing.    You just have to limit it by SOP type to invoice as the orders and quotes also reside in history.

    Dan Liebl, CMA CPIM | Senior Consultant | OTT,Inc | DLiebl@OTT-inc.com

  • manelson05 Profile Picture
    manelson05 660 on at
    Re: Total items sold

    I have the following but I am not able to define my date range for a three month period.

    How does this look, valid?

    SELECT

    m.CUSTNMBR, d.ITEMNMBR,

    d.ITEMDESC, SUM(d.QUANTITY) AS 'Total_QTY'

    FROM SOP30200 m

    inner Join SOP30300 d on m.SOPNUMBE = d.SOPNUMBE

    WHERE Year(m.docdate) = 2011 AND m.SOPTYPE = 3

    GROUP BY

    m.CUSTNMBR, d.ITEMNMBR, d.ITEMDESC

  • Verified answer
    Community Member Profile Picture
    Community Member on at
    Re: Total items sold

    If you just want to add a filter on the date for a range then see below please, I am using TWO that's why the dates are for 2017

    Declare @Startdate date

    Declare @EndDate date

    Set @Startdate = '01/01/2017'

    Set @EndDate = '03/30/2017'

    SELECT

    m.CUSTNMBR, d.ITEMNMBR, m.docdate,

    d.ITEMDESC, SUM(d.QUANTITY) AS 'Total_QTY'

    FROM SOP30200 m

    inner Join SOP30300 d on m.SOPNUMBE = d.SOPNUMBE

    WHERE Year(m.docdate) = 2017 AND m.SOPTYPE = 3

    and m.DOCDATE between @Startdate and @EndDate

    GROUP BY

    m.CUSTNMBR, d.ITEMNMBR, d.ITEMDESC, M.DOCDATE

    order by 3 asc

  • manelson05 Profile Picture
    manelson05 660 on at
    Re: Total items sold

    Francisco,

    This appears to give me the range I need. I am now being told that the range of parts I have looked up are top level assemblies and that they want to see all parts associated with each assembly that was sold. IS it even possible for me to do this now?

  • Dan Liebl Profile Picture
    Dan Liebl 7,320 on at
    Re: Total items sold

    You could tie to the BOM tables or to the picklist tables so it could be done but may be tricky with the picklist tables..   The picklist tables would be a many to many relationship unless there is a SOP number on the MO (auto created).  The BOM table is BM010115 orr BM010116 (I believe the first one is the component or detail).   I forgot what the picklist table is although I want to say it is MO1400 or MO1410.   I can doublecheck if you need.  Don't have access to a table list at the moment.   The pickilst would indicate the actual qty's used, and the BOM tables would be a static.   Which number to they want.

    Thanks for the assistance Francisco!!   I have associates that assist me with scripts when I need them.  

    Dan Liebl, CMA CPIM | Senior Consultant | OTT,Inc | DLiebl@OTT-inc.com

  • manelson05 Profile Picture
    manelson05 660 on at
    Re: Total items sold

    Here is an example of the request I was given before heading home, I am not sure how to even do this at this point. My sql is getting better the more I do it but this one has me scratching my head.

    Here is an example of what I am looking for. The query Francisco helped me complete gives me all the information problem is how do I know which items out of the total per part is not a seperate piece part that was sold?

    PN Description Finished Good Item

    M98-027-0000 1. Bracket Assembly, H24-5 to T&E                                                         K24-026

    M98-026-0000 1. Bracket, H24-5 to UPA,  Sand CARC                                                                     K24-026

    M24-956-0000 1. Bracket, Adapter, ACH, 6-Hole, 10mm K24-026/K24-095/M24-700

    M24-955-BLOX                   1. Block, Guide, Right, H24 Mount                                                                       K24-026

    M24-954-BLOX                   1. Block, Guide, Left, H24 Mount                                                                         K24-026

    M24-950-BLOX                   1. Block, Travel Lock, H24-6                                                                             K24-026/K24-095/M38-210

    M24-901-0000 1. Cradle Assy H24-5, Sand CARC                                                                         K24-026/K24-095/M24-700

    M24-515-BLOX                   1. Train Lock Block, H24-3/-5/-6 (Rev A)                                                                 K24-026

    M24-296-SLOT                   1. Pintle, H24, Slotted for use with Adapter, H24/UPA                                                   K24-026/K24-095/M38-210

  • Dan Liebl Profile Picture
    Dan Liebl 7,320 on at
    Re: Total items sold

    Do the parts for the assemblies vary by work order?   Or are they consistent with the BOM?  If they vary, I am not sure it can be accomplished.    Are items lot numbered?    I guess I am also trying to understand what benefit this information is.   Isn't the usage of the components enough?

    Dan Liebl, CMA CPIM | Senior Consultant | OTT,Inc | DLiebl@OTT-inc.com

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