"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""" NUMBERING THE TIERS -- SUBNUMBERS IN FORM REGIONS """""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""" PRODUCT : R:BASE VERSION : 3.1 CATEGORY : PROGRAMMING SUBCATEGORY : TOOLS """""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""" From Gail Coverdell, 155 Good Country Road, Kalispell, MT 59901. Gail is a science teacher and small business application developer. You can reach her at 406-752-4435. Often it's convenient to have R:BASE automatically number the tiers (lines) in a region. When to Use Subnumbers """""""""""""""""""""" Typically, the form looks like TRANFORM in the CONCOMP database with one or more autonumber columns such as CUSTID or TRANSID at the top part of the form to identify it and a subnumber column such as LINENUM or DETAIL# in the form's region to number the tiers (lines). Below are examples of items that you might want to number in a region: o Line items in a customer's order o Tasks under a unique objective o To-do list items for a specific date o Chapters under a book name o Symptoms for a patient on a specific visit to the doctor. Numbering Automatically """"""""""""""""""""""" R:BASE automatically numbers autonumber columns, assigning the next number when you move to a new row. But you don't want a subnumber column to be an autonumber column because you want subnumbers to start over for each new region (group of rows). To number subnumbers automatically, use the following technique. But first check the "Guidelines and Restrictions" section below to make sure your form is a good candidate for automatic subnumbers. Creating a Subnumber Form """"""""""""""""""""""""" Complete the following steps to make an ordinary form region into one that automatically numbers its lines (tiers). In order for this to work, the table associated with the region must not contain a computed column. The following explanation assumes that the subnumber column is an INTEGER named LINENUM. If your subnumber column has some other name, change all the occurrences of LINENUM to the correct name. This technique also assumes that you have set ZERO ON. +------------+ | STEP 1 | +------------+ Start Forms and create or modify the form region where you want to number the tiers. Locate a single-character TEXT variable named VNEW in the leftmost spot of the region. Then locate the subnumber column (LINENUM in this example) immediately to the right of VNEW. VNEW must come before LINENUM in the field list. Customize the field settings for VNEW to answer "Yes" to the first two questions. Then give VNEW a default value of a pound sign (#). Next, customize the field settings for LINENUM to answer "No" to the first two questions. Locate any other fields you want to have in the tier, and duplicate the tier for the region. +------------+ | STEP 2 | +------------+ Choose "Define..." under Variables, and enter the following six expressions in this exact order: vnew TEXT = '#' vcan INTEGER = (IFEQ(.vnew,'#',1,0)) vnewnum INTEGER = (INT(.vnew)) vtest INTEGER = (IFEQ(.vnew,'#',0,.vnewnum)) vseq INTEGER = (.vcan*(.vseq+1)+.vtest) linenum = (IFEQ(.vnew,'#',(.vseq+1),(.vseq))) +------------+ | STEP 3 | +------------+ Each time you use the form, you must define VSEQ as an INTEGER and set it to zero as shown in this example: SET VAR vseq INTEGER = 0 ENTER formname. Now the R:BASE form will automatically number the tiers in the region. All you need to do is enter the starting number when the cursor is on the pound sign (#). Example of How to Use the Form """""""""""""""""""""""""""""" When you use the modified form, enter the starting subnumber for the region when the cursor is sitting on the pound sign in the VNEW field. After you enter the first number, the form expressions take over to number the rest of the lines automatically. The cursor never enters the subnumber field. It moves up and down the rows in the VNEW field that's located immediately to the left of the subnumber field. VNEW has a pound sign (#) default. For example, when you use the form to edit data, the left side of the tiers in the region might look like this: # 1 Suit... # 2 Shirt... # 3 Shoes... # 4 Blouse... The cursor skips over the number, going from the pound sign to the item. To change a line number, enter the new number while the cursor is sitting on the pound sign. As soon as you press [Enter], R:BASE will move the number to the number column and put a pound sign back in the pound-sign column. When you use the form as a data entry form, the first line number will be wrong. Change it to whatever beginning number (usually 1) you want. Then as you move to subsequent rows, the form expressions will automatically number the rest of the lines in the region by using the increment (usually 1) specified in the expressions. Guidelines & Restrictions """"""""""""""""""""""""" Here are some general guidelines and restrictions: o You must change the first number in the series. Then the form will take over from there. You can change a subnumber at any time. o You may need to change the subnumber again if, for example, you enter five rows, and then press [F7] a number of times to go back and make changes to previous rows. When you press [F8] to move forward again to enter another detail number, the subnumber in the next new row will be too high. Change it to the next number in the series. Then the expressions will again take over to number the subsequent new rows. o This technique may not work correctly if the table that contains the subnumber column also contains a computed column. For example, this technique won't work with TRANFORM in the CONCOMP database because the region's table has a computed column. Expressions Explained """"""""""""""""""""" Here's what each expression does: o VNEW sets the variable VNEW to a pound sign (#). VNEW is located in the region immediately preceding the subnumber. When you enter a new value into the VNEW field, VNEW momentarily changes to that value, but as soon as you press [Enter], the form evaluates the expressions and sets VNEW back to a pound sign (#). o VCAN uses the IFEQ (if equal) logical function to check and see if you just entered a number in VNEW. If you did, the expression sets VCAN to zero to cancel subnumber autonumbering. The cancellation occurs in VSEQ, which multiplies the result by VCAN. o VNEWNUM uses the INT (convert to INTEGER) function, added in R:BASE 3.1A, to convert any number entered in the VNEW field from a TEXT data type to INTEGER. The result is a null value if you use INT on a non-numeric TEXT value. o VTEST uses the IFEQ logical function to again test the value of VNEW. If VNEW contains a number, the form needs to restart the sequence with that number, so it sets VTEST equal to the number stored as an INTEGER in VNEWNUM. If VNEW contains a pound sign, sequencing should proceed normally, so it sets VTEST to zero. o VSEQ adds one to itself to increment the subnumber. But if you just entered a new beginning number into VNEW, VCAN is zero, so it cancels the addition, and VTEST has the new number in it so it sets VSEQ to the new number. o LINENUM is the INTEGER subnumber column. This expression again uses IFEQ to test the value of VNEW. If VNEW is a pound sign, the expression sets LINENUM to VSEQ plus one. But if you just entered a new starting number, the expression sets LINENUM to that number. The value of LINENUM appears in the field on the form and is loaded into the table. At first glance, you may think the last two expressions will increment LINENUM twice, (once in VSEQ and again in LINENUM). But actually LINENUM only adds one to the previous value of VSEQ. R:BASE doesn't write the new global variable value computed in VSEQ until you save the form. The value of VSEQ has changed in the form but the dotted global variable value has not changed yet. When you use a dotted global variable to the right of an equal sign, R:BASE reads the old dotted global variable value, not the form expression value. Both the VSEQ and the LINENUM expressions read the same old global variable value for .VSEQ, so you need to increase both VSEQ and LINENUM for each row in the region. The VSEQ expression ensures that once you save that row, the VSEQ global variable will be increased by the increment in the VSEQ expression making it ready for the next row. Changing the Increment """""""""""""""""""""" To use an increment larger than one, change the VSEQ and LINENUM expressions. For example, to increment by five instead of one, define the two expressions this way: vseq INTEGER = (.vcan * (.vseq + 5) + .vtest) linenum = (IFEQ(.vnew,'#',(.vseq+5),(.vseq))) Expression Processing Examples """""""""""""""""""""""""""""" Here's an example to show you how the expression values change. Say you are moving from the fourth to the fifth line in the region. The last row you saved changed both LINENUM and VSEQ to 4. Here's how the expressions evaluate in the fifth row: o vnew = # o vcan = 1 o vnewnum is null because o vtest = 0 o vseq = (1 * (4 + 1) + 0) = 5 o linenum = (4 + 1) = 5 Now, say you move on to the sixth row in the region. Here's how the expressions evaluate: o vnew = # o vcan = 1 o vnewnum is null o vtest = 0 o vseq = (1 * (5 + 1) + 0) = 6 o linenum = (5 + 1) = 6 Next, say you enter a new number in VNEW (the pound sign) to make the next row 8 instead of 7. Here's what happens in the expressions: o vnew = 8 o vcan = 0 o vnewnum = 8 o vtest = 8 o vseq = (0 * (6 + 1) + 8) = 8 o linenum = 8 Here's the next record: o vnew = # o vcan = 1 o vnewnum is null o vtest = 0 o vseq = (1 * (8 + 1) + 0) = 9 o linenum = (8 + 1) = 9