Project and Portfolio Management Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

How do I reference TOKEN fields in SQL query?

SOLVED
Go to solution
Highlighted
BillPriceFerg
Occasional Advisor

How do I reference TOKEN fields in SQL query?

I have a TOKEN field called DETAILED_DESCRIPTION. I want to use the data in that field to ultimately create a portlet which simply displays the detailed description. When I try and add [REQ.xx.DETAILED_DESCRIPTION] in the SQL query I get different errors about unkown table. How do I properly build the SQL query to use the TOKEN DETAILED_DESCRIPTION and do I need to edit the WHERE clause or Filter? Attached are screenshots. What database is the actual values for a TOKEN stored so I can just grab them? Documentation reference appreciated since I cannot seem to find it. Thanks in advance for your help.
6 REPLIES
hyllplan
Frequent Visitor

Re: How do I reference TOKEN fields in SQL query?

You cant use request tokens in a datasource. [REQ.VP.X] for example referance a container of data of a specific request and is only avaialable within a workflow/request. You have to refer to the table holding the data when creating a datasource. Download a ppm-pdf called "data model" or someting and you will find out.
Sascha Mohr_1
Esteemed Contributor
Solution

Re: How do I reference TOKEN fields in SQL query?

Hi.
If it is the default description field it is the column "DESCRIPTION" in kcrt_requests, otherwise you need to look into the request types for the field and in the field details dialogue on the storage tab. Depending on where the field is defined (request type vs. request header type) it is a parameter column in kcrt_request_details od kcrt_req_header_details.
hth
BillPriceFerg
Occasional Advisor

Re: How do I reference TOKEN fields in SQL query?

Thank you for your help. It was comforting to know those suggestions I had already looked into. I have the Data_Model_Guide.pdf and have been using it. The two Requests tables were the ones I was searching and would have expected to see the value in there. I feel comforted to know I was in the right place. However I get no values when I perform SQL queries. I have created screen shots of the custom field in Request builder, I captured the shot of the parameter42, but when I query, I'm getting a blank value for 99% of the fields. Could it be permissions??? Notice its a greyed field in the request builder. Please see attached.
BillPriceFerg
Occasional Advisor

Re: How do I reference TOKEN fields in SQL query?

Solved. The values were stored in the kcrt_req_header_details even though in my mind they were custom and should have been stored in the kcrt_request_details...since its not really a 'header' Are only "closed" requests stored in kcrt-request_details?
Sascha Mohr_1
Esteemed Contributor

Re: How do I reference TOKEN fields in SQL query?

No, not used closed requests. If you add fields in the request type definition they will be in kcrt_request_details; if you add fields in the request header type definition they will be stored in kcrt_req_header_details.
Maybe your request has more than 50 custom fields? Then PPM will use two (or more) rows per request identified by the "batch_number" column.
BillPriceFerg
Occasional Advisor

Re: How do I reference TOKEN fields in SQL query?

The values were stored in the kcrt_req_header_details
//Add this to "OnDomLoad" event