Project and Portfolio Management Practitioners Forum
cancel

Assigning request using tokens when the field is a multi-select with more than one item.

Highlighted
john.jeremiah
Trusted Contributor.

Assigning request using tokens when the field is a multi-select with more than one item.

I am routing a Risk request based on information on the associated proejct and am using workflow exectution steps and tokens to resolve the data for where a risk should be routed. When I use the following commands to assign the RISK to the project's PM, and the project only has one PM, it works fine:

Assign to PM
ksc_set TEMP_REQ_ID="[PRJ="[REQ.P.KNTA_MASTER_PROJ_REF]".PROJECT_REQUEST_ID]"
ksc_set TEMP_PM_ID="[REQ="[TEMP_REQ_ID]".P.KNTA_PROJECT_MANAGER]"
ksc_set TEMP_PM_NAME="[REQ="[TEMP_REQ_ID]".VP.KNTA_PROJECT_MANAGER]"
ksc_store REQ.ASSIGNED_TO_USER_ID=[TEMP_PM_ID],[TEMP_PM_NAME]


However when I add a second PM to the project, I get the following error from the last step in execution:
"
KSC Store

Source Command:Assign to Project Manager

REQ.ASSIGNED_TO_USER_ID="30094,30091", "Nathan Oppedahl,John Jeremiah"
ERROR while setting token.
ORA-01722: invalid number {KNTA_EXECUTION-140}

UPDATE KCRT_REQUESTS set ASSIGNED_TO_USER_ID = '30094,30091' where REQUEST_ID = 100648
"


My intent has been to store the "PM"s in a local field on the Risk Request and then use SQL in to parse out one of the PM's to assign it to.... with something like...

select substr(RD.Parameter17 ,1,instr (RD.Parameter17 ||'#@#','#@#')-1) UserID,
substr(RD.Visible_Parameter17 ,1,instr (RD.Visible_Parameter17 ||'#@#','#@#')-1) PC_Name
from kcrt_request_details RD, kcrt_fg_pfm_project PJ
where RD.request_id=PJ.request_id and
prj_project_id= [REQ.P.KNTA_MASTER_PROJ_REF]


However, when I try to access and store the KTNA_PROJECT_MANAGER data through TOKENS, I get either the USER ID in both fields, or the User Name in both fields - depending on the visible or invisible field.
...

Any suggestions?
1 REPLY
john.jeremiah
Trusted Contributor.

Re: Assigning request using tokens when the field is a multi-select with more than one item.

As I worked this question, I found the following solution that works:

In a command set, use KSC_ITG_RUN_SQL to look up the PM name and parse it out.

ksc_itg_run_sql QUERY_STRING="Select substr(PRJ_PROJECT_MANAGER_USERNAME,1,instr (PRJ_PROJECT_MANAGER_USERNAME||'#@#','#@#')-1) from kcrt_fg_pfm_project where prj_project_id = [REQ.P.KNTA_MASTER_PROJ_REF]" ENV_NAME="[SOURCE_ENV.ENVIRONMENT_NAME]"

I think store the PM's name on the Risk request and use it to route it to the PM when appropriate.