Back

Table Cell formating
lookupLens
A lens to lookup descriptions of codes and create popups for <select> options There are 2 types of lookup values. The first is looking up the description of a code. For example mapping the country code ca to Canada, and uk for United Kingdom. The second is when you are editing a record and you want the user to be able to lookup all legal values for that field in a popup menu. 1. Lookup a Code Sometimes you are using an abbreviation or a code in a column. You want to translate the abbreviation to something more natural. Use the Lookup Value feature to do so. You can lookup a value by using a SQL select statement where the first column selected matches the code/abbreviation, and the second column matches the translated value to display. See example below. SELECT col_id,col_to_display FROM table To lookup the country names from a list of country codes, you could use: SELECT Code, CountryName FROM CountryTable Alternately you can text mappings. Make the first character equals (=), and follow it with your mappings. In the example below, we want to set "1" to lookup "one", "2" to lookup "two", etc. =1/one/2/two/3/three/4/four 2. Lookup Popup Values When editing or creating a record, you can give the user a list of legal values to select from by entering SQL of the form SELECT DISTINCT colname FROM table If you do not want to use SQL and want to perform text lookups, make use double equals(==) and follow it with your mappings. In the example below, we want legal values to be Yes or No: ==Yes/No The double equals is available in phpLens 1.1 onwards. Using variables in your SQL In phpLens 1.2 onwards, you can access PHP global variables in your SQL statement provided you prefix the statement with a '%'. See examples in the syntax section. Hot Update of Lookups You might have a popup that is tied to the value of another field. For example, if you have a popup field taken from a SQL column called "states" that displays the US states, and a second popup field with data taken from SQL column "cities" that displays the cities in that state. When you change the state, the cities popup will change also. You can perform a hot update of the cities popup like this: `$lens->lookupLens="states^select name from states;"; $lens->lookupLens .= "cities^#select name from cities where state={states}"; $lens->phplensDatabase = 'oracledb'; // database defined in phplens.config.inc.php` Place a # as the first character of the select statement of the field that requires a hot update, and incorporate the controlling field (states here) as part of the sql statement as a {variable}. This hot update uses javascript to make a request to phplens/phplens_remoteq.php. You might need to configure the above file so that the correct database connection settings are set. The remoteQuery and phplensDatabase properties might need to be configured also. The phplensDatabase property is the name of the $PHPLENS_DATABASE entry (from config/phplens.config.inc.php) being used to connect to the database. Alternatively, you can modify the phplens_remoteq.php script yourself. The remoteQuery property is the full http path to the phplens_remoteq.php file - you only need to set this property if you move this file out of the phplens directory. The SQL used in the hot update is stored in the html generated by phpLens. As an extra security step, the SQL string is fingerprinted using MD5 with a salt. The salt can be configured using the constant PHPLENS_MD5_SALT in phplens.inc.php. The file phplens_remoteq.php detects if the SQL has been tampered with using the MD5 fingerprint and refuses to execute the SQL in this case. This works in both Edit and Filter mode. Since phpLens 1.3.15. Evaluating PHP Code in Normal Lookup New to phpLens 1.9 is the ability to execute PHP code. =% means evaluate PHP code, the PHP code must return values in a string of the form "1/one/2/two". Eg. $lens->lookupLens="col1^=%ReturnString()"; ==% means evaluate PHP code, the PHP code must return values in a string of the form "1/2/3/4". Evaluating PHP Code in Hot Update Since 3.3, phpLens allows you to execute a function in a Hot Update. All source code must be placed in the APPLETS_DIR.'/remoteq' directory, typically found in phplens/builder/applets/remoteq. Remoteq stands for remote query. For example, you have a field emp_name that hot updates based on changes in the company_id field. The hot update calls a PHP function called 'test', stored in the file 'remoteq/test.inc.php'. Then you define in the lookup: $lens->lookupLens = 'emp_name^#MetaPHP test.inc.php test({company_id})'; The first character, #, indicates that it is a hot update. The parameter MetaPHP indicates that is is PHP code, and not SQL. The following parameter 'test.inc.php' is the name of the file located in the remoteq directory. Lastly, the function (defined in test.inc.php) and the parameters follow. The {company_id} means that company_id is passed as the function's first parameter, and is called every time the company_id changes. The function must return an array, or false on failure. If string keys are defined, then they are the option values, while the values become the option text. One global variable is available to the function, $DB, which is the default database connection. For example: `function test($co_id) { global $DB; $co_id = (integer) $co_id; // sanitize param for security $rs = $DB->Execute("select emp_no,emp_name from employees where company=$co_id"); if (!$rs) return false; return $rs->GetAssoc(); # eg. array('23' => 'Jack Szo', '24' => 'Mary Lamb'); }` Note: We use a very simple parser. Each parameter must be separated by a single space. Conditional Lookups There is a 3rd lookupLens parameter which allows you to define in which states (EDIT, VIEW, NEW, FILTER, FILTERVIEW) the lookupLens is evaluated. The following means use 1st SQL lookup when editing, otherwise for all other states, use the second SQL stmt. $lens->lookupLens='col1^select f from table^EDIT^select f2 from table2'; To simplify matters, two additional states were added: "+VIEW" means display this lookup in VIEW, FILTER and FILTERVIEW states. Conversely, we have "-VIEW" which means display this lookup in EDIT or NEW states. $lens->lookupLens='col1^select fld from table^+VIEW^select fld2 from table2'; This means that the 1st select statement is used when VIEWing the data (VIEW/FILTER/FILTERVIEW states), otherwise the 2nd select statement is used. $lens->lookupLens='col1^select f from table^VIEW^select f2 from table2^FILTER^select f3 from table3^FILTERVIEW^select f4 from table4'; In the above example, VIEW, FILTER and FILTERVIEW states have their own select statements. All other lookups use "select fld4 from table4". Note that "-VIEW" and "!VIEW" mean the same. Implementing a Single Checkbox For boolean fields which require a single checkbox, try this (for the field checkcol, with a label 'Yes'): $this->inputTypeLens = 'checkcol^checkbox'; $this->lookupLens = 'checkcol^=1/Yes'; Syntax `$lens->lookupLens='Column^sql statement'; $lens->lookupLens='Column^=1/one/2/two'; $lens->lookupLens='Column^%select value,caption from $table'; $lens->lookupLens="Column^%select value,caption from map where key=\$key";` Basic:Yes Advanced/Enterprise:Yes DynamicEdit:Yes [Version 1.0]