Back

Editing, Updating and Creating Records
searchLookupLens
Dynamically search for record and store results in fields when editing or new record Suppose you have an customer complaint form (accessing the COMPLAINT table) where you need to search for a customer name, and store the customer id and address in the fields from the CUSTOMER table. However you don't want to use a normal lookupLens because your CUSTOMER table has over 100,000 records. So you want the user to enter a search string first to narrow down the list of records to return from the server, and then click on a search button. As shown below: [ Enter Customer Name ] [SearchBtn] After clicking on the search button, a list of records will be displayed, and the user can select one of the records. This will copy the selected CUSTOMER.NAME to the COMPLAINT.NAME field, and copy the search results from CUSTOMER.NAME to COMPLAINT.NAME, CUSTOMER.ID to COMPLAINT.CUSTID and CUSTOMER.ADDRESS to COMPLAINT.ADDRESS. To add a Search button to the name field, use the following. The {#} is replaced with the text the user enters: $lens->searchLookupLens = "name^select name from customer where name like '{#}%' "; To allow searching only in the New record form and not the Edit record form, place after the SQL statement this option !NEW : $lens->searchLookupLens = "name^select name from customer where name like '{#}%' ^!NEW"; Suppose you don't want to populate the New record form when you find a match, but switch to editing the selected record, set the 2nd field of the select statement to the primary key of the record to be edited (custid in this example), and place after the SQL statement this optiion !NEWEDIT : $lens->searchLookupLens = "name^select name,custid from customer where name like '{#}%' ^!NEWEDIT"; To copy the CUSTOMER fields (name,id,address) to the COMPLAINT fields (name,custid,address), use: $lens->searchLookupLens = "name^select name,name,id,address from customer where name like '{#}%' ^name^custid^address"; The column name is repeated twice in the SQL statement because the first mention of name is the text to display, and the second is the field to copy from. Now you might require the user enter at least 3 characters in the search field before you allow them to click on the Search button. To do so add a '>3' to the property: $lens->searchLookupLens = "name^select name,name,id,address from customer where name like '{#}%' ^name^custid^address^>3"; Advanced Lookups The above examples assume that you are modifying the name directly. But suppose we store cust_id, a foreign key to the CUSTOMER table in COMPLAINT.cust_id and not the name. We don't want the user to search for the cust_id, which is an integer and internal to the database. We want to search by name instead. First we need a mapping between the CUSTOMER and COMPLAINT tables. This is done using the normal lookupLens. We don't want to pull the whole CUSTOMER table over (to PHP) to do a join, so we use a hot update that only works in NEW and EDIT mode. $lens->lookupLens = 'cust_id^#select cust_id,name from customer where cust_id = {cust_id}^-VIEW'; Then have a searchLookupLens for the cust_id field, such as the following: $lens->searchLookupLens = "cust_id^select name||'-'||address,name,cust_id from customer where name like '{#}%' "; There must be 3 columns in the select statement, first the column to show to the end-user to select from a popup (in this example, the name followed by the address), second the value to display in the text field after selection, and third the actual value to store in the COMPLAINTS table, cust_id. It will create special input tags like this: [ Enter name here ... ] [cust_id] [Search Btn] The [cust_id] field can be set by selecting an item from the results of the search. Basic:No Advanced/Enterprise:Yes DynamicEdit:Yes [Version 3.1]