Help getting the 'created by' user into a query

[former_member391531]

1-atc-new-customers-list.txt

Hi, I have a query that shows all the newly created customer accounts.

I'm using the OCRD table. I need to add the 'created by' user to the script if possible. I can't see any field that gives me the user that created the account in this table. Does anyone know how I could get the into my script? At the moment, this information is on the Tools-Show history screen. Thanks, Eoin.

My query:

SELECT T0.[CardCode], T0.[CardName], T0.[CmpPrivate],T0.[CreateDate], T1.Groupname, T0.[Phone1],T0.[CntctPrsn],T0.[E_Mail],T0.[StreetNo],T0.[Address], T0.[City], T0.[County], T0.[Country], T0.[ZipCode], T0.[QryGroup1] as 'Account Reviewed', T0.[MailStrNo], T0.[MailAddres], T0.[MailCity], T0.[MailCounty], T0.[MailCountr], T0.[MailZipCod] FROM OCRD T0 inner join ocrg T1 on t0.groupcode=t1.groupcode
created-by-field.pngWHERE T0.[CardType] ='C' and T0.[CreateDate] > '2018-01-20' AND T0.[QryGroup1]='N' ORDER BY T0.[CreateDate]

[hdolenec · Accepted solution]

There is a creating user data in OCRD table in the field UserSign. If you join it to OUSR table, you can get the name of the user

[former_member391531 · Accepted solution]

That's great, thanks.

Do you know the syntax to add two joins in a query?

[hdolenec · Accepted solution]

FROM OCRD T0 inner join ocrg T1 on t0.groupcode=t1.groupcode INNER JOIN OUSR T2 ON T0.UserSign = T2.USERID

[former_member391531 · Accepted solution]

Thank you very much Hrvoje. That's exactly the help I needed. All sorted.