Project and Portfolio Management Practitioners Forum
cancel

Request Tokens in Validation where query

SOLVED
Go to solution
Highlighted
Islam Nofal
Super Contributor.

Request Tokens in Validation where query

Hello,

 

we have a condition in our company where we need the user to select value from a drop down menu, and the result of the selected value is going to be used in the filter query on the next field.

 

For Example

 

user select Country=X

I need to configure the validation for the cities to return only the cities in this country

Then I need to configure the site to return the sites in the selected city.

 

I created three Tokens and three validation but my problem is how to concatinate the result of the first token in the second validation and so on.

 

Thanks in advance for support

Islam Nofal

4 REPLIES
Jason Nichols K
Acclaimed Contributor.

Re: Request Tokens in Validation where query


Islam Nofal wrote:

Hello,

 

we have a condition in our company where we need the user to select value from a drop down menu, and the result of the selected value is going to be used in the filter query on the next field.

 

For Example

 

user select Country=X

I need to configure the validation for the cities to return only the cities in this country

Then I need to configure the site to return the sites in the selected city.

 

I created three Tokens and three validation but my problem is how to concatinate the result of the first token in the second validation and so on.

 

Thanks in advance for support

Islam Nofal


This is very easy to do.  In the second, third, or how ever many levels you like, you just use the Token of the previously selected field in the WHERE clause of the SQL statement.  Let's say you have a field called country and you have two values in it (US, CA) and the Token is called P_COUNTRY and resides in the Request Type (Parameter1).  Now let's say you have a second field called State/Provence and it's populated by SQL Statement.  You also have another validation that has a list of states and provences for each country.  The codes for those states would be prefaced by the country code (US or CA) followed by the state/provence code (US_AK, US_AL, CA_AB, CA_ON, etc.).  In the SQL for the second field, you would have the following code:

 

SELECT lookup_code, meaning

FROM KNTA_LOOKUPS

WHERE lookup_type = 'MyStateList'

AND lookup_code like '[REQD.P.P_COUNTRY]_%'

 

A similar pattern would be used to select cities within that state and then sites within a city.

Islam Nofal
Super Contributor.

Re: Request Tokens in Validation where query

Hi

 

 

 

 

 

Jason Nichols K
Acclaimed Contributor.
Solution

Re: Request Tokens in Validation where query

From your example to test the token, you need to enclose the tokens in single quotes.  PPM will not do that automatically, all it does is convert the token to the string value of that token.  Here is an example of one validation that I set up for a previous client (the token is in the Header):

 

SELECT lookup_code, meaning
FROM knta_lookups_nls
WHERE lookup_type = 'XXX - CR Type'
AND '_' || lookup_code LIKE '%_' || '[REQ.P.P_DEPLOYMENT_TYPE]' || '_%'
AND enabled_flag = 'Y'
AND upper(meaning) like upper('?%')
ORDER by SEQ

Islam Nofal
Super Contributor.

Re: Request Tokens in Validation where query

One more point to add, the validation must be of type auto complete, i tried it as drop down list but also not working , it worked with auto complete.