Project and Portfolio Management Practitioners Forum
cancel

Rule with a multi select validation?

Highlighted
Andy McMahon_1
Super Contributor.

Rule with a multi select validation?

Hey guys. Weird one for you.  

I have a validation that is multi select.  The options are "Scope, Schedule, Budget and System."  

I need to create a rule that will change another field IF "System" is selected in there anywhere. Meaning, if it's system all alone, or if it's System and Budget (and so on).  

Is it possible, or will I have to create a rule for every possible combination containing "System"?

 

If it's the latter, do I have to create a seperate rule for if budget is before system and then if system is before budget?  Am I making sense? 

5 REPLIES
Jim Esler
Acclaimed Contributor.

Re: Rule with a multi select validation?

We have a case where a multi-select field has one value of 'Other' in the dropdown list. If 'Other' appears in the list, another field that is marked as required is cleared. If 'Other' is not in the list, the other field is populated with the string 'N/A'.

 

The rule to do this is executed for any value of the first field. The SQL is:

 

select decode(instr('[REQD.VP.FIELD1]','Other'),0,'N/A',''),
decode(instr('[REQD.VP.FIELD1]','Other'),0,'N/A','')
from dual

Andy McMahon_1
Super Contributor.

Re: Rule with a multi select validation?

I wish I understood that.  

I mean, I see that I have to write SQL, but that realm of my reality is a little muddy.... I'll have to play around with it.  

AlexVastartis
Frequent Contributor.

Re: Rule with a multi select validation?

Andy,

 

Here's a brief explanation of the SQL Jim has provided.

 

An Oracle SQL decode function acts as an 'If Then Else' statement seen in programming logic.

An Oracle SQL intsr function searches the given string for a string value and returns its location. In Jim's case 'Other'. In your Case 'System'.

 

In the given SQL, the instr function is used to detect if the value exists in the string.

Search for 'Other' in the field


instr('[REQD.VP.FIELD1]','Other'

 

 If the value exists the instr will return a number greater than zero. Therefore, if the instr returns a zero, thesearched string value does not exist in the field.

 

In the given SQL, the decode function checks the value returned from the instr and produces a value based on its result.

 

If the field has a value, it will return a zero Then produce 'N/A' Else produce Null

select decode(instr('[REQD.VP.FIELD1]','Other'),0,'N/A','')

 

The result value 'N/A' or Null will be placed in the Result Field. The statement is duplicated in order to fill both the visible and hidden parameters.

 

If you want the result field to be populated with a particular value the given SQL will work, with minor edits (Replace the field token, search string and results). If you want the result field modified in another way you may need another solution.

 

Feel free to expand on how you would like the result field modified.

 

Alex

 

 

AlexSavencu
Acclaimed Contributor.

Re: Rule with a multi select validation?

Guys,

 

pardon me for asking, but isn't there a limitation of the rules NOT to be able to select a multi-select field in the dependencies window?

 

cheers

alex


--remember to kudos people who helped solve your problem
Jim Esler
Acclaimed Contributor.

Re: Rule with a multi select validation?

We are using this in 7.5 SP7 in a rule with a single dependency on a multi-select enabled field (contains any value). The option to add the dependency works normally and all of the options for dependency types are available. It is just tricky to look for a specific value in the list.