Technical support memos


February 18, 1998
Using maintain database
Advanced Accounting 5.0 and 5.1 (applies to TAS Professional generally as well)
Betty Vowles and Anthony J. Frates (Copyright 1998 Addsum Business Software, Inc.)

Some words of caution:

The procedures described in this technical memo give a user unrestricted access to the underlying data in your accounting system. You should take the necessary precautions (e.g. by using the password and security facilities) to prevent unauthorized access to sensitive data such as payroll and general ledger records.

Please note, too, that changes you make to the underlying data may affect the accuracy of your accounting system. For example, changing one side of a journal entry may cause your accounts to go out of balance. Please use extreme caution!

Make sure you have a recent back-up copy of the data file [(If you have a current backup tape of your data and can restore files from it if necessary--that will be sufficient. If you don't have a backup tape, you may wish to make quick copy onto your hard drive.) See step #4 if you don't know the name of the file.]

  1. Select F-File Utilities (in the DOS mode of Advanced Accounting 5.1 or via program selection ADV 5.1 File Utilities) 
  2. Select Maintain Database
  3. Enter the file name and then the extension*. You can get a list of available files and descriptions by pressing the F2 key.  
  4. *File extension of B normally signifies your main or default company. If you have more than one company add the additional identifying one or two digit(s). You can determine what the company codes are by going to U-Utilities and then I-Change Company Code. If you receive the message that "There is only one company in the location file" than the extension would just be the default of "B." 

  5. After you choose the file, the fields within that file are displayed. You may choose all the fields in a file by pressing the F3 key or moving the cursor to the proper line in the choose list and pressing the ENTER key. When a field is chosen a star '*' appears to the left of the name. To unchoose a field press the ENTER key again and it will disappear. The cursor automatically proceeds to the next line when choosing manually. The easiest thing to do here is to simply press the F3 key and proceed to the next step. 
  6. Once you have chosen all the appropriate fields press the ESC key. Just press the ENTER key when asked for Owner Name. 
  7. Next the the key names for this file will be displayed. A key name is a pre-defined index key which determines how information can be sorted in the file and how records can be found or located. They are typically based on one field (but sometimes more for "multi-segment" keys) in the file. 
  8. Rather than limit yourself to being able to sort/find records by just one of these keys, it is normally advisable to simply press ESC here to choose all of the index keys and then the database maintenance screen will be displayed. 

  9. The names of the fields will be displayed down the left-hand side of the screen and the actual entry fields are immediately to the right in reverse video. The total number of records will be in the upper right-hand corner of the screen to the right of the "Tot" identifier.. Move to a field that has a ">" in front of it (if you pressed ESC in step 7 above) or to the field you selected as a key. You can press F5 to find the first record in this file sorted by the key you have selected or on which your cursor is flashing. Press F6 to get the last record by the selected key. To do a search for a specific alpha character string, type the enough of the initial identifying characters (for example XER to find XEROX) and press the F9 key (do NOT press the enter key). Once you have "found" or retrieved a record using the F5, F6 or F9 keys, you can then scroll through the records: F8 will move you ahead one record, F7 moves back one record. To do a find on a date field (assuming the date field is a key) simply type in the desired date and, without pressing the enter key, press F9. For a numeric index field, type in the number and press F9. The F5, F6 or F9 fields can be further limited by applying a "filter expression" - see step #12 for further information on filters. 
  10. When a record has been located you can change any desired field simply by moving to that field and typing in replacement characters (a Ctl-U will clear the information contained in a given field). You can use the HOME and END keys to go the first/last characters in the field. If you need to edit an array field you will notice the standard array element notation as part of the field name. If you move the cursor to that field once a record has been found, you can 'page' through the elements in that field by pressing the PAGE UP (previous element), PAGE DOWN (next element), HOME (first element) or END (last element) keys. The current element number is displayed. 
  11. If there are more fields than will fit one page, you can reach subsequent pages by pressing the ^PAGE DOWN key (press the CONTROL key and the PAGE DOWN key at the same time) or to go to previous pages the ^PAGE UP key. Even if you change pages, you will still be on the same record. 
  1. To save a record after making any changes, press the F10 key, to delete a record press the F4 key and to clear the record buffer press the F3 key. F3 just clears what is currently displayed on the screen and does not affect what is permanently stored on your hard disk. F10 re-saves a record if you are editing an existing record; otherwise it will add a new record to the file (i.e. if you do not first find a record using F5, F6 or F9 and press the F10 key, a new record will be added). 
  2. FILTERS: You can apply a filter to restrict the records you access, print, change and so on. A filter is an expression involving data fields that constrains future operations (such as a print) to records that meet the expression's criteria (i.e., make the expression evaluate to .T. or "true"). You enter a field by pressing ^F and setting a field name equal to something. For example, the expression BKAR.INV.CUSNME = "VANGUARD" would 'filter' the records in the BKARINV file returning only those where the CUSNME field was set to VANGUARD.  
  3. An expression can contain field names and constants or literals separated by Boolean operators: 

    > greater than
    < less than
    = equal to
    <> not equal to
    >= greater than or equal to
    <= less than or equal to
    $ is included in ("in-string" operator) 

    Expressions can be joined or chained using connectives:

    .a. AND
    .o. OR
    .n. NOT

    Special functions: 

    CTOD(), LOC(), MID(), TRIM(), UP() (see Appendix A of your manual) 

    For example, the expression (BKAR.INV.NUM < 5000) .a. (BKAR.INV.CUST = "CA") would restrict finds to those invoice records for invoices numbered below 5000 where the customer state was CA. As another example, the expression BKAR.START.DATE>CTOD("09/01/93") could be used to find customers whose start date was after September 1, 1993. (CTOD is a function that converts an alpha string to a date type field.) A particularly useful place to use a filter is in the BKGLTRAN file. To find the needle in the haystack, you could find a particular amount by entering a filter such as BKGL.TRN.AMT=5.23. Then press F5 to find the first record that had a $5.23 amount. Press F8 to find the next record, etc. For a more useful example, a filter of BKGL.TRN.DATE=ctod("01/01/96") .a. BKGL.TRN.AMT=5.23 would find those entires on January 1, 1996 that had an amount of $5.23. 

    In the filter expression you may only use fields which have been selected or chosen (marked with an asterisk). 

  4. Press the ESC key twice to leave the maintain database program
Copyright ã 1998 ADDSUM BUSINESS SOFTWARE, INC. Salt Lake City, Utah
ADDSUM is a registered service mark of Addsum Business Software, Inc.
Advanced Accounting is a trademark of Business Tools, Inc.
Technical support phone number: 801-277-9240