Select Fields

A field in a TStruct with mode of entry as Select is a select field. For a select field , a drop down list will be provided at run time. Selection fields are classified into the following.

  • From TStruct
  • From SQL result
  • From List

Selection from TStruct

  • A Select-From TStruct  field  is a field that has the mode of entry as Select with From TStruct in Details property.
  • To get a list of data entered in another Tstruct, the Tstruct name and a field name is chosen in Select TStruct and Select Field to list in selection window.Based on the selection made,an SQL statement is automatically framed and displayed. This can be altered to suit the requirements.
  • The SQL statement should have minimum 2 fields. The first field is called the id field and the second field is called the selection field. The selection field values will be shown in the drop down box for selection at run time. When a value is selected, its corresponding id field value is stored into this field in the db table.
  • Referential integrity is created between the field in the current tstruct and the tableid field of the source table.
Select From Tstruct

Select From Tstruct

Example

In a Item issued screen, the item name could be a drop down from the Inventory master. This can be defined as follows

Mode of entry= Select

Select TStruct(source)= Inventory master

Select field to list in selection window(source field)= ItemName in inventory master

Auto Select

  1. If there is only one value in the drop down and the Auto Select is set to true, then the value will be automatically selected without the user having to choose its value.

Selection window height & width

  1. The Selection window height and width can be changed. The height & width are set as number of pixels.

Data source

  1. Specify the Data source in case you are selecting the Tstruct from another schema.

Selection from SQL

  • A Select-From SQL  field  is a field that has the mode of entry as Select with From SQL Result in Details property.
  •  The result of an SQL is shown as drop down values. The Source field is the name of the field that is displayed as drop down values. The db table to which the source field belongs to is called the source table. This field is also referred as the selection field.

Example

In a sales invoice screen, the product names could be picked from a list of products. The list should display only products for which stock is available. This can be implemented by defining the product field as selection field in the invoice screen. An SQL statement can be attached to the field. The SQL statement can be used to get only a list of products that have stock by joining to the stock table.

Save normalized

  1. If save normalized is set on, then the record id will be saved in the field and not drop down value. The record id should be first field in the SQL statement and the selection field should be second field in the SQL. The record id should only be a numeric field. The selection field can only be a character field.
  2. If save normalized is off, the selection field should be first column in the SQL. The selected value will be saved in this field.
  3. Referential integrity is created between the field in the current Tstruct and the source field in the source table. If normalized, the referential integrity is created between this field and the tableid field of the source table.

Some rules for Select-From SQL

  1. The Source Tstruct can be provided optionally. This is used to provide a hyper link to the source tstruct. The hyper link will be available in list views if this field is displayed as a column in the list view.
  2. The SQL statement can have parameters. The parameters are preceded with colon. Any field in the current tstruct can be used as a parameter to the SQL.  The parameter field should be before the current field.
  3. Any portion of the SQL statement can be dynamically constructed. This is done by using a field name within curly braces in the SQL. At run time the value in the field will be replaced at the position of the tag. For example “Select name, address from {schema}customers” . “schema” is a field in this Tstruct that should be before this field. If the value in the schema field is ‘Master.’, then the resultant SQL statement will be “select name, address from master.customers”.
  4. The values entered in a grid field can be concatenated into a comma separated string and used in the SQL text. For example consider an SQL statement as ‘Select name, address from employee where empcode in (’emp001′, ’emp002′, ’emp003′). Emp001, emp002 & emp003 are values entered in a grid field. This can be achieved by defining the SQL as “Select name, address from employee where empcode in {empcode*}”. empcode is a grid field. All the values entered in this column will be converted as one string and replaced at the position of the tag.
  5. The fields that are used as parameters either through colon or through curly braces are called parameter fields. At run time, when the values entered in the fields are changed the SQL is fired and results are populated again. This is done only if all the parameters fields in an SQL statement have values.
  6. In case the number of rows in the drop down is more than a few thousand rows then the system will take a long time to display the list. So, an option to display the rows as pages is available. This is achieved by defining a dynamic filter.
  7. An expression can be provided for a select field. The result of the expression will be set as the default value for the field. The default value will be populated into the field only if the field value is empty.
  8. Any value entered or selected in a select-field should be part of the SQL result.
  9. The fill-fields that depend on a select-field should be a column in the SQL result.
  10. If Auto select is set to true and the SQL returns only one row, then the value will be automatically selected.
  11. All the rules are applicable even when the field value is filled through a CSV import, SQL post or a Genmap.

Selection from List

A Select-From List  field  is a field that has the mode of entry as Select with From List in Details property.The dropdown values will be a list of predefined values.

Example

Consider a field that should display a pre defined list of units of measurement as choices in the drop down. The field is a selection field with values From list. The units of measurements can be entered as list values.

Some rules for Select-From List

  • The maximum width of the list values cannot exceed 50 characters.
  • Only values in the list can be selected into this field. This validation is applicable even when field values are posted through genmap or done through CSV import.
  • A default value can be set into this field by setting it an expression.