Project and Portfolio Management User Discussions
cancel

SQL Validations not populating as expected

SOLVED
Go to solution
Highlighted
skbd Super Contributor.
Super Contributor.

SQL Validations not populating as expected

Has anyone else run into this?  The validations behind fields on my forms and CRs were populating as expected, but just recently the ones which do DB SQL lookups are now giving me a new message::

"Please make an entry above and click "Find," or show all possible values"

The 'show all' is a link which, when clicked, gives me the proper set of values the SQL should have provided right up front.

Now, my users have to click an extra time to do their work. 

Anyone have any ideas on this?Validations.JPG

1 ACCEPTED SOLUTION

Accepted Solutions
MIF Respected Contributor.
Respected Contributor.
Solution

Re: SQL Validations not populating as expected

Hello there,

You can check if you accidentally set AUTO_COMPLETE_LONG_TYPE_CULLTEXT_REQUIRED server configuration parameter to true. 

 

Murat Akbar
MIF Software & Consultancy - Istanbul
http://www.mif.com.tr
8 REPLIES
skbd Super Contributor.
Super Contributor.

Re: SQL Validations not populating as expected

I failed to mention, that I am running Version 9.32.0002

MIF Respected Contributor.
Respected Contributor.

Re: SQL Validations not populating as expected

Hello,

It seems you're having problem with user input part of sql.
That part looks like  "Upper(something) LIKE Upper('?' || '%')..."
If you post sql we could help more.

Murat Akbar
MIF Software & Consultancy - Istanbul
http://www.mif.com.tr
skbd Super Contributor.
Super Contributor.

Re: SQL Validations not populating as expected

That was what I thought too at first, but then none of my validations have changed during this period when I am seeing this and it is showing up on more and more of the validations as time progesses.

I did shut down the application and removed the tmp/ and work./ directories and re-ran kUpdateHTML.sh as well trying to sort out the issues, but that had no effect.

I also checked server resources but nothing seems to be lacking.

I am really confused by this new behavior.

 

skbd Super Contributor.
Super Contributor.

Re: SQL Validations not populating as expected

Here is one of the validation SQLs that is showing the behavior - it has not been changed in over a year I am sure:

SELECT distinct(u.user_id), u.username, u.first_name, u.last_name, u.email_address
FROM KNTA_USERS u,knta_user_security us, knta_security_groups sg
WHERE  u.user_id = us.user_id
AND sg.security_group_id = us.security_group_id
and sg.security_group_name like 'ACI % Developer'
and u.username <> '[REQD.VP.UAT_TESTER]'
AND '[REQ.APPLICATION_CODE]' in (u.visible_user_data1,u.visible_user_data2,u.visible_user_data3,u.visible_user_data4,u.visible_user_data5,u.visible_user_data6,u.visible_user_data7)
AND NVL(u.start_date, CURRENT_DATE) <= CURRENT_DATE
AND NVL(u.end_date, CURRENT_DATE + 1) > CURRENT_DATE
order by 2

skbd Super Contributor.
Super Contributor.

Re: SQL Validations not populating as expected

Today, I found that the OOTB validation for the Request field in the Search | Request screen is showing the same behavior.

MIF Respected Contributor.
Respected Contributor.
Solution

Re: SQL Validations not populating as expected

Hello there,

You can check if you accidentally set AUTO_COMPLETE_LONG_TYPE_CULLTEXT_REQUIRED server configuration parameter to true. 

 

Murat Akbar
MIF Software & Consultancy - Istanbul
http://www.mif.com.tr
skbd Super Contributor.
Super Contributor.

Re: SQL Validations not populating as expected

This seems to have worked!!

I have no idea if the setting changed or not - I certainly didn't change it.

What exactly does that particular setting do? will there be any other downstream impacts as a result of having made the change?

MIF Respected Contributor.
Respected Contributor.

Re: SQL Validations not populating as expected

Hi,

Parameter is there for performance improvement as well as user experience improvement. If you have very large set of values for auto complete fields it will take some time to populate them all.

For short, if you don't have performance problems or users complaining about waiting for a list to be populated then leave the parameter as false.

Murat Akbar
MIF Software & Consultancy - Istanbul
http://www.mif.com.tr