|01 Select Record Before selecting records using one or more of the key fields, you must define a set of variables containing the values you wish to specify as the key fields. If, for example, you want to select all the records with the last name Smith, you must create a string variable: VARNAME="Smith." You can define variables using either the Module or the Variable icons. Then, using the Select Record icon, first enter the database filename and press the Return key on your keyboard. This will open the database and display all the fields highlighting the key fields with a > character. You can double click on the key fields and a list of variables will be presented for you to link variables to key fields for searching the database. For instance, the variable VARNAME in the above example can be assigned to the key field LASTNAME. Since you have specified VARNAME to be equal to the string "Smith" all the records where the value of the variable matches the contents of the field will be selected. Thus records containing either Smith as last names, or Smithsons, or any record with a last name whose first five characters match Smith will be selected. If you want to restrict the search to just Smith, then you must terminate your variable value with the | (vertical bar) character i.e. VARNAME="Smith|." This will ignore all records with last names that do not exactly match Smith. Also it is important to note that the key values such as "Smith" are case sensitive. If your database contains last names with the first letter capitalized i.e. "Smith" rather than "smith" then your key value for searching must also be "Smith." The Select Record icon can be a parent to the other database icons which perform the read and write operations. As a parent the Select Record icon acts like a loop in that the actions of its children are performed repeatedly until all the selected records have been processed. In our example above a Select Record icon will process all records with the last name that matches the key value of Smith. If you do not link any variables to key fields the Select Record icon selects the very first record in your data file. As a parent it then loops through all the data records sequentially. If the Select is not a parent then it selects only the first record for use by its siblings. Note that you may have as many as ten separate database files open for selection in your application. Each of the database file requires a separate Select Record icon. This is useful for instance in a case where you have stored the names and addresses in one file and the test scores or other vital statistics in another file. By using two separate Select Record icons you can select and read information from both the files. In the current version of AmigaVision if you use Select Record icon to open an eleventh database file it will be ignored. Once a database file is opened by the Select Record icon then the file is automatically closed by AmigaVision at the end of executing an application. Click on the |12Directory|12 gadget to select a database through the file requester or type the database name in the filename field and press the Return key. The specified file's fields are shown in the window with the key fields marked by the > character. Clicking on the field name in the list presents a list of variables that have been defined in the flow. Select a variable from the list to link it to a field. Repeat this procedure for each of the fields you want to link to a variable. Be sure that the variables you are using have been assigned values prior to the Select Record icon. When Select icon executes it first locates one or more records using the specified key fields and then uses the non-key fields, if any, to locate the record precisely. If a key field is not assigned a variable, the interpretation is SELECT ALL the record that matches that key. If no fields are assigned to variables, all records are selected starting from the first one. If more than one key field is linked to variables, the interpretation is SELECT ALL record that match key field 1 and key field 2 and key field 3 and so on. For example, if you link the key field LASTNAME to the variable name VARNAME and STATE to VARSTATE, and you have defined VARNAME="Smith|" and VARSTATE= "Virginia|," then you will select all the records for employees named Smith who live in Virginia. You are also allowed to use non-key fields in addition to the key fields to select records. If Select icon fails to find a single record in the database that matches the specified keys then it does not perform the actions of its children and the application moves to the next icon. If you want to perform specific actions such as adding a new record then you must use variables to determine if the Select icon failed to locate a record. In our above example, just prior to the Select icon define a variable using the Variables icon called VARNAME = "NONE." Then use the Select icon to select the record. Following the Select icon use a Read/Write icon to link the variable VARNAME to the LASTNAME field and read the record. The reading operation will fail if no record has been selected. You can use a conditional icon such as If-Then or If-Then-Else to test see if VARNAME == "NONE." If it is TRUE then you can assume that the Select icon failed to locate a record. |02 Read/Write Record This icon is the primary method for updating information in a single record in the database and can be used in the following way. Assume that you have created a database of employee information which contains the field SALARY. You want to update the SALARY field to reflect a 5 percent pay increase. First, use the Variables icon to set up a variable called MEMSAL and one called NEWSAL. Next, use the Select icon to select the first record. Now use the Read/Write icon to read the contents of the SALARY field into the variable MEMSAL. Now you will need another Variables icon to calculate NEWSAL: NEWSAL=MEMSAL * 1.05. To complete the process, use another Read/Write icon to update the value of the variable NEWSAL to the field SALARY in the current record of the database. Note that this will be an update operation, since you are replacing existing values in your data file. The first Read/Write icon reads values from the currently selected database record fields into variables. After the variables have been modified, the second Read/Write icon updates the fields in the database. Specify the name of the database file to be used in the filename field. You may type the filename or click on the Directory gadget to open the File requester (Chapter 3). Press the Enter key. Be sure that the database file you have specified has been opened previously by a Select Record icon. If you do not have a Select Record icon defined then your Read/Write icon will fail to do anything. Click on the multistate gadget to select one of three operations: |12Read|12, |12Update|12, or |12Insert|12 a record. Read is used for reading the data fields in the currently selected record into the variables that have been linked. If the operation is an Update, the action is to update the values in the specified fields of the currently selected record. The Insert is for adding new records to the database. You can use it, for instance, if the Select operation fails to find a record with key fields matching key values specified. A list of the available fields is shown in the window. Clicking on the field name in the list presents a list of previously defined variables. You can select variables from this list and link them to the fields. The linking operation is identical to that in the Select Record icon. It is a good practice to use different variables for record selection and for record reading/writing. For instance, you may want to use multiple Read/Write icons for reading the same record into different variables in your application. |03 Delete You use the Delete icon to remove a record from the database. Records to be deleted must first be selected with the Select Record icon. Specify the name of the database file to be used in the |12Filename|12 field. You may type the filename or click on the |12Directory|12 gadget to open the File requester (Chapter 3). |04 Variables You may use the Variables icon to define variables by assigning values or expressions to them. In AmigaVision, variables can be created using a Module icon, a Subroutine icon, or the Variables icon. Variables created by Module and Subroutine icons are called 'local'. For instance, a variable defined in a Module icon will be useful only within that Module. Similarly a variable defined in a Subroutine icon has validity only within that Subroutine. The Variables icon, in contrast, can create variables that can be used throughout an application. Thus they are called 'global' variables. In addition to creating global variables, the Variables icon can be used for modifying the values of even the local variables. In general, when you use the Variables icon if the variables you use have not been previously defined, you will be creating global variables. The first Variables icon sets up variables to specify key fields for data selection and update operation. The second modifies the variables which will be used to update the database. The |12Delete|12, |12Insert|12, and |12Move|12 gadgets are used to arrange expressions in the expressions (or variable definitions) window. When you click on Insert, the Expression Editor (Chapter 5 in the user manual) is presented and you may define a number of variables. When you exit the Expression Editor by clicking on the |12Ok|12 gadget, the variables along with their defining expressions will appear in the expressions window. To insert expressions in a particular place in the list of existing expressions in the window, click on the expression below which you wish to insert. When the expression is highlighted, click on the Insert gadget. To delete an expression from the list, click on the expression in the window. Then click on the Delete gadget. To move an expression elsewhere in the list, click on the expression. Click on the Move gadget. To move a variable definition above its original position, click on the definition above which you want to move. To move a variable definition below its original position, click on the definition below which you want to move. Double clicking on a variable definition presents it in the Expression Editor for editing. |05 Output You may use the Output icon to produce a line of formatted output of values of variables in your application. The output can be sent either to a file or to a printer. If the output is sent to a file it is saved in the ASCII format. Note that the Output icon allows you to format only a single line of output that is a maximum of 132 characters. If you need multiple lines (for example, to include headings in a report), you must use one Output icon for each line. Specify the name of the output file to be used in the Filename field. You may type the filename or click on the Directory gadget to open the File requester (Chapter 3). Click on the multistate gadget to specify the output destination: Printer or File. If you select File option then an ASCII file is created using the specified filename. If a file by that name already exists the output is simply appended to the end of the file. When you click on the |12Var|12 gadget the variable list is presented. When you select a variable from the list, it will appear in the Var field. When the Output icon is executed the value of this variable will be printed at the column location that you specify. You have the option of specifying a text string instead of a variable. Click on the |12Text|12 field to type in a literal string of text to be output. This is useful for specifying a title, column heading, or other fixed textual information on the line to be printed. After specifying a variable or a text string to be output you have to click on the |12Column|12 gadget to enter the starting column number for printing. The maximum number of columns is 132. You should carefully plan the output so that string variables such as names have adequate space. The Specify Value requester is presented (see Chapter 3). There you can enter the column number. Thus you have to allocate all the variable values you want shown in a single line over the limited space of 132 columns. When you have specified the variable or text string and the corresponding column location click on the |12Insert|12 gadget to add the specification to the output format window. This window shows the starting column location; a code letter (T or V) indicating whether at that location a text string or a variable value is being printed; and either the name of the variable or the text string. The |12Delete|12, |12Insert|12, and |12Move|12 gadgets are used to arrange the output format items in the window to the left of them. The order of items in the list does not matter as the printing occurs according to the specified column location. To insert a new item at a particular place in the list in the window, click on the item below which you wish to insert. When the item is highlighted, click on the Insert gadget. To delete an item from the list, click on the item and then click on the Delete gadget. To move an item elsewhere in the list, click on the item and then click on the Move gadget. Then to place the item above its original position, click on the item above which you want to move. Or to place the item below its original position, click on the item below which you want to move. Double clicking on an item presents it for modification in the Var (or Text) and the Column fields. After modification you must insert it again using Insert gadget and use the Delete gadget to delete the original item. |06 Data Form You use the Data Form icon to create forms on the screen for data entry. A data entry form is a screen which contains data fields into which the user types information. The data fields for the form are created in the Object Editor (Chapter 5 in the user manual). In the data field requester, define the field type and length. Available field types are shown in the Database Editor section of Chapter 5 in the user manual. In addition to data fields, the form may include hit boxes for entering non-field responses. As an example, the form might include hit boxes for OK and CANCEL, which could be used to exit the form. You can validate the data entered by the user in a form by checking each field. The Object Editor lets you specify validation expressions and error messages for each field. Alternatively, you may validate the data entered in all the fields after the user exits the form. You may also specify response strings for exiting and/or aborting a form. Click on the |12Object Editor|12 gadget to enter the Object Editor (Chapter 5 in the user manual). In the Object Editor, you define the screen appearance and all the data fields for the form. For each of the data fields you must specify a variable in the Object Editor. This data entered by the user and the variables in your application are thus linked. The value of the linked variable gets updated once the data is entered for each field. In the Object Editor you can also specify hit boxes for exiting or aborting the form. Click on the multistate gadget to specify whether input should be validated for each field or when the form is exited. If you choose Validate Fields, the exit condition for each field is checked as the data for the field is entered. If you select Validate on Exit, field entries are not validated until the form is exited. Note that if you select validate on Exit, you must specify a string in the |12Exit On|12 field for the form. This string must match the response string you specified for hit box when you created the data form in the Object Editor. Similarly, if you want to provide the user an abort option in the data form, you must specify a hit box with a response string. This same string must then be entered in the Forms requester in the field for the |12Abort On|12 string. A a form is exited normally when the user exits the form by clicking on the hit box with the response string specified next to Exit On. The values of variables that are linked to the data fields are then modified and saved. However, if the Abort condition occurs, variables are reset to their values prior to the Data Form icon. Click in the |12Exit On|12 field and type in the string which will cause the form to be exited. Click in the |12Abort On|12 field and type in the string which will cause the form to be aborted. This is an example data form created in the Object Editor. The form has three fields: NAME, SSN, and COURSE. It also contains a hit box with the response string "OK." |07 Form Exit You use the Form Exit icon to exit from a data form without the use of a hit box. The Form Exit icon is not required for each Data Form icon. Normally, a data form is exited when either the Exit On string or the Abort On string is encountered. You may, however, include hit boxes for other response strings on the form. You might then check for one these other response strings and conditionally execute other icons. The Form Exit icon could then be used to exit the Form icon. Using the multistate gadget you may specify the mode as either Exit or Abort. If you specify Exit, the variable values adjusted by the data fields in the form are modified and saved. If you choose Abort, variable values are reset to their values prior to the Data Form icon.