Project and Portfolio Management Practitioners Forum
cancel

Rule to have subsets

Raka_1
Absent Member.

Rule to have subsets

we have 2 fields field 1 and field 2. what we want to do is to select a value 'a' in field 1 and then when we go to field 2 it should show only r,s,t in the drop down menu. if we select 'b' in field 1 then only x,y,z should be available in the drop down menu. either i can do it with USER DATA or table component.....
6 REPLIES
Sascha Mohr
Regular Contributor.

Re: Rule to have subsets

For the second field you need to use a validation that looks like this:

SELECT fiscal_period_id,
long_name,
KNTA_I18N_Format_Utils.Format_Date(start_date,1, [SYS.USER_ID], [SYS.USER_ID],0),
KNTA_I18N_Format_Utils.Format_Date(end_date,1, [SYS.USER_ID], [SYS.USER_ID],0)
FROM ppm_fiscal_periods
WHERE period_type = 4 AND
start_date >= (SELECT start_date
FROM ppm_fiscal_periods
WHERE fiscal_period_id = [REQ.P.KNTA_PLAN_START_DATE]) AND
UPPER(long_name) LIKE UPPER('%?%') AND
(long_name LIKE '%' || UPPER(substr('?', 1, 1)) || '%' OR
long_name LIKE '%' || LOWER(substr('?', 1, 1)) || '%')
ORDER BY start_date

This is the validation for the planned end period of the default project details request. See that it uses the token of another field in the second part of the query.
Raka_1
Absent Member.

Re: Rule to have subsets

I created a user data for field 1 and gave all the values in the user_data and then for field 2 i created a SQL custom validation which is like

Select visible_user_data2
from knta_lookups
where lookup_code like [TOKEN of Field 1]

but it gives the error component or validation id = null

ORA-00904: "": invalid identifier
Raj Ghimire
Absent Member.

Re: Rule to have subsets

This should be pretty easily achievable using a validation with Userdata. You can have a list validation with values eg. r,s,t,x,y,z etc.
And then Each value can have Userdata to associate them with either a or b.

Then use validation in each field which pulls info from the above validation and the value in other field using KNTA_LOOKUPS table.
eg.
SELECT lookup_code, meaning
FROM knta_lookups
WHERE lookup_type = 'Sub-Departments'
AND user_data1 = '[REQD.P.DEPT_IMPACT]'

-Raj
Sascha Mohr
Regular Contributor.

Re: Rule to have subsets

What you actually need are 3 validation:
Validation 1 for the first field (with the user_data field)
Validation 2 for the second field (the same that you used for the user_data field of the first validation)
Validation 3 that filters validation 2 by what is chosen in the first field.

1&2 should be list validations (e.g. autocomplete), number 3 should be an sql validation looking like this:

SELECT lookup_code, meaning
FROM knta_lookups
WHERE lookup_type = 'My_Lookup_Type' -- <- this is the validation 2
user_data2 = '[REQ.P.TOKEN]' -- <- token of field one

Further explained: you will need to retrieve both lookup_code and meaning as you need a visible and a non-visible value; you need the single-quotes around the token as it may be text; if validation 1 allows multiple values, it will be a little more complex.
Raka_1
Absent Member.

Re: Rule to have subsets

I created 3 validation lists

1st one in a an auto-complete list with the user_data with the validation of 2nd field.
With values 1,2,3

1 >>> a,b,c
2 >>> d,e,f
3 >>> g,h,i


2nd one is also an auto-complete list with user_data with the validation of 1st field. and then I am opening each validation value and associating it with the value of field 1.
Like in field 2 there are values a,b,c,d,e,f,g,h,i so for each



3rd one is SQL list with the following query

Select lookup_code, meaning
from knta_lookups
where lookup_type = ''
and user_data1 = [TOKEN OF THE FIRST FIELD]
Highlighted
Sascha Mohr
Regular Contributor.

Re: Rule to have subsets

You have multiple values in the user_date of number one, therefore you need to tweek it a little:
Select v2.lookup_code, v2.meaning
from knta_lookups v2
where v2.lookup_type = ''
and InStr((SELECT v1.user_data1 FROM knta_lookups v1 WHERE v1.lookup_code = '[TOKEN OF THE FIRST FIELD]'), v2.lookup_code) !=0

I inserted validation 1/2 here to make it a little more readable.