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) / serial/lot and bin...
Microsoft Dynamics GP (Archived)

serial/lot and bin on inventory transaction reports and smartlist

(0) ShareShare
ReportReport
Posted on by Jennifer Kaufman

The inventory transacation smartlist provides all the information I need except serial/lot and bin information.  Can this be added?  Same goes for the inventory transaction history report.  I prefer not to purchase any customization tools.  If it's not possible without a special tool, what tables can I combine to create a custom report?

*This post is locked for comments

I have the same question (0)
  • Community Member Profile Picture
    Community Member on at
    Re: serial/lot and bin on inventory transaction reports and smartlist

    IV transactions:

    Inventory Transaction Work (IV10000)

    Inventory Transaction Amounts Work (IV10001) 

    Inventory Transaction History (IV30200)

    Inventory Transaction Amounts History  (IV30300) 

    Serial/Lot tables in IV:

    Inventory Serial and Lot Number Work (IV10002)

    Item Serial and Lot Number History (IV30400)

    Bin tables in IV:

    Inventory Transaction Bin Quantities Work (IV10003)

    Inventory Transaction Bin Quantities History (IV30302)

     

    Hope that helps.

  • Richard Whaley Profile Picture
    Richard Whaley 25,195 on at
    Re: serial/lot and bin on inventory transaction reports and smartlist

    You would need to use SmartList Builder or your report writer (GP, SRS, Crystal, etc) to create this display as the standard SmartList does not include serial/lot or bin information.

    The list of tables needed has been posted.

  • Jennifer Kaufman Profile Picture
    Jennifer Kaufman on at
    Re: Re: serial/lot and bin on inventory transaction reports and smartlist

    That's a good start. I also need to include Sales transactions.  What tables do I use for that?

  • Ron Wilson Profile Picture
    Ron Wilson 6,010 on at
    Re: Re: Re: serial/lot and bin on inventory transaction reports and smartlist

    Jennifer,

    Victoria Yudin has a GREAT resource on her website for all the major GP tables.

    http://victoriayudin.com/gp-reports/sop-tables/

    The above is a link to the SOP table information.

    As stated before, to modify the smart list, you will need smart list builder.  If you don't have smart list builder and don't want to spend the $ on it, then you will need to use SRS, Crystal, etc.

    Ron

  • Richard Whaley Profile Picture
    Richard Whaley 25,195 on at
    Re: Re: Re: serial/lot and bin on inventory transaction reports and smartlist

    SOP10201 has the serial/lot numbers tied to sales orders and line items (work and history in one table)

    SOP10200/SOP30200 has the details of the line items (work / history)

    SOP10100/SOP30100 has the order headers (work/history)

  • Rob Klaproth Profile Picture
    Rob Klaproth 1,730 on at
    RE: serial/lot and bin on inventory transaction reports and smartlist

    This provided me all the info I needed for my SmartList; however, I do have an issue with linking both SOP and Inventory transaction lot #s together.  When I add the SOP Lot # table (SOP10201) it works fine and shows me the lot/serial info for the SOP transactions.  But when I try to do a match table from the Inventory Serial/Lot Number Work to the Sales Serial/Lot Work and History, it breaks the SmartList and no records are returned.  Is it not possible to link all these together, should I have one SmartList for Sales Transactions and another for Inventory?  Maybe I am just using the match tables wrong?

  • Rob Klaproth Profile Picture
    Rob Klaproth 1,730 on at
    RE: serial/lot and bin on inventory transaction reports and smartlist

    I think I just answered my own question.  In inventory document type 5 is sales return and 6 is sales invoice.  In Sales, 4 is a return, and 3 is an invoice, so when you try to match the tables, if one of the key fields is document type on the join then it won't return any records.  Unfortunately, if you remove the document type you would not properly be linking everything since it's possible to have duplicate document numbers in inventory, i.e. doc # 00000003 for a adjustment and the same doc number for a transfer.  

  • NelsonC Profile Picture
    NelsonC 282 on at
    RE: serial/lot and bin on inventory transaction reports and smartlist

    I am trying to do the same thing wondering if you would share your SQL query so I don't have to recreate it..

  • Community Member Profile Picture
    Community Member on at
    RE: serial/lot and bin on inventory transaction reports and smartlist

    Hi Rob,

    Can you share the query?

  • Community Member Profile Picture
    Community Member on at
    RE: serial/lot and bin on inventory transaction reports and smartlist

    Could you please share your query?

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