Tersus: Find

FindDate: Jul 10, 2017 Find - Retrieve selected records from a database table or view This action retrieves from a database table (or view) all records (or all records that match a specific selection condition) and returns multiple data structures, each containing the content of one of the retrieved records. The database table/view is identified by the tableName property of the repetitive exit's data type. This action does a "simple find" - either selects all records from the table/view, or records in which specific fields contain exactly the values specified by the action's triggers. When more complex retrieval conditions are required, use Advanced Find or Database Query. To learn more on the way data structures are saved as database records, refer to Insert. Triggers [none required] The action can be used with any number of triggers (including none). If the action has no triggers, then all the records in the table/view are retrieved. If you want to retrieve all records for which a specific field has a certain value, use the action with one trigger, whose name is the name of the corresponding leaf of the output data structure. E.g. if the output is a structure with leaves Name and Age, and you want to retrieve all records where Age has a specific value, use the action with one trigger Age (the value of which will determine at runtime what records are retrieved). Similarly, if you want to retrieve all records based on the values of multiple fields, add a trigger for each leaf whose content should be used to select the records. In the above example, use two triggers, one called Name, specifying the required value of the Name leaf, and one called Age, specifying the required value of the Age leaf. The name and data type of each trigger should exactly match the leaf whose content it specifies. Thus, if the Age leaf of each output data structure is of type Number, then the Age trigger of the action should also have a Number data type. The trigger's name must be Age, not anything else (e.g. age or AGE). Be careful when using optional triggers - if no input is received through an optional trigger, then the corresponding field in the database record should be empty (null). Any record with a value in this field will not be retrieved (in a future release there will be an option to ignore a missing value and interpret it as "anything" - retrieving records regardless of the content of the database field). <Order By> (non-repetitive trigger [not required]): Optional sorting criteria specifying the order of the returned data structures.

The sorting criteria is a comma separated list of record field names (e.g. "Age" specifies sorting the returned records in increasing order of age). If sorting in decreasing order is required, the field name should be followed by "DESC" (e.g. "Age DESC" specifies sorting in descending order of age; "Age DESC, Name" specifies sorting in descending order of age, and for each age, sorting alphabetically according to the name.

Technically, <Order By> is the "ORDER BY clause" of an SQL "Select statement", but you don't have to be familiar with SQL to do simple sorting. The names of the record fields mentioned in <Order By> should be their names in the database, which match the names of the corresponding leaves of the output data structures (e.g. if we add a Family Name leaf to the data structure, then the corresponding field in the record would be Family_Name).

<Data Source> (non-repetitive trigger [not required]): Name of data source to use (if different from the default data source; see Connecting to an External Database). <Auto Commit> (non-repetitive Boolean trigger [not required]): Indication whether to interact with the database in auto-commit mode.

If <Auto Commit> exists and a Yes value is received, the database action is performed in auto-commit mode, regardless of any active transaction (see Database Transaction Support in Tersus).

Exits <Records> (repetitive exit [required]): The retrieved records (each output item is a data structure containing the content of one of the retrieved records). The data type of the exit should have a tableName property specifying the name of the database table/view from which records are retrieved. <None> (non-repetitive empty exit [not required]): Indication that no matching record has been found. Notice: The two exits represent two mutually exclusive possibilities: The repetitive exit <Records> is activated only if at least one record has been retrieved; the empty exit <None> is activated only if no record has been retrieved. Usage Example 1 "Common/Templates/Database/Find/Test3/Test Sorting":

Usage Example 2 "Generate Requisition List" (in stage 8 of the tutorial):