A lens to lookup descriptions of codes and create popups for <select> options
Default:
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 dropdown/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" are equivalent.
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';
Hotupdate Selection
Sometimes you want to have a dropdown menu that changes the selected option when another field changes. This is not your typical hotupdate (where the options also change); in this scenario, only the selection changes, the options displayed do not change. Available in phplens 4.9.11 and above.
To implement this, use
$lens->lookupLens = 'select val,txt from table^SELECT^#select val,txt from table where key={OTHERFIELD}';
The first sql is used to populate the options. The 2nd sql is used to select the options based on a hotupdate due to changing {OTHERFIELD}. If the 2nd sql returns multiple rows, only the first row is selected if the input item is not a multiple select.
Multiple SQL Lookups
We now allow you to have multiple SQL lookups. For example, if field1 has a SQL lookup depending on fieldA, and has a different SQL lookup when fieldB is changed, then you can implement it provided you define which event triggers it:
$lens->sql = '#select a from table where col={fieldA};^-VIEW^'#select b from table where col={fieldB}^-VIEW';
In the above example, when editing or creating records (-VIEW or !VIEW), the SQL used for the lookup will depend on the field selected. This also works when using hotupdates (SELECT). In this case make sure you make all fields dependent on the hotupdate:
$lens->sql = "#select lookups from table where col={fieldA};^SELECT^#select selectedval from table where col={fieldB} and {fieldA} != 'Some Rubbish String #$%!";
Selective Autoupdate
Disable autoupdate on change in hotupdate with lookupLens. For example:
$lens->lookupLens =
'MTH^#select mths from table where year={YR};'.
'DAY^#select days from table where year={YR} and mth={MTH}';
In the above case, MTH always changes when YR is modified, which would cause DAY to be updated twice (once for YR and once for MTH), so we use the {#YR} syntax to indicate that YR is used by DAY field's SQL, but does not auto-update DAY when YR changes, and auto-update only when MTH changes.
$lens->lookupLens =
'MTH^#select mths from table where year={YR};'.
'DAY^#select days from table where year={#YR} and mth={MTH}'; // use {#YR}
Syntax
$lens->lookupLens='Column^sql statement';
$lens->lookupLens='Column^=1/one/2/two';
$lens->lookupLens='Column^==1/2/3/4'; # values and text of options are the same
$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]
|