Thought this little “ditty” might be of use to someone…As Magic/SDE Administrators you are trying to persuade your users to enter their User Availability data and keep it up to date. Whilst this information is useful for the routing of calls etc. it is not displayed anywhere and consequently is only useful when actually assigning calls to users. It might be beneficial to be able to see, at a glance, all the users and whether (according to the user’s User Availability) they are in or out.
The key to doing this lies in joining the SMSYSUSERDATA and _PERSONNEL_ tables together. What I’ll demonstrate in this post is two ways in which this data can be used:
Report
So the easy solution is to build a report – an IN/OUT board if you will, that will show the status of all members of staff. The SQL for this is:
SELECT P.[CODE], P.[NAME] + ', ' + P.[FNAME] As 'Name', CASE WHEN GETDATE() BETWEEN A.[NOTAVAILABLESTART] AND A.[NOTAVAILABLEEND] THEN 'Out' ELSE 'In' END As 'In/Out'
FROM _SMDBA_._PERSONNEL_ P JOIN dbo.SMSYSUSERDATA A ON A.[SEQUENCE] = P.[SEQUENCE] WHERE P.[_INACTIVE_:] = 0 ORDER BY P.[NAME]
Quite obviously you can change the query to your hearts content to return further fields e.g. phone number etc. You could also add an optional parameter to be able to search for a specific user as well.
This solution is really easy to do but has the obvious limitation that the user’s need to run the report for it to be of any real use.
Calculated Field
So the alternative is to create a calculated field in the Support Staff module with the following SQL:
(SELECT CASE WHEN GETDATE() BETWEEN "NOTAVAILABLESTART" AND "NOTAVAILABLEEND" THEN 'Out' ELSE 'In' END FROM "SMSYSUSERDATA" WHERE "SEQUENCE" = "BASE"."SEQUENCE")
Now, if you then create a Quick View based on the Support Staff module you can add this field in it and even colour code based on whether the user is in or out.
In Out 01
In Out 02
As always, comments (positive and negative) are always welcome. Thanks for reading.
JOATIT 