Skip to main content
Skip table of contents

Database Lookup


Providers

ODBC - Select this to use an ODBC datasource.

SQL - Select this when using a Microsoft SQL server.

Connection String

For ODBC provider using the Microsoft Text Driver would be:

DSN=odbccsv

For SQL provider would be something like:

Data Source=192.168.0.53;Initial Catalog=Active;User ID=sa;Password=Password

Query

This is the query to run, all of the values returned will get put into the results.

Placeholders

LookupFieldType = TextBox

Variable

Result

Example

{{searchValue}}

Searchvalue

SELECT SupplierName FROM Supplier WHERE SupplierName LIKE '%' + {{searchValue}} + '%'

{{searchValue|format("%{0}%")}}

%searchvalue%

SELECT RTRIM(SupplierName),RTRIM(SupplierID) FROM Supplier WHERE SupplierName LIKE {{searchValue|format("%{0}%")}}

LookupFieldType = DropDown, CheckBoxes, MultiSelect, or RadioButtons (can also use these for TextBox)

Variable

Result

Example

{{fields["dbs_suppliername"]}}

dbs_suppliername

SELECT RTRIM(SupplierName) FROM Supplier WHERE RTRIM(SupplierName) LIKE '%' + {{fields["dbs_suppliername"]}} + '%'

{{fields["dbs_lookup"]|split("|",2)}}

Splits dbs_lookup on | and returns 2nd part.  e.g. if dbslookup = 1|2|3  then {{fields["dbs_lookup"]|split("|",2)}} returns 2

SELECT RTRIM(SupplierName) FROM Supplier WHERE RTRIM(SupplierID) = LTRIM(RTRIM({{fields["dbs_lookup"]|split("|",2)}}))

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.