I've looked at the object models of the database over and over but cannot find out how to identify the date an item was created.
The main goal is that I need to get a list of all the new items created in the last 2 yrs, 1 yr, and 6 months.
I've looked at the object models of the database over and over but cannot find out how to identify the date an item was created.
The main goal is that I need to get a list of all the new items created in the last 2 yrs, 1 yr, and 6 months.
The best you can do is use the Date Last Maintained (DATELASTMN) field.
If you use the ICUNIT table, you should be able to get a decent date to work with;
SELECT MIN(dbo.ICUNIT.AUDTDATE) AS CREATED, dbo.ICITEM.ITEMNO AS ITEM
FROM dbo.ICITEM INNER JOIN
dbo.ICUNIT ON dbo.ICITEM.ITEMNO = dbo.ICUNIT.ITEMNO
GROUP BY dbo.ICITEM.ITEMNO
Brilliant! ICUNIT is rarely ever touched after the item is created!!! THANK YOU!
Brilliant! ICUNIT is rarely ever touched after the item is created!!! THANK YOU!
You're welcome! :-)
Hmm, for whatever reason, this returns way more than it should. We may have done an inventory dump and load a few months back which, I assume, would have touched the dbo.ICUNIT.AUDTDATE. Oh well.
That's correct. The AUDT fields are used primarily for database concurrency but sometimes we can get away with using them for other purposes - until a dump/load happens.
Would getting the date of the first receipt be of any use or does your query need to know when the master record was created?
Date of first receipt would be great also. Where might I find that?
ICHIST or ICIVAL. There's also potentially ICILOC.DATEACTIVE.
Looks like this gets me a good result for first received date for each item:
SELECT MIN(TRANSDATE) AS FIRTRECDATE, ITEMNO AS ITEM
FROM dbo.ICHIST
GROUP BY ITEMNO
*Community Hub is the new name for Sage City