Project and Portfolio Management Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

rule for multi-select field

Highlighted
Erik Cole
Honored Contributor

rule for multi-select field

Does anyone know how to make a rule that is triggered on field change of a multi-select field, and populates a multi-select as well?

With a single value, I could use rule SQL such as:

Select val1, val2 From Dual Where ORG_UNIT_ID = [REQD.P.ORG_ID]

But with multi-select, the SQL would have to use an IN predicate and return a single, tabular row instead of several rows. Has anyone done this?
6 REPLIES
Tim Brandish
Frequent Visitor

Re: rule for multi-select field

Erik,

write a stored function that takes the multi-select values, parses them out, run query (use dynamic sql if you have to) and place the results into a cursor which you loop through and build your return string. (ie. 'val1#@#val2#@#val3' for hidden and 'val1; val2; val3' for visible)

Erik Cole
Honored Contributor

Re: rule for multi-select field

Here is how I ended up solving this, in case anyone else is interested:

It required a generic function (listed below) to create a list of delimited values from a multi-row query.

In our case, the trigger field is a multi-select of Organization Units. We have an org unit user data field that contains the uid/name of the Senior Architect for that org unit.

When the user selects the org unit(s) the rule populates another field with the matching Senior Architect(s), returning them in the list format 'val1#@#val2#@#val3' for hidden and 'val1; val2; val3' for visible values.


Rule SQL:

select join(cursor(select USER_DATA1 as names from KRSC_ORG_UNITS where ',' || replace('[REQD.VP.P_OTHER_IT_ORGS]','; ',',') || ',' like '%,' || to_char(ORG_UNIT_NAME) || ',%'),'#@#') as P,
join(cursor(select VISIBLE_USER_DATA1 as names from KRSC_ORG_UNITS where ',' || replace('[REQD.VP.P_OTHER_IT_ORGS]','; ',',') || ',' like '%,' || to_char(ORG_UNIT_NAME) || ',%'),';') as VP
from dual


Function:

CREATE FUNCTION JOIN
(
p_cursor sys_refcursor,
p_del varchar2 := ','
) return varchar2
is
l_value varchar2(32767);
l_result varchar2(32767);
begin
loop
fetch p_cursor into l_value;
exit when p_cursor%notfound;
if l_result is not null then
l_result := l_result || p_del;
end if;
l_result := l_result || l_value;
end loop;
return l_result;
end join;
/
eps414
Regular Collector

Re: rule for multi-select field

Erik,

This is actually a question about another thread that you replied to but the only way to contact you I could find. It has to do with the pdf you posted about approving workflow steps via email. Do you have a copy of the jar file that the document refers to? Also, did you ever attempt to get it working?

Eric
Erik Cole
Honored Contributor

Re: rule for multi-select field

Eric,

No I don't have the jar. I think you have to pay HP PSO for the solution, and I have also heard that it doesn't work with 7.1. But I am still exploring how we could do it as it seems like great functionality.
eps414
Regular Collector

Re: rule for multi-select field

Erik,

Thanks.

Eric
Semenov Alexand
Esteemed Contributor

Re: rule for multi-select field

Hi Erik.
It's possible in 7.5. See DemandCG.pdf use advanced rules.
//Add this to "OnDomLoad" event