{"id":4079,"date":"2016-05-06T20:20:50","date_gmt":"2016-05-06T20:20:50","guid":{"rendered":"https:\/\/alt2.minisoft.com\/support\/?p=4079"},"modified":"2020-06-25T13:37:50","modified_gmt":"2020-06-25T20:37:50","slug":"eformz-table-lookup","status":"publish","type":"post","link":"https:\/\/minisoft.com\/support\/eformz-table-lookup\/","title":{"rendered":"eFORMz Table Lookup"},"content":{"rendered":"<p>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.<\/p>\n<p><strong>Requirements<\/strong><\/p>\n<ul>\n<li>eFORMz 10.6 or later<\/li>\n<li>A database connection to a table with customer data<\/li>\n<li>An eFORMz project that creates PDF files of customer invoices<\/li>\n<li>A\u00a0variable in the project that uniquely identifies the customer, such as a customer number or name. Creating a variable like this is explained in\u00a0<a href=\"\/support\/index.php\/positional-vs-direct-input-variables\/\">https:\/\/minisoft.com\/support\/index.php\/positional-vs-direct-input-variables\/<\/a>. This example uses a variable called CustNum.<\/li>\n<\/ul>\n<p>In this scenario, the invoice does not include customer&#8217;s email addresses. You can use a Table lookup using the variable CustNum to get the email address from the database.<\/p>\n<ol>\n<li>To create a variable for the email address, CustEmail, right click Variables &gt; Add Variable &gt; By position. Name the variable CustEmail and click OK.<\/li>\n<li>Right click the variable CustEmail &gt; Add Function &gt; Table lookup.<\/li>\n<li>Specify the following values:<br \/>\n<table>\n<tbody>\n<tr>\n<td><strong>Field<\/strong><\/td>\n<td><strong>Value<\/strong><\/td>\n<\/tr>\n<tr>\n<td>Database<\/td>\n<td>The name of the database connection to use<\/td>\n<\/tr>\n<tr>\n<td>Table<\/td>\n<td>The name of the table in the database<\/td>\n<\/tr>\n<tr>\n<td>Key<\/td>\n<td>\u00a0The column in the table that uniquely identifies the customer<\/td>\n<\/tr>\n<tr>\n<td>Column<\/td>\n<td>The column \u00a0in the table whose value is assigned to the variable<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>In this scenario, the key is the variable CustNum, and the column is email address:<img decoding=\"async\" loading=\"lazy\" class=\"size-medium wp-image-4096 aligncenter\" src=\"https:\/\/alt2.minisoft.com\/support\/wp-content\/uploads\/2016\/05\/Email-300x266.jpg\" alt=\"Email\" width=\"300\" height=\"266\" srcset=\"https:\/\/minisoft.com\/support\/wp-content\/uploads\/2016\/05\/Email-300x266.jpg 300w, https:\/\/minisoft.com\/support\/wp-content\/uploads\/2016\/05\/Email-150x133.jpg 150w, https:\/\/minisoft.com\/support\/wp-content\/uploads\/2016\/05\/Email-250x221.jpg 250w, https:\/\/minisoft.com\/support\/wp-content\/uploads\/2016\/05\/Email-203x180.jpg 203w, https:\/\/minisoft.com\/support\/wp-content\/uploads\/2016\/05\/Email-339x300.jpg 339w, https:\/\/minisoft.com\/support\/wp-content\/uploads\/2016\/05\/Email.jpg 383w\" sizes=\"(max-width: 300px) 100vw, 300px\" \/><\/li>\n<li>Click OK.<\/li>\n<li>Verify the correct values in the Variables window.<\/li>\n<\/ol>\n","protected":false},"excerpt":{"rendered":"<p>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 [&hellip;]<\/p>\n","protected":false},"author":12,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[91,3,1,10],"tags":[84],"_links":{"self":[{"href":"https:\/\/minisoft.com\/support\/wp-json\/wp\/v2\/posts\/4079"}],"collection":[{"href":"https:\/\/minisoft.com\/support\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/minisoft.com\/support\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/minisoft.com\/support\/wp-json\/wp\/v2\/users\/12"}],"replies":[{"embeddable":true,"href":"https:\/\/minisoft.com\/support\/wp-json\/wp\/v2\/comments?post=4079"}],"version-history":[{"count":4,"href":"https:\/\/minisoft.com\/support\/wp-json\/wp\/v2\/posts\/4079\/revisions"}],"predecessor-version":[{"id":8115,"href":"https:\/\/minisoft.com\/support\/wp-json\/wp\/v2\/posts\/4079\/revisions\/8115"}],"wp:attachment":[{"href":"https:\/\/minisoft.com\/support\/wp-json\/wp\/v2\/media?parent=4079"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/minisoft.com\/support\/wp-json\/wp\/v2\/categories?post=4079"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/minisoft.com\/support\/wp-json\/wp\/v2\/tags?post=4079"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}