Using UDF’s in Business Rules

In my last post I created a User-Defined Function (UDF) to determine if a given datetime fell within or outside working hours (as defined by a given work schedule). This post will discuss how to use a function such as this in business rules.

Ok so lets say you want to check in a business rule (doesn’t matter whether normal or client side) if the current time is within the work schedule. For the sake of argument we will assume you have a work schedule called NORMAL.

All you need to do is add the following Condition:

{MATH, (SELECT "_SMDBA_"."IsWorkTime"('NORMAL', GETDATE()))} EQUAL TO 1

Consequently the business rule you are creating will only fire if the current time falls within the selected work schedule.

Hope this helps.

Function to check if datetime is in work time

A couple of posts recently in the Yahoo MagicSolutions forum made me realise that there was no easy way of checking if a given datetime was during or outside working hours (as defined by the work schedule). Consequently I set about writing a UDF that would accept a work schedule name and datetime as an input and return 1 if the time fell within the given work schedule and 0 if it did not.

Because of the way things get formatted using WordPress I have saved the function as a text document available here: IsWorkTime Function

Depending on where you want to use UDF’s in Service Desk Express you need certain permissions to do so. Personally, whenever I create a function like this (assuming it is not returning any privaleged information) I grant execute permissions to everyone. To do this simply run the following SQL:

GRANT EXECUTE ON [_SMDBA_].[IsWorkTime] TO Public

In my next post I’ll discuss how to use UDF’s in business rules…