21. 4GL Statement Glossary : Look_up()
 
Share this page                  
Look_up()
Fetches field values from the database or an array so that the application user can select from a pop-up list.
Syntax
ret_val = callframe look_up 
  ( ii_query =  query; |ii_array = array,
  columnname = byref (varname),
     {columnname = byref (varname),}
  ii_field1 = column {, ii_fieldn = column}
  [,ii_qualify = ' ']
  [,ii_field_titlen = fieldtitle ]
  [,ii_titles = 1 ]
  [,ii_title = title]
  [,ii_rows = rows]
  [,ii_startcolumn = startcolumn]
  [,ii_startrow = startrow] )
ret_val
Specifies an integer value determined as follows:
If you use ii_query, ret_val is the number of the row in the data set that was chosen. If no row was chosen, ret_val is a non-positive integer (0 or a negative number).
If you use ii_array, ret_val is the array index of the row that was chosen. If no row was chosen, ret_val is a non-positive integer (0 or a negative number).
ii_query
Specifies the 4GL select query that fetches the information to be displayed in the pop‑up table field and returned to the varname parameters. Used when the lookup comes from the database.
ii_array
Specifies the array containing the information to be displayed in the pop‑up table field and returned to the varname parameters. Used when the lookup comes from an array.
columnname
Specifies the field in the look_up() pop‑up from which you select the value; determined as follows:
If you use ii_query, then columnname is the name of a column returned by the query (one columnname is required).
If you use ii_array, then columnname is the name of an attribute in the array (columnname is optional).
The data from columnname is returned to the corresponding varname.
varname
Specifies the 4GL variable or simple field to which the value of columnname is returned.
ii_fieldn
Specifies a string expression whose value is the name of the nth table field column to be displayed in the lookup table field, determined as follows:
If you use ii_query, ii_field is the name of a column returned from the query.
If you use ii_array, ii_field is the name of an attribute in the array.
You must include at least one field.
ii_qualify
If you specify ii_qualify = '', you are prompted whether to qualify the values in the query before fetching them. You cannot specify any other value than ''.
You are not prompted if you omit the keyword ii_qualify.
You can use ii_qualify with ii_query only.
ii_field_titlen
Specifies an optional string that is used as the title for the nth column of the table field. If not specified when ii_titles is specified, the column name is used.
ii_titles
If you specify ii_titles = 1, the columns in the lookup table field are titled.
If you omit the ii_titles parameter, and do not specify any ii_field_titlen parameters, the columns are not titled.
ii_title
Specifies an optional string to be used as a title for the whole displayed table field
ii_rows
Specifies the number of rows to be displayed in the table field. Default: 4.
ii_startcolumn
Specifies the starting column for the displayed pop‑up lookup form. If not specified, the pop‑up is displayed floating.
ii_startrow
Specifies the starting row for the displayed pop‑up lookup form. If not specified, the pop‑up is displayed floating.
Description
The look_up frame is a built-in function you can use in ABF applications. It allows you to implement a user-specified lookup of form values from the database or an array with a single call. The look_up frame is not available in QUEL.
The command retrieves all rows selected by the query (or all rows in an array), displays the values found in a pop-up table field with columns as specified by the ii_field values, and provides a selection menu. When the application user selects a row of the table field, look_up returns the value from the selected source column of the table to the variables (or fields on the current form) that you specify in the byref() clauses.
Look_up returns a status value that is the number of the record in the array or data set for the look_up query that was selected by the end user. If no records are selected, or you cancel the query, look_up returns 0 or a negative number.
The following table lists the keyword parameters for the look_up frame. To select a value, you must include either of the following:
ii_query, ii_field1, and at least one columnname parameter
ii_array and ii_field1
If you use ii_query, and the ii_qualify parameter is passed an empty string, the look_up frame qualifies the input query. Look_up() displays a qualification form that lists the displayed columns to be fetched and allows you to enter qualification views. This is in addition to any qualifying conditions specified in the where clause of the query.
To qualify a query, the frame displays a qualification form that lists the displayed columns to be fetched and allows you to enter qualification values for these columns. Only displayed columns are qualified; hidden columns are not qualified and thus are completely unknown to the end user.
The qualification form runs in FRS query mode, which enables the values to be qualified by operators entered along with the field values. It appears in the window as a pop‑up form if it is small enough; otherwise it appears in full‑screen size.
The look_up() qualification frame has the following menu items:
Go
Executes the lookup frame with the qualified values
Cancel
Cancels the operation and return to the previous frame
Help
Displays help for the frame
After 4GL qualifies and runs the query, the rows appear in a table field with the columns specified by the ii_fieldn keywords. Additional attribute values fetched by the query but not specified as display columns are hidden columns in the table field. That is, the query must specify and fetch all desired columns while the ii_fieldn keywords specify those that are displayed. This table field appears in pop‑up form if it is small enough.
By default, if you specify ii_titles = 1, the titles of the columns are the same as the names of the columns fetched from the database (if you specified ii_query) or the names of the attributes of the array (if you specified ii_array). Specifying the title columns by the values in the ii_field_titlen keywords is optional. No column titles are displayed if neither ii_titles nor any optional column titles are specified.
If you specify ii_query and the query does not fetch any rows, the "No rows retrieved" message appears and the look_up frame returns without setting any of its input parameters. If only one row is fetched, the lookup function returns immediately without displaying the row and sets any varname parameters with values from that row. The look_up frame runs this form with the following menu items:
Select
Selects the current row
Cancel
Returns to the calling frame without returning any values
Help
Displays Help for this frame
The Select menu item returns the values of the current row by reference (including hidden columns) to the calling frame as specified by the named column parameters. These named parameters, which correspond to each of the columns fetched by the query, identify global variables in the look_up frame from which values can be returned when a row is selected.
You can use by reference expressions to manipulate the values in fields, table field columns, record attributes, and array record attributes by referencing the desired component.
Examples
The following example fetches all the rows of the database table Description, which has columns name, id, and description. It displays the values of the name and description columns in a pop-up table field along with a selection menu. When you select a row of the table, the statement returns the value from the id column of the table corresponding to the selected row into the field Proj_id on the current form. Note that commas are used as parameter list separators.
callframe look_up 
  ( ii_query = select * from description;
    id = byref(proj_id),
    ii_field1 = 'name',
    ii_field2 = 'description');
For an example of filling a lookup table from an array, see the confirm procedure in the Employee sample application chapter, Sample 4GL Application.