February 1996 - Lantica Software, LLC
Contents
1. and no way to specify for a blank field whether to print a zero or not You can apply these settings only ona global basis and the implications can be serious See the sidebar Secure Your Global Format Options Spacing between columns It s tempting to adjust the spacing between columns setting for an individual report so it prints with just the right overall width on the screen or on paper By doing so however you re reformatting every report in every database in Q amp A A safe column spacing setting is 2 Default to repeating values Default to repeating values is a less hazardous global setting because you can reverse it by including the R code in the relevant field at the Column Sort Spec If a new report is repeating values in a column and you don t want it to simply add the R code to the field Perish the thought that you have to change this setting at the Report Global Format Options screen Action on column break The Action on column break option is trickier than at first glance A column break occurs when the value in a sorted column changes With Skip line set to Yes Q amp A inserts a blank line at column breaks to make the groupings more discernible The Q amp A User Manual page 4 60 for both Q amp A 4 0 and 5 0 states that this global setting can be reversed similar to the R code for repeating values by using the CS code in the Column Sort Spec This is incorrect A positive2. package The Q amp A 4 0 compatible version requires some additional steps since 4 0 can t call external programs from inside a database but detailed instructions for using the Evaluator with Q amp A 4 0 are also included Available from PO Box 9034 8 The Quick Answer A February 1996 The Program Evaluator 79 Quick Answer subscribers 49 plus 2 shipping Credit cards accepted Specify for Q amp A 5 0 or 4 0 Marble Publications Gaithersburg MD 20898 800 780 5474 301 424 1658 Fax 301 424 1658 Inos HN In Q amp A 5 0 while typing your program you can press Alt C to copy it and start a second copy of Q amp A with Evaluator ready to run and your programming code inserted In Q amp A 4 0 you copy and save the program to a Write document close the database open Evaluator in Add Data mode then insert the saved document into the Evaluator s code field You can create macros to automate the process The Evaluator then asks if you d like to run a complete analysis of your program in one fell swoop or stop at the first error I find that stopping at the first error is the better choice since an error corrected early won t unnecessarily flag others that might not be errors once the first one is corrected Either choice begins the evaluation process A nifty progress indicator shows how much of the code has been analyzed If you selected Stop on first error the Evaluator examines the code until it finds the f
3. Code Int Tn Code 1 17576 17576 1 676 65 Chr Int Tn Code Int Tn Code 1 676 676 1 26 65 Chr Int Tn Code Int Tn Code 1 26 26 1 1 65 Close the field then press F10 to run the update The update program will generate codes in the following manner Number Code Result 1 AAA 2 AAB 3 AAC and so on 26 AAZ 27 ABA 28 ABB and so on to 17 756 ZZZ Jeff Nitka holds a Bachelor of Science degree in mathematics and computer science He develops Q amp A applications part time for Epoch Software 908 874 3989 Jeff is the author of the Q amp A Program Evaluator a program debugging utility available from Marble Publications Inc A More on Quick Edit Pick Lists In my article in the October issue Create Versatile Pick Lists I described how to create Userselect lists that you can modify without reprogramming The technique involves storing the list items in the Lookup Table where you can easily add or delete them rather than hard coding them into the Userselect command Here s a variation on that technique that lets you store an entire list in one Lookup Table cell Open the Lookup Table for any database In the Key column type Colors Press Tab to move to column 1 press F6 to expand the field and type a list of colors in any order like this Red Green Blue Black White Yellow Brown Turquoise Purple Pink Tan Gray Mauve Cya
4. Preview to Yes or No and so forth Although the settings here affect only new reports they apply to all databases Global Page Options You use the Define Page screen to set your page options including page dimensions margins headers and footers Although the settings here affect only new reports like the Report Global Print Options they apply to all databases If you ve ever been baffled by inappropriate headers mysteriously appearing on your new reports the culprit is the Report Global Page Options It can be confusing because after all Q amp A forces you to specify a database before it lets you set the Global Page Options Naturally you d think you re setting a default report header only for the database you selected but as it turns out that s not the case Global Format Options You can set the following Global Format Options but Marble Publications Inc before you do pause and reflect for a moment they have a long reach of spaces between columns Variable 1 2 3 etc Default to repeating values Yes No Action on blank value Print 0 Action on column break Skip line Leave blank Don t skip line The range of Q amp A s Global Format Options couldn t be broader Despite having to specify a database to get to this screen the settings you make here apply to all reports including existing ones in all databases Consequently you have no way to set for a single database the spacing between columns
5. Try This Powerful Solution If you answer No at the Askuser prompt you decide not to launch the program the final block of code executes This displays the Shell operation Try This Powerful Solution The technique described in this article provides a fast reliable way to suspend what you re doing to switch to an external program then automatically be returned to the record you were working on The external program of course can be a second copy of Q amp A and when it is you can reap special benefits Suppose you re in the middle of entering a new sales order whose programming executes in Add Data mode when you need to check available stock the customer s record an earlier invoice or the receivables file before printing and saving it To meet such needs why not create a custom external programs menu with selections such as Check Stock Search Customer Search Invoice and Check Receivables You can set each selection to launch a second copy of Q amp A along with the appropriate macro to automate the task in that second copy This way you can quickly switch to any database then return to that pending sales order when you exit Q amp A copy 2 Marble Publications Inc You can create a custom external programs menu for each database you work in One database for example could include selections for tasks A C D and G while another might include selections for tasks B E and F This way only the ap
6. amp A 4 0 and later programming including Q amp A for Windows There are a variety of ways to employ Q amp A s versatile XLookupr range command you ve probably come up with a few yourself In his Program Spec column Jeff Nitka describes ways to use XLookupr that have probably never occurred to you Q amp A 5 0 lets you quickly switch to another DOS program then return to the same record you were working on By making the new Shell and Userselect commands work together you can even customize external program menus for each database and rig them up so you can temporarily suspend what you re doing switch to any record in any database then just as easily switch back to the one you were working on Awesome advantages here I ll fill you in Tom Marcellus Editor Oops In the November 1995 article How to Lookup and Display Information from Multiple Records Listing 1 page 7 suffers from two typos The second RecordNo parameter in line two is missing an opening quote and the end of the last line needs a closing French brace Thanks to reader Jerry Altshuler from Chicago for bringing these to our attention The Quick Answer A February 1996 Volume 7 Issue 2 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 iss
7. of Address2 Suite B 8 CoAddr1 CompuServe aje City Las Vegas CSZ Dictionary flexibility you ll need Q amp A 5 0 s State NU HotShot Graphics Zip 89121 r new Shell command With Phone 702 433 0566 W Phone2 702 644 6468 H Norton Utilities Shell you can switch to an Fax 702 732 3333 Fax2 WordPerfect 5 1 external program from any database record then be returned to the same record when you quit the other program But there s more By combining Shell with another new 5 0 command Userselect you can create an external programs menu for each database Not only that you can include dozens of programs or batch files on your custom menus Notes Esc Exit FirstName Contact ID NOVE891 Type Quick Answer Installing printers printer drivers lasers Creating a spreadsheet like database Adding Help fields to forms Retrieved form 1 Fi Help Figure 1 You can specify which programs and batch files are available to run from any database Create as many custom external program menus as you like with no limit on the number of programs you can access this way C 0 N T A C T S SELECTION MS DOS Editor Tree Disk Utility Total Forms 96 Page 1 of 1 Alt F6 Tab le F Search F8 Ca lc F1 Cont inue Marble Publications Inc The Quick Answer A February 1996 13 database form If you don t use a pointing device with O amp A see the sidebar If you Work Witho
8. 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 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 Marble Publications Inc Columnar Reports Continued from page 1 columns onto a second page The reason is that these settings affect not just new reports but existing reports as well Q amp A assigns column headings to reports in the following order of precedence 1 The column heading as defined at the Column Sort Spec For example the column heading for a field named S Name might be this 10 H Surname 2 The column heading as set at the Report Global Options Column Headings Widths screen 3 The name of the field as it appears in the Set Field Names Spec 4 The field label as it appears on the database form Q amp A will set the column width to the column heading or the longest entry in the field whichever is greater unless the column width has been defined at the Report Global Options Column Headings Widths screen Global Print Options You use the Print Options screen to specify your printer set Page
9. what happened to your personal spelling dictionary simply rename your existing QAPERS DCT dictionary to QA5PERS DCT and you should be all set O amp A 5 0 recognizes the latter filename Jerry Altshuler Chicago Illinois The Quick Answer A February 1996 5 Early Field Reports on Q amp A 5 0 Congratulations You finally received your copy of Q amp A 5 0 for DOS and can t wait to install and start using it 0S 50 Bill Halpern and Jeff Nitka report on documentation and usage issues in the initial 5 0 release issues you should know about Q amp A 5 0 Documentation and Related Issues By Bill Halpern For a text field if the key value is Smith and no Smith record is found XPOSTR will post to the record with the next nearest key value lower in the alphabet than Smith Sanders 1 If you re running a Network Pack copy of Q amp A tor eram pie IE woulen ipost toa amiie 4 0 and try to upgrade it with the 5 0 installation disks you ll receive an error message indicating a problem with the QA1 EXE file The problem isn t with the installation disks The network version makes the QA1 EXE file read only and the installation routine can t overwrite it You can solve the problem by using the DOS ATTRIB command to remove the read only attribute from your existing QA1 EXE file prior to installing the upgrade Here s a sample command that does the trick Substitute your own drive and path for thos
10. 98 9034 Second Class Postage Paid at Rockville MD
11. Report Set Global Options any database even a dummy database Columnar Global Options Set Format Options At the Report Global Format Options screen it would set for example Variable column spacing Default to repeating values Leave blank and Skip line by pressing V Tab Y Tab L Tab S then F10 and three Esc s You could name the macro Restore Report Options and it would look like this lt begdef gt lt alt0 gt lt name gt Restore lt sp gt Report lt sp gt Options lt vidoff gt rs lt capsf4 gt lt enter gt MYDB DTF lt enter gt cfv lt tab gt y lt tab gt l lt tab gt s lt f10 gt lt esc gt lt esc gt lt esc gt lt enddef gt To have Q amp A run it on startup add the m switch to the command line Here s a sample batch file that starts Q amp A and runs the Alt 0 macro Continent field not only removes the blank line between the Africa and America groups but you ll also sacrifice any subcalculations based on a change in the continent See sample report 3 in Figure 1 Looking at the three report formats shown in Figure 1 sample report 1 shows a blank line when the Continent column breaks lt Action on column break has been set to Skip line Sample report 2 is the same report but with subtotals and a blank line between sorted groups If the Global Option Action on column break is set to Don t skip line the report will still look like this except that the blank line under the exampl
12. This free back issue of THE QUICK ANSWER is provided courtesy of S same The Monthly Newsletter for Sesame Database Manager http www insidesesame com Read a Free Issue of Inside Sesame AND L SOFTWARE LLC Makers of Sesame Database Manager Compatible with Symantec Q amp A http www lantica com Quick Answer F February 1996 uary 1996 The independent monthly guide to O amp A expertise All About Columnar Report Global Options Just when you thought you had Q amp A s Global Options logic down cold something new and mysterious begins affecting your report designs Turns out the culprit is another of those cursed options Your only salvation is knowing exactly how and when Q amp A applies them to your reports By Alec Mulvey OU can fall into a trap when using Q amp A s Columnar Report Global Options It s not that the options don t work The problem is that some of them work far beyond what you d expect to be their reasonable scope I ll cover each of these options in detail and show you what to watch out for Applying Report Global Options The following four selections appear on the Report Set Global Options menu for columnar reports Set Column Headings Widths Set Format Options Set Print Options Set Page Options The Q amp A User Manual acknowledges but doesn t explain or justify the fact that two of these selections affect not only new reports as you d expect b
13. e See sample z Ta subtotal rows will be P c reports 1 and 2 in Figure E ane suppressed 1 Placing CS in the Sample report 3 Continent 1 AS Continent 1 AS Continent 1 AS CS Country 2 Country 2 Country 2 Population 3 Population 3 ST Population 3 ST Continent Country Population Continent Country Population Continent Country Popul ation Africa Zaire 25 6 Africa Zaire 25 6 Africa Zaire 25 6 Africa South Africa 25 6 Africa South Africa 25 6 Africa South Africa 25 6 Africa Nigeria 64 7 Africa Nigeria 64 7 Africa Nigeria 64 7 Africa Egypt 38 1 Africa Egypt 38 1 Africa Egypt 35 1 Africa Ethiopia 25 6 Africa Ethiopia 25 6 Africa Ethiopia 25 6 Aaa America Mexico 52 a America Mexico 52 a Total 182 6 America U 5 a 215 3 America U S A 215 3 America Argent ina 25 7 America Argent ina 25 7 America Mexico 62 2 America Brazil 110 2 America Brazil 110 2 America U s d 215 3 Asia Burma 31 2 America Argent ina 25 7 Asia Vietnam 46 4 Asia Burma 31 2 America Brazil 110 2 Asia Vietnam a lala alaatatal Total 413 4 Asia Burua 31 2 Asia Vietnam 46 4 Figure 1 Three report formats depending on the Action on column break global setting and the CS code See text for explanation 4 The Quick Answer A February 1996 Marble Publications Inc includes no blank lines or subtotals Here the CS code in the sorted Continent field takes precedence suppressing skipped lines and subcalculations regardless of whether the global Action on colu
14. e shown ATTRIB R C QA4 QA1 EXE 2 Be sure to peruse and print for future reference the READ ME file that comes with O amp A 5 0 You ll find it in your Q amp A program files directory It highlights important issues and syntax errors that appear in the Application Programming Tools APT manual and the Getting Started guide bundled with the upgrade 3 The READ ME file contains much on the XPOST and XPOSTR posting commands Don t use the syntax for these commands as printed in the APT manual The correct syntax is in the READ ME file A possibly misleading statement appears in the APT manual on page 104 where it states The XPOSTR statement edits the record containing the nearest key word if such a record exists Actually XPOSTR first looks for a record containing an exact match on the key value If it fails to find one it then posts to the record with the next lower not the nearest key value For example if the key value is a number field and if XPOSTR is looking for a match on 1000 and doesn t find one it will post to a record with the next lowest key value In sucha case XPOSTR won t post to a record with 1001 in the key field even though 1001 would be the key value nearest to 1000 The Quick Answer A February 1996 record which might be the nearest but alphabetically higher key value e is higher in the alphabet than blank 4 Page 29 of the Application Programmi
15. end is finally in sight Just this last line of code F10 to save it and the dreaded red error message appears Not a valid Program Spec Press F1 for help You know as well as I do that pressing F1 is not going to help All the help screens in the world won t tell you that in line 72 at the 32nd character position you typed a colon where a semicolon ought to be I m happy to tell you that now real help is only a keystroke away Masterful programming by Quick Answer regular Jeff Nitka brings a new a utility that cites line and position number of errors in your Q amp A programs What s more his help screens show the kind of error it is and recommend solutions I call it masterful because he does all this ina Q amp A database Assuming you ve upgraded or soon will to version 5 0 I ll review Jeff s Q amp A Program Evaluator from that standpoint noting the exceptions for O amp A 4 0 users QA for Windows uses the 4 0 language so you ll need the Evaluator for Q amp A 4 0 if you re using the Windows version of Q amp A Vins probably been there It s late at night Installation and use Installing the Program Evaluator is as simple is copying the EVAL database to your default Q amp A data directory If you re using the Evaluator with Q amp A 5 0 two simple macros and a selection on the External Programs menu items puts all the Evaluator s power just a few keystrokes away Instructions are provided with the
16. es Marble Publications Inc and modifying then inserting them into another database is a snap For those using the Evaluator 4 version the process can be simplified by using Windows and multiple copies of Q amp A See the January 1994 The Quick Answer I could find no programming commands that stumped the Evaluator Multiple nested If Then statements indirection field names instead of program ID numbers Gosubs and you name it were all evaluated properly and any errors correctly reported You can fool the Evaluator but only by statements that are legal in Q amp A and would be unlikely errors in any case The Evaluator accepts programming statements of any length up to Q amp A s maximum 32K per field and all the new programming commands in Q amp A 5 0 are included in the Evaluator 5 version Do you need it If your programming is limited to statements such as gt 1 goto 2 then the Program Evaluator is probably not for you On the other hand the Q amp A 5 0 upgrade brings exciting new programming possibilities and more of us are likely to be writing lengthier and more powerful programs Thanks to Jeff Nitka finding that missing French brace at half past midnight is going to be much easier Joe Bryant a veteran Q amp A enthusiast and application developer resides in Moore Oklahoma 405 794 7892 fax 405 799 0499 OU Do It Enter this Quick Answer Contest and Win whatever The list of search values m
17. escriptions and paths to the ShellButton field Limit your descriptions to 19 characters that s the maximum you can display on a Userselect list Use the pipe 1 14 The Quick Answer A February 1996 lf You Work Without a Mouse A Shell button won t be practical if you don t use a pointing device with Q amp A In this case you can make it a regular field and use whatever technique you like to move the cursor to it when you want to launch a program If however you have a mouse connected to your PC but just don t use it with Q amp A these button fields alone might change your mind Why because it s easy to make such fields inaccessible except by clicking on them with a pointing device When you click on a field Q amp A moves the cursor to it even when the fields around it make it inaccessible from the keyboard See Streamline Tasks with Custom Windows like Gadgets in the January 1996 issue character to separate the descriptions from their paths and be sure your paths and filenames are correct If you re using the character in a DOS batch file choose another separator character one that doesn t appear in any of your descriptions or paths When typing your programs and paths add a comma as shown in Listing 1 to the end of each description path string except for the final string and surround each string with quote marks Don t forget the plus sign a
18. f you have long product names Or what if you want to include 3 4 or 5 pieces of data in the Info field In such cases 15 characters just aren t enough to work with One solution is to encode your data using a method that assigns a unique identifier to each unique record and use a text field to store a numerical sequence number For example you could encode Product 1 with 0001 Product 2 with 0002 and so forth This way you ll be able to encode up to 9999 unique records and lengthy product names won t prevent XLr and XLr from performing reliably If you want to get fancy here s an approach that can encode a large amount of data using just three characters I chose letters instead of numbers because there are 26 letters as opposed to 10 numbers and three letters can uniquely identify up to 17 576 26 x 26 x 26 different items Here s how you can make Q amp A automatically encode your items 1 Redesign a database adding a new text field named Code 2 Set Number to 0 press Ctrl F8 in Add Data mode type 0 then press Enter If you re already The Quick Answer A February 1995 11 using Number in the database you can note it s current value then reset it to that number once you ve finished this procedure 3 Mass Update the database In the Update Spec move to the Code field press F6 to expand it and type the following two statements 1 Code Str Number Code Chr Int Tn
19. g this program ming statement 1 Left Whole Name Instr Whole Name This has worked well for me but now I need to return the last word in a field in this case a last name Is there a similar statement I can use Michael Tamarkin York Pennsylvania The following statement will return the last word in a field Be aware though that for a Whole Name field it ll return suffixes such as Jr III and Phd 1 Mid Whole Name Instr Repllas Whole Name 4 1 50 The statement employs three Q amp A functions Mid Instr and Repllas Mid needs to know the starting position and the length of the string in characters to return Therefore Repllas is used to replace the last space in the field with a caret so 10 The Quick Answer A February 1996 that Instr can pass the caret s position to Mid which can then return the final 50 characters starting one character after the caret 05 1 Our law office creates numerous documents for each client and we like to store them in directories that correspond to clients ID numbers Often I need to save a document for a new client whose directory doesn t yet exist so I have to save it elsewhere create the directory then resave or copy the document to the new directory Can I make Q amp A create a new directory when I m adding a new client s database record Create Directories On The Fly Paul Embley Phoenix Arizona Yes Yo
20. global setting can be reversed CS for Cancel skipped lines but a negative one can t If you set Don t skip line globally you won t be able to get a blank line in any report in any database At least not one created in the normal way via a column break Moreover if you place a CS code in a sorted field at the Column Sort Spec you ll not only cancel skipped lines you ll also cancel subcalculations In The Quick Answer A February 1996 3 other words if you use the AS CS ascending sort cancel subcalculations codes in a field Q amp A will sort that column but won t generate subcalculations subtotals or subcounts in columns to the right of it even if those columns contain the appropriate calculation codes The purpose of the CS code is to sort by a field without that field producing unwanted subtotals For example if you set Action on column break to Skip line then place AS in the Continent field Q amp A will sort on that field and skip a line when the continent changes Any column to its right will show a subcalculation when a break occurs from Africa to America for Protect Your Format Options Because the Global Format Options are the trickiest of the bunch and affect all reports you should decide on which settings to use and stick to them If someone else might change the settings you can run an autostart macro to restore them Such a macro Alt 0 through Alt 9 would select
21. he appropriate sub list and place your final selection in the Color field You can simplify the program by appending the table s column numbers onto the initial list selections Change the three entries in column 1 so that each is followed by 19 spaces then the corresponding column number like this Red 19 spaces 2 Green 19 spaces 3 Blue 19 spaces 4 This way the initial list will still display Blue Green and Red the column numbers won t show and you can then use the following abbreviated statement to get the same result as the previous If Then statement lt Userselect Lookup Colors Num Userselect Lookup Colors 1 Color Cnext Tom Marcellus Marble Publications Inc Create a More Versatile Program Launcher You re in the middle of adding a new record when you have to switch to another program Wouldn t it be nice if you could simply click your mouse to switch to it then automatically return to the same record you were working on By Tom Marcellus F you spend a good part of your day in Q amp A but use other DOS programs as well chances are you could benefit from making Q amp A your control center You can do this in Q amp A 4 0 by adding your alternate DOS programs or batch files to the Q amp A Main menu via Utilities Set Alternate Programs This way you can launch an alternate program and automatically return to Q amp A when you exit the other program The drawback is that
22. ilities our clients can generate ASCII export files then electronically transmit them by modem to the bank credit card company a mainframe billing system even the IRS for processing Usually these outside systems generate a response file that the client then needs to process Here s a case in point A fitness club electronically bills membership fees to members credit cards on a monthly basis Of the 1000 plus credit card transactions the club transmits 30 on average are rejected because the card has expired or is over limit Here s part of a typical ASCII response file of rejected card numbers REJECTS ASC the credit card company transmits back to the fitness club NA N 4109740000013733 1111 8900 NA N 4114501229017365 1111 8900 NA N 4121340000802641 1111 5000 NA N 4121449998218163 1111 5000 NA N 4121741297937067 1111 8900 NA N 4128002600308758 1111 8900 NA N 4209707439227043 1111 6000 RC300 DECLINE RC301 DECLINE RC301 DECLINE RC300 DECLINE RC300 DECLINE RC300 DECLINE RC300 DECLINE Dw Marble Publications Inc Sure Fire Wholesale Retrieves ins When the club receives this file each of the 30 or so members has to be called to obtain revised credit card information The person making the calls used to display the pertinent members records by typing the rejected credit card numbers in the card number field at the Retrieve S
23. irst error then displays a help window describing the nature of the error along with a status window showing its line and position With a Complete Analysis a list of all errors and their locations appears The Evaluator then offers you the opportunity to press F6 to open the field editor and make the suggested changes Pressing F6 again often presents another help screen with more detailed information concerning proper syntax Once you ve made your changes you can then repeat the process as many times as necessary to correct any additional errors When your code has been corrected pressing Alt R returns you to the exact position in which you left your database with the now corrected code ready to be inserted Version 4 0 requires that you reopen the original database and navigate to the appropriate field Marble Publications Inc It really works Iran the Evaluator extensively intentionally making single and multiple errors in blocks of code then challenging the Evaluator to find them Overall it did an admirable job locating the errors and describing exactly what was wrong Sometimes the line and position indicator didn t point precisely to the error though it accurately pointed to the cause of it For example a missing opening parenthesis pointed to the command that required it which is what caused the error instead of the exact location of the missing parenthesis The Program Evaluator isn t without limitati
24. mn break is set to Skip line or don t skip line Therefore to have blank lines in your report you must have a sorted column your Global Format Options must be set to Skip line and there must not be a CS code in the sorted field at the Column Sort Spec Here are the rules e With Global Format Options set to Don t skip line you ll see no blank lines in any report in any database The Global Format Options Skip line Don t skip line setting has no affect on subcalculations e Adding a CS code to a field at the Column Sort Spec suppresses blank lines and subcalculations e If Global Format Options are set to Don t skip line then the CS code simply means Cancel subcalculations Action on blank value This Report Global Format Option setting seems innocuous it appears to have merely a cosmetic function Under certain circumstances though it can have serious ramifications If for example your report averages a series of fields your results can be affected by what you ve told Q amp A to do with blank values Rather than just Printing Zeros Q amp A also performs calculations with them This caught me badly a few years back with a series of reports that analyzed academic results The reports contained a number of calculations based on averages Everything was fine until suddenly one day the averages were wrong What I had done was change the Action on blank value setting for reports in a different da
25. n Off White Orange Next add a text field named Color to the same database and program it as follows You can place the program in the Program or Navigation Spec lt Userselect Lookup Colors 1 Color Cnext Save the Spec switch to Add Data and move to the Color field Q amp A will display your list of colors in alphabetical order and place your selection in the Color field To change the items on the list change them in the Lookup Table no reprogramming is required You can extend the technique to create sub lists that conditionally appear depending on the selection you make from the initial list To see how this works leave the Lookup Table s Key column as it is but add the following items to columns 1 2 3 and 4 12 The Quick Answer A February 1996 0Ss0 Column 1 Red Green Blue Column 2 Light red Dark red Medium red Fire engine red Hot pink Column 3 Light green Dark green Medium green Forest green Pea green Sea green Column 4 Light blue Dark blue Medium blue Sky blue Sea blue Save the table and replace the previous program with this one lt Userselect Lookup Colors 1 Color If Color Red Then Userselect Lookup Colors 2 Color Else if Color Green Then Userselect Lookup Colors 3 Color Else if Color Blue Then Userselect Lookup Colors 4 Color Cnext Now when you select the primary color from the initial pick list Q amp A will display t
26. ng Tools manual lists abbreviations for Q amp A functions and commands and contains an error not addressed in the READ ME file The correct abbreviation for USERSELECT is USL not US However the correct abbreviation for USERSELECT is US as shown 5 Pages 28 and 70 of the APT manual discuss the new MACRO command The statement on page 70 If a macro is named the macro name must be used can be misleading because you must always use the macro name in the MACRO command Here s why When you press Shift F2 to begin recording a macro Q amp A prompts you to enter the keystroke combination key identifier or hot key to assign to it Suppose you press the key combination Alt Z then record your macro When you re finished recording and press Shift F2 again Q amp A gives you the opportunity to assign a descriptive name to the macro If you do suppose you name it Add a Record that s the name you must use in the MACRO command like this MACRO Add a Record In contrast if you don t assign a descriptive name to the macro when saving it you leave it as Alt Z Q amp A names the macro lt altz gt which you must then use in the MACRO command but only in the form MACRO lt altz gt You must include the angle brackets or MACRO won t work Q amp A 5 0 Bugs By Jeff Nitka 1 When defining custom help screens Q amp A 5 0 drops any help screen for the last field in the Marble Publicatio
27. ns Inc database Help screens for all other fields appear to be properly retained 2 When designing or redesigning a database any Global Format Options date format containing a slash separator won t display properly O amp A will display them in the European style of DD MM YY For example if you choose date format 7 03 19 68 Q amp A 5 0 will display 19 03 68 All other date formats are okay The closest alternative is the date format with the dash separator 3 With databases created in the Q amp A 5 0 beta version I experienced a problem when running saved reports in the production release When I ran a report with a macro then killed the macro by pressing Esc Q amp A 5 0 placed garbage values in the last fields of the Retrieve Specs for every saved report I remedied this by removing the garbage data and resaving the reports 4 In the final beta version of O amp A 5 0 the Format Spec rejects the traditional JC Justify Center code though it accepts a JM Justify Middle code This has been corrected in the production version Bill Halpern is president of Professional Computer Technology Associates 100 Jericho Valley Drive Newtown PA 18940 215 598 8440 CompuServe 71023 356 Jeff Nitka develops Q amp A applications part time for Epoch Software 908 874 3989 We have a number of clients who use O amp A to automate sales and other transactions Because of O amp A s flexible import and export fac
28. o keep warm in not so sunny Florida when news arrived that Q amp A 5 0 was finally shipping Now I can cop my plea Over the past few months we ve run a number of 5 0 specific articles and tips I reviewed Q amp A 5 0 in August Some readers anxiously awaiting the upgrade feared we d flipped out and decided to service the beta crowd The truth is we thought 5 0 would ship much sooner and well we got kind of worked up about the prospects We apologize to anyone put off by the premature inclusion of 5 0 related material but think of it this way you won t have to wait months now for the essential 5 0 tips and techniques Right on time 5 0 beta testers Bill Halpern and Jeff Nitka report on documentation and usage issues and a few bugs in the initial release If you ve been confused over how and why your O amp A 4 0 columnar reports are affected by global options settings don t expect 5 0 to clear the air The logic behind these options if you want to call it that hasn t changed one whit But there s new hope Alec Mulvey tells you everything you ve ever wanted to know about report global options but weren t sure how to ask Though Q amp A informs you when you ve made a programming goof it doesn t tell you where it is what makes it an error or how to correct it If you could use this helpful information when toiling away in the Program Spec Joe Bryant reviews an indispensable programming utility that works with Q
29. o run ShellButton Userselect ShellButton If ShellButton Then Msg Shell operation abandoned ShellButton Shell Chome If ShellButton lt gt Then If Askuser Run the program Left ShellButton Instr ShellButton 1 Mid ShellButton Instr ShellButton 1 40 Then ShellButton Shell1 Mid ShellButton Instr ShellButton 1 40 ShellButton Shell Chome Else Msg Shell operation abandoned ShellButton Shell Chome That s all there is to it An hour or so of work and you ve got a convenient tailor made menu to launch your external DOS programs You won t have to press keys to shell out just click your mouse And because your menu is a list there s no limit to the number of alternate programs or DOS batch files you can include on it Once you ve added the ShellButton and its program to one database it s a snap to add it to others Simply follow the six steps listed earlier to add the button to a form Then after using the Clipboard to copy the program edit the list of items you want to appear on the menu for that database Tom Marcellus is editor of The Quick Answer and author of PC World Q amp A Bible published by 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 PO Box 9034 Gaithersburg MD 208
30. ons It doesn t verify field names against the actual field names in the database If your program contains ZipCode the Evaluator will accept it even though the field name is really Zip_Code an error Q amp A will catch Hard coded dates and times of day for example a statement such as 10 10 Sep 96 are accepted only as text values 10 10 Sep 96 or as part of a function 10 D 10 Sep 96 even though QA will accept them all three ways Because the Program Evaluator is a standalone Q amp A database you can insert and evaluate programming code from another database or use the Evaluator to write and save code you ll eventually port to another database This is especially helpful if you re programming lengthy statements but still need access to your Q amp A databases for other work Pulling programming statements from one database How Would Q amp A user Joe Dokes has an application requirement that can be met using 5 0 s tools The question is how would you tackle it We ll publish the best solution and send the winner a 100 prize Here s the requirement The starting point is the Q amp A Main menu With one keystroke or mouse click Joe wants a list of search values from his favorite database to appear so he can conveniently select the record to view Entries will be judged on their adaptability and efficiency You can illustrate your technique using any database employees companies products recip
31. ookupr XLr function to use in ways that might not be obvious You re probably familiar with using XLr to increment records like this lt If Entry then Entry and Add X1lr Fn 999999 Entry Entry 1 Here XLr scans the current database for the record with the highest number in the Entry field then returns that number plus 1 to the same field in the current record You might also know how to use XLr to maintain a running balance in a checkbook database like this to find the last record and bring the balance forward into the current record lt Current Balance XLr Fn 999999 Entry New Balance These are useful applications for XLookupr but they make me yawn XLr and XLr become exciting when I use a text value for the second argument Because text comparisons work differently from numeric date or money comparisons special care has to be taken But the results are worth it When you have XLr or XLr compare key text values those values should be fixed in length to ensure accurate record retrievals Consider the following statement Price XLr File ABC123 Product Code Price If the values in the external key Product Code field aren t all the same length Q amp A might not return the record you want Take for instance ABC1225 and ABC121 Which one looks smaller and which one will the sample XLr statement retrieve Multiple search capabilities A nifty and p
32. pec This meant lots of keystrokes and of course mistakes Q amp A 5 0 s Retrieve Spec field editor changed all that Now the caller simply presses F6 to expand the credit card number field presses F8 selects Document Insert inserts the REJECTS ASC file then presses F10 to display the pertinent records Because REJECTS ASC is a semicolon delimited file it s ideal for retrieving multiple records What s more only the credit card numbers will retrieve records The other values such as R NA and DECLINE don t match any credit card numbers and therefore don t affect the search There s a limit on the number of entries you can insert in a Retrieve Spec this way available memory plays a part in it But if the file is too big you can always pull it up in Write and break it into smaller chunks And if it happens to be comma delimited you can always use Search and Replace to change the commas to semicolons Gordon Megis Professional Computer Technology Associates The Quick Answer A February 1996 7 Review The Q amp A Program that Catches Programming Errors Q amp A will snag your programming goofs but it won t tell you where they are or what makes them errors or advise how to correct them Help is here and even if you only occasionally work in the Program Spec here s one tool you ll be thankful you have on hand Reviewed by Joe Bryant You ve been programming for hours and the
33. propriate tasks are available from each database s external programs menu This approach is illustrated by the final line in the first block of code shown in Listing 1 Calendar C QA QA COM alCALENDAR ASC m3 This selection launches another copy of Q amp A invoking the CALENDAR ASC macro file s Alt 3 macro which opens a calendar database Any macro Alt 0 through Alt 9 can be made a start up macro If you need more start up macros you can create additional macro files each with its own set of Alt number macros When you exit the second copy of Q amp A the macro file in memory before you switched to it is automatically reloaded The Quick Answer A February 1996 15 abandoned message reassigns the Shell label to the field and moves the cursor to the home field You can of course adapt the programming to your needs Listing 1 The ShellButton program displays your custom list of external DOS program descriptions and passes the path and filename of your selection to Shell lt ShellButton Norton Utilities D NORTON NU EXE HotShot Graphics C HOT HSG EXE XTree Disk Utility D XTREE XTREE EXE WordPerfect 5 1 C WP51 WP EXE m alts wpm Communications C COMIT COMIT EXE Dictionary C DICTIONA AHD3MEM EXE CompuServe C CSERVE DOSCIM CIM EXE MS DOS Editor C DOS EDIT COM Calendar C QA QA COM alCALENDAR ASC m3 Msg Select the external program t
34. ractical application of this idea is to use a field that stores two or more pieces of information This way you can use XLr and XLr to retrieve information based on multiple search criteria To illustrate suppose an Info field stores a text concatenation of a product name and sales amount In this case XLr or XLr can be made to find the largest individual purchase of a particular item Because the Marble Publications Inc XLookupr and Data Encoding Tricks text values in Info must be the same length Info s program might look something like this 100 Info Product Text 8 Len Product Text 5 Len Int Sales O Str Int Sales Text 2 Len Mod Sales 100 100 0 Str Mod Sales 100 100 The program generates a text value that s always 15 characters in length regardless of the name of the product provided it s less than nine characters or the sales amount as long as it s less than 100 000 If an external file stores invoice data for several products and contains such an Info field then the following XLr statement will return the name of the customer who placed the largest order for product ABC123 CustName XLr INVOICE ABC123 9999999 Info Name Encode your data I ve limited the text string to 15 characters because accurate text comparisons with Q amp A lookup functions are good only through that point But what i
35. s case an Askuser box appears prompting you to confirm that you want to run the selected external program If you find the Askuser confirmation prompt intrusive you can leave it out of the program This Marble Publications Inc way once you ve selected your program from the list O amp A will launch it without checking with you first The Askuser box shows the selected program s description as well as its full path See Figure 2 To do this Askuser s code separates the program description from its path at the character and displays them on separate lines prefaced by the statement Run the Program If you answer Yes to the Askuser prompt the fifth block of code executes passing the path and filename everything following the character to the Shell command which launches the program In this example the paths are limited to C 0 N T A C T S FirstName Gina Contact ID NOVE8S1 LastName Novelle t User Query Retrieved form 1 Total Forms 96 Page 1 of 1 Esc Cance l Continue Figure 2 When you choose a program to run an Askuser box confirms the program and path and prompts for your final okay 40 characters which should be more than enough When you return from the external program to the same record the Shell label is reassigned to the ShellButton field the cursor is moved to the home field and you can continue See the sidebar
36. t the end of all the lines except the last You can type your program descriptions in any order when the time comes to build the Userselect list Q amp A will organize them alphabetically You can add start up parameters and switches recognized by your external programs In Listing 1 WordPerfect is started with an Alt S macro Similarly a second copy of Q amp A the Calendar selection is started with an autoload al macro file and start up macro in this case a macro file named CALENDAR ASC and its Alt 3 m3 macro During data entry once the Listing 1 program has copied your descriptions and paths to the ShellButton field it creates and displays the menu courtesy of the ShellButton Userselect ShellButton statement along with a message prompting you to make a selection Because the character is at the 20th character position it and the paths that follow it don t appear on the list just your program descriptions The third block of code executes if you display the list then press Esc or click outside of it you decide not to launch a program In this case the ShellButton field is blanked a Shell operation abandoned message appears the Shell label is reassigned to the ShellButton field and the cursor is moved to the home field the first field on the form The fourth block of code executes when the ShellButton field isn t blank that is when you ve selected a program from the pop up menu In thi
37. tabase not realizing that doing so would affect existing reports in every other database Following on the left are the report results I got with Action on blank value set to Leave blank On the right is the same report but after the Action on blank value global option had been changed to Print 0 Notice that Q amp A divided the total by three entries producing a different average Marble Publications Inc Charles 9 Charles 9 Bill Bill 0 Diana 9 Diana 9 Total 18 Total 18 Average 9 Average 6 Because this situation exists in QA 5 0 as well as QA 4 0 you might find Table 1 helpful in steering your way through the rough waters of the Report Global Options Table 1 How Q amp A applies Columnar Report Global Options Global Option Reports Databases Changes Menu Selection Affected Applied to Saved in Column Head All including Only the one DTF file ings Widths existing reports specified Format Options All including All QA CFG file existing reports Print Options Only new reports All QA CFG file Page Options Only new reports All QA CFG file Alec Mulvey owns Keyword Consultancy and Training a Symantec Approved Training Center in Ascot near London England He has been working in Q amp A for seven years and is also a Microsoft Certified Professional and Trainer Fax 44 1344 884 962 100327 44 compuserve com Update your Personal Dictionary DOS If you ve upgraded to Q amp A 5 0 and are wondering
38. u can use O amp A for DOS 5 0 s Shell command to create a new directory on the fly Here s a sample program to use as a guideline It assumes a field named Client ID gt If Add and Client ID lt gt Then 10 Shell MD C CLIENTS Client ID You can use Shell to invoke DOS commands as well as execute batch files and launch DOS programs In this example Shell invokes the DOS Make Directory MD command naming the directory after the new Client s ID Field 10 can be anywhere on the form Q amp A places a zero in it when Shell has performed successfully Dave Reid is a Symantec senior support analyst providing second level assistance to the technical support representatives He s also the coauthor of The Q amp A 4 0 Wiley Command Reference published by John Wiley and Sons and works as an independent Q amp A consultant PO Box 12083 Eugene OR 97440 Have a nagging question Send it to Help The Quick Answer Marble Publications Inc PO Box 9034 Gaithersburg MD 20898 9034 or fax to 301 424 1658 Please include your name address and phone number along with your Q amp A version number and whether DOS or Windows and a detailed description of the problem We will publish those questions we feel are of general reader interest Individual responses are not possible Marble Publications Inc The Program Spec By Jeff Nitka often put the XLookupr XLr range statement and its sibling XL
39. ues 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 for the same price as a single copy Second class postage paid at Rockville MD POSTMASTER Send address changes to The Quick Answer PO Box 9034 Gaithersburg MD 20898 9034 Copyright 1996 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 Prodigy NEPY97A 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 respective 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
40. ust include all the records in a database containing at least 15 records You must fully describe your technique including any global settings macros programming and database design requirements Entries must be submitted on disk in Q amp A Write format accompanied by a printed copy and be received by March 1 1996 Mail your entry to Marble Publications The Quick Answer Contest PO Box 9034 Gaithersburg MD 20898 9034 The Quick Answer A February 1996 9 Edited by Dave Reid WYSIWYG Word Processing 105 I thought the word processor in O amp A for DOS 4 0 and above was supposed to be WYSIWYG what you see is what you get No matter which font I use the screen displays monospace text even though the printed document is correct How can I see my fonts on screen Nalani Poffenbarger San Diego California Q amp A for DOS s word processor runs in text mode not WYSIWYG mode like a Windows word processor However it still breaks the lines of text correctly because it calculates the widths of the characters in the font and uses them to determine how many letters will fit on a line This pseudo WYSIWYG capability can be particularly helpful with large fonts You can always use Page Preview a selection on the Print Options screen to get a realistic representation of your fonts and page Return the Last Word in a Field 00s WIN In the March 1994 The Quick Answer I found out how to return the first word in a field usin
41. ut a Mouse 1 Decide where to place the button You ll want to prevent the cursor from entering it unless you click on it or otherwise deliberately move to it One option is to place it at the bottom of the form as shown in Figure 1 2 When adding the button field in Redesign use the lt and gt characters to define it Make it labelless and seven characters wide so you ll wind up witha space on either side of the Shell label which you ll add later 3 At the Format Spec center justify the field using the TJC code 4 At the Field Names Spec assign the name ShellButton to the field This way you won t confuse it with actual data fields 5 At the Palette Spec set the field s background color to contrast with the form s background color so the button stands out then set a contrasting text color 6 Add the label to the button by setting the field s Initial Value to Shell For existing records run a Mass Update with an Update statement 1 Shell in the ShellButton field You can optionally make the field read only This won t prevent its program from executing when you click on it but it will make the Shell label uneditable from the keyboard Programming ShellButton Listing 1 shows the on field entry program for the ShellButton field which you click on to execute The program begins by temporarily assigning your external program or batch file d
42. ut also existing reports These are the Columns Headings C 0 N T Columnar Report Global Options Alec Mulvey 1 README 1ST Tom Marcellus cccccccccccccccceeeeeeeees 2 QuickTip Update Your Dictionary Jerry Altshuler 5 Early Reports on Q amp A 5 0 Meigs and Nitka 6 QuickTip Sure Fire Retrieves Gordon Meigs 7 O05 Widths and Format Options selections And as if this weren t trouble enough three of the options affect not only the database for which you specify them remember you must select a database to access these global options but all other databases as well And to add insult to injury one of the menu selections Set Format Options affects existing reports in other databases I ll cover these global Format Options last as they can be particularly tricky First let s look at the other three Global Column Headings Widths When using the Column Headings Widths options try to avoid assigning a lengthier column heading or greater width to a field in a way that might affect the total width of existing reports and force some Continues on page 3 E N T S Review Catch Programming Errors Joe Bryant 8 Help Dave Reid m e ee 10 Program Spec XLookupr and Encoding Jeff Nitka 11 QuickTip Quick Edit Pick Lists Tom Marcellus 12 Create a Launcher Tom Marcellus 0 0cccccccccceeeereee 13 README 1ST VER the Christmas holidays I was trying t
43. you re limited to six alternate programs and you can launch them only from the Main menu Enter Q amp A 5 0 Q amp A 5 0 s new External Programs menu gives you more selections and greater flexibility You can install up to nine programs or batch files on it press Alt F9 from anywhere in Q amp A to launch one then be returned to the same screen when you quit the other program However you re limited to the one menu you mos The technique Figure 1 shows a contacts database with just such a custom external programs menu displayed Simply clicking on the Shell button in the lower right hand corner of the screen displays your custom external programs menu containing descriptions for all the programs you might want to run With the technique I ll show you you can add a Shell button like this to any database then add the programming that displays your custom external programs list on demand Although the Shell command requires the full path and filename of the EXE COM or BAT file to run your program won t include these on the menu but will pass them to Shell automatically Creating the button Follow these steps to add a Shell button it s nothing more than a special kind of field to your can t create an External Programs menu for one database have a second menu for another database and so LastName Novelle forth Address1 4180 S Sandhill Company Compuj Communications To add this kind
Download Pdf Manuals
Related Search