The ad-hoc lookup SQL statement can be any valid SQL Statement. For example:
SELECT * from VendorCache
SELECT VendorName, VendorCode from VendorCache
SELECT * from VendorCache WHERE CompanyCode = ‘abc’
SELECT * from VendorCache WHERE CompanyCode = ‘abc’ ORDER BY VendorName
Likewise, any stored procedure can be used. For instance, if you had a sproc ‘GetVendors’, it can be named:
GetVendors
Named parameters can be used with Stored Procedures. To use a named parameter, enclose the parameter in angle brackets, with a closing bracket preceded with a /, like an xml tag:
GetVendors <@CompanyCode = abc/>
Field values on a Smart Form field can also be passed to a lookup. This is done by using a token, tokens are enclosed within XML tag begin < and end /> tags, in the form of <[fieldname].Text/>
For example, if you have a field named VendorCode the token would be:
<[VendorCode].Text/>
The Execute as Stored Procedure option must be unchecked, otherwise the tokens will not be replaced.
EXEC Custom_GetVendorDefaultRemitCode @VendorNum='<VendorCode.Text/>'
Including Control Values in Lookups