Using Dates from HEAT in Frontange ITSM

If you've tried to integrate HEAT into ITSM one of the problems you may have discovered is centred around HEAT's storage of dates. The HEAT product uses standard varchar fields to store data from it's dates and this is where the problem comes from when integrating with ITSM. ITSM deals with dates in a datetime field and hence using any of the standard functions fail.

For example if you wanted to add a dashboard part that looked at all calls where the RecvdDate (the date a call is opened in HEAT) is in the range of the last week you'd find that ITSM didn't recognise any date fields.

These are a set of steps that will allow you to see a Date field inside ITSM. (I'll assume that both are installed on SQL Server back ends for this)

Step 1 - Creating the Date Fields in the HEAT database
Open SQL Enterprise manager and browse to the HEAT Database
Browse to the Table list, select the calllog table and select to Design the Table
Add a field call dRecvdDate to the Calllog table (This is the field that will be used in the ITSM product and needs to be created of the data type datetime)
Save the changes to the table.

Step 2 - Refreshing the Table Definition in ITSM
Open the ITSM Administrator
Create a new Definition Set
Select the All Business Objects
Find the CallLog object, right click and select Refresh
(You will now see the new dRecvdDate field created as a DateTime Field)
Commit the Definition Set (and wait the 2-90mins)

Step 3 - Removing the Fields from the HEAT Database
Open SQL Enterprise manager and browse to the HEAT Database
Browse to the Table list, select the calllog table and select to Design the Table
Remove the field call dRecvdDate to the Calllog table
Save the changes to the table.

Step 4 - Modifying the ITSM View
(For each external table link to a SQL database there is a view created in the ITSM Database)
Open SQL Enterprise manager and browse to the ITSM Database
Browse to the View list, select the calllog View and select to Design the View
Change the view to the following
Select *, CAST(RecvdDate + ' ' + RecvdTime AS dateTime) AS dRecvdDate
from Heat.dbo.Calllog
( ensure that the statement Heat.dbo.calllog is the connection to your HEAT SQL database)
Save the changes to the View.

You will now have the field dRecvdDate in the ITSM database schema that is the datetime representation of the HEAT RecvdDate and RecvdTime fields. (Note this is only valid in ITSM 5.03 and 5.04 at this stage)