Item Create Date Query

[Former Member]

Hi All,

I want to create a query from the change log that will display the date and user that created an Item Master Data document. I pull the item and date fields from AITM table, but when I introduce the OUSR table for the user name I return one entry for every user in the system. Is there a different field I should be pulling for the user name associated with the first instance? Thanks!

[JesperB1 · Accepted solution]

Hi Emily,

Try the following query:

==============================

SELECT T0.[ItemCode], T1.[U_NAME] as 'User that created Item', t0.updatedate

FROM AITM T0 inner join OUSR T1 on t0.usersign = t1.internal_K

WHERE T0.[ItemCode] = '[%0]'

group by T0.[ItemCode], T1.[U_NAME], t0.loginstanc, t0.updatedate

having t0.loginstanc = (select min(loginstanc) from AITM where itemcode = '[%0]')

=============================

Please note that the editor will remove the square brackets around %0, you need to add them for the query to work.

To get the user that last changed the item just change the 'min' to 'max'. I didn't manage to get them in the same query by using the username, if the user code is ok you can have both the creator and the last person that changed it in the same query.

Hope it helps.

Jesper