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 
The quickview is awesome! 🙂
Hi anomynous,
Glad you like. As you can imagine, thinking of things that others will find useful/interesting can be a challenging affair…
Thanks for the appreciation.
Regards,
Alan
In setting this up I started getting errors when trying to open any staff popup.
(The SELECT permission was denied on object ‘SMSYSUSERDATA’ …)
To resolve I granted select rights to smsyspublic in table smsysuserdata.
Other than this this little feature is working great for us! Thank you!
Hi CC,
Many thanks for this – I didn’t get this error but that could have been that I was on a different version when I “developed” this.
Thanks again.
Regards,
Alan