Project and Portfolio Management Practitioners Forum
cancel

Filter field selection for validations with 1:1 Correspondence

SOLVED
Go to solution
Highlighted
Alex Madison
Respected Contributor.

Filter field selection for validations with 1:1 Correspondence

Thanks a lot for sharing and providing a fantastic insight with PPM troubleshooting methods. Have been bombarded and faced with unusual and unique solution finder these days one of which is:
We are developing a report, and during this time when the users select one field in the report input, the second field should be populated automatically.
Field 1 values: 1, 2, 3, 4, 5
Field 2 values: A, B, C, D, E

There is a 1:1 correspondence for the above values such that 1:A, 2:B, 3:C, 4:D, 5:E.
When the user selects a value for field 1, say "1", Field 2 should automatically be populated with "B". Is this possible?

Te above fields correspond to project status. In our example, when the user selects a request status "In Progress", the other field should be populated with "Complete" as the status of the project could only go from "In Progress" to "Complete".
If a user selects the status "Complete" in field one, field 2 should autopopulate with "Closed" as in the workflow, the request only goes from "Complete" to "Closed".

I hope the explanation is not confusing, please let me know if I could help in simplifying the explanation any further.
Thanks!
6 REPLIES
Mahen M
Acclaimed Contributor.
Solution

Re: Filter field selection for validations with 1:1 Correspondence

If it is a one to one mapping then why you need the second filter in place?

I hope you can hardcode the value in your query, You can write a CASE statement or a UNION to acheive this.

I am not sure how to write rules with Reports.

Regards,
Mahendran M
Jason Nichols K
Acclaimed Contributor.

Re: Filter field selection for validations with 1:1 Correspondence

Alex,

Mahendran is right, if it is a one to one mapping, there is no need to have another field in a Report Filter list. Unlike what he said, though, I would use a DECODE to do the mapping in the WHERE section of the first filter.

AND PARAMETERx = '[P.FILTER1]'
AND PARAMETERy = DECODE('[P.FILTER1]'
,'1','A'
,'2','B'
,'3','C'
,'4','D'
,'5','E'
,'NOT FOUND')
Alex Madison
Respected Contributor.

Re: Filter field selection for validations with 1:1 Correspondence

Hi Jason and NIchols,
Thank you so much for the update. As an update, during the process of seeking solution, it has been determined that it goes beying 1:1 map correspondence.
For Field 1, the validations are 1, 2, 3, 4, 5 and multiselect

For Field 2, the validations are A, B, C, D, E and multiselect.

Some of the values in field 1 need more than one value in field 2

For instance:
If Field 1 value is: 1
Field 2 value should be: A, B and C

If Field 1 value is: 2
Field 2 value should be: A, B, C and D

and likewise.
Would really appreciate if you could suggest a strategy around this.
Jason Nichols K
Acclaimed Contributor.

Re: Filter field selection for validations with 1:1 Correspondence

Alex,

If the second field is still a set list based on what the first field is set to, then a slight variation may do the trick:

AND PARAMETERx = '[P.FILTER1]'
AND INSTR(DECODE('[P.FILTER1]'
,'1','A,B,C'
,'2','A,B,C,D'
,'3','A,B,C,D,E'
,'4','C,D,E'
,'5','B,C,D,E'
,'NOT FOUND'), PARAMETERy) > 0
Mahen M
Acclaimed Contributor.

Re: Filter field selection for validations with 1:1 Correspondence

This is bit confusing for me, You mean to say that all the probability can occur like the one mentioned below

1 A, B, C, D, E
2 A, B, C, D
3 A, B, C
4 A, B
5 A

1 B, A, C, D, E

.....and so on

If yes... Please check whether you can define all the possible conditions in a validation or a decode statement and try to bring in the values of the validation in the report?

If it becomes so complex why can't you give the second field as a editable field to the end user? What is the problem associated with it?

Regards,
Mahendran M
Alex Madison
Respected Contributor.

Re: Filter field selection for validations with 1:1 Correspondence

Thanks Jason (and Mahendra). I agree with the solution provided by you. I would post further comments incase a bottlenexk arises.
Thank you.