Filtering Lookups

A coworker sent me an email today asking about a SQL query for returning results based on the value in a setup table.  I sent him the correct syntax and then inquired as to the purpose of this.  His response was that he had a request from a customer to filter the item lookup on the sales line table based on a setting in Sales & Receivables Setup.  He was going to use a table linked to a view, which among other things would be rather messy.  I then proceeded to show him the following method for creating a filtered lookup.

It is a little known fact that code in the OnOpenPage trigger of a lookup page is executed when the lookup dropdown is displayed.

Here is a lookup to the item table in a Cronus database.

Now, let's say we want to filter to just items beginning with "11".  We could add this code to the OnOpenPage trigger of the Item List.

Now the lookup returns this.

Now, what if we want to make it conditional?  In this case, the filter not only needed to be active based on setup but also be active based on context.  When the lookup was being done from the sales line, the lookup needed to be filtered but when the item lookup was accessed from anywhere else, it needed to be unfiltered.  That had me stumped for a few minutes but then I realized there was a simple solution.  In the relationship between the Sales Line and the Item table, we could introduce a meaningless filter to notify the lookup that the filter needed to be active.

Here is a filter that serves no purpose whatsoever but can be picked up by the item list.  The fact that it's in FILTERGROUP 0 is an added bonus

Going back to the item list, we just need to change the code a little.

Now, the item lookup on sales documents is filtered but the item list is unaffected in other areas of the system.

We can also add other criteria to this as needed, such as enabling it for certain users.