Skip to Content

Query to show when a field has been changed

Hi

A user of mine needs to know when a user defined field has been updated, he then wants to receive an alert showing the changed record.

The fields I need to include in the query / alert are :T0.[internalSN], T0.[itemCode], T0.[U_LicenceCode], T0.[U_LicenceDate]

The last two are the fields that will trigger the alert.

Any suggestions would be appreciated.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Oct 16, 2017 at 03:10 PM

    Hello Neil - the AITM table holds historical information on Items. I am not sure where the User Defined Fields are located in your system, but here is some SQL written a few years back to help one Customer to find out what happened to the Item Master Data Record using AITM. Maybe you can use this as a starting point.

    You would want to update the WHERE clause with something like "WHERE DATEDIFF(DAY,T0.UpdateDate, GETDATE()) < 3" which shows all of the updates done in the past three days.

    Regards - Zal

    COPY WHAT IS UNDER THIS LINE DOWNWARDS TO USE AS SQL

    --H-IN Item Info from AITM UoM and Quantity Chgs Ver 1 ZP 2014 06 07

    --DESCRIPTION: SQL lists out changes made to Items on the Item Master Data Window. Fields can be changed according to AITM.

    --USAGE: Purchasing Agents and Production Personnel

    --AUTHOR(s):

    --Version 1 Zal Parchem 07 June 2014

    SELECT

    T0.[ItemCode],

    T0.[ItemName],

    T0.[BuyUnitMsr],

    T0.[NumInBuy],

    T0.[UpdateDate]

    FROM AITM T0

    WHERE

    T0.[UpdateDate] > '[%0]'

    Add comment
    10|10000 characters needed characters exceeded