Jump to content
Dan Munns

Table Join query

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 

Share this post


Link to post
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

Share this post


Link to post
Share on other sites

@Dan Munns 

8 minutes ago, Dan Munns said:

The only question / answer I want is 'Change Type'

If you only want this, why don't you just add another filter criteria for this (along with the existing filter criteria)? Like this...

image.png

Share this post


Link to post
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 :( 

Share this post


Link to post
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... 

Share this post


Link to post
Share on other sites

@Victor sorry I meant it is captured in my custom PC form. We don't use the default one. 

I did originally join the h_itsm_changerequests table, only to find 'Change Type' empty. It took me a while to realise why.... 

Share this post


Link to post
Share on other sites
13 minutes ago, Dan Munns said:

I meant it is captured in my custom PC form

@Dan Munns yes, I understand... My suggestion remains valid ;) 

Share this post


Link to post
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

Share this post


Link to post
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...