Having problems with your account or logging in?
A lot of changes are happening in the community right now. Some may affect you. READ MORE HERE
Highlighted
Respected Contributor.. M_A_A Respected Contributor..
Respected Contributor..
999 views

Limiting Auto Complete Muli-Select field

Jump to solution

I have a Validation List called "Parents" as a multi-selection field.  I have another Validation List called "Children" as a multi-selection list.  I have used User Data to define "Children' user_data1 values based on "Parents".

I need to create a validation lookup that limits the "Children" selection list by all the selections in the "Parent" List.  I am having some real issues getting this accomplished.

I am assuming that the basic Validation SQL i need is:

SELECT lookup_code, meaning , visible_user_data1
FROM knta_lookups
WHERE lookup_type = 'Childrens List'
and (user_data1 in ('[REQ.P.P_PARENTS]')
and enabled_flag = 'Y'
order by visible_user_data1,meaning

But of course that won't work, since REQ.P.P_PARENTS contains "; " as a delimiter, and SQL wants each list item inside a single quote and separated with a comma.

I cannot figure out anyway to provide the values in "Parents" to be used as a lookup in "Children" validation SQL to limit the selection list for "Children" by the list of user selected  "Parents" only.

Example:

Parents list is "A,B,C,D"

Children list with user_data1 = "A" is "a,,e,f"

Children list with user_data1 = "B" is "b"

Children list with user_data1 = "C" is "c,y,z"

User selects Parents = "A; C"

I want the selection list for Children to only be "a, e, f, c, y, z" ...  no Children associated with Parent B or D should be in the available Children selection list.

Anybody solved a similar problem?

0 Likes
1 Solution

Accepted Solutions
Outstanding Contributor.. Amishra Outstanding Contributor..
Outstanding Contributor..

Re: Limiting Auto Complete Muli-Select field

Jump to solution

Hi there again, 

I may have some solution for you, please refer attached doc; 

I created some similar setup in our environment and it is working. 

You may have to alter your parent validation to store number in the code, instead of character. 

Regards,
Ajay Mishra
10 Replies
Aurobinda Super Contributor.
Super Contributor.

Re: Limiting Auto Complete Muli-Select field

Jump to solution

Hi ,

Although not tried or implemented this. But, you may try below way to achive this. Test the query in SQL developer / Toad first and then try putting in token format.

SELECT lookup_code, meaning , visible_user_data1
FROM knta_lookups,
(SELECT trim(column_value) res from xmltable(concat(concat('''',REPLACE('A; C','; ',''||''','||'''')),''''))) RES1
WHERE lookup_type = 'Childrens List'
--and user_data1 in ('[REQ.P.P_PARENTS]')
AND USER_DATA1 IN (RES1.RES)
and enabled_flag = 'Y'
order by visible_user_data1,meaning

--It will return a, e, f, c, y, z" ...   and no Children will be listed whic are associated with Parent B or D .

If above works for you, try simillar way to put the code in Workbench. Mostly, you need to explore this part (or someone else can suggest you, as have not implemented this scenario )

SELECT lookup_code, meaning , visible_user_data1
FROM knta_lookups,
(SELECT trim(column_value) res from xmltable(concat(concat('''',REPLACE('[REQ.P.P_PARENTS]','; ',''||''','||'''')),''''))) RES1
WHERE lookup_type = 'Childrens List'
--and user_data1 in ('[REQ.P.P_PARENTS]')
AND USER_DATA1 IN (RES1.RES)
and enabled_flag = 'Y'
order by visible_user_data1,meaning

 

0 Likes
Respected Contributor.. M_A_A Respected Contributor..
Respected Contributor..

Re: Limiting Auto Complete Muli-Select field

Jump to solution

Thank you for your quick response.

That was very similar to my 1st attempts at this resolution.  But, instead i did not use a resultant field for the results of concat, just placed the concat's select inside the USER_DATA1 IN clause.

Unfortunately, your solution got the exact same results, which makes me think that something else may be going on.

I tested your solution in my SQL tool, and PPM's Workbench SQL Runner.  Results were successful.  Of course using literals 'A; C', not  [REQ.P.P_PARENTS].

Then I implemented into the Validation List.  Results unsuccessful.  If only one Parent is selected it works.  But as soon as I select multiple parents the selection list says "No items were found - press to show all".  When i remove the [REQ.P.P_PARENTS] and replace with a literal list. 'A; C', in the Validation List's SQL, it works fine and I get the children for only A and C Parents in the seleciton list.

Maybe I am not using the correct list delemiter?  A showMessage rules shows the delimiter of [REQ.P.P_PARENTS]  is '; '.  I tried '; ' and '#@#', but always the same result of when one Parent is selected, the children list is correct, when more than one Parent is selected the list says "None Found".

Am I trying the correct list delimiter?

But, one other note, i created a Request Type Rule that did "select ('''' || replace('[REQ.P.P_PARENTS]','; ', ''',''') || ''''),('''' || replace('[REQ.VP.P_PARENTS]','; ', ''',''') || '''') from dual" and placed the results in a Request Type Field.

Then in the USER_DATA1 IN validation list SQL query, i used that Request Type Field's value.  A showMessage gave me a correct looking and properly parsed WHERE IN value for the Request Type Field.  But, the results were the exact same, one Parent worked fine, but multiple parents were "None Found".  My conclusion was the delimiter is truly a "; " and there is some other problem!

0 Likes
Outstanding Contributor.. Amishra Outstanding Contributor..
Outstanding Contributor..

Re: Limiting Auto Complete Muli-Select field

Jump to solution

Havent you heard of .TO_STRING function in PPM?

Just add TO_STRING after token name, PPM will automatically do the separate the values in comma format for you. 

Your revised query will have this  (user_data1 in ('[REQ.P.P_PARENTS.TO_STRING]')

 

 

Regards,
Ajay Mishra
0 Likes
Respected Contributor.. M_A_A Respected Contributor..
Respected Contributor..

Re: Limiting Auto Complete Muli-Select field

Jump to solution

Ajay, I was really excited to see that TO_STRING function.  But it did not work for me.

Here is the actual SQL in the Validation -

SELECT lookup_code, meaning , visible_user_data1
FROM knta_lookups
WHERE lookup_type = 'IRM Customer Subset Stakeholders List'
and USER_DATA1 in ('[REQ.P.P_STAKEHOLDERS.TO_STRING]')
and enabled_flag = 'Y'
order by visible_user_data1,meaning

And it just does not provide the results correctly.

I have attached a picture of the full Validation.

I tried all different combos of parens, single quotes, double quotes.  But nothing seemed to work properly for this situation.

So I am still looking for a solution! 

0 Likes
Aurobinda Super Contributor.
Super Contributor.

Re: Limiting Auto Complete Muli-Select field

Jump to solution

Hi,

If the previous approaches did not work for you and if you are still looking for it and OK to have a slight different approach , you may follow below steps.

1. Make sure your the validation of parent is validated by drop down (component type).

2. The the child field first and then the parent filed in request form, so that it would be appropriate in frontend.

3. set parent field as display only field . i.e. not at all editable at any step/status from front end.

4. In Child filed set the sql similar to below and add filter filed of parent field.

--in configuraiton tab

select lookup_code,meaning,user_data1
from knta_lookups
where lookup_type='Child List'
and UPPER(meaning) LIKE UPPER ('?' || '%')
[FILTER_FIELD_SQL]

--in filer fields tab

create a new field

provide the parent DD list (mentioned in step1)

Set token as "P_PARENT" and add below condition.

and user_data1 = '[P.P_PARENT]'

5. now click on view full query and verify it.

6. Modify the filter layout for better look and feel.

7. add a new rule "populae parent details on filed change" (if required add the same in page load also)

--dependancies

child filed contains any value.

--result fields

parent field

--logic :SQL Default:

select distinct listagg(user_data1, '#@#') within group (order by meaning),listagg(user_data1, '; ') within group (order by meaning)
from knta_lookups
where lookup_type='Child List'
and meaning in (select trim(column_value) res from xmltable(('"'|| replace('[REQD.VP.P_CHILD]', '; ', '","')|| '"')))

 

Hopefully, this will help you.

In this scenarion : user have option to select based on child list and / or parent list in the first field i.e. child field. Once details are field in child the parent details will populate in parent field which is a display only field.

0 Likes
Outstanding Contributor.. Amishra Outstanding Contributor..
Outstanding Contributor..

Re: Limiting Auto Complete Muli-Select field

Jump to solution

Ok, that is not good. 

What is happening when you are selecting child validation from front end? Is there any error?

Other option would be to break the code (to debug) add some special characters in the sql for it to fail and then you can grab the exact SQL that is being passed in the validation. 

Did you try to remove the quotes?

 

Regards,
Ajay Mishra
0 Likes
Outstanding Contributor.. Amishra Outstanding Contributor..
Outstanding Contributor..

Re: Limiting Auto Complete Muli-Select field

Jump to solution

Apologies looks like this TO_STRING doesnt work in field validations - spent good couple of hours to see if that works. I will pass for now. It does work perfectly fine in portlets and workflow step command. 

One way to solve this problem would be to define this parent child relation ship in some custom table and pull information directly from there - that way you will not have to pull it via tokens. 

Alternatively, you may contact Microfocus and ask them why 'TO_STRING' is not working and see what they have to say. 

Apologies once again. 

 

Regards,
Ajay Mishra
0 Likes
Outstanding Contributor.. Amishra Outstanding Contributor..
Outstanding Contributor..

Re: Limiting Auto Complete Muli-Select field

Jump to solution

Hi there again, 

I may have some solution for you, please refer attached doc; 

I created some similar setup in our environment and it is working. 

You may have to alter your parent validation to store number in the code, instead of character. 

Regards,
Ajay Mishra
Respected Contributor.. M_A_A Respected Contributor..
Respected Contributor..

Re: Limiting Auto Complete Muli-Select field

Jump to solution

Ajay, you have come through once again!!!  As always, thank you so much for assisting me with this matter.

Your multi-select example worked perfectly for my situation.  I have been trying so many different things, for weeks now, and nothing was working.  Your solution was the answer.

Its is frustrating that the obvious things did not work -

  1. replacing the token's alpha-chars and using the result in a SQL WHERE IN clause
  2. Using the WHERE IN with a token variable containing string chars
  3. the STRING_TO not working in Validation SQL
  4. And now the solution, requiring numeric values in Validation Item CODE fields, not ideal... but still a usable solution which i am now implementing.

I was about to create a MicroFocus support ticket to see if they could figure it out.  But once again Ajay GREAT WORK on your part.

Thanks so very much for working this out for us.  I owe you a debt of gratitude!

Michael A.

Outstanding Contributor.. Amishra Outstanding Contributor..
Outstanding Contributor..

Re: Limiting Auto Complete Muli-Select field

Jump to solution

Cheers Michael, Always a pleasure and yes, it is frustrating! I have a ticket open with MicroFocus to see why .TO_STRING doesnt work in validations. 

If i hear back any further from them i will let you know. 

Regards,
Ajay Mishra
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.