Internet Toolkit/Internet Toolkit.iso/tutorial/pc_learn/dbtips.tut

Text File | 1993-08-16 | 16.4 KB | 320 lines

       The following information is reprinted with permission, Ultimate
       Power Tips 1.0A (c) 1992, 1993  Paul Scanlon, Scanlon Enterprises


       ----------------------------------------------------------------

                              DATA BASE POWER TIPS

       ----------------------------------------------------------------

        Effective Data Base Reports

        Effective data base reports turn data into information, but if
        your readers can't tell what they're looking at, the report is
        nothing but a dead tree! An informative title and clear column
        headings go a long way toward telling a reports story.

        Use two-part report headlines. For the title, carefully, choose
        descriptive words commonly employed in your business which never
        change. Next, build a modifiable heading, which identifies the
        data set on the particular report. This is especially important
        if you use the same report form (or layout) for more than one
        type of report. Suppose, you use the same layout to generate two
        reports on customers, but select the listings by different
        criteria. One report might show all of your customers, while the
        other might include only those who haven't bought anything this
        year. Using the same report form, not only saves you time, but
        if you use a layout consistently, your co-workers will know, for
        instance, that the last purchase date is always in the fifth
        column. But, if both reports have the same headline, you'll have
        some confused and angry co-workers. Everyone will be much
        happier if you name these reports something like "Customers" and
        "Inactive Customers". Most data managers let you supply report
        headings at run time.

        Sorted Details: The heading can do double duty, by telling your
        readers what order the records are in. Most people, assume that
        a report is sorted by, or printed in the order of, its left-hand
        column. It's therefore best to design a report form with the
        sorted column (be it NAME, ZIP or something else), on the left.
        This is NOT always possible, especially if you're using the same
        report form for a number of reports. The solution, specify the
        sort order in your heading, as in "Inactive Customers (No
        Purchase in 1991)".

        At the head of the Column: After the report heading, the column
        headings are the best clues to what's on the report. The easiest
        (but not necessarily the smartest) thing to do is simply accept
        default in many report writers and use the field names provided.
        If the fields have simple names, like, "Phone", that's no
        problem, but a column headed "DTPOST" will confuse the boss.
        Therefore, when the field name is a code or abbreviation, spell
        it out in the column heading. For instance, "Date Posted" is
        much easier to understand at a glance, that "DTPOST". If your
        description column heading is wider that its column, split it
        into two lines. You can do this with virtually all report
        writers. Once you've spelled out your column headings, format
        then for easy reading. Parallel justification is important. A
        left justified or centered heading will look confusing over a
        column of right justified numbers. AS a general rule, you should
        left justify character column headings, center those above dates
        and long descriptions and right justify heading over numbers.
        Other column heading design tips: Use uppercase and lower case
        letters. They're easier to read than all uppercase or lower
        case. Put a line below each column heading, stretching the full
        width of the column. If you've put some thought into your report
        heading and column headings, the people who read your reports
        will be happier. In the long career run, this will make you a
        happy camper.



        Naming Reports As You Print Them

        How would you go about printing, from a data base of all your
        customers, a report of just those customers added since last
        July? Assuming your report form is called CUSTLIST.FRM and your
        date field is called "First_Sale", the obvious answer is to use
        the command "REPORT FORM CustList TO PRINT FOR First_Sale >= C
        TO D("07/01/90")". One extra step will give your report a title
        that accurately reflects the data in the new report. The dBase
        REPORT command, has a little used HEADINGS option, that lets you
        add a title at print time. If you change the command to "REPORT
        FORM CustList TO PRINT FOR First_Sale >= C TO D("07/01/90")
        HEADING "Customers Added Since July 1, 1990", the resulting
        report will be much clearer to your co-workers.


        Enhance Your dBase Logic Field Usage

        Tired of explaining what T and F stand for in DBase III Plus
        Reports? If your DBase reports often display the contents of
        logical fields, you'll produce more readable output by using
        DBase's IFF() function with the Active expression to substitute
        words for the logical values. Suppose you have a file called
        Vendors with a logical field called Active, containing T & F in
        a report column, instead of the Active Expression itself, use
        "IFF(ACTIVE,'YES','NO'". You can also use "IFF(ACTIVE,
        'Active','Inactive'".



        Add Printer Codes for Better-Looking dBase Output

        You can spruce up dBase III plus reports by using printer
        control codes to produce compressed type, underlining, boldface
        and other print styles. Just use the following commands. ".SET
        DEVICE TO PRINT"
                  ".@ PROW(),PCOL() SAY CHR(15)"
                  ".SET DEVICE TO SCREEN"
        In the example above, "@ PROW(),PCOL() SAY" sends the control
        code "CHR(15)" (compressed type for Epson and IBM compatibles),
        to the printer at its current location. Substitute other control
        codes (which of course must be specific to your printer) to
        effect other print styles. The commands can be entered at the
        dot prompt, saved in their own command file or used within other
        command files.



        Paradox Shortcut, Pick Your Own Editor

        If you don't like the Paradox Script Editor, you can assign
        another ASCII text editor, such as Brief or Word, to run in
        Paradox. First be sure the text editors directory is referenced
        by the system PATH statement. From Paradox's main menu, select
        Script Play and type "CUSTOM" and press <ENTER>. Now answer the
        prompt about your monitor, and from a subsequent menu, choose
        Pal Editor. Type the name of the executable file that invokes
        your text editor, and follow it with a space and an asterisk.
        The asterisk, indicates that you want whatever script file is
        loaded when you invoke the editor. Also, you can put an
        exclamation point before the editors file name to give the
        editor more memory, and/or you can add another space and two
        more asterisks to end the line if your editor can accept a line
        number that jumps you to that position in the script. To exit,
        press <ENTER>, select Return Do-It, Do-It, save the CFG file to
        either the harddisk or Network. Paradox will close. The next
        time you load Paradox, Script Play will summon your favorite
        editor.



        dBase & Foxbase do Multiline Reporting

        Still stuck with an old dBase III Plus or Foxbase report writer?
        It may seem impossible to build a multiline report, but it's
        NOT. Here's the trick. Placing a semicolon within the column
        contents will wrap the column to the next line. This lets you
        load a multiline group of expressions, such as a three line
        address block, into a single column. To do this, enter the
        column contents, such as the following, into a single column.

        TRIM(FNAME) + " " + TRIM(LNAME) + ";" + ADDRESS + ";" +
        TRIM(CITY) + " " + STATE + " " + ZIP

        The above should be entered continuously. Set the column width
        no narrower that the widest possible combination of data
        appearing between the semicolons, otherwise, you'll get
        unintended word wrap effects. Unfortunately, there's no easy way
        to box and address. For that you'll have to upgrade.


        From a Table to a Form

        A Browse screen table view shows you many records at once, while
        an Edit screen form gives you a clearer view of one record.
        dBase III Plus, offers no way to toggle easily between these
        views, but you can create such a toggle by programming. Here's
        how to do it with the least amount of code :
                X = .F.
                DO WHILE .NOT. X
                   BROWSE
                   EDIT
                   @21,37 SAY "Exit?" GET X
                   READ
                ENDDO
                RETURN
        This example assumes that your data file is already open. The
        program starts a loop, that brings up the Browse screen,
        (dBase's table view). Here, you can scroll through your data and
        even edit it. When you exit Browse, by pressing <ESC> or <Ctrl>W
        the Edit screen appears, showing the record that was highlighted
        when you exited Browse. When you exit Edit, with <ESC>, you'll
        be asked if you want to exit the program. If you respond with
        "Y" or "T", the program terminates. If you answer "N" or "F",
        the program resumes looping, and brings you back to the Browse
        screen.



        Making a Form Your Own

        When you view data in Paradox, <F7> acts as a hot key for going
        between the table and form views. By default, the form displayed
        with the hot key is a simple vertical listing of your table's
        fields. But Paradox lets you redesign this form to make it
        easier to read. For instance, you can place CITY, STATE & ZIP
        fields on a single line. To change the form display, select
        Forms Change from the MAIN menu. Enter the name of your table
        when prompted, and press <ENTER> to select the highlighted "F"
        from the list of forms that can be changed. (Other forms are
        listed by number. If the table has no other forms, only the "F"
        will be displayed.) Enter a description for the form, or press
        <ENTER> to keep the default, STANDARD FORM. That boring Standard
        Form now appears. Pressing <F10> brings up a menu of options for
        changing the look and, for that matter, the functionality of the
        form. For instance, you can use the Area Move command to move a
        field to another part of the screen, or the Border Place command
        to draw a box. Explore the menus to see what else is available.
        Once you are satisfied with your new form, press <F2> to save
        your work and exit. The next time you press <F7> from a table
        view, the current record will pop up using your new and improved
        form.



        Copy a Calculated Field in Paradox

        When you're designing a report, with a number of similar,
        complex, calculated fields, it's handy to copy a formula from
        one field to another. Unfortunately, Paradox, has no commands
        for doing this. You can get around this shortcoming, by creating
        an instant Script macro when you first enter the formula. While
        designing a report, before creating the first calculated field,
        press <Alt><F3> to start keystroke recording to an Instant
        Script. Then press <F10> and select Field Place Calculated. Next
        type in your formula, and press <ENTER> <Alt><F3> to end
        recording. When you want to place a similar calculated field in
        another location, simply press <Alt><F4> to play the Instant
        Script. You will soon be prompted to place the field. Press
        <ESC> <Ctrl>F, edit the formula, press <ENTER>, and place the
        field as prompted.



        Multi-Config for Q&A

        Changes made at Q&A's Utilities Set Global Defaults screen are
        saved in a file called QA.CFG. If you want to have alternative,
        reusable configurations, save one configuration before creating
        another, and copy this file to another name, such as QA2.CFG. To
        use QA2.CFG, copy it back to QA.CFG. This copy back and forth
        can be automated via a batch file. Don't forget to save the
        original QA.CFG, by copying it to a temporary file, such as
        QA.TMP, before you copy the QA2.CFG to QA.CFG.

        EXAMPLE: COPY the current QA.CFG file to QA1.CFG, you have now
        cloned the current config information. Now, enter Q&A's
        Utilities Set Global Defaults Screen, and set up your second Q&A
        configuration. EXIT Q&A and copy the new QA.CFG to QA2.CFG. Now,
        make two batch files, one called QA1.BAT the other QA2.BAT. The
        first line in each batch file will copy the correct QA config
        file. For QA1.BAT, this should be "COPY QA1.CFG QA.CFG", and for
        QA2.BAT, this should be "COPY QA2.CFG QA.CFG".



        Getting the Right Record With DBase III Plus

        Set Relation, which links two data bases by a common key, is one
        of the most powerful DBase III Plus commands. However, Set
        Relation has one undocumented quirk that can produce undesirable
        results. When DBase searches the child data base for the first
        key that matches the parent data base's key, the matching record
        may be a deleted one, even if Set Delete is on. To avoid this
        problem, ensure that the child data base contains no deleted
        records before you create the relation by issuing a Pack
        command.



        Duplicating Paradox Entries

        When working with Paradox 3.0+, it is often necessary to
        duplicate the contents of a field from one record to the next.
        To do this, use the Paradox "Ditto" key, which copies the value
        from the same field of the previous record. The "Ditto" key is
        the combined keystroke <Ctrl>-D. This key combination is listed
        in the index of both the Paradox Introduction and the User's
        Guide manual.




        Reporting From a Paradox Query

        Paradox offers no direct way to print a report of only some of
        the records in a table. If you need a list of customers added
        since last July, for instance, you can't use your Customer
        table's Report R to print it. You can create an Ask query, that
        lists only those customers, then use the Tools Copy Report
        command to copy your report form from the Customer to the Answer
        table. Printing Answer's report rather than the Customer's will
        give you the selection you want. Even then, the reports' title
        won't state which customers are listed, and Paradox 3.0+ has no
        way to change a report title when you print it. So, before
        printing, the report, you'll have to use the Report Change
        command to change the name manually. Do this after you've copied
        the report to the Answer table, and be sure to change the name
        of the copy, not the original. If you have Paradox 3.5 is
        easier. When creating or changing the report, put a calculated
        field in the title's place, using a variable name as the
        expression. From then on, changing the contents of the variable
        will change the title of the report. <Alt><F10>MiniScript is an
        easy way to do this.



       Tutorial finished. Have you registered PC-Learn to receive your
       bonus disks? Registration is encouraged. Shareware works on the
       honor system! Send $25 to Seattle Scientific Photography,
       Department PCL6, PO Box 1506, Mercer Island, WA 98040. Latest
       version of PC-Learn and two bonus disks shipped promptly!