February 1997 - The Quick Answer
Contents
1. Goto Done2 Done lt Macro Next Record The FILES database is for use only by the macro driven procedure The Case No field s program prevents anyone from manually adding records to it The initial Alt 1 macro that launches the procedure runs the Unique Case Output FOLDERS report I mentioned earlier then imports the resulting ASCII file into FILES DTF It then opens FILES DTF in Search Update mode presses F10 at the Retrieve Spec and presses tab from the Case No field of the first record That s it for the Alt 1 macro Now the program in FILES DTF s Donel field takes over Step 1 of Donel s program invokes the Run FILES Report macro This macro presses Shift Tab to move back to the Case No field and copies the Case Number to the Clipboard It then saves and exits the record and runs the FOLDERS DTF Files List by Case Rev2 report pasting the Case Number into the report s Retrieve Spec Be sure the Case No field is Speedy The canned report is printed to a disk file named FILES The same macro then returns to FILES DTF in Search Update mode pastes the same Case Number into the Retrieve Spec pulls up the same record it left a moment ago and presses Tab to move to the Donel field Since Donel now contains Done part 2 of Donel s program executes The Shell command moves the FILES file the disk report for the current Case Number to the matching WordPerfect subdirectory and the Goto comm2. Most people at the firm aren t Q amp A literate shame on them but they all know WordPerfect One of the paralegals who manages the FOLDERS database decided she was going to make everyone s life easier by creating a no brainer way for anyone to find file folders This was her objective Each WordPerfect Case Number subdirectory will include a document named FILES that lists the existing file folders for that Case Number A quick look at the FILES document for any case Network Server H for example will help the user determine which folder WordPerfect is needed and where it s H AWP51 stored Case files H WP51 FILES Although the paralegal got 02345 HAWP51 FILES C02345 NNSS the system going it was taking Documents an enormous amount of time to print each of the FOLDERS reports to disk some 300 of 02346 NISS p Cases 10 11 12 12 February 1997 Volume 8 Number 2 Tracking Files in the Law Office Tom Marcellus Editorial My Eyes My Eyes Tom Marcellus QuickTip When aText String Includes a Date Gordon Meigs The Program Spec Squeezing in Additional Help Jeff Nitka Help T J Shuflin How to Create a Duplicate Record Report William Halpern QuickTip Shelling to Windows 95 Programs Gordon Meigs Updated or Outdated Alec Mulvey QuickTip Custom Picklist Sorting Alec Mulvey QuickTip Build a Text String a Step at a Time Gordon Meigs QuickTip Sorting Large
3. Single User Version error message appears when someone tries to start another Q amp A session with your single user version of Q amp A already active After returning from the screen saver to the desktop Q amp A is minimized on the Task Bar at the bottom of the screen but is still active Turning off the screen saver setting for Q amp A should help reduce the possibility of users trying to start a second instance of Q amp A Note that Q amp A 5 0 has the ability to run multiple copies of a single user version of Q amp A via the External Programs menu or Shell programming command But this is an entirely different matter Using Text and Field Data with the Askuser Command s9 I created a Q amp A 5 0 database so my school can store information about student suspensions and unexcused absences I ve programmed it to maintain a running total of these values for each student Now I d like to program it with the Askuser command to prompt for a decision regarding expulsion ld like the Askuser box to show the student s name the numbers from the Suspensions and Absences fields and some fixed text but I can t figure out how to do it Leslie Warner Metairie Louisiana Here are some fields and programming that make the Askuser command give you what you need Name 1 Student ID 5 Suspensions 20 Absences 30 lt 35 Has Str 20 Suspensions and Str 30 Unexcused Absences The Quick Answer Februar
4. the field would be 10 updated every time you viewed the record Both of these date fields should be made read only File Design File Program a File Read only fields so they can t be changed from the keyboard Taking it further To have even more precise information you can add Time Added and Time Modified fields to complement the Date Added and Last Modified field If the database is used on a network you can also include a field that shows who last modified the record It can be important to know precisely when a record was added or last modified Examples are support desks or companies that track response times on telephone requests A field that stores time values needs to be formatted H for hours You can use Time as an Initial Value to time stamp new records and the Time function in a program to return the time of day the record was last modified If users log into a Q amp A database with a password it s easy to capture their user IDs For new records you can t do it with an Initial Value so you ll need some programming along these lines Record Added By 320 If Add and 320 Then 320 UserID Last Modified By 340 If Update and Modified Then 340 UserID The User field is a text field All the programming for the modified fields can be in a single field like this 300 If Update and Modified Then LastModDate Date LastModTime Time LastModUserID UserID
5. F8 at the Program Spec and type 10 in the on record entry field The following sample program works for five rows of fields under columns headed Target Date Job Description and Job Status The fields themselves have no labels and are numbered as follows Target Date fields 20 22 24 26 and 28 Job Desc fields 40 42 44 46 and 48 Job Status fields 60 62 64 66 and 68 Today s Date lt 10 10 Date If Add then Cnext If 20 lt gt and 20 10 lt 5 and 60 lt gt C then Color 20 15 4 If 22 lt gt and 22 10 lt 5 and 62 lt gt C then Color 22 15 4 If 24 lt gt and 24 10 lt 5 and 64 lt gt C then Color 24 15 4 If 26 lt gt and 26 10 lt 5 and 66 lt gt C then Color 26 15 4 If 28 lt gt and 28 10 lt 5 and 68 lt gt C then Color 28 15 4 If you update a Target Date or Status field you ll need to save the record then return to it so the program can recalculate and conditionally apply the colors A quick press of F9 then F10 displays the previous record assuming you aren t in the first of a group of retrieved records then return you to the one you just updated The Quick Answer February 1997 Selection List Problem 60 I download names and addresses of residents in a geographical area then import them into my Q amp A 5 0 database My database includes Street Address and Street Name fi
6. With these various fields Initial Values settings and programs you can not only determine when the database was first used and last used in terms of adding and updating records but also which records were added or modified during any period of time at which you care to look This can be useful if for example you re a developer who needs to know if any data was changed between the time you made a copy of the database and returned with the amended version How old is the database But what about time stamping the database as a whole As I mentioned earlier the DOS date stamp is no help because it The Quick Answer February 1997 only tells you when someone last opened the file Beginning with Q amp A 4 0 you can attach a file description to any Q amp A database or Write document After you create the database you can go to a Q amp A List Files screen highlight the file press F6 and enter a description of up to 72 characters You can include the date and even the time the database was first used knowing these won t Initial Values When Upgrading If you re upgrading from Q amp A 4 0 to 5 0 it s best to remove any Date and Time Initial Values before converting the database to Q amp A 5 0 There s a bug in Q amp A 5 0 that can make a converted database that includes these Initial Values misbehave if it s subsequently run under Windows You can reinstate the Initial Values once you ve converted the database to Q amp A 5
7. database you haven t looked at for a few years the one with the DOS 1994 time stamp will be redated by DOS as soon as you search it whether you modify anything or not That s why I consider it essential to keep tabs on dates and ensure database records are appropriately date stamped I ll show you how I do it Two essential fields Every database that contains time sensitive information and time sensitive is a relative term should have a field that shows the date the record was added to the database and another that shows the date it was last modified Stamping the record with the date it was added to the database is easy all you have to do is type Date in the appropriate date field at the Initial Value Spec File Design File Customize File Set Initial Values See the sidebar Initial Values when Upgrading As for the date the record was last modified you can use a simple program File Design File Program a File Program Form like the following in the Last Modified field 300 If Modified then 300 Date This works well as long as the Calculation Mode is set to Automatic QA 4 0 doesn t have the Modified function so you have to use the Update function This isn t as reliable as it will update the record when F8 Manual Calc is pressed even if the record wasn t modified at all One combination not to use is Update with the field s program set to execute on record exit In this case
8. for the same price as a single copy Bulk Mail Permit No 4297 Rockville MD POSTMASTER Send address changes to The Quick Answer PO Box 9034 Gaithersburg MD 20898 9034 Copyright 1997 by Marble Publications Inc All rights reserved No part of this periodical may be used or reproduced in any fashion whatsoever except in the case of brief quotations embodied in critical articles and reviews without the prior written consent of Marble Publications Inc Address editorial correspondence HELP questions or requests for special permission to Marble Publications Inc The Quick Answer PO Box 9034 Gaithersburg MD 20898 9034 Phone 800 780 5474 or 301 424 1658 Fax 301 424 1658 CompuServe 73370 1575 For Q amp A technical support call Symantec 503 465 8600 Q amp A is a trademark of Symantec Corp Other brand and product names are trademarks or registered trademarks of their resp ective holders This publication is intended as a general guide It covers a highly technical and complex subject and should not be used for making decisions concerning specific products or applications This publication is sold as is without warranty of any kind either express or implied respecting the contents of this publication including but not limited to implied warranties for the publication quality performance merchantability or fitness for any particular purpose Marble Publications Inc shall not be liable to the purchaser or any
9. just the item description you can use an on field exit program like this gt 50 If Instr 1234567890 Left 50 1 Then 50 Mid 50 3 99 If there are more than nine items on the list you can use two digit prefixes such as 01 02 and so forth to display a custom sorted list of up to 99 items QA Alec Mulvey Keyword Training and Consultancy Ascot England 11 a Suppose you have afield in your database to store a structured statement like this Ti You want to control what goes into the statement to ensure it s accurate as to spelling and grammar and you want to assemble it with minimum keystrokes The solution is to use a series of Q amp A 5 0 s Userselect or XUserselect commands so you can select the various components in logical succession To see how the technique works create a little database with fields for First Name 10 Last Name 20 and a custom text field 80 Suppose you re telemarketing credit cards and you want to complete the form with the person s name and a complete sentence in the custom text field like this Miss J ones does not wish to receive an American Express card Dr Smith does wish to receive a VISA card Miss J ones does not wish to receive an American Express card In the Program Spec number the fields as indicated earlier and place the following program in the custom text field Q amp A can t sort a database of more than about 110 000 records unl
10. person A different approach needed Q amp A is very capable when it comes finding and removing duplicate records Your choices though are limited to one of the following e Simply find and remove the duplicate records e Find and remove the duplicate records to an ASCII file so you can review the deletions Both of these approaches have their drawbacks In the first case you can t predict which record s in a set of duplicates Q amp A will remove Will Q amp A nix the latest record in the set with the new phone number and address Will it delete John Smith ID 345 the unique John Smith In the second case you re left with an ASCII file Though you can use it to create another database to compare to the first one or to review the records before finally deleting those you really don t want wouldn t it be wiser to run an initial report that shows all the duplicate records Figure 1 shows what such a report might look like At a glance you can see you need to review 522 records only a portion of them are shown in Figure 1 and you can do so by checking the person s first name and city This is important because J and Joseph Davis and Mike and Michael Alexander might be the same person Using the ID numbers as a reference you can easily decide which records are really duplicates and which should be removed You could include other fields in the report perhaps to tell you which is the most recent record in each set of du
11. 0 change no matter what is subsequently done with the database Be aware though that because the file description is stored in the DTF file adding one will itself update the file s date stamp If you re running the Q amp A Network Pack on your PC you ll receive the error message File is in use cannot read or write description if you try to add a description to the database you ve just worked with To release the file lock open then close another database or press Shift F6 at the Main menu to clear the User ID and password Yet another way to permanently record the date a database goes on active duty is to print it on the database form itself Developers often do this by including the database s version number the date it was created and of course their name and contact information This doesn t require a field you simply add it to the form as background text QA Alec Mulvey is a Q amp A Consultant and Symantec Approved Trainer He owns Keyword Training and Consultancy in Ascot near London England He s also a Microsoft Certified Trainer and Certified Professional Fax 44 1344 884 111 100327 44 compuserve com Picklists whether Q amp A 4 0 style Restrict Spec lists or Q amp A 5 0 Userselect lists are sorted alphabetically by Q amp A But what if you want one or more items to appear at the beginning or end of the list With a Q amp A 5 0 Userselect list you can type a space in front of any
12. 3 Rt Help2 Len Help2 Width Help2 if Mid Help2 Width Help2 1 lt gt and Mid Help2 Width Help2 1 1 lt gt then Help2 Lt Help2 Width Help2 Help2 Repllas Help2 Help3 Rt Help2 Len Help2 In Help2 Help3 Help2 Lt Help2 In Help2 1 else Help2 Lt Help2 Width Help2 Return Using this technique you can increase or decrease the width of each Help field in the Design Redesign Spec and the program compensates The results of Help1 Help2 and Help3 each a 42 character field look like this Please choose from the following options If none of the options apply press Escape When done press F10 QA Jeff Nitka works for a chemical manufacturer and develops Q amp A applications part time for Epoch Software 908 874 3989 J eff is the author of the Program Evaluator a Q amp A program debugging utility and FaxMan a Q amp A faxing database both of which are available from Marble Publications EDITED BY Tij SHUFLIN Graphic Images in Forms and Documents lve downloaded some GIF image files and would like to use them in my Q amp A for Windows documents and forms Can I And if so how do I do it Kevin M Carman Brockton Massachusetts I ll assume you want to insert a graphic image as part of a letterhead in Q amp A Write document You also asked about placing a graphic image on an input form so Ill assume you wan
13. Databases Gordon Meigs Documents them one for each open case then copy the files over to the 02347 corresponding WordPerfect subdirectories NANS She wanted to automate the entire process including copying the Documents Figure 1 The H WP51 FILES directory has a or so WordPerfect subdirectories subdirectory for each Case Number containing all the document files relating to the case report files to the appropriate WordPerfect subdirectories This way she could flick a switch to update every FILES document in all 300 Continues on page 3 My Eyes My Eyes Tom MARCELLUS EDITOR O you did not suddenly go colorblind Check your socks Yep Same two colors as usual But to keep The Quick Answer coming to you at the same subscription price we ve had to make a few changes including switching to one color printing We ll continue to pack just as many Q amp A tips tricks and techniques as we can into each issue and maintain the high level of quality to which you re accustomed Q amp A s Remove Duplicate Records command can be injurious to your data You can t always specify or predict which records in a group of dupes you want Q amp A to toss out Q amp A keeps the first one it finds and chucks the rest The Duplicate Records to ASCII option provides a safety net but shows only the nixed records not the ones left behind That s why I m impressed with Bill Halpern s clever approach t
14. Tracking File Folders in the Law Office Tom MarceLLUS In a fast paced document centric paper mill like a large law firm you Si need all the help you can get Here s a litigation folder tracking system 60 even non Q amp Aers can use to quickly find out what s where ORDPERFECT and Q amp A are popular with the legal profession In many law offices WordPerfect generates the documents and Q amp A tracks client billing and litigation related information When the law firm I recently did some work for takes on a new case they assign a Case Number then create a new subdirectory under WordPerfect using the same case number All WordPerfect documents relating to the case are stored in this subdirectory making them easy to manage and find see Figure 1 But not only do law firms have electronic files to keep tabs on they have the physical file folders to track too Nothing generates paperwork like litigation This particular firm usually has about three hundred cases in process and folder control is crucial Dozens of file folders can be generated for just one case during the course of a lawsuit and the attorneys and staff need a fast way to find out which documents are in what folders They track file folders with Q amp A When a new folder is created a record is added to the FOLDERS database showing the Case Number the three lines that appear on the physical folder label and a key word that indicates where the folder can be found
15. and moves the cursor to the Done field Done2 s program invokes the Next Record macro This one deletes the current record since it has served its purpose and is of no further use displays the next record in Search Update mode and presses Tab from the Case No field to run the report for this second Case Number The looping routine continues in this manner until all 300 or so FILES DTF records have been processed and deleted and all the WordPerfect Case Number subdirectories contain updated FILES reports On a fast PC all you see while the procedure is running is each DOS command echoed as it moves each FILES file from Q amp A to the corresponding WordPerfect subdirectory D QA DOCS FILES gt C WP51 FILES CO3456 FILES ok The entire procedure 300 new reports takes just a few minutes Here are the three macros that run it lt begdef gt lt alt1 gt lt name gt lt caps gt altl lt caps gt lt vidoff gt rp lt capsf4 gt d qa data folders lt enter gt unique lt sp gt case lt sp gt lt sp gt output lt enter gt n lt enter gt files asc lt enter gt y lt esc gt fuis lt capsf4 gt d qa docs files asc lt enter gt lt capsf4 gt d qa data files lt enter gt 1 lt 10 gt lt rgt gt lt f10 gt lt esc gt lt esc gt fs lt enter gt lt f10 gt lt tab gt lt enddef gt lt begdef gt lt nokey gt lt name gt Run lt sp gt FILES lt sp gt Report lt vidoff gt lt capstab gt lt f11 gt lt capsf10 gt lt esc gt rpfolders lt
16. dependence 00465 Davis James Newark 00467 Davis Joseph Independence 00456 Davis Shane Cedar Bluff 00468 Smith Ben St Louis 01532 Smith Dan Hammond 01530 Smith David Westview 01531 Smith George Upper Marlboro 01536 Smith Godfrey Newport News 01529 Smith Gordon Millersville 01537 Smith Harold Alexandria 01538 Smith John Boulder 00123 Smith John Boulder 00987 Smith John Boston 00345 Smith Lawrence Baltimore 01528 Smith Mayer Fairfax 01539 Smith Robert U 01533 Zimmerman Geo Virginia Beach 01829 Zimmerman George Virginia Beach 01824 Count Figure 1 Portion of a sample duplicates report The Quick Answer February 1997 The Last Name and First Name fields are sorted alphabetically and made invisible so they control the sort order but don t appear in the report A unique field such as ID Number must be included for this technique to work When you ve filled out the Column Sort Spec press F8 for the Derived Columns screen and create the following 10 derived columns Heading Duplicate Last Name Formula No 1 1 Column Sort Spec 500 I Heading Name amp Code Formula No 2 Txt 1 500 1 Column Sort Spec 25 1 24 Str 4 Heading Prior First Name Formula No 3 2 Column Sort Spec 490 I Heading Prior Code Formula No 4 4 Column Sort Spec 480 I Heading Matching Last Name Formula No 5 Txt 25 lt gt 470 500 Column Sort Spec 30 C Heading This NC Dupe Formula No 6 25 Colu
17. e as the previous parameter because it s the same field 4 Starting range in this case whatever you ve typed in the Street Name field 5 Ending range in this case also whatever you ve typed in the Street Name field 6 The field you want to receive the list item you select Tom Marcellus QA T J Shuflin is a forensic chemist and lab director of a crime laboratory in Alexandria Louisiana Since serving as the first president of the National Q amp A User Group in 1991 92 he remains active with the group by maintaining its Internet Web site at http www timetrend com tshuflin How to Create a WILLIAM HALPERN Before you purge those duplicate records you might want to review them in a report to ensure you don t chuck the wrong dupes Here s a derived column technique that gives you the information you re after v0 have a client who recently created a 2 500 record database of sales agents by importing records from various old databases and ASCII files Although we expected some duplication of information the problem was that the agents would be assigned identification codes using simple Numbers to link them to sales Thus it wouldn t do to have John Smith ID 123 credited with a sale then have John Smith ID 987 credited with the next one when they were the same person No problem you say Just run the Sales Report by name Oh I forgot to mention that John Smith ID 345 is a different
18. ecords in FOLDERS for each Case Number I started by creating a supplementary database to contain one record for each unique Case Number I named the database FILES DTF and added these three fields to it Case No Donel Done2 To fill FILES DTF s Case No field I created a FOLDERS DTF report named Unique Case Output with these specs Case No 1 AS I Heading Formula 1 Column Sort Spec 50 I Heading Formula Text 1 lt gt 50 1 Column Sort Spec 2 HS Print to Disk These specs output only unique Case Numbers in alphabetical order and suppress the column heading and separator line The resulting disk file is then imported into FILES DTF as a Standard ASCII file using a 1 in the Case No field at the Merge Spec and selecting no quotes and comma field delimiter at the ASCII Options screen Controlling the procedure Now that FILES DTF contains one record per unique Case Number its programming can print to disk the 300 or so reports destined for the WordPerfect subdirectories The three FILES DTF fields contain the following programs Case No lt If Add Then Msgbox This is supplementary database used to compile FILES reports You cannot add records to it Exit The Quick Answer February 1997 Donel lt If Donel Then Donel Done Macro Run FILES Report Else If Donel Done Then Done2 Shell Move d qa docs FILES c wp51 files Case No
19. elds and I have a separate database that contains 10 000 or so street names As I go through the newly imported records need to lookup some of the street names to be sure they re valid and correctly spelled I ve tried XLookup but I have to type in the entire street name to find a match I ve also tried XUserselect but it takes forever for the list to appear and I get only the first 3 000 street names Is there a way I can type a few letters of the street name and get a list of the streets that start with those letters Brian Watson Auckland New Zealand Yes You need a program like this for your Street Name field gt If Street Name lt gt Then XUserselectR STREETS Street Street Street Name Street Name Street Name Notice that this is the XUserselectR command with an R for range on the end The command uses the letters you type in the Street Name field as the starting and ending range parameters for the selection list Accordingly if you type in three letters and press Enter a list appears that includes all the street names that begin with those three letters Here s a brief explanation of XUserselectR s six parameters in the order they appear in the sample command 1 Name of the external lookup database 2 Name of the Speedy field in the external database 3 Name of the field in the external database whose values you want to appear on the selection list In this case it s the sam
20. enter gt files lt sp gt list lt sp gt by lt sp gt case lt sp gt lt sp gt rev2 lt enter gt y lt f12 gt lt 10 gt lt 10 gt lt f 10 gt lt enter gt files lt enter gt lt esc gt fsfiles lt enter gt lt f 12 gt lt f10 gt lt tab gt lt enddef gt lt begdef gt lt nokey gt lt name gt Next lt sp gt Record lt vidoff gt lt f3 gt ys lt enter gt lt f10 gt lt tab gt lt enddef gt The report I thought it would lend a classy touch if the folder entries in the FILES reports looked like actual file folder labels Figure 2 shows one with a few dummy entries Here are the Specs for the Files List by Case Rev2 report shown in Figure 2 Figure 3 shows the special derived column formula that produces the boxed column format Case No 1 AS Linel 3 Line2 4 Line3 5 Alpha 2 AS Heading 45 Folder Label Formula See Figure 3 Column Sort Spec 6 As you can see from Figure 3 the box is drawn with ASCII graphics characters Moving clockwise from the top left the box s corners are made with ASCII 218 191 217 and 192 respectively The horizontal lines are made with ASCII 196 and the vertical lines with ASCII 179 The derived column s width is fixed at 45 characters Because the vertical lines use one space each I subtracted the length of each column s value from 43 and padded the difference with spaces That s the Text 43 Len 3 for the column 3 text value The Text 30 at the end
21. ese lists and Msg with its 80 character limit might not be enough room to say what needs to be said I solve the problem by adding a Help field to the database using one of three options Option 1 I place a three line 43 or so character field named Help in the top left corner of the screen This way I can precede any field s program with a command to display the help text in the Help field The following sample program shows how it works In this case a pop up selection list offers three choices and the Help field explains what they do Action lt Help Choose from the following list Choose to save and exit this record 1 Choose 2 to save this record and continue Choose 3 to cancel this operation Action Userselect 1 Save amp Exit 2 Save amp Continue 3 Quit Action Left Action 1 If Action then goto Action else If Action 1 then Exit else If Action 2 then Save else Macro Quit Option 2 Option 1 s multiline field requires that you reserve three lines for the single Help field A way around this is to place three labelless fields Help1 Help2 and Help3 in the top left corner of the page formatted like this in the Design Spec A v The advantage to this design is that you can place other fields to the right of the three Help fields and you can center justify each field s text to improve appearance One drawback though is that you have to break up your help me
22. ess the field you re sorting on is Speedy This isn t just a Q amp A 5 0 limitation Its mentioned in the Q amp A 4 0 READ ME file though the maximum number of sortable Sorting Large Databases Build a Text String a Step at a Time lt 30 If Add then 30 Us Dr Mr Mrs Miss 20 Q Us does does not wish to receive Us a VISA a MasterCard a Discover an American Express card When adding a record three pop up selection lists appear in turn when you reach the custom text field The first is for the title then does wish or does not wish followed by the list of credit cards You have three fast and easy selections and you re done no typing required Here are two variations for special circumstances e You can use Us Insert variabletxt if the selection list is frequently updated lengthy or is used in several databases This way you can edit the list as a Write text file and avoid having to reprogram the database You can create a database containing a record for each selection and use acommand such as XUs phrases dtf phrase to display the list You ll need to restrict such a list to less than 1 000 entries because of memory constraints and displaying it in a network environment is likely to be sluggish The previous option is faster even when the text file is large Gordon Meigs PCTA Newtown PA Wilh p03 records is incorrectly stated the
23. mn Sort Spec 470 I Heading Prior City Formula No 7 3 Column Sort Spec 460 I Heading City Formula No 8 Txt 25 lt gt 470 460 Column Sort Spec 33 Heading Code Formula No 9 Txt 25 lt gt 470 Str 480 Column Sort Spec 36 Heading First Name Formula No 10 Txt 25 lt gt 470 490 Column Sort Spec 31 When you ve added all 10 derived columns press F10 select your printer and print the report How the derived columns work Three techniques come into play in this report design First you have to sort the records by last name which your Column Sort Spec takes care of Second you must have Q amp A check to see if the current record has the same last name as the previous record You accomplish this with a duplicate last name field derived column 1 that Q amp A doesn t evaluate until it processes the next record Third you delay the printing If the records match then Q amp A creates a column value consisting of the last name first name and ID number derived column 2 The data you want to print from each subsequent record is prepared in derived columns 1 Last Name 3 First Name 4 ID and 7 City The delayed printing is controlled by checking that in a record where the last name is the same as the previous record the combination value created in derived column 2 doesn t match the one in the last record Txt 25 lt gt 470 Admittedly the logic isn t for the timid But
24. o previewing potential duplicate records Can you tell how out of date the information in a database is A database s DOS date stamp could be as recent as last month while the bulk of its records might be ancient Can you afford to mail to a list of year old addresses Alec Mulvey shows you how he keeps tabs on time sensitive information like this Recently we heard from a large law firm that uses Q amp A to track litigation file folders Everyone at this firm knows WordPerfect but not Q amp A so one of the paralegals wanted Q amp A to generate daily file folder reports for each case and place them in the appropriate WordPerfect Case Number subdirectories This way anyone working in WordPerfect can quickly view a list of existing file folders for any case But how do you automate running some 300 reports one report per open case and have Q amp A move each to its corresponding WordPerfect subdirectory I ll show you how QA Quick The Independent Guide to Q amp A Expertise Editor Tom Marcellus Publisher Michael Bell Copy Editor Laurie Moloney The Quick Answer ISSN 1052 3820 is published monthly 12 times per year by Marble Publications Inc 9717 Delamere Ct Rockville MD 20850 Cost of domestic subscriptions 12 issues 79 24 issues 142 Outside the U S 12 issues 99 24 issues 172 Single copy price 10 outside the U S 12 50 All funds must be in U S currency Back issues are available upon request
25. of each line forces Q amp A to break the line and continue on the next one The result is that as long as each line of text inside the box Line1 Line2 and Line3 columns 3 4 and 5 in this case doesn t exceed 43 characters they all fit nicely and the label box retains its shape You don t have to be a law firm to reap the benefits of a Send Q amp A questions to Help The Quick Revised 01 18 97 05 06p Case No Alpha 30019 Able ist line Able folder label text 2nd line Able folder label long text entry 3rd line Able fldr label text Baker 2nd line Baker fldr 1bl txt 3rd line Baker folder label text ist line Baker folder label text Charlie ist line Charlie folder 1bl text 2nd line Charlie folder label text entry 3rd line Charlie folder label Dog ist line Dog folder label text 2nd line Dog folder label text Look 1 Next Doc 2 Prev Doc Figure 2 This sample FILES report Files List by Case Rev2 shown in WordPerfect DOS 5 1 s Look screen came straight from Q amp A 5 0 s Report module and the FOLDERS database It isn t touched up This Derived Column Formula e Text 43 4 83 Text 43 Len 83 Text 30 I 84 Text 43 Len 84 Text 30 j 85 Text 43 Len 5 Text 30 E E Text 43 sd wie s Produces this Boxed Format Text from report c
26. olum 83 Text from report colum 4 Text from report colum 85 Figure 3 The formula for the derived column and the resulting label like output folder tracking system like this If your company s activities center around documents and file folders and people need fast access to up to date information on where things are this technique can go a long way toward helping everyone be more productive QA Tom Marcellus is editor of The Quick Answer and author of PC World Q amp A Bible IDG Books His QuickClick Calendar Plus a time and activity tracking database for Q amp A 5 0 is available from Marble Publications publisher of The Quick Answer Stumped Answer Marble Publications Inc PO Box 9034 Gaithersburg MD 20898 9034 or fax to 301 424 1658 Please include your name address phone number and your Q amp A version number and whether DOS or Windows and a detailed description of the problem We II publish those questions we feel are of general reader interest individual responses aren t possible The Quick Answer February 1997 l Jerr Nitka HEN developing applications for others I try to offer liberal dosages of Msg Msgbox and Help screens to guide users through data entry With Q amp A 5 0 though a problem arises when you want to display some help text at the same time you display a pop up Userselect or XUserselect selection list You can t use Help or Msgbox in conjunction with th
27. other person or entity with respect to any liability loss or damage caused or alleged to be caused directly or indirectly by this publication Articles published in The Quick Answer do not necessarily reflect the viewpoint of Marble Publications Inc oN You might need to combine a text message with a date value to autodate a When a Text String IndudesaDate 9 1997 01 07 Paid in Full reminder note or place a date notationin TO get amore familiar date format change your program to this arecord f you have Paid in Full in field 102 and want it to appear in field 103 preceded by today s date you can string the two values together with a program like this 01 07 97 Paid in Full 103 Str Date 102 However you ll get this oddball result and you ll get this 103 Mid Date 6 5 Mid Date 3 2 102 Gordon Meigs PCTA Newtown PA The Quick Answer e February 1997 Tracking File Folders Continued from page 1 A supplementary database needed The FOLDERS database contained all the data needed for the FILES documents Here are the pertinent fields Case No Folder Label Linel Line2 Line3 Alpha Each physical file folder label has three lines matched in the FOLDERS database by the Line1 Line2 and Line3 fields The Alpha field contains a key word about the folder that indicates its location in the filing cabinets Because there might be a dozen or more r
28. plicates But how do you design such a report when Q amp A has no retrieval functions that can find duplicate records The basic concept for the report Ill show you is to find a record where the last name is the same as the previous record Then because you know it s a match you print the information from that previous record In other words Q amp A prints the information from a record after it looks at the following record and qualifies it as a duplicate In essence you re having Q amp A look at two records simultaneously But there s a fault in this logic if the last record in the set is a duplicate it won t print because there s no duplicate record following it Fortunately we can sidestep this problem by including a record in the database where the only field filled is the Last Name field and it contains ZZZ so it Il always be the last record Creating the report Start by designing a columnar report with a Retrieve Spec that includes all the records Fill out the Column Sort Spec like this Last Name 1 AS I First Name 2 AS I City 3 1 ID Number 4 1 Matching Last Name First Name City Code Adams John Washington 00131 Adams Kenneth Reston 00133 Adams Stephen Louisville 00132 Adams Thomas Camp Springs 00134 Alexander Jeffrey Washington 00147 Alexander Mike Camp Springs 00149 Alexander Michael Camp Springs 00148 Davis A K Richmond 00463 Davis Archie Richmond 00458 Davis Brack Lexington 00461 Davis J In
29. re as 150 000 recently sorted on a non Speedy field a 127 000 record database in Q amp A 4 0 and 5 0 Both versions quit sorting at about 110 401 records Gordon Meigs PCTA Newtown PA MARBL PUBLICATI PO Box 9034 Gaithersburg MD 20898 9034 NS Bulk Mail Permit No 4297 Rockville MD
30. se Also we ve lost data because users sometimes exit Q amp A improperly by clicking on the Windows 95 X button in the upper right hand corner of the window then answering Yes at the waming screen The other problem is that the screen saver turns on when Q amp A is inactive for a couple of minutes Then when someone sits down to use Q amp A and clicks on the Q amp A icon on the desktop a Single User Version error screen appears What can I do to make it easier and safer to run Q amp A under Windows 95 Sherry McCloud Lansing Michigan You can control the way Q amp A for DOS runs under Windows 95 To make Q amp A run in Full screen mode where it will look the same as when running in DOS exit Q amp A right click the Q amp A shortcut icon on the desktop and select Properties from the pop up menu The Properties dialog box contains several tabbed sections Open the Screen tab first In the Usage area select the Full screen check box to place a check mark in it then click Apply Next choose the Misc tab In the Foreground section clear the Allow Screen Saver box by clicking on it If you want to prevent users from switching Q amp A into a window where they can exit the program by clicking on the Windows 95 X button clear the Alt Enter check box in the Windows shortcut key section Once again click Apply Finally click OK at the bottom of the Properties dialog box to return to your Windows 95 desktop The
31. selection s you want to appear at the top of the list In a restricted values list Q amp A ignores such spaces but you can precede the entry with a character that sorts before or after the usual alphabetic characters The Table of ASCII Characters in the Q amp A User Guide appendix is actually a sorting table For example you can precede a value with a hyphen to place it at the top of the list or ASCII character 250 a small dot to force it to the bottom of the list You type the character by holding down the Alt key and typing 250 on the numeric keypad For example a list where you want Blue and White at the top and Orange and Purple at the bottom would look like this Blue White Black Green Red Yellow Orange Purple You can strip the special characters from the resulting selection using a suitable on field exit program The Quick Answer February 1997 P Custom Picklist Sorting 3 If you need to custom sort all the items on your selection list you ll need another approach Suppose you want a series of sizes small medium large and so forth to appear on the list in ascending size order like this X Small Small Medium Large X Large Ok WNP In this case you precede each item with anumber or letter Once you ve made your selection you can have your program strip out the selection number or to process the selections as numeric values leave the number and strip out the description following it To leave
32. ssage like this Helpl Choose 1 to save and exit this record Help2 Choose 2 to save this record and continue Help3 Choose 3 to cancel this operation Another drawback is that for every help message you want to display you have to know the width of the fields so The Quick Answer February 1997 tiie Squeezing in Additional Help you don t include too much help text in them Too much text causes an arrow at the end of the field with the remaining text hidden Option 3 The third option addresses the inconvenience of Option 2 even though the three Help fields are designed the same way Simply assign the entire help message to Help1 then perform a Gosub to another field to parse the message into the three Help fields Here s a sample program that does the trick Action lt Helpl Please choose from the following options If none of the options apply press Escape When done press F10 Gosub Help2 Help2 lt Clear Help2 Help3 if Len Help1l gt Width Help1 then Help2 Rt Help1l Len Help1l Width Help1 if Mid Help1 Width Help1 1 lt gt and Mid Help1 Width Help1 1 1 lt gt then Helpl Lt Help1 Width Help1 Helpl Repllas Help1 Help2 Rt Helpl Len Helpl In Help1 Help2 Helpl Lt Helpl In Help1 1 else Helpl Lt Help1 Width Helpl1 if Len Help2 gt Width Help2 then Help
33. t be in too small a window To enlarge the window click on the Maximize Restore icon the middle icon in the upper right hand corner of the title bar You can then select a larger font for your Q amp A window by clicking on the font tool the leftmost selection on the tool bar The 10 x 18 and 10 x 20 fonts look similar enough to Q amp A running full screen If you exit Q amp A while it s running in a window Windows 95 starts Q amp A in the same window the next time But you can always toggle between window and full screen mode at any time QA Gordon Meigs PCTA Newtown PA Using old information particularly names and addresses can mean wasted money and effort Here are some tips and techniques you can use to keep tabs on how current your data is ATA without a time frame can be useless Take a marketing database of prospective customers If the names and addresses are more than a few years old the entire file might be worthless People will have moved phone numbers and area codes will have changed and the company might no longer be at the same address If the information was captured during the last few months though then it s probably safe to assume it s still valid But how can you tell Without an appropriate time stamping mechanism in place you can t Unlike word processing and other types of computer files a database s date stamp is updated when you simply open the file and view a record Thus that old
34. t the image in the form design not in a picture field in the database First you ll have to get the image into Windows Clipboard You can do this by creating the image in a Windows program or in your case since you have the image bring it into a Windows program then copy it to the Clipboard A graphics program that supports the GIF file format is your best bet Next open the Q amp A for Windows database Display the appropriate input form by clicking Design Input Forms on the Select menu On the Edit menu click Paste and the image should appear on the Form You can then click and drag the image or resize it When you re done resave the form To place the image in a database field you ll first have to redesign the database to include a picture field Once that s done you can copy the image to the Clipboard as outlined earlier Open the database display the appropriate record in Add Edit mode select the picture field then choose Paste from the Edit menu To insert a graphics image into a Q amp A Write for Windows document see pages 6 26 through 6 34 in the QGA for Windows User s Manual for step by step instructions You can Paste the graphic image on the page or Insert it into a frame Problems Running Q amp A for m DOS Under Windows 95 T My office staff uses Q amp A 4 0 for DOS on a PC with Windows 95 and we ve been having a few problems First of all because Q amp A runs in a window it s difficult to read and u
35. the results are accurate and easy to work with when you can t afford to risk tossing out the wrong duplicate records QA Bill Halpern is executive vice president of Professional Computer Technology Associates in Newtown Pennsylvania Bill has been designing and installing Q amp A business applications for the past nine years 215 598 8440 71023 356 compuserve com With Windows 95 and Q amp A 5 0 s External Programs and Shell features you can shell out to a Windows program from Q amp A do some work in the other program then retur to Q amp A and the screen you were on You might want to run a mail merge using MSWord for Windows or transfer data to or from an Excel spreadsheet this way If you re running Q amp A in full screen mode you ll find yourself on the Windows 95 desktop with Q amp A minimized on the task bar when you exit the external program you launched from Q amp A You ll have to click on the Q amp A button on the task bar to return to Q amp A If you re launching a Windows program from a specific Q amp A record you might like to return to that record automatically The Quick Answer February 1997 F Shelling to Windows 95 Programs 3 when you exit the Windows program avoiding the task bar and the additional mouse click To do this run Q amp A in a window not in full screen mode You can toggle between full screen and window mode by pressing Alt Enter When you first toggle Q amp A into a window it migh
36. y 1997 Expulsion Decision lt 40 If Askuser 1 35 Do you wish to Expel Then 40 Will be Expelled Else 40 Will not be Expelled Next Field 50 Field 35 is a single character labelless read only field used to hold some of the information This way the Askuser command in field 40 can combine field information with the fixed text and produce an Askuser box that prompts for an answer about whether the student should be expelled or not Use Color to Attract Attention cD l have a Q amp A 5 0 database that contains among other fields a field for the current date and several rows each containing a Target Date J ob Description and Job Status field I d like to program the database so that when retum to a record to update any of its Job Status fields any lines with incomplete jobs will stand out I d like to be warned when a Status field doesn t contain C and there are five or fewer days between the current date and the Target Date How can I do this Ed Michelson Atlanta Georgia With Q amp A 5 0 one of the easiest ways to draw attention to a field is to use Color to conditionally highlight it You can create a program that will turn any Target Date field meeting your condition into bright white text on a red background Place the program in the Today s Date field and make it the on record entry field This way it ll execute as the record is displayed The Today s Date is field 10 so press
Download Pdf Manuals
Related Search