Microsoft Dynamics GP is licensed, for full users, on a concurrent user basis. This means that you can create more users than can be logged in at the same time; unfortunately, this means that if users don't log out correctly, that the license remains in use.
The below script can be plugged into a SmartList Designer to give easy visibility of who logged in before the current day.
CREATE VIEW uv_AZRCRV_GetPriorDayLogins AS
/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 2.0 UK: England & Wales (CC BY-NC-SA 2.0 UK).
*/
SELECT
['User Activity'].USERID AS 'User ID'
,ISNULL(['Users Master'].USERNAME, 'User Master record not found') AS 'User Name'
,ISNULL(['Company Master'].INTERID, 'Company Master record not found') AS 'Inter ID'
,['User Activity'].CMPNYNAM AS 'Company Name'
,FORMAT(['User Activity'].LOGINDAT, 'yyyy-MM-dd') AS 'Login Date'
,FORMAT(['User Activity'].LOGINTIM, 'hh:mm:ss') AS 'Login Time'
FROM
ACTIVITY AS ['User Activity']
LEFT JOIN
SY01400 AS ['Users Master']
ON
['User Activity'].USERID = ['Users Master'].USERID
LEFT JOIN
SY01500 AS ['Company Master']
ON
['User Activity'].CMPNYNAM = ['Company Master'].CMPNYNAM
WHERE
['User Activity'].LOGINDAT <= DATEADD(DAY, -1, GETDATE())
GO
GRANT SELECT ON uv_AZRCRV_GetPriorDayLogins TO DYNGRP
GO
Can ISC Software Solutions help?
ISC Software Solutions are UK and Ireland based experts on Microsoft Dynamics GP. If you'd like to speak to someone about support, consultancy, upgrades, implementation, development, GP Elementz add-ons or portals, or anything else Dynamics GP related, you can use the form below.
3 thoughts on “SQL View to Return Prior Day Logins”