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.
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
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
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.
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