Lookup SQL Statement Syntax and Usage

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 begin < and end > tags, in the form of <fieldname.Text/>.

For example, if you have a field namged 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/>'