Service Desk Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

Report about relations - SD 4.5

Highlighted
Mariano Bonina
Regular Collector

Report about relations - SD 4.5

How do you obtain the date of relation created between an incident and a problem ?
In the Table ITSM_SERVICE_RELATION not found

(In the Table ITSM_SERVICE_RELATION is not found, only info about OID_SER and OID_PRO)

Thanks
1 REPLY
Vasily Kamenev
Honored Contributor

Re: Report about relations - SD 4.5

Hi
Use transactions from SQL server. 1-st create a trigger for table ITSM_SERVICE_RELATION, columns SRE_PRO_OID and SRE_INC_OID. Trigger must have EXEC store procedure, exm: EXEC sp_123. Store procedure possible be the next:

UPDATE ITSM_PRO_CUSTOM_FIELDS
SET PCF_PROBLEMDATE5 = GET_DATE
WHERE PCF_PRO_OID in (
SELECT ITSM_PRO_CUSTOM_FIELDS.PCF_PRO_OID
FROM ITSM_SERVICE_RELATIONS INNER JOIN
ITSM_PROBLEMS ON ITSM_SERVICE_RELATIONS.SRE_PRO_OID = ITSM_PROBLEMS.PRO_OID INNER JOIN
ITSM_INCIDENTS ON ITSM_SERVICE_RELATIONS.SRE_INC_OID = ITSM_INCIDENTS.INC_OID INNER JOIN
ITSM_PRO_CUSTOM_FIELDS ON ITSM_PROBLEMS.PRO_OID = ITSM_PRO_CUSTOM_FIELDS.PCF_PRO_OID
WHERE (ITSM_PRO_CUSTOM_FIELDS.PCF_PROBLEMDATE5 IS NULL))

GO

in my example date set in PR in date CF number 5, of cource, possible change that and insert infromtion about in History.
Beliave that help you.

Vassili
//Add this to "OnDomLoad" event