• 0 Vote(s) - 0 Average
  • 5
  • 4
  • 3
  • 2
  • 1
Thread Modes

ROOT CAUSE field query in the wizard "WF-FINISH QUALIFICATION"
#1
I'm trying to modify the Wizard “APPROVAL (WF - FINISH QUALIFICATION)” root cause field properties (using the “Override the Combo Query” option), to show ONLY the root causes available/assigned to the category selected in the same wizard step in the “CATEGORY” field... but with no success L
 
Any tip, idea, workaround,…? Or at least anybody knows if it’s possible?

Thumbnail(s)
   
Bucefalos, proud to be a member of EV CONNECT FORUM since Aug 2016.

#2
Wink 
(02-27-2020, 11:06 AM)Bucefalos Wrote: I'm trying to modify the Wizard “APPROVAL (WF - FINISH QUALIFICATION)” root cause field properties (using the “Override the Combo Query” option), to show ONLY the root causes available/assigned to the category selected in the same wizard step in the “CATEGORY” field... but with no success L
 
Any tip, idea, workaround,…? Or at least anybody knows if it’s possible?

SD_REQUEST.REQUEST_ID=@ID@ should be in WHERE section 

P.ABBE
ABBE Philippe, proud to be a member of EV CONNECT FORUM since Nov 2015.

#3
Thank you Philippe for your help!

I tried it, but then.... the typical "multi-part identifier... could not be bound" error message appears  Sad

I have attached a couple of screenshots with both ways I have tried:
  • ERROR 1: the @ID@ is not recognized
  • ERROR 2: the @ID@ is recognized.... but then in the INNER field, the SD_REQUEST.SD_CATALOG_ID fails as a multi-part identifier.

Thumbnail(s)
       
Bucefalos, proud to be a member of EV CONNECT FORUM since Aug 2016.

#4
You forget SD_CATALOG :

inner join SD_CATALOG_ROOT_CAUSE on AM_REFERENCE.REFERENCE_ID=SD_CATALOG_ROOT_CAUSE.ROOT_CAUSE_ID
inner join SD_CATALOG on SD_CATALOG_ROOT_CAUSE.SD_CATALOG_ID=SD_CATALOG.SD_CATALOG_ID
inner join SD_REQUEST on SD_CATALOG.SD_CATALOG_ID=SD_REQUEST.SD_CATALOG_ID

And use SD_REQUEST.REQUEST_ID in (@ID@) instead =
P.ABBE
ABBE Philippe, proud to be a member of EV CONNECT FORUM since Nov 2015.

#5
Thank you Philippe for your help!
As always, you were truth.... in this case about the need to add the indicated additional INNER JOINS.

Now, I can save the wizard with no error message, but when I click on Finish on the action and the Wizard should appears... next typical pop-up error appears:

"An error occurred. The Wizard must be stopped.


order(1,392) expected tokenSad + - . IDENTIFIER INDEX CASE NOT JOIN VALUES ALL BY CURSOR TYPE IS LIKE NULL TRIGGER UNIQUE USER
Error Code : 1"

I though I would be able to solve it myself (usually with this error I just need to check the "SQL grammar"), but in this case I don't know what else to try. When I click on "More details", this is the failing SQL query showed (for me, it seems OK):

Select AM_REFERENCE.REFERENCE_ID, AM_REFERENCE.REFERENCE_EN from AM_REFERENCE 
INNER JOIN SD_CATALOG_ROOT_CAUSE on AM_REFERENCE.REFERENCE_ID=SD_CATALOG_ROOT_CAUSE.ROOT_CAUSE_ID 
INNER JOIN SD_CATALOG on SD_CATALOG_ROOT_CAUSE.SD_CATALOG_ID=SD_CATALOG.SD_CATALOG_ID INNER JOIN SD_REQUEST on SD_CATALOG.SD_CATALOG_ID=SD_REQUEST.SD_CATALOG_ID 
WHERE SD_REQUEST.REQUEST_ID in (532935)
GROUP BY order by REFERENCE_EN

Any last tip/help regarding it?
Bucefalos, proud to be a member of EV CONNECT FORUM since Aug 2016.

#6
(03-03-2020, 06:49 AM)Bucefalos Wrote: Thank you Philippe for your help!
As always, you were truth.... in this case about the need to add the indicated additional INNER JOINS.

Now, I can save the wizard with no error message, but when I click on Finish on the action and the Wizard should appears... next typical pop-up error appears:

"An error occurred. The Wizard must be stopped.


order(1,392) expected tokenSad + - . IDENTIFIER INDEX CASE NOT JOIN VALUES ALL BY CURSOR TYPE IS LIKE NULL TRIGGER UNIQUE USER
Error Code : 1"

I though I would be able to solve it myself (usually with this error I just need to check the "SQL grammar"), but in this case I don't know what else to try. When I click on "More details", this is the failing SQL query showed (for me, it seems OK):

Select AM_REFERENCE.REFERENCE_ID, AM_REFERENCE.REFERENCE_EN from AM_REFERENCE 
INNER JOIN SD_CATALOG_ROOT_CAUSE on AM_REFERENCE.REFERENCE_ID=SD_CATALOG_ROOT_CAUSE.ROOT_CAUSE_ID 
INNER JOIN SD_CATALOG on SD_CATALOG_ROOT_CAUSE.SD_CATALOG_ID=SD_CATALOG.SD_CATALOG_ID INNER JOIN SD_REQUEST on SD_CATALOG.SD_CATALOG_ID=SD_REQUEST.SD_CATALOG_ID 
WHERE SD_REQUEST.REQUEST_ID in (532935)
GROUP BY order by REFERENCE_EN

Any last tip/help regarding it?

Hello,
When you have some SQL error, try testing the query in a sql viewer/monitoring :
In your case you should have something after GROUP BY (order by REFERENCE_EN)
Edit wizard and "AM_REFERENCE.REFERENCE_EN" in group by section

P.ABBE
ABBE Philippe, proud to be a member of EV CONNECT FORUM since Nov 2015.

#7
I tried to add “AM_REFERENCE.REFERENCE_EN" in the “GROUP BY” field but then the pop-up error message says:
  • Column AM_REFERENCE.REFERENCE_ID” is invalid in the select because it is not contained in either an aggregate function or the GROUP BY clause
 
Then I tried to add “AM_REFERENCE.REFERENCE_EN, AM_REFERENCE.REFERENCE_ID" to the “GROUP BY” field and no error message appears, but when click in the ticket action on FINISH and the wizard is executed, the “root cause” combo doesn’t shows anything L

(I love EV "power"/"versatility".... but anytime I would kick it grrrrrr)

Thumbnail(s)
       
Bucefalos, proud to be a member of EV CONNECT FORUM since Aug 2016.

#8
(03-03-2020, 11:55 AM)Bucefalos Wrote: I tried to add “AM_REFERENCE.REFERENCE_EN" in the “GROUP BY” field but then the pop-up error message says:
  • Column AM_REFERENCE.REFERENCE_ID” is invalid in the select because it is not contained in either an aggregate function or the GROUP BY clause
 
Then I tried to add “AM_REFERENCE.REFERENCE_EN, AM_REFERENCE.REFERENCE_ID" to the “GROUP BY” field and no error message appears, but when click in the ticket action on FINISH and the wizard is executed, the “root cause” combo doesn’t shows anything L

(I love EV "power"/"versatility".... but anytime I would kick it grrrrrr)

Hello,
Do you have root cause in your catalog (for this request)?
P.ABBE
ABBE Philippe, proud to be a member of EV CONNECT FORUM since Nov 2015.

#9
(03-04-2020, 04:09 AM)ABBE Philippe Wrote:
(03-03-2020, 11:55 AM)Bucefalos Wrote: I tried to add “AM_REFERENCE.REFERENCE_EN" in the “GROUP BY” field but then the pop-up error message says:
  • Column AM_REFERENCE.REFERENCE_ID” is invalid in the select because it is not contained in either an aggregate function or the GROUP BY clause
 
Then I tried to add “AM_REFERENCE.REFERENCE_EN, AM_REFERENCE.REFERENCE_ID" to the “GROUP BY” field and no error message appears, but when click in the ticket action on FINISH and the wizard is executed, the “root cause” combo doesn’t shows anything L

(I love EV "power"/"versatility".... but anytime I would kick it grrrrrr)

Hello,
Do you have root cause in your catalog (for this request)?
P.ABBE

Hello Philippe.

Yes, I have root causes assigned to the category 
(I attach a screenshot to show it).

Additional info: 
  • We want/need to use only "Authorized Root Causes" for specific catalog categories to be used in the last ticket workflow step of "Closing operation" type...... to force the technicians to change the default ticket category to the ticket final one and then be able to select the proper "Root cause" option.
As additional test, I tried to add root causes also to our default category ("Automatic ticketing"), but the root cause combo persists not showing anything  Sad

Thumbnail(s)
   
Bucefalos, proud to be a member of EV CONNECT FORUM since Aug 2016.

#10
(03-04-2020, 07:01 AM)Bucefalos Wrote:
(03-04-2020, 04:09 AM)ABBE Philippe Wrote:
(03-03-2020, 11:55 AM)Bucefalos Wrote: I tried to add “AM_REFERENCE.REFERENCE_EN" in the “GROUP BY” field but then the pop-up error message says:
  • Column AM_REFERENCE.REFERENCE_ID” is invalid in the select because it is not contained in either an aggregate function or the GROUP BY clause
 
Then I tried to add “AM_REFERENCE.REFERENCE_EN, AM_REFERENCE.REFERENCE_ID" to the “GROUP BY” field and no error message appears, but when click in the ticket action on FINISH and the wizard is executed, the “root cause” combo doesn’t shows anything L

(I love EV "power"/"versatility".... but anytime I would kick it grrrrrr)

Hello,
Do you have root cause in your catalog (for this request)?
P.ABBE

Hello Philippe.

Yes, I have root causes assigned to the category 
(I attach a screenshot to show it).

Additional info: 
  • We want/need to use only "Authorized Root Causes" for specific catalog categories to be used in the last ticket workflow step of "Closing operation" type...... to force the technicians to change the default ticket category to the ticket final one and then be able to select the proper "Root cause" option.
As additional test, I tried to add root causes also to our default category ("Automatic ticketing"), but the root cause combo persists not showing anything  Sad

Hello,
I see the problem @ID@ refere to ACTION_ID (not REQUEST_ID). I test the query below with success :

INNER JOIN SD_CATALOG_ROOT_CAUSE on AM_REFERENCE.REFERENCE_ID=SD_CATALOG_ROOT_CAUSE.ROOT_CAUSE_ID   
INNER JOIN SD_CATALOG on SD_CATALOG_ROOT_CAUSE.SD_CATALOG_ID=SD_CATALOG.SD_CATALOG_ID 
INNER JOIN SD_REQUEST on SD_CATALOG.SD_CATALOG_ID=SD_REQUEST.SD_CATALOG_ID 
INNER JOIN AM_ACTION ON SD_REQUEST.REQUEST_ID=AM_ACTION.REQUEST_ID

AM_ACTION.ACTION_ID in (@ID@)

AM_REFERENCE.REFERENCE_ID, AM_REFERENCE.REFERENCE_$lng

P.ABBE
ABBE Philippe, proud to be a member of EV CONNECT FORUM since Nov 2015.






Users browsing this thread: 1 Guest(s)