Martyn Houghton Posted January 23, 2019 Posted January 23, 2019 We are in the process of enabling visibility of Change Requests on the customer portal (see post link at the bottom), which will mean our existing change requests which where created before catalog options for changes where in place will be view-able to the customer. Therefore before making this change I want to review all customer view-able comments on the timeline of the effected requests. I can see from other posts on the forum about reporting on the activities, which I have based my initial query on, but there appears to be a missing table relationship between the itsm_requests and the h_buz_comments. select h_pk_reference,h_buz_comments.h_comment,h_buz_comments.h_visibility from h_itsm_requests RIGHT JOIN h_buz_comments ON h_itsm_requests.h_activity_stream_id = h_buz_comments.h_activity_id where h_pk_reference='IDXCR00076979' I can link h_itsm_requests to h_buz_activity_streams using the h_activity_stream_id, but do not seem how that would then let me link to the timeline comments in h_buz_comments. Therefore I must be missing one or more tables relationships which tie the timeline comments to the the request. Can you advise on what I am missing? Cheers Martyn
Martyn Houghton Posted January 23, 2019 Author Posted January 23, 2019 After a bit more work and walking through the logic, I had forgotten comments exist against a post, which in the table structure are h_buz_activities, so I need to traverse through this table to link to the comments. Therefore in order to display the comments I used the following SQL. select r.h_pk_reference,c.h_comment,c.h_visibility from h_itsm_requests r RIGHT JOIN h_buz_activities a ON r.h_activity_stream_id=a.h_target RIGHT JOIN h_buz_comments c ON a.h_id = c.h_activity_id where r.h_pk_reference='IDXCR00060328' Hopefully that might help someone else if they have the same requirement to report on comments. Cheers Martyn 1
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now