Issue with Dynamic query lookup field

[MRPR]

Hi All

 

Currently, I've an issue with  dynamic lookup field. The lookup field values are not available for selection when typed and even when searched in the browse window, the value is not filtered. I've checked for the lookup filter suggestion and it's all looking good. Is there anything else that could cause this issue? 

 

Thanks in advance. 

 

Monica

[David Morton]

Sounds like the SQL that drives the dynamic lookup is not doing what you think it should do!

Perhaps if you post it here that might give us a clue?

[MRPR]

Hi Dave_3.0 - the query is simple one - just reading list of projects were the project manager is particular resources. 

select inv.id
, inv.name
, inv.code
from inv_investments inv
join srm_resources res on res.user_id=inv.manager_id
where (res.unique_name= '***' or res.unique_name='YYY')
and inv.odf_object_code='project'
and inv.is_active=1

I've mapped it to a attribute in a custom object and the search is not working on the attribute.

[David Morton]

Typo?

where (res.unqiue_name= '***' or res.unique_name='YYY')

[MRPR]

Yeah Exactly, I've it changed in the reply. The data looks good in actual lookup. I get rows returned in the lookup field. but the issue is that it's not allowing to me search in the lookup field or in the lookup pop up window 

[David Morton]

And you started up saying you had checked the definition of the lookup

Not sure I can add anything else, if the lookup is defined OK on the Browse Window and Auto-Suggest tabs then I'm out of ideas.

 

Perhaps create a new lookup (same SQL) and a new attribute and see if that works (i.e. something "weird" is going on) - would need to be talking to support then I think. 

[MRPR]

Yeah, i tried with it! But nothing changed. Still filter is not working on the lookup. I would have to check with CA support on it. 

[Tim1302647]

Did you include @FILTER@ in the NSQL for the lookup query?  I believe that has to be included in the WHERE clause in order to use filtering in the browse window.

[MRPR]

Thank you Tim1302647, that resolved the issue!