Showing results for 
Search instead for 
Do you mean 
Community Home Request Access Read Blogs Share Your Ideas Search Community View My Settings
Reply
Copper Elite Contributor
Posts: 72
Registered: ‎04-01-2009
Accepted Solution

Exclude certain records from a lookup - LAN v7.2.1

Hi all.

 

We currently have a lookup that looks at a field (myField) in a table (C_myTable), the lookup uses a pick list (Company Names)

 

The Main Table: ACCOUNT

Search Field: ACCOUNT.myTable.myField

ID Field: ACCOUNT.Accountid

Name Field: ACCOUNT.Account

Pick List: Company Names

Field Type: String

 

But we want to change it so it only displays records whose (myField_Status) is ACTIVE. How can I do this from the Edit Lookup form?

 

Thank you in advance!

Gold Super Contributor
Posts: 3,087
Registered: ‎03-19-2009

Re: Exclude certain records from a lookup - LAN v7.2.1

[ Edited ]

VERY easy.. here's something right out of my KB that we have been using for years.... This is a method that was originally posted to the Partner's NG:

 

 

'HowTo - LookupEdit with Multiple Restrictions
A question that surfaces fairly often deals with LookupEdit controls and how to do more with the LookupRestriction properties, such as add multiple conditions to the restriction. Although it is undocumented, you can do that with the existing LookupEdit - you just have to put things in the wrong place. Let's say you want to add a restriction to a LookupEdit for all contacts with an areacode of "623" AND that have a title of "Developer".
LookupRestrictField = "WORKPHONE"
LookupRestrictOp = " LIKE '623%' AND TITLE = 'Developer' AND "1" = "
LookupRestrictValue = "1"
Or you could do it like this:
LookupRestrictField = "CONTACTID"
LookupRestrictOp = " IN (SELECT CONTACTID FROM CONTACT WHERE WORKPHONE LIKE '623%' " & _                   
                         "AND TITLE = 'Developer') AND "1" = "
LookupRestrictValue = "1"
A few things to point out, make sure you notice the extra space at the start of the operator (ie: before "IN" or "LIKE" etc). This is needed because when the final query is constructed it just appends the three values together without spaces (so you'd get something like "CONTACTIDIN..." without the space). Also, the reason why you have to put all that in the RestrictOp property is because when the final query is constructed the RestrictValue gets enclosed in single-quotes. That is also the reason for the AND "1" = at the end.
--
RJ Ledger - rjledger@rjlSystems.net +1 603.369.3047 x101

".. Innovators in Mobility - Experts in Workflow Automation..."
http://www.rjlSystems.net - blog: www.rjlSystems.net/blog.html
Copper Elite Contributor
Posts: 72
Registered: ‎04-01-2009

Re: Exclude certain records from a lookup - LAN v7.2.1

Thanks for the reply RJ. But do I have to add this to a plugin?

 

I can't do this from the Lookup Manager in the Architect?

Silver Contributor
Posts: 835
Registered: ‎03-24-2009

Re: Exclude certain records from a lookup - LAN v7.2.1

you change the actual LookupEdit Control itself......not the Lookup.....

you need access to the Form\Plugin that the LookupEdit Control is on.....

 

 

RJ Samp