Shareware Overload

home *** CD-ROM | disk | FTP | other *** search

/ Shareware Overload / ShartewareOverload.cdr / database / rbnotes2.zip / EDTORIAL.15 < prev

Wrap

Text File | 1986-09-09 | 81KB | 2,345 lines

MICRORIM TECHNICAL NOTE ________________________________________________________ SEARCHING FOR EMBEDDED BLANKS IN TEXT STRINGS DATE : 07/86 NUMBER : EX-7-1 PRODUCTS : R:BASE 5000 VERSIONS : 1.01 : R:BASE SYSTEM V : 1.0 CATEGORY : INQUIRY SUBCATEGORY : BLANKS/WILDCARDS ________________________________________________________ DESCRIPTION: How can I use wildcards to search for an embedded blank, other character, or a specific format of characters stored in a TEXT or NOTE data type column? EXPLANATION: You can use asterisk and question mark wildcards in any WHERE clause in both R:BASE System V and R:BASE 5000. The asterisk will replace one or more characters and the question mark will replace one character. In R:BASE System V, you can also use wildcards in rules. For example, you can use rules to ensure that a number with a specific format is entered correctly. The rule: "Wrong format, try again" CUSTNUM EQ "??-????" will ensure that: ■ CUSTNUM column must always have an entry ■ Entry into the CUSTNUM column must always be exactly seven characters long ■ There must be a dash in position three If, using the above example, you wanted the system to insert the dash automatically, you could do it easily with an R:BASE System V form. First, set up two fields (V1 and V2) on the form with a dash between them like this: Enter the customer number: SE-S E The first SE combination is the location of the TEXT variable V1 and the second SE is the location of the TEXT variable V2. Now, during data entry, the operator's cursor will jump over the dash while entering the customer number. Finally, set up the following expression to concatenate the two variables, insert the dash, and store the result in the TEXT column CUSTNUM: CUSTNUM = ((CTXT(.V1))+"-"+(CTXT(.V2))) SOLUTION: Put the embedded blanks between two asterisks and enclose the whole thing in quotation marks in the appropriate WHERE clause. The asterisks serve as wildcards and the quotes tell R:BASE to consider whatever is found between the quotes to be one value. For example, to search for one embedded blank in a customer name stored in a column named CUSTOMER, put one space between the asterisks in the following SELECT command line: SELECT ALL FROM COMPANY WHERE CUSTOMER = "* *" To search for two blanks, put two spaces between the asterisks: SELECT ALL FROM COMPANY WHERE CUSTOMER = "* *" This tip works equally well on columns defined as NOTE data types in R:BASE System V. If you wanted to search for a blank (or any other character) in a specific position of a text string, you would use the question mark. For example, SELECT ALL FROM COMPANY WHERE CODENUM = "?? *" will look for a blank in the third position of the CODENUM column. Checking specific positions in the line can be helpful in: ■ Verifying that the data has been entered correctly ■ Counting the occurrences of certain values in certain positions in the string ■ Working with codes where every position in the code has a specific meaning In R:BASE System V, on TEXT (but not NOTE) data type columns, you can use the SLOC function to locate the exact position of a character. If, however, you are looking for a blank, set a variable to a blank first, and then use the variable in the function. This is necessary because the SLOC function does not recognize a blank between two quotes. For example, the following will locate the position of the first blank in the NAME column and then change the value of the 1STBLANK column to that position number: SET VAR VBLANK TO " " CHANGE 1STBLANK TO (SLOC('NAME',.VBLANK)) IN tblname WHERE NAME EXISTS MICRORIM TECHNICAL NOTE ________________________________________________________ CUSTOM PAGE NUMBERING DATE : 07/86 NUMBER : EX-7-2 PRODUCT : R:BASE SYSTEM V VERSION : 1.0 CATEGORY : REPORTS SUBCATEGORY : CUSTOM PAGE NUMBERS ________________________________________________________ DESCRIPTION: I need to do custom page numbering on my report. Specifically, sometimes I need to start numbering the pages of my report at a number other than one. For example, I might be printing a whole series of reports in a WHILE loop and I want the final output to look like one long report. At other times, I need to reset the page numbering within a long report so that I can break the report apart and give a separate report to each customer or department. This is necessary when I am printing, for example, multiple page bills for customers or doing departmental reports. I want to number the pages starting with one for each new customer or new department. EXPLANATION: By using the #PAGE system variable in R:BASE 5000 and R:BASE System V, each report printed will always begin with page number one and sequentially number each subsequent page. However, because R:BASE System V uses global variables and has logical functions, you can modify your R:BASE System V report to begin page numbering at any number you choose. Also, using R:BASE System V, you can apply this same method to resetting page numbers when a break occurs. SOLUTION: Using this four-step solution you can create custom page numbers that will begin with any number you choose or will begin renumbering each time your break column changes. STEP ONE In this method, a global variable VPAGE is used to number the pages rather than the #PAGE system variable. Define the following two variables in the expression mode of reports by choosing Expression from the Reports Definition Menu: |------------------------Reports Definition Menu------------------------------| | Edit Expression Configure Draw | |-----------------------------------------------------------------------------| and then choosing Define from the Expression Menu: |----------------------------Expression Menu----------------------------------| | Define Delete Retype Reorder | |-----------------------------------------------------------------------------| Enter the following two expressions by first entering the variable name and type at the Enter expression: prompt and then entering the actual expression at the next Enter expression prompt: 1: INTEGER : VP1 = (.VP1 + 1) 2: INTEGER : VPAGE = (IFEQ(.VP1,1,(.VPAGE + 1),.VPAGE)) The second expression is telling R:BASE to increment the VPAGE variable by one if this is a new page. VP1 will only be equal to one if there has just been a page break. STEP TWO Escape back to the Reports Definition Menu and choose the Configure option. You will get the configuration menu like that shown below. Toggle the YES in the Page row and Variable Reset column so that the menu looks like the following: Lines Per Page .................: 60 Remove Initial Carriage Return..: [NO ] Manual Break Reset .............: [NO ] Page Footer Line Number.........: 0 BREAKPOINTS FORM FEEDS Break Variable Header Footer Column Reset Before After Before After -------- ------ ------ ------ ------ ------ Report [NO ] [NO ] [NO ] [NO ] Page [YES] Break1 [None] [NO ] [NO ] Break2 [None] [NO ] [NO ] Break3 [None] [NO ] [NO ] Break4 [None] [NO ] [NO ] Break5 [None] [NO ] [NO ] Break6 [None] [NO ] [NO ] Break7 [None] [NO ] [NO ] Break8 [None] [NO ] [NO ] Break9 [None] [NO ] [NO ] Break10 [None] [NO ] [NO ] Now you will be presented with the list of variable choices to add to the reset list, pick VP1 to cause the VP1 variable to be reset to zero every time there is a page break. Now, at the top of every page, VP1 will be reset to zero and it will immediately be changed to one (1) when the first expression is executed. The second expression will cause VPAGE to be incremented by one (1) only if VP1 is equal to one (1), meaning that there has just been a page break. STEP THREE Escape back to the Reports Definition Menu and choose the Edit option. Using the [F6] key, locate the VPAGE variable on your report wherever you want the page number to print. STEP FOUR Now you can start your page numbering at any number you want by setting the variable VPAGE to that number at the R> prompt before you begin printing the report. Unless VPAGE is cleared with the CLEAR command, VPAGE will continue to increment each time the report is printed in a WHILE loop. Take care, however, that you do reset any variables being used in the report to total. ALTERNATE METHOD If you want to break on the customer identification number and begin to number each page for a particular customer, go through the same four step process but modify the second step to add the break column (CUSTID, DEPTNUM, or whatever). Add the VP1 variable to the reset list for that break, and indicate that you do want a page break before each break heading. Lines Per Page .................: 60 Remove Initial Carriage Return..: [NO ] Manual Break Reset .............: [NO ] Page Footer Line Number.........: 0 BREAKPOINTS FORM FEEDS Break Variable Header Footer Column Reset Before After Before After -------- ------ ------ ------ ------ ------ Report [NO ] [NO ] [NO ] [NO ] Page [YES] Break1 [CUSTID] [YES] [YES] Break2 [None] [NO ] [NO ] Break3 [None] [NO ] [NO ] Break4 [None] [NO ] [NO ] Break5 [None] [NO ] [NO ] Break6 [None] [NO ] [NO ] Break7 [None] [NO ] [NO ] Break8 [None] [NO ] [NO ] Break9 [None] [NO ] [NO ] Break10 [None] [NO ] [NO ] MICRORIM TECHNICAL NOTE ________________________________________________________ MAKING A REPORT WITH REPORTS EXPRESS DATE : 07/86 NUMBER : EX-7-3 PRODUCT : R:BASE System V VERSIONS : 1.0 CATEGORY : REPORTS SUBCATEGORY : HOW TO DEVELOP ________________________________________________________ DESCRIPTION: What is the procedure to follow when creating a report with R:BASE System V Reports EXPRESS? EXPLANATION: Making a report with R:BASE System V Reports EXPRESS is basically a six-step process as outlined in the solution below. Some of the steps are optional. For example, if your report does not have breakpoints, skip step two. It is not required that you follow the steps outlined below. Other ways may work as well as the one we present here. SOLUTION: Here is the six step report development method: STEP ONE: DECISIONS ■ Make a rough sketch of the way you want the report to look. ■ Where will you get the information for the report? List the columns and their associated tables in their places on your report sketch. List the variables you will need and any expressions. Choose the table or view that will be the base table. If your report will be using more than one table or view, make sure that the base table or view is on the many side of a one-to-many relationship. ■ All the other tables in your list, other than the base table, will be lookup tables for this report. Lookup tables contain information that you want to look up and must be on the one side of a one-to-many relationship. For example, you might want to look up a name and address for a particular customer number, or you might want to look up a part description based on a part number. ■ Are any of the lookup tables "many" tables? In other words will you want to look up more than one row? A yes answer here means that you will need to create a VIEW and use the VIEW as the base table for the report. ■ Decide how you will get the values for your report. Will the values come from the columns in the tables? Which columns? Will you need to calculate or concatenate by using expressions? What expressions are needed? ■ Are you going to do subtotals? Do you need subheadings or subfootings? Do you want to group information based on a column value? If the answer to any of these questions is yes, then you will need to set up breakpoints. Breakpoints allow you, for example, to print all the information grouped or totalled by customer, by department, or by account number. If you will be using breakpoints, list the break columns. ■ Enter Reports EXPRESS, choose the database, choose the base table, and then you will be presented with the Reports Definition Menu: |------------------------Reports Definition Menu------------------------------| | Edit Expression Configure Draw | |-----------------------------------------------------------------------------| STEP TWO: SET UP BREAK COLUMNS If you have no breakpoints in your report, skip this step. If you have subtotals or groupings that require subheadings or subfootings, enter the break columns in this step. Choose Configure and you will be presented with the following configuration table: Lines Per Page .................: 60 Remove Initial Carriage Return..: [NO ] Manual Break Reset .............: [NO ] Page Footer Line Number.........: 0 BREAKPOINTS FORM FEEDS Break Variable Header Footer Column Reset Before After Before After -------- ------ ------ ------ ------ ------ Report [NO ] [NO ] [NO ] [NO ] Page [NO ] Break1 [None] [NO ] [NO ] Break2 [None] [NO ] [NO ] Break3 [None] [NO ] [NO ] Break4 [None] [NO ] [NO ] Break5 [None] [NO ] [NO ] Break6 [None] [NO ] [NO ] Break7 [None] [NO ] [NO ] Break8 [None] [NO ] [NO ] Break9 [None] [NO ] [NO ] Break10 [None] [NO ] [NO ] Enter the break columns in the Break Column area under the BREAKPOINTS heading. Leave the Variable Resets at NO for now. When you are finished, press the [ESC] key to get back to the Reports Definition Menu. STEP THREE: DEFINE VARIABLES, EXPRESSIONS, AND LOOKUPS You will need to set up expressions to: ■ Look up information in other tables ■ Make totals, subtotals, or grandtotals ■ Use expressions or functions in your report ■ Use global variables Choose Expression from the Reports Definition Menu. You will be prompted to enter the expression. See chapter six of the User's Manual for instructions on setting up lookups, variables, and other expressions. Remember that all item names appearing to the left of the equal sign in the expression list are actually global variables. When defining them for the first time, you can assign a data type to them by entering the following prior to entering the actual expression: varname datatype For example, to pretype a variable VTOT to CURRENCY data type, you would enter the following: VTOT CURRENCY Now you can enter the actual expression for VTOT. After entering all the expressions for the report, you are ready to actually key in the report, locate all the columns and variables, and create the report sections. Press [ESC] to get back to the Reports Definition Menu. STEP FOUR: ENTER TEXT, CREATE SECTIONS, & LOCATE VARIABLES/COLUMNS Choose Edit from the Reports Definition Menu. You will be presented with a blank screen with RH highlighted near the upper left corner and the word EXPAND on the bottom line. Now you can set up the report sections, enter the actual text information, and locate all the variables and columns. The word EXPAND at the bottom of the screen means EXPAND mode is on. You can set up your sections with EXPAND mode on and then toggle it off, by pressing the [F9] key, when you want to enter text or locate a column/variable. At this point, decide on the sections you will have in your report. There are seven different section types to choose from: report header (RH); page header (PH); break header (H1...H0); detail (D); break footer (F1...F0); page footer (PF); report footer (RF). If you had ten breaks defined, it would be possible to have as many as 25 secions in one report. You do not have to have all sections in your report. In fact, a one section report will print if that one section is a detail, break header, or break footer section. Note that in order for a report to print, it must have a detail, break header, or break footer section. You can move from section to section with expand mode on by pressing the [F8] key to go forward and the [F7] key to move back. If, for example, you do not want a report header, press the [F8] key as your first action in this step, and you will see the RH change to a PH. Now if you do not want a page header, press the [F8] key again to get to the next section and so on. When you are in the right section, press the [ENTER] key to mark as many lines of that section type as you want. When you have marked enough lines, press [F9] to turn expand off. Now, enter the text you want to print in that section and locate columns and variables by pressing the [F6] key. After pressing the [F6] key, you will be presented with the following prompt: Column or variable name: You can type in the name or pick it from a list. If you want to pick the column or variable from a list, press the [ENTER] key when prompted and you will see the following menu: |------------------------------Locate Menu------------------------------------| | Variables Columns | |-----------------------------------------------------------------------------| Now you can choose Variables to get a pick list of all the defined variables, or you can press Columns to get a pick list of all the defined columns. The pick list that comes up is only one line long but you can get to another line of variables or columns with the down arrow key or by tabbing over to the last entry in the line with the [TAB] key. This makes locating easy. After entering all the text and locating all the variables and columns for that section, press the [F9] key to toggle EXPAND back on. Press [F8] to go to the next section. Continue the process until you have set up all your sections. When you finish, press [ESC] to return to the Reports Definition Menu. STEP FIVE: CONFIGURE RESET VARIABLES AND LINES PER PAGE Complete this step if you want to change the default number of lines per page from 60 or if you need to have any variables reset during report processing. If you are doing subtotaling you will definitely need to reset your subtotaling variables for each new customer, department, or whatever your grouping is. To add variables to the reset lists, choose the Configure option and you will get the configure chart. Go to the appropriate position under the Variable Reset column on the chart. Press [Y] to add to the list. You will be able to pick the variables to add to the list. STEP SIX: TEST, FIX, AND TEST AGAIN Print the report and make sure you are getting what you want. Few people have everything exactly right the first time. You will want to see how the report looks and then go back into Reports EXPRESS to make any modifications that you find necessary. For example, you might want to change the section markings on your report. To do this: ■ Move the cursor next to the start of the section you want to change. ■ Toggle EXPAND on by pressing the [F9] key. ■ Select the section you want by pressing the [F8] key to go to forward through the possibilities or by pressing [F7] key to go backwards. ■ Once you have the section you want, you will need to get inside the highlighted marking bar on the left of the screen to mark the entire section. Do this by pressing the [SHIFT][TAB] key combination. ■ Once inside, use the down arrow key to remark the complete section. When you are finished, press [ESC] to escape from the highlighted marking bar. This has been a quick overview of the procedure to follow in developing an R:BASE System V report. For additional information, work through the two tutorials in the Learning Guide and study chapter six in the User's Manual. MICRORIM TECHNICAL NOTE ________________________________________________________ DIFFERENCES BETWEEN R:BASE 5000 AND R:BASE SYSTEM V DATE : 08/86 NUMBER : EX-8-1 PRODUCT : R:BASE SYSTEM V VERSIONS : 1.0 R:BASE 5000 1.01 CATEGORY : FEATURES SUBCATEGORY : SYSTEM V VS. 5000 ________________________________________________________ DESCRIPTION: What are all the differences between R:BASE 5000 and R:BASE System V? EXPLANATION: The differences between R:BASE 5000 and R:BASE System V are extensive. This technical note summarizes many of the major differences between the two products and provides some basic tips on making use of the additional power in R:BASE System V. This is not a complete list of everything that R:BASE System V can do. Only major differences are listed. R:BASE 5000 is also a powerful product and has been ranked as the number one database management product by Datapro Research Corporation in September 1985 and February 1986. To get a complete picture of what you can do with R:BASE System V, add the following to all the powerful features already found in R:BASE 5000. SOLUTION: Definition EXPRESS And Database Structure _________________________________________ R:BASE 5000 gives you Application EXPRESS. R:BASE System V gives you Definition EXPRESS, Application EXPRESS, Forms EXPRESS, and Reports EXPRESS. R:BASE System V databases have an expanded structure and a new easier way to define databases using EXPRESS technology. R:BASE System V offers the following new types of columns, tables, definition methods, and ways to modify database structure: NOTE DATA TYPE NOTE is a new data type that can contain up to 4092 characters of text (including spaces). A NOTE is a variable length column; only the actual number of characters entered are stored. If you enter 1200 characters in a NOTE column in one row and another row in the table has 50 characters in the same NOTE column, then the two rows together will only use up 1250 bytes for that particular column. The maximum number of characters (4092) can be visualized as a very full, single spaced, typed page. For example, an entire page of information with 60 lines of 68 characters each, would fit into a single NOTE column. You can still search for a particular character, word, or phrase in the NOTE column by using the CONTAINS operator in the WHERE clause. You can store long pieces of textual information, display that information in scrolling windows, review or change the information, or search for key words or phrases. DOUBLE DATA TYPE The new DOUBLE data type gives you double precision real numbers with up to 15 digits of accuracy instead of the six digits that you get with the REAL data type in R:BASE 5000. CURRENCY DATA TYPE The new CURRENCY data type replaces the DOLLAR data type in R:BASE 5000. In R:BASE System V you can specify the currency format to print in U.S. Dollar format, British Pound Sterling format, etc. Now you can internationalize your applications. COMPUTED COLUMN R:BASE System V allows computed columns. Each computed column has an expression associated with it showing how it is calculated. Each computed column can be any of the data types except for NOTE. Computed columns are calculated based on values stored in other columns in the same table. If a value changes in one of the columns contained in the computed column's expression, the value of the computed column is automatically updated. You can use constants, other columns, system variables (such as #DATE and #TIME), and even SuperMath functions, directly in the definition of the computed column. For example, a computed column can automatically calculate the extended price of an order, or the future value of an investment. By using date math (for example, subtracting the system date from a future date stored in the data) you could automatically calculate items that decrease or increase in value as time goes on. A computed column can also automatically concatenate text columns together to make a composite identifier (also known as a composite key or a composite ID column). For example, a computed column COMPID can be the automatic concatenation of two other columns such as SUBKEY1 and SUBKEY2. If either SUBKEY1 or SUBKEY2 is changed, COMPID will automatically reflect the change. You can set up a rule to ensure uniqueness of the composite key. VIEWS A view is a dynamic ad hoc combination of related tables. Only the definition of the view is stored on disk. The data brought up in a view is not stored on disk; it is created and used on an as needed basis. Views can, for example, be used by reports, the CROSSTAB command, the SELECT command, and other commands. A view can contain a selected group of columns from one table, or it can hold a combination of columns from up to five different tables. Each of the tables used in a view needs to have at least one column in common with the others. Views containing only one table may be used with the EDIT and the BROWSE commands. Views are very useful in creating multi-table reports. Views are dynamic; the data for the view is found and put together (based on the stored definition) every time it is referenced. If you are currently using any of the relational commands before printing a report, you may want to switch to using a view in order to avoid having to use up disk space for the resulting table. Views can be easily defined using Definition EXPRESS or Prompt-By- Example (PBE). EFFICIENT USE OF SPACE If you remove the last table created and you have not edited NOTE type columns, R:BASE System V will automatically retrieve the extra space that was being used by that table. In other words, file two of the database will automatically shrink in size. For example, create a temporary table by combining two permanent tables using the INTERSECT command. Next, browse through the resulting temporary table with the BROWSE command and then delete the temporary table using the REMOVE command. The space used by the last table in the database is automatically recovered. To find out which table is last, issue the following command and look at the last name in the list: LIST TABLES RULES Rules can be easily defined and modified using Definition EXPRESS without having to program. Also, by combining rules with computed columns, you can set up more powerful rules without having to write a program. For example, you could have a rule check for a unique combination of first and last name. To accomplish this, set up a computed column that automatically concatenates first and last name and then set up the rule on that computed column. PASSWORDS Passwords for databases, tables, and views can be easily defined using Definition EXPRESS. STRUCTURAL MODIFICATION Using Definition EXPRESS, you can modify the database structure even if data has already been loaded into the table. Or, if you prefer, use the new REDEFINE command which replaces the R:BASE 5000 CHANGE COLUMN command. Application EXPRESS ___________________ Application EXPRESS has the following additional capabilities in R:BASE System V. TEMPLATES Using Application EXPRESS, you can now integrate custom templates into your application. Templates are ASCII files with R:BASE commands and operator prompts in them. They are similar to customs and macros in Application EXPRESS. With templates you can prompt the user for the parts of a command line that will be different each time the application is run. Templates use the system parameter variables %1 through %9 to pass user input directly into the command lines included in the template. Templates are best suited to situations where a table or column name will be different each time the application is run. PATHS Application EXPRESS now supports paths. Your Application EXPRESS files do not need to reside on the same directory as your database. SORTING The sorting capability in Application EXPRESS now supports up to 10 sort levels. SET APPLICATION ENVIRONMENT Now you can set the color, bell, and error messages for an application created from Application EXPRESS. New Commands And Features _______________________________ 70 FUNCTIONS The 70 SuperMath functions work throughout the product. They are specialized functions that work on literal values, variable values, and column values, and include: ■ Mathematical functions such as log, mod, absolute value, square root, minimum, maximum, average, etc. ■ Trigonometric functions ■ Hyperbolic functions ■ String manipulation functions such as determining the length of a string, getting a piece of a string, putting one string into another, locating a string in another string, changing a string to all caps, changing a string to all lower case, changing a string to all lower case with the first letter of the first word capitalized, changing a string to all lower case with with the first letter of all words capitalized, left justifying, right justifying, centering, etc. A string used in a string manipulation function can be a literal (actual string of characters), a value in a variable, or a value in a TEXT column. The following command, for example, will, for every row in the table, determine how long the person's name is and store the number in the LENGTH column. NAME is a TEXT column and LENGTH is an INTEGER column. CHANGE LENGTH TO (SLEN('NAME')) IN + tblname WHERE NAME EXISTS ■ Date and Time Functions pull out the integer month, day, year, hour, minute, second, or day of week; or pull out the text month or text day of week; or change a calendar date to a Julian date. Finally, you can switch from integer hour, minutes, and seconds to a time hh:mm:ss value and from integer month, day, and year to a date mm/dd/yy. ■ Financial functions such as future value, present value, and many others. ■ Logical functions (IFEQ, IFLT, and IFGT) compare one item to another and based on the result, assign different values to the function. Logical functions allow simple conditional processing in forms and reports as well as in command files and macros. ZIP Run other .EXE and .COM programs without leaving R:BASE System V. You can, for example, be in R:BASE and ZIP out to write a letter with your word processor and, when you quit, be automatically back in R:BASE. BROWSE The new BROWSE command makes it possible to browse forward and back through the rows in your table without making any changes to the data. If you wish to make changes, use the EDIT command. IMPROVED ...WHERE COUNT... R:BASE 5000 has a WHERE COUNT EQ clause. R:BASE System V adds to this feature. Now you can use any of the following WHERE clauses (n is a number representing the position of the row in the table): ■ WHERE COUNT EQ n ■ WHERE COUNT NE n ■ WHERE COUNT LT n ■ WHERE COUNT LE n ■ WHERE COUNT GE n ■ WHERE COUNT GT n SECRET PASSWORD ENTRY The USER command now automatically prompts the operator to enter his or her password. The password is NOT displayed on the screen as it is being entered. AMPERSAND VARIABLES Ampersand variables (&varname) are similar to dotted variables (.varname) in that they tell R:BASE System V to get the value of the variable and put the value into the command where the &varname is located. However dotted variables are used in WHERE clauses to replace values. Ampersand variables can replace anything in the command line. An ampersand variable can hold several words, a part of a command, or even an entire command. For example, it is often handy to have a quick way to qualify all rows in a WHERE clause for a CHANGE command or to delete all the rows in a table. In R:BASE System V, you can assign a global WHERE clause to an ampersand variable in your RBASE.DAT initial startup file and then use it throughout your application. First, put the following command into your RBASE.DAT file (#1 is interpreted by R:BASE as the first column in the table): SET VAR VALL TO "WHERE #1 EXISTS OR #1 FAILS" Now you can use the variable VALL to provide a global WHERE clause by including it in the command with an ampersand in front of it. For example, to delete all the rows in a table named ANALYZE you would use the following command: DELETE ROWS FROM ANALYZE &VALL CLS COMMAND The new CLS (clear screen) command allows you to clear the entire screen or to select only certain rows of the screen to clear. CLEAR COMMAND ENHANCED R:BASE System V allows you to specify a list of variables with a single CLEAR command. RBEDIT ENHANCED RBEDIT, the editor that we provide as a stand-alone module outside of R:BASE System V and as an internal command inside R:BASE, has been enhanced. Now you can mark blocks, copy blocks, and delete blocks. In addition, you can use RBEDIT to edit exec type files (created with the RECORD command). Using RBEDIT in this way you can edit and change the actual keys that you want executed in a particular exec. For example, if you want the [ESC] key to be pressed three times in succession in a particular exec file, edit the exec file with RBEDIT and actually type in the characters: [ESC][ESC][ESC] in the appropriate spot. PROMPT-BY-EXAMPLE (PBE) Use EXPRESS-like menus to guide you through the command-generation process. PBE supports almost all the R:BASE System V commands. PBE is an excellent way to make learning R:BASE System V easier. You can also define your own prompts to be used with the PBE code. One customer was able to replace 100 lines of code with one customized PBE prompt. RECORD/PLAYBACK MACROS By pressing [F7] you can start an automatic recorder, record your keystrokes, then store them to a file (called an exec file) or assign them to a function key. Use the R:BASE System V RECORD/PLAYBACK feature anywhere in R:BASE System V. The file you create can be edited using RBEDIT to further customize the macros you create. You can use an exec file as an automatic startup file in Application EXPRESS, Forms EXPRESS, Reports EXPRESS, Definition EXPRESS, or FileGateway. Include the name of the exec file on the appropriate line in the RBSYSTEM.ASC file. KEY MAP MACROS You can assign R:BASE System V commands to a key map. This means that if you have assigned a command or set of commands to, for example, the [ALT][F10] key combination, when you hold down the [ALT] key and press the [F10] key, the commands stored in the key map will be executed. This is useful when you want the operator to be able to "push a button" to begin program execution. You might assign several different RUN commands to different keys and then the operator will not have to remember the name of the command file. Key maps are not automatically saved so if you want to save and reuse your key maps during other R:BASE sessions, turn RECORD on (to create an exec file) while defining the key maps for the first time. When you have defined all the key maps, use the [F7] key to turn RECORD off. Now you can playback the exec file, using the PLAYBACK command, to automatically define your function keys. If you put the PLAYBACK command into your RBASE.DAT initial command file, your key maps will be automatically assigned each time you load R:BASE System V. You can assign up to 40 key maps with a total of 512 characters stored in all of them. You can assign the function keys themselves, the function keys in combination with the shift key, the function keys in combination with the control key, and the function keys in combination with the alt key. We recommend, however, that you use just the [ALT] and [CTRL] key combinations in order to avoid overwriting the function keys that R:BASE System V uses. CROSSTAB You can analyze inter-column relationships with the new CROSSTAB command. CROSSTAB allows you to crosstabulate the number of occurrences (count), averages for numeric data types, sums, minimums, and maximums. NEW COMPUTATIONAL CAPABILITIES New features in the COMPUTE command allow you to compute the standard deviation and the variance as well as being able to use expressions including the 70 SuperMath functions. MULTI-USER OR SINGLE USER R:BASE System V is switchable between single and multi-user. NEW SET PARAMETERS R:BASE System V has several new SET parameters: ■ TOLERANCE: The SET TOLERANCE command allows you to set a range for numbers of the REAL and DOUBLE data type. For example, by setting the tolerance to .5, you would bring up all the rows where TOTAL was between 9.5 and 10.5 in the following command: SET TOLERANCE .5 EDIT ALL FROM tblname WHERE total = 10.0 ■ ZERO: SET ZERO ON means that all null values will be treated as if they were zero in mathematical calculations. ■ TIME FORMAT, TIME SEQUENCE, DATE FORMAT, DATE SEQUENCE: With TIME and DATE you can now set the format in which the TIME or DATE column will be printed as well as the sequence in which they will be entered. The format and sequence need not be the same. You can, print literals in the DATE format and can use a new WWW format to print the day of the week. For example, the following command: SET DATE FORMAT "WWW+ MMM+ DD, YYYY" would produce the following output format for a date of 10/12/86: Friday October 12, 1986 See your manual for a complete description of all the formats at your disposal. ■ CURRENCY: The CURRENCY option allows you to specify the currency symbol and to specify whether it precedes or follows the number. ■ MULTI: The MULTI switch turns multi-user on and off. ■ WAIT, VERIFY, and LOCK: These are associated with the multi-user option. SETS SAVED WITH DATABASE Several of the SET parameters are now saved with the database. Whenever you open a database these saved parameters will be reset to what they were when that database was last closed. The SET PARAMETERS that are saved with the database are: ■ AUTOSKIP ■ BELL ■ CASE ■ CURRENCY ■ DATE FORMAT ■ DATE SEQUENCE ■ TIME FORMAT ■ TIME SEQUENCE ■ NULL ■ REVERSE ■ TOLERANCE ■ ZERO All parameters are reset to the defaults each time R:BASE is loaded, but the parameters listed above are automatically changed to those stored for the database when the database is opened. This means that if you want to set specific parameters for a command procedure, you should first open the database and then issue the series of SET commands. BACK UP TO FLOPPIES Back up and restore large databases to multiple floppy disks using R:BASE commands. EXPRESSIONS With R:BASE System V fully supports a large variety of complex expressions and parentheses are fully supported. SELECT ANALYSIS With R:BASE System V you can do quicky "what if" analyses without actually changing the data stored in your database and without having to create a report. Do it by using expressions in a SELECT command. For example, you could see what it would cost you to give everybody a 10 percent raise in pay by comparing the bottom line totals from the following two SELECT commands: SELECT SALARY=S FROM PEOPLE SELECT (SALARY*1.10)=S FROM PEOPLE STAR MULTIPLY The symbol for multiplication in R:BASE System V is the asterisk. R:BASE System V will still attempt to correctly interpret the X as a multiplication symbol if there are spaces on either side of it, but in general you should use the asterisk. SPACES NOT REQUIRED R:BASE System V does not require spaces around operators in expressions. For example, (A*B) meaning "A times B" is perfectly legal in R:BASE System V. SMOOTH SCROLL When using the EDIT command, the display screen will now smoothly scroll up and down. LONG COMMAND LINES With R:BASE 5000, command lines can be up to 1600 characters long, but each line of the command must be 79 characters or less with a plus symbol (+) to continue to the next line. With R:BASE System V, you can still continue the lines if you like but there are four additional features. First, with R:BASE System V, your total command line can be up to 5000 characters long. Second, if you are using RBEDIT to write a command file, you are still limited to a max of 79 characters per line. If, however, you use another editor or a word processor (under nondocument or nonformat mode), to write your command files and macros, a single line may be longer than 79 characters. In fact, it may be as long as 1000 characters without having to continue it onto the next line. Third, if you are entering the command at the R> prompt, you do not have to enter the plus sign. R:BASE System V will automatically insert the plus symbol and continue on the next line. You can key in your command without stopping. Fourth, as long as you do not hit the enter key until the end of the entire command line, you can bring back an entire multiple line command by using the [CTRL][right arrow] keys or the [END] key. This is very handy if you make a mistake in a long command line. Also, the [HOME] key will move you to the front of a command line without zapping the command line. If you want to zap the command line, that is easily accomplished with the [CTRL][left arrow] keys. BEEP The new BEEP command will cause your computer to beep. Forms EXPRESS _____________ R:BASE System V has a powerful new Forms module. By Using R:BASE System V Forms EXPRESS you can, without having to write a program, set up forms that can do all of the following : MULTIPLE TABLES A form can give you access to up to five different tables. When using the form, the operator can move easily from one table area to the next by using the [F9] key. CUSTOM COLORS Assign different colors to different columns, variables, and regions located in the form. DRAW BOXES Draw single-line or double-line boxes around data entry regions or anywhere else on the form. Use the arrow keys to draw in all directions and use the [F4] key to "put the pen down" and "pick the pen up" from the screen. You are actually drawing on the screen and do not have to remember ASCII codes. Even corners are done automatically. MULTIPLE ROW REGIONS Define a special region on the form where multiple rows will be displayed or entered. For example, display one row of customer information from the CUST table at the top of the form and display five invoice rows from the INVOICE table for that customer. If the customer has more than five invoices, the operator can scroll through the rows in the region by using the [F8] key to go forward and the [F7] key to go backward. MULTIPLE PAGES A form can have up to five pages. When creating the form, press the [PGDN] key to begin creating a new page. Later, the operator can move from page to page in the form by using the [PGUP] and [PGDN] keys. VARIABLES IN THE FORM Global variables can be used directly in the form. R:BASE System V forms may make R:BASE 5000 variable forms less necessary. You can use your R:BASE System V form to update global variables for use throughout R:BASE. You can control whether or not the operator will be able to change the value, what color it will be, and define a default value for it directly in the form. You can also use variables for display purposes only. For example, you can display the current date by using the system variable #DATE. FUNCTIONS AND EXPRESSIONS Expressions can be used directly in the form to automatically calculate fields based on the value of other fields. Using this feature you can: ■ Automatically assign unique numbers by concatenating the julian date, hours, minutes, and seconds from the system date and time. ■ Use conditional functions to automatically display the absolute value of a negative (or credit) balance column in a red field and display a positive (or debit) balance in a blue field. ■ Do on screen calculations. CUSTOMIZE MENUS When using a form, R:BASE System V automatically displays a menu at the top of the screen. There is one menu for data entry and another for editing. You can modify these menus. The title can be changed and you can reorder or delete the choices presented in the menus. In this way, your application can control the operator's choices. PROTECTION In addition to revising the menus you have many other ways to protect data and control how the form is used. You can add these protections directly in Forms EXPRESS without having to program them. ■ You can protect specific fields in the form, not allowing the operator to edit some fields while allowing edits to other fields. This is done by assigning field characteristics to each field (variables and columns) located in the form. To assign field characteristics, answer the questions that Forms EXPRESS asks. ■ You can have one form for data entry and another for editing by copying the data entry form to a new name and then changing the characteristics on the new form to make it an edit only form. ■ Protect your tables by customizing table characteristics. Allow (or prevent) adding new rows. Allow (or prevent) replacing existing rows. Allow (or prevent) deleting rows. ■ Assign read-only and/or modify passwords to the form that will override the table passwords. LOOKUPS Look up values already in a table and display them on the screen during data entry. One master lookup expression will make all the columns in the looked up row available for locating on the form. In other words, it is not necessary to have an expression for each column in the row. SELECTIVELY REUSE FIELDS In addition to having default values, you can selectively repeat fields from the previous record. In R:BASE 5000 you must reuse the entire previous record. In R:BASE System V you can select which fields to reuse. EASY FORM MANAGEMENT By picking from Forms EXPRESS menus you can easily: ■ List, remove, create, change, or copy a form. ■ Rename or delete a table associated with a form. This will not rename or delete the actual table but will change the table information in the form. ■ Reorder the tables used in a form. For example, change the first table, in a three table form, to the last table. ■ Define, delete, retype, or reorder the expressions associated with each table used in a form. ■ Customize lookups. ■ Change the prompting order of the fields. In addition there is a new command: REMOVE FORM formname Reports EXPRESS _______________ Reports EXPRESS works in a manner similar to forms. You will use many of the same function keys to perform similar functions. For example, in both Forms EXPRESS and Reports EXPRESS, use the [F6] key to locate a field (column or variable). DRAW BOXES Draw boxes and borders for your report. Use the arrow keys to draw in all directions and use the [F4] key to "put the pen down" and "pick the pen up" from the screen. You are actually drawing on the screen and do not have to remember ASCII codes. Even corners are done automatically as you change directions. Keep in mind, however, that the borders may not print on the printer the way they look on the screen. For the borders to print on your printer you will need a graphics printer that supports ASCII codes greater than 127. WIDER REPORTS Reports in R:BASE 5000 can be up to 131 characters wide. Reports in R:BASE System V can be up to 255 characters wide. EASY FIXED FOOTER In R:BASE System V, it is easy to make a page footer print on a specific line of your report. In R:BASE 5000, it is necessary to set up vertical tabs and send specialized printer control codes to your printer. REPORTS MAINTENANCE Use the main Reports EXPRESS Report Options Menu to list reports, create a report, change a report, copy a report with a new name, or remove a report. In addition, R:BASE System V has a new command: REMOVE REPORT rptname VARIABLES IN REPORTS Global variables are used exclusively in R:BASE System V reports. There are no specialized report variables that are not global variables, as there are in R:BASE 5000. All variables used in R:BASE System V reports are global variables. EXPRESSIONS AND FUNCTIONS Expressions and functions can be used directly in the report. This includes the 70 SuperMath functions. You can put more than one operator on a line and use parentheses in your expressions so you may be able to reduce the number of variables necessary in your report. By using the logical functions IFEQ, IFLT, and IFGT, you can do some conditional processing in R:BASE System V reports. For example, if a result was negative, you could print the absolute value in a credit column. If the result turns out to be positive, you could print it in a debit column. You would do this by locating two fields VTOTCR and VTOTDR and loading the actual result into the appropriate variable while setting the other to zero. If the actual result was in a VACT variable, the following two expressions would accomplish this: VTOTCR = (IFLT(.VACT,0,(ABS(.VACT)),0)) VTOTDR = (IFLT(.VACT,0,0,.VACT)) FileGateway ___________ FileGateway is the recommended method in R:BASE System V for importing and exporting files. IMPORT AND EXPORT In addition to the importing capability of R:BASE 5000 FileGateway, R:BASE System V FileGateway makes it possible to export Lotus 1-2-3 worksheets, Symphony worksheets, VisiCalc worksheets, fixed format ASCII files, and delimited ASCII files. The export facility, like the import facility, is menu driven and easy to use. The AS ASCII option on the UNLOAD command is no longer available in R:BASE System V. Therefore, to unload data in one continuous line for each row in the table, you will need to use R:BASE System V FileGateway rather than the UNLOAD command. SAVING CONVERSION PARAMETERS Use the RECORD and PLAYBACK features in R:BASE System V to save your FileGateway parameters and automate the file conversion process for both importing and exporting files to your R:BASE System V database. RECORD is toggled on and off with the [F7] key. Playback is initiated with the [F8] key. Compatibility With Our Other Products _____________________________________ We will be making the Extended Report Writer (XRW), the Program Interface (PI), and CLOUT (our natural language interface option) compatible with R:BASE System V databases in the near future. There will also be a Runtime version of R:BASE System V available soon. R:BASE 5000 databases currently work with XRW, the PI, CLOUT, and there is a Runtime version of R:BASE 5000. Environment ___________ R:BASE System V requires more memory and a hard disk. R:BASE 5000 will run on a dual floppy machine. R:BASE System V program files are larger than R:BASE 5000 program files. R:BASE 5000 comes complete on six (6) disks. R:BASE System V is a complete system of programs and is shipped with a total of eleven (11) disks. R:BASE System V uses up more of the available space on the hard disk for the program files. R:BASE 5000 will leave more room for larger databases. This is an important consideration for those who have small hard disks. R:BASE 5000 is a powerful product, and if you have a small system, you may find that you do not need the extra power available in R:BASE System V. We will continue to sell and support R:BASE 5000 so that you can choose the product that suits your needs. R:BASE System V requires 512K bytes of memory in single user mode and 640K in multi-user mode. R:BASE 5000 will run with as little as 256K bytes of memory (on an IBM-PC with no hard disk and no memory resident programs). R:BASE 5000 Multi-user requires 640K on file server and 512K on each of the nodes. If you are using Multi-user, the hard disk requirement of R:BASE System V may be significant. In both R:BASE System V and R:BASE 5000 Multi-user your application will run faster if you install the R:BASE program files on each and every node in the network. If you have R:BASE System V and want to install it on all the nodes, each node must have a hard disk and 640K of memory. R:BASE 5000 Multi-user can be installed on dual floppy nodes containing only 512K of memory. It is NOT required, with either product, that you install R:BASE on all nodes. However, on most networks, if you do install the R:BASE program files on all of the nodes, your applications may run faster. MICRORIM TECHNICAL NOTE ________________________________________________________ TIPS ON CONVERTING FROM R:BASE 5000 TO R:BASE SYSTEM V DATE : 08/86 NUMBER : EX-8-2 PRODUCT : R:BASE SYSTEM V VERSIONS : 1.0 CATEGORY : CONVERTING SUBCATEGORY : FROM R:BASE 5000 ________________________________________________________ DESCRIPTION: How do I convert from R:BASE 5000 to R:BASE System V? EXPLANATION: Converting your R:BASE 5000 application to an R:BASE System V application involves two procedures: database conversion and application/R:BASE code conversion. SOLUTION: As a first step in converting to R:BASE System V from R:BASE 5000, back up your database and then follow the instructions in the white Conversion Guide booklet to convert your database structure and begin to make alterations to your R:BASE applications. After you follow the instructions in the Conversion Guide, converting your programs from R:BASE 5000 to R:BASE System V will be mostly a matter of revising your application to take advantage of the new power features in System V and adjusting your programs to follow new syntax rules. If you are using variable forms you may want to replace them with R:BASE System V forms. Variable forms, developed in R:BASE 5000, will work in R:BASE System V, but you will find that R:BASE System V forms make your application run faster. Create your new forms using Forms EXPRESS. This step alone has been reported to make some applications run two to four times faster. There are also some differences between the two products that you will want to allow for. R:BASE System V functions and complex expressions require new strict syntax rules. You may need to make some revisions to your R:BASE 5000 code to comply with these new syntax rules. Here are some tips to help you convert from R:BASE 5000 to R:BASE System V. Keep in mind that when we use the word parse or any variation of the word parse, we mean the way R:BASE reads, evaluates, and interprets commands and expressions. ■ All variables in R:BASE System V are global variables. If you have report variables with the same name as global variables, rename one or both of them. If you print the same report more than once in the same session, you will need to clear your variables or set them to zero. R:BASE 5000 has report variables that are unique for that report. R:BASE System V does not have separate report variables. For example, if your report has six variable expressions in it, using the variables: VCOUNT VTOT1 VTOT2 VTOT3 VNAME and VCITYSTZ, you might want to use the following commands to print the report: SET ZERO ON SET NULL " " CLEAR VCOUNT VTOT1 VTOT2 VTOT3 VNAME VCITYSTZ OUTPUT PRINTER PRINT yourrept OUTPUT SCREEN SET NULL -0- CLEAR VCOUNT VTOT1 VTOT2 VTOT3 VNAME VCITYSTZ This code will do a number of things for you. First, zero is set on, which means that in any math involving null values, the nulls will be treated as if they are equal to zero. Setting null to a blank in the second command line, will cause all nulls to be printed as blanks. The CLEAR command allows you to specify a list in R:BASE System V; you no longer have to enter a CLEAR for each of the variables. The last commands set null back to -0- and clear the variables again. ■ R:BASE System V needs to be explicitly told what is an expression, what is a variable, what is a value, and what is a column name. If you have an expression, let the expression parser know it by surrounding the entire expression with parentheses. For example, SET VAR vcommis TO 100 SET VAR vnum TO (15 % .vcommis + $25.00) SET VAR v3 TO (.v1 + .v2) Lookups are the exception to the rule. Do NOT use parentheses in lookups in reports or forms, and do NOT include mathematical functions and expressions in lookups. ■ Enclose all literal date entries in quotes. If the quotes aren't included, the expression parser in R:BASE System V will perform two division operations. For example, the date 10/10/86 will not be understood as a date unless it is enclosed in quotes. Instead it will be parsed as 10 divided by 10 divided by 86. ■ Although R:BASE 5000 required spaces around all operators in an expression, R:BASE System V does not. The following is a command that will work correctly in R:BASE System V. SET VAR vdate TO ("10/10/86"+90) If your R:BASE 5000 expressions have spaces in them, there is no need to remove the spaces. R:BASE System V disregards spaces. ■ Pretype any value being that might be misconstrued as something other than a single item. For example, a part number such as 510-12 or 1005-A, might be interpreted as two items with a minus sign between them. To tell R:BASE that this is a single item and not an expression, you must enclose the value in quotes or pretype it as text. Otherwise, the expression parser will attempt to subtract the portion of the item that follows the dash (-) from the part that precedes the dash. The following will work correctly: SET VAR vpartno TEXT SET VAR vpartno TO 1005-A ■ To tell the parser that a name you are using in an expression is a column name, enclose the name in single quotes. For example: 'CUSTID'. Be careful to use only the right leaning single quote as both the open single quote and the close single quote. Make sure that none of your column or variable names contain a special character. Do not use math operators in variable names or column names. Also, names should not be made up entirely of numeric characters. If a column or variable name is a number, it may be mistaken for a number rather than a column or variable name. These are examples of variable and column names that should NOT be used: AA%BB 01 XYZ-1 12345 TOT/AMT ■ In most cases, you should set the data type of a variable before using it in an expression. The following example illustrates an operation that is acceptable in R:BASE 5000 but is not valid in R:BASE System V when the variable is not pre-typed. The desired result is abc1. SET VAR vprodid TO abc + 1 To get the above SET VAR command to function in R:BASE System V, you can do either of the following: ■ Establish the data type of the variable prodid as TEXT prior to using it in the expression. This can be done with the SET VAR vprodid TEXT command. ■ Use an appropriate function in the expression to make the expression valid. For example, if you want to concatenate, you will need to tell the expression parser that the number one (1) should be used as a TEXT one rather than a numeric one (1). To do this you would need to use the CTXT function. Without the CTXT function, the parser would think you were trying to add text to a number and would give you an error message. For example, the following would be the way to do the above SET VAR command in R:BASE System V: SET VAR vprodid TO (abc + ctxt(1)) Simply enclosing the number in quotes is not sufficient. It is necessary to actually use the CTXT function on the number to literally tell the expression parser to treat the one as a TEXT element. ■ To use a comma in a concatenation operation, a variable must sometimes be defined equal to the value ",". An example is shown here: SET VAR vcomma TEXT; SET VAR vcomma TO "," SET VAR vcsz TO (.vcity+.vcomma&.vstate&.vzip) It may not always be necessary to set a variable to the comma first, but rather than attempt to remember when you have to and when you don't, it will be easier to always use a VCOMMA variable. ■ Try to adhere closely to the convention of beginning all variable names with a V. You will need the convention in R:BASE System V to be able to quickly determine if you have a variable or a column name. MICRORIM TECHNICAL NOTE ________________________________________________________ TIPS AND AXIOMS FOR USING R:BASE SYSTEM V EXPRESSIONS DATE : 08/86 NUMBER : EX-8-3 PRODUCT : R:BASE SYSTEM V VERSIONS : 1.0 CATEGORY : EXPRESSIONS SUBCATEGORY : TIPS AND AXIOMS ________________________________________________________ DESCRIPTION: What general tips should I keep in mind in using the new expressions and functions in R:BASE System V? EXPLANATION: R:BASE System V allows complex expressions including 70 SuperMath functions. To assist you in becoming more familiar with the R:BASE System V expressions, we have compiled this list of tips and fundamentals relating to expressions. What Is An Expression? An expression is a mathematical operation involving two or more operands (also known as arguments) and any of the valid math operators. An expression can also be a string operation such as concatenation. Expressions can be used in a myriad of places throughout R:BASE System V. For example, expressions are valid in: ■ Up to ten computed columns per table ■ Reports ■ Forms ■ WHERE clauses (as long as the WHERE clause is not on a lookup) ■ The CHANGE, SET VARIABLE, COMPUTE, IF, WHILE, and SELECT commands Expressions can be as long as 160 characters and can be nested up to 75 parentheses deep. Functions are acceptable operands within an expression and functions can be nested inside other functions. Expressions can have up to 25 columns and variables in them and up to 50 total items. Creative use of nesting and functions can produce very powerful results. For example, if you had a discount structure that was based on quantity ordered, you could set up a computed column that will automatically compute the discount for you. In this example, if a customer buys five or less gadgets then there is no discount. If she buys six to 100, she will get a 10% discount. If she buys over 100, she will get a 25% discount. There are three applicable columns: QTY INTEGER *(giving the quantity ordered) PRICE CURRENCY *(the unit price of the item) EXTENDED CURRENCY *(the computed column that determines the discount to apply and calculates the extended price minus the applicable discount) The discount percentage is the result of the following nested function: (IFLT(QTY,6,0,(IFLT(QTY,101,10,25)))) The above function expression reads as follows: If the quantity is less than six make the discount zero. If the quantity is not less than zero and is less than 101, make the discount ten. Otherwise, make the discount 25 because that means that quantity is 101 or greater. Now, by taking the above nested function and nesting it into the EXTENDED column (defined as a computed column with a CURRENCY data type), the complete expression for the EXTENDED column is as follows: ((QTY*PRICE)-((IFLT(QTY,6,0,(IFLT(QTY,101,10,25))))%(QTY*PRICE))) You now have a computed column that is calculated based on the values in other columns and based on a specific conditional grouping. Commands That Use Expressions CHANGE colname TO (expression) IN... CHANGE commis TO (15%'saleprce') IN #2 COLUMNS - entered in conjunction with a column definition totprce (115%('qty'*'cost')) CURRENCY COMPUTE operation (expression) FROM.. COMPUTE ALL (expression) FROM... COMPUTE commis AS SUM (2.5%'invtotal')... EXPAND tblname WITH colname =(expression) + datatype length EXPAND custlist WITH + wholenm=('firstnm'&'lastnm') TEXT 25 IF condlist THEN an expression can be a condition IF totwt EQ (1.286*'gross') THEN IF ('onhand'*'cost') GT (.95*'budget') THEN REDEFINE colname1 TO (expression)...IN... REDEFINE col3 TO ('col1'*'col2') IN tab1 SELECT collist (expression) FROM... SELECT #2 #3 #7 ('qty'*'cost')=S FROM tab1 SET VARIABLE varname1 TO (expression) SET VAR area TO (.#PI*r**2) SET VAR wholenm TO (.fn & .ln) WHERE condlist... an expression can be a condition WHERE netval GT ('rating'*'avesal') WHILE condlist THEN an expression can be a condition WHILE duedt LE (.#DATE + 30) THEN WHILE (.v1**2) NE (105%.totrow) THEN Expression Limits 160 Total number of characters per expression 10 Computed columns (which are, in effect, expressions) per table 25 Columns and/or variables per expression 75 Nesting levels (except for text expressions where nesting is not allowed. 50 "Items" per expression For example: ((a + b) - (c + sqrt(d))) - - - - - - - - has a total of eight items including: a + b - c + sqrt d. Expression Axioms ■ COMPUTE cannot use TEXT expressions ■ TEXT expressions will not support nesting ■ TEXT expressions must be enclosed in parentheses when used as a "value". For example, ...WHERE csz EQA (cty&st&zip) ■ Arguments used with TEXT functions must be constants, columns, or variables ■ REDEFINE cannot be used on a column that is used in a computed column definition. For example, if COL3 is defined as (COL1 * COL2), neither COL1 or COL2 can be REDEFINED ■ NOTE columns cannot be used in expressions nor can NOTE columns be the result of an expression ■ NOTE columns cannot be computed columns New Math Operators ■ Unary plus (+) ■ Unary minus (-) which is the same as multiplying by -1 ■ Multiplication (* replaces the X symbol, however R:BASE 5000 commands using the X operator with spaces before and after, will function correctly in System V) ■ ** Exponentiation Some Parsing Information First, the expression parser determines if the item is a variable, column, or a value. The following takes priority with the parser: ■ .varnm - the dot (.) tells the expression parser that the item is a variable ■ 'aaaaa' - the single quotes indicate that this is a column name. Use only the "right leaning" single quote located on the quotations key on your keyboard. The left leaning single quote will not work. ■ "aa bb" - the double quotes means treat this as one item. In R:BASE System V both numeric items and text items may be enclosed in quotes. Second, the expression parser checks whether the item is a valid INTEGER, DOUBLE, CURRENCY, DATE, or TIME. This is the exact order that the parser uses in determining the type. Notice that REAL is not in the list. If an item has not been pretyped but is a valid REAL, it will automatically be given a data type of DOUBLE. The only way to assign a REAL data type is to pretype the item. Third, the expression parser checks whether an item is a function, column name, or variable. Fourth, if the item meets none of the above tests, the parser treats the item as a literal text string. Evaluation Order For Expressions Complex expressions are evaluated using the following order: ■ Expressions inside internal parentheses are resolved first ■ Functions are evaluated second ■ Unary + or - situations are resolved next ■ Exponentiation operations are then performed ■ Multiplication or division operations are resolved in a left to right order next ■ Percent operations are done as the next to last step ■ Addition or subtraction operations are done last Expression Evaluation Examples ((2*6)/(2**2+4)) Internal parentheses: 12/(2**2+4) Within the second set of parentheses: Exponentiation: 12/(4+4) Addition: 12/8 Division: 1.5 (Answer) (-.num*(2+2)**3) assuming num=3 Internal parentheses: -.num*4**3 Unary -: -3*4**3 Exponentiation: -3*64 Multiplication: -192 (Answer) ((12-SQRT(16)/2)%20) Internal parentheses Functions: (12-4/2)%20 Division: (12-2)%20 Subtraction: 10%20 Percent: 2 (Answer) NULL Values In Expressions NULL values may be handled in one of two ways. They can either be treated as 0 or as a "black hole." The SET ZERO ON/OFF command controls this situation. If ZERO is set off (and off is the default setting), numeric expressions containing NULL values will always have NULL as an answer. With ZERO set on, NULL values are treated as if they are equal to zero in numeric operations. Also, be aware that numeric variables that have NULL values when added together will cause a zero result, if ZERO has been set on with the SET ZERO ON command. For example, if variable V1 is NULL and varible V2 is NULL, the following code will make variable V3 equal to zero: SET ZERO ON SET VAR V3 = (.V1 + .V2) If ZERO is set off, then V3 in the above example would be NULL. MICRORIM TECHNICAL NOTE ________________________________________________________ MODIFYING THE RBSYSTEM MAIN MENU IN R:BASE SYSTEM V DATE : 08/86 NUMBER : EX-8-4 PRODUCT : R:BASE SYSTEM V VERSIONS : 1.0 CATEGORY : PROGRAM SUBCATEGORY : RBSYSTEM MENU ________________________________________________________ DESCRIPTION: I want to customize the main menu that comes up when the operator enters RBSYSTEM to add a DOS batch file. EXPLANATION: The main RBSYSTEM menu can be customized by modifying the RBSYSTEM.ASC file. When R:BASE System V was shipped to you the main menu looked like this: |------------------------------------------------------------------------------| | | | *************** |-----------------------------| | | ********************* | R:BASE System V | | | *************************** |-----------------------------| | | ****************************** | Definition EXPRESS | | | **************** ************ | Application EXPRESS | | | *************** ************* | Forms EXPRESS | | | *************** ************** | Reports EXPRESS | | | *************** *************** | R:BASE | | | **************** ***************** | RBEDIT | | | ***************** ************* | FileGateway | | | ***************** ************** | CodeLock | | | ****************** *************** | Return to DOS | | | ****************** **************** | | | | ****************** ***************** | | | | ****************** ****************** | | | | ****************** ******************* |-----------------------------| | | | | | | Copyright (c) by Microrim, Inc., 1986 | |------------------------------------------------------------------------------| And the RBSYSTEM.ASC file looked like this: SET COLOR FORE 7 SET COLOR BACK 0 1 "Definition EXPRESS" "RBDEFINE -R -F7 -B0" 2 "Application EXPRESS" "EXPRESS -R -F7 -B0" 3 "Forms EXPRESS" "FORMS -R -F7 -B0" 4 "Reports EXPRESS" "REPORTS -R -F7 -B0" 5 R:BASE "RBASE -R -F7 -B0 -P" 6 RBEDIT "RBEDIT -R -F7 -B0" 7 FileGateway "GATEWAY -R -F7 -B0" 8 CodeLock "CODELOCK -R -F7 -B0" 0 "Return to DOS" Notice that when the product is shipped there are nine choices listed in the RBSYSTEM.ASC file. If you wish to modify the current choices in the menu, read the instructions beginning on page 10-10 of the R:BASE System V User's Manual. The main reasons for modifying the main menu are: ■ To change the order of the choices listed in the menu. ■ To remove choices from the list ■ To add an .EXE or .COM executable file to the menu ■ To change the options (-P, -M1, etc.) on one or more of the choices ■ To change the colors for a particular module ■ To add a DOS batch file to the choices. ■ To add an auto execute file to an R:BASE module, or have several R:BASE modules with each one automatically executing a different R:BASE application. The manual covers how to do all of these modifications except for the last two. This technical note will address how to add a DOS batch file to the menu choices and how to have several R:BASE applications in your menu. SOLUTION: To add a DOS batch file to the RBSYSTEM menu, first, write the batch file that you want to use. For example, a batch file that did a directory, paused, and then returned to the RBSYSTEM main menu would look like this: ECHO OFF DIR PAUSE EXIT The PAUSE command in DOS stops execution of the batch file and displays the message: Press any key to continue The EXIT command gets you back to the RBSYSTEM main menu. It is not required, but it is fine to have it in the file if you want it. Second, add the batch file line to the RBSYSTEM.ASC file. It does not have to go at the bottom of the file. Put the line in the position you want it to display in the menu and assign a number larger than 99 and a choice name to it. For example, if you have a batch file that does some disk management functions and the file is named MANAGE.BAT, your entry in the RBSYSTEM.ASC file should be similar to the following: 100 "Disk Management" "COMMAND /C MANAGE.BAT" Your RBSYSTEM.ASC may look like this after adding the MANAGE.BAT batch file, removing the EXPRESS options, and changing the order on the RBEDIT and FileGateway options: SET COLOR FORE 7 SET COLOR BACK 0 5 R:BASE "RBASE -R -F7 -B0 -P" 7 FileGateway "GATEWAY -R -F7 -B0" 100 "Disk Management" "COMMAND /C MANAGE" 6 RBEDIT "RBEDIT -R -F7 -B0" 8 CodeLock "CODELOCK -R -F7 -B0" 0 "Return to DOS" The COMMAND /C portion of the line is required. It invokes COMMAND.COM (the file that comes with DOS) and then calls the batch file MANAGE.BAT. You need to have your COMMAND.COM file in the root directory of your hard disk. Make sure that the root directory is included in your DOS path command. Notice that even though the R:BASE System V modules have been rearranged, the numbers associated with each module have not been changed. No matter where you locate the R:BASE line, it always keeps it's number 5, FileGateway keeps 7, RBEDIT keeps 6, etc. It is also possible to repeat the R:BASE number 5 line as many times as you like with different parameters, choice name, and auto execute files associated with it. This is great for choosing applications from the the RBSYSTEM menu. For example, the RBSYSTEM.ASC file listed below would give the operator the option of choosing the following three ways of running R:BASE: ■ R:BASE with prompts ■ R:BASE without prompts ■ Orders Application SET COLOR FORE 7 SET COLOR BACK 0 5 "R:BASE with prompts" "RBASE -R -F7 -B0 -P" 5 "R:BASE (no prompts)" "RBASE -R -F7 -B0" 5 "Orders Application" "RBASE -R -F7 -B0 ORDERS.CMD" 7 FileGateway "GATEWAY -R -F7 -B0" 6 RBEDIT "RBEDIT -R -F7 -B0" 0 "Return to DOS" The operator would now get the following RBSYSTEM menu: |------------------------------------------------------------------------------| | | | *************** |-----------------------------| | | ********************* | R:BASE System V | | | *************************** |-----------------------------| | | ****************************** | R:BASE with prompts | | | **************** ************ | R:BASE (no prompts) | | | *************** ************* | Orders Application | | | *************** ************** | FileGateway | | | *************** *************** | RBEDIT | | | **************** ***************** | Return to DOS | | | ***************** ************* | | | | ***************** ************** | | | | ****************** *************** | | | | ****************** **************** | | | | ****************** ***************** | | | | ****************** ****************** | | | | ****************** ******************* |-----------------------------| | | | | | | | | Copyright (c) by Microrim, Inc., 1986 | |------------------------------------------------------------------------------|