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