Announcements
No record found.
Could anyone help me with generating a Smartlist of all items tracked by Lot that are expiring this year, including their location, lot numbers and expiration dates? Thanks in advance.
*This post is locked for comments
anwser below.
Hi,
The information your looking for is in the table IV00300.
The friendly name is : Item Lot Number Master
There is the way to get a new SmartList with all the infos you need.
In Dynamics GP 2015
Start Smartlist
Click on Inventory >> Items >> *
Click the "New" button below the menu.
This will open the SmartList Designer.
Choose a name like "Item Lot Number" and enter it in the "List Name" just below the menu and icons.
On the left side, in the Database View select :
+Microsoft Dynamics GP
+Tables
+Inventory
Find "Item Lot Number Master" and click the little box.
This will add the fields in the right section "Selected Fields"
On the middle of the window, there is a section name "Relationship".
In this section...
Click on the arrow below "TableName" and select "Items"
Click on the arrow below "FieldName" and select "Item Number"
Click on the arrow below "TableName2" and select "Item Lot Number Master"
Click on the arrow below "FieldName2" and select "Item Number"
Between the "FieldName" and "TableName2", there is an arrow were you can choose the kind of link. Choose "Inner Join".
Click on the "ok" button
Now you have a new smartlist yellow folder in the Inventory section.
All the fields you need are there...
Here`s a picture of what you have to do:
I did everything as described but once I click the "Ok" button, I can't find the new Smartlist anywhere.
Hi anajimenez,
Sorry about that. I used the "sa" user, so I already had access.
You have to give access to your SmartList.
First, to do that, you must use a User with "Power" access like "sa" (System Administrator) or "dynsa" or a user with the "power" Role.
With the user that have an access to change the security in GP:
Open the "User Security Setup" window in the "Administration" Pane or with the menu.Dynamics GP > Tools > Setup > System > User Security
Select the user and company to add the SmartList.Find the role you whant to add the SmartList and double click on it.
It will open the "Security Rôle Setup" window.Find the Task you whant to add the SmartList and double click on it.
It will open the "Security Rôle Setup" window.Select "Smartlist" in the Product combobox.Select "Smartlist Object" in the Type combobox.Select "Smartlist Object" in the Series combobox.Select "Full" in the User Type combobox.
Below, in the Access List pane, select the new SmartList.Click Save.Close all the security windows.
The user you just give access should log out and log back in GP to see the new SmartList.
I got my Smartlist! Thank you so much!!!
CREATE VIEW ABC_LOT_MASTER_QTY
as
select a.ITEMNMBR as 'Item Number',
b.itemdesc as 'Item Description',
b.ITMCLSCD as 'Item Class Code',
a.LOCNCODE as 'Location Code',
a.LOTNUMBR as 'Lot Number',
a.QTYRECVD as 'QTY Receipt',
a.QTYSOLD as 'QTY Sold',
a.ATYALLOC as 'QTY Allocated',
a.QTYRECVD -a.QTYSOLD-a.ATYALLOC as 'QTY Available'
from IV00300 a
left join IV00101 b on a.ITEMNMBR = b.ITEMNMBR
GO
GRANT SELECT ON ABC_LOT_MASTER_QTY TO DYNGRP
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.
Congratulations to our 2026 Super Stars!
Thanks to all of our 2025 Community Spotlight stars!
These are the community rock stars!
Stay up to date on forum activity by subscribing.
