How to Work with Processes > Working with the Local Database Processes Group

Process Purpose

The "Local Database Select Record" process is used to retrieve a record from a table or view of the local database.

When you add a process, you are required to define its settings. This occurs in the process's properties window which is displayed automatically after having added the "Local Database Select Record" process. In this case, the properties window includes three tabs - "General", "Query" and "Advanced".

If any subsequent edition is required, double-click the process to open its properties window and enter the necessary modifications.

"General" tab

Fill in the following options:

Select … From …
Table Name	Use the drop-down to define the table to select data from. If needed, click to join tables. See To Join Tables. As an alternative, you can enter the name of the table or the variable containing the table's name. In either case, the required field names (in the "Field" column) must be entered manually and enclosed in square brackets []. Ex: The table name is defined with the variable "P_descending". The name of the required table field is "date" so the "Field" column was filled in with "[date]".
Field column	Select the table field(s) for the search from the drop-down. If you have entered the name of the table manually or used a variable (in the "Table Name" field), the drop-down will be empty. You must enter the name of each intended field manually and always enclose it in square brackets [].
Store in Variable column	Click to select the variable that will receive the data from the corresponding table field. See To Select/Create a Variable.

Use the editing icons to the right of the table to move the rows up and down and to delete or add more rows.

Proceed to the "Query" tab.

"Query" tab

Fill in the following options:

Where
Where Clause	Click to create the appropriate query.You can create up to 80 Where conditions. See To Use the Query Assistant.
Order By
Field	Define the result order by selecting the appropriate table fields.

Use the editing icons to the right of each table to move the rows up and down and to delete or add more rows.

Click the "Advanced" tab to continue.

"Advanced" tab

Fill in the following options:

Store Number of Record(s) in Variable
Variable	Click to select the variable that will receive the number of records selected. See To Select/Create a Variable.
If … Go to
No Record	Select a target location from the drop-down or list, in case a "No Record" occurs. See Detail of a window below. When defining a screen as a target destination (ex: via a “Go to” process), you CANNOT use variables to specify the name of that target screen. You must select the intended screen from the available drop-down/list.
SQL Error	Select a target location from the drop-down or list, in case an "SQL Error" occurs. See Detail of a window below. When defining a screen as a target destination (ex: via a “Go to” process), you CANNOT use variables to specify the name of that target screen. You must select the intended screen from the available drop-down/list.

Detail of a window:

"S:Menu" is a screen included in the same program as the process.

"R:Routine_1" is a routine included in the same program as the process.

If required, use the icons on the top of the properties window:

Click it to create a new table in a local database. See Creating a Table.

Click it to view the data in the selected table. See Viewing/Editing a Table.

Click it to test SQL scripts. See To Test SQL Scripts.

Use the icon to attach any relevant notes to this process. Click it and enter your notes in the resulting text box. These notes will be displayed in the corresponding "Actions" tab or "Process" window (in the "Notes" field) and in the "Developer Report".

After filling in the required options, click to conclude, or to abort the operation.

The added process is displayed in the corresponding "Actions" tab or "Process" window.

If you want to use a label as a target destination, you can use the "Auto-Label" mechanism. This alternative to the "Set Label" process allows you to create a label in the properties window of a process - specifically, in the fields used to define target destinations (ex: the "If Error..." type fields). See To Automatically Create a Label.

Use the right-click in MCL-Designer's input boxes to access some related options as well as the general "Cut", "Copy"; "Paste"; "Search" actions (active/inactive according to the current context).

Ex: If you right-click the "Variable" input box (included in a "Conversion's" properties window), you are provided with general editing/search actions and other more specific options such as "Variable Select" (see "Variable Select"); "Variable Insert" (see "Variable Insert"); "Insert Special Character" (see To Insert Special Characters into a Control's Text Input Field) and "Localization Select" (see Localization List).

If you right-click another input box, it may provide other possibilities.