x Want to participate? Join the group to interact
  • State Suggested Answer
  • Replies 3 replies
  • Answers 1 answer
  • Subscribers 101 subscribers
  • Views 1070 views
  • Users 0 members are here
Browse Forums
Announcements
268
topics
260
replies
Databases and Operating Systems
756
topics
2k
replies
Financials Suite
1k
topics
3k
replies
General Discussion
2k
topics
10k
replies
Operational Suite
851
topics
2k
replies
Reporting and Analytics
1k
topics
3k
replies
Reports, Macros, and Customizations
876
topics
3k
replies
Sage CRM for Sage 300
182
topics
410
replies
wheumann

Need help with logic re icitem

SUGGESTED
Posted By

I'm trying to create a query for a client to find items that haven't been used since a certain date (ICILOC.LASTUSED).  They don't want to see items that were created recently and haven't been used yet (LASTUSED=0), but still show items that were created a few years ago and never used. The intent is to find items that are still active, but probably should not be.  There's no "Create Date" in ICITEM.  The closest i can find is the AUDTDATE of ICUNIT, because that doesn't usually change over time but there must be a more accurate way.  What am I missing?

  • 0

    Look in ICHIST.  If no records, then no transactions.

  • 0
    SUGGESTED

    Try this query;

    SELECT dbo.ICITEM.FMTITEMNO, dbo.ICITEM.[DESC], dbo.ICITEM.INACTIVE, MAX(dbo.ICILOC.LASTUSED) AS [Last Used]
    FROM dbo.ICITEM LEFT OUTER JOIN
    dbo.ICILOC ON dbo.ICITEM.ITEMNO = dbo.ICILOC.ITEMNO
    GROUP BY dbo.ICITEM.FMTITEMNO, dbo.ICITEM.[DESC], dbo.ICITEM.INACTIVE
    HAVING (dbo.ICITEM.INACTIVE = 0) AND (MAX(dbo.ICILOC.LASTUSED) IS NULL OR
    MAX(dbo.ICILOC.LASTUSED) >= 20151231)

  • 0 in reply to Ascend Business Solutions

    Thanks - this is essentially what I have already, but it includes items that were set up recently for future projects, but not used yet. We're trying to determine what's obsolete.

    So to exclude those items, I'm looking for the date it was created. If a user added "do not use" or "on hold for now" to the description, AUDTDATE would have the date of that change, not the date the item was created. I also looked at ICUNIT.AUDTDATE, since that rarely changes. That may be as close as we can get, and just have the users go through the never-used items one by one to determine what can be deleted or made inactive.

*Community Hub is the new name for Sage City