eFORMz Table Lookup

Table lookup is a simple way to get one value from a row of a relational database. If you require multiple values, use SQL Lookup. In this scenario, you have invoices that eFORMz makes into PDF files and semds email messages to customers. You have the customer number, but need to look up the customer email address to send it.

Requirements

• eFORMz 10.6 or later
• A database connection to a table with customer data
• An eFORMz project that creates PDF files of customer invoices
• A variable in the project that uniquely identifies the customer, such as a customer number or name. Creating a variable like this is explained in https://minisoft.com/support/index.php/positional-vs-direct-input-variables/. This example uses a variable called CustNum.

In this scenario, the invoice does not include customer’s email addresses. You can use a Table lookup using the variable CustNum to get the email address from the database.

1. To create a variable for the email address, CustEmail, right click Variables > Add Variable > By position. Name the variable CustEmail and click OK.
2. Right click the variable CustEmail > Add Function > Table lookup.
3. Specify the following values:
   

   Field	Value
   Database	The name of the database connection to use
   Table	The name of the table in the database
   Key	The column in the table that uniquely identifies the customer
   Column	The column  in the table whose value is assigned to the variable

   In this scenario, the key is the variable CustNum, and the column is email address:

4. Click OK.
5. Verify the correct values in the Variables window.