Jump to content

Table Join query


Dan Munns

Recommended Posts

Hi all,

I am trying to add answers from custom PC forms into a report. I have linked h_itsm_changerequests and h_itsm_requests via Request ID

I can't work out the required joins for the h_bpm_pro_cap_custom_questions table though. I think it joins the Entity urn and the only other table I can find with PC urns is in h_bpm_procapture_access (h_access_by_urn)

I can't however link any of the PC tables to h_itsm_requests with any success. 

Any help please? 

Thanks 

Link to comment
Share on other sites

Ok cancel this as I found the tables to join in another post (amazing what you can do after lunch) 

However the output lists all requests multiple times due to each question and answer generating a new line. I only want one question / answer on the report so is there a way I can format this? 

@Victor @Bob Dickinson any ideas? I kind of need this report today (sorry) 

Thanks

Link to comment
Share on other sites

@Victor because we don't use the change type field and the change type is captured in the PC (although now thinking about it I could probably do that via automated tasks) 

However for the changes already in the system (over 100) I need this report. 

*pops off to see if the automated task thing will work...*

Edit: *pops back again*

It would seem that you cannot use automated tasks to set the change type :( 

Link to comment
Share on other sites

@Dan Munns 

2 minutes ago, Dan Munns said:

the change type is captured in the PC

Yes, and this is what my filter suggestion is doing... filtering on the h_question filed from the h_itsm_questions table... to return records where h_itsm_questions.h_question = "Change Type" ...

By the way, you report is joining h_itsm_requests and h_itsm_questions tables... the "Change Type" filed you are referring to is part of the h_itsm_changerequests table... 

Link to comment
Share on other sites

Well I have managed to filter as per your suggestion 

59 minutes ago, Victor said:

filtering on the h_question filed from the h_itsm_questions table... to return records where h_itsm_questions.h_question = "Change Type" ...

and that works. All I have to do now is add automated tasks for closure categories for successful and failed changes to the BPM and it's all good. 

Thanks @Victor

Link to comment
Share on other sites

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...