Archive-name: databases/approach-mailing-list-faq
Posting-Frequency: monthly
Last-modified: 1996/09/11


              APPROACH USERS MAILING LIST FAQ
              -------------------------------

                     11 September, 1996


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

Subject: 1. Welcome

This FAQ contains a summary of answers to Frequently Asked
Questions on the Approach Users Mailing List, as well as other
material that may be helpful to a wide range of Approach users.

At this stage the content relates to Approach v2.01 and 3.0*
unless specified as Approach 96.

Not all of the mailing list discussion makes it into the FAQ
because some of it will probably be of use only to the person
who asked the question, and would therefore simply be wasting
space in a FAQ. However that information is not lost. I maintain
a database of all the posts to the mailing list, so if others
ask similar questions at a later date I will answer with the
information I have on record, and add it to the FAQ - if the
question does in fact become 'frequently asked'.


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

Subject: 2. Table of Contents


Contents
--------

     1. Welcome
     2. Table of Contents
     3. Notices
         \ How to get the latest edition of the FAQ
     NEW \ How to automatically receive the AUML newsletter
             containing new and updated FAQ articles
         \ How to get the Archive of Scripts
         \ Contributions, comments, corrections, or feedback
     4. Our collective wisdom so far...
         \ The latest version
         \ How does Approach compare to other DB's?
         \ Lotus Web Site and technotes
         \ Books
 UPDATED \ Other information sources about Approach
         \ Getting started with joined databases!
         \ Help me design my database! (joins, repeating
               panels, what data in which database etc...)
         \ Designing and problem solving techniques
         \ Stand-alone databases using Approach?
 UPDATED \ Can Approach handle big databases?
         \ Optimizing the speed of a database
 UPDATED \ Running Approach on a network
     NEW \ "Could not load infobox dynalink" error
 UPDATED \ dBase IV limitations
         \ "Page Fault... at 0137:0049eb64" in Approach 96
         \ Problems with repeating panels
         \ Leading zeros
         \ Trailing zeros
         \ Working with Paradox database files
         \ Free Approach 3.02 Upgrade
         \ Calculating ages from birth dates
         \ Calculating number of weekdays
         \ 21st Century Dates
         \ Dates before 1929
         \ Saving records in their sort order
         \ Record numbering
         \ Header / Footer problems in v2.1
         \ Automatically opening files
         \ Automatically opening a particular form
         \ Automatic macro on opening/closing View file
         \ Deleting the body in summary reports
         \ Preserving found set when switching forms
         \ Keeping users out of design environment
         \ Stopping people accidentally entering data
         \ Searches on multipage forms
         \ MS-Windows 95 printing blank pages (v2 & v3)
         \ Radio Buttons field label
         \ Finding the previous weeks records
         \ Finding records which do not contain *text*
         \ Finds on repeating panels fields
         \ Making empty fields disappear
         \ Running a Macro in a different .apr files
         \ Converting numbers to words 34 to thirty-four?
         \ Making keyboard entries into a formula or macro
         \ Sliding fields together on mailing labels
     5. How the Approach Users Mailing List works
         \ Changing Your Subscription Address
         \ Question and Answer Posts
         \ NO, IT IS NOT OK TO ATTACH FILES
         \ comp.databases
         \ Lotus
         \ Mail errors after posting to the list
         \ Mail storms
     6. Acknowledgments
         \ copyright notice and editor


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

Subject: 3. Notices


How to get the latest edition of the FAQ
----------------------------------------

Send an email to MAISER@rmgate.pop.indiana.edu with the words
SEND AUML-FAQ.DOC in the body of the message.

This FAQ is approved by the *.answers moderators and is
therefore also available at from the following sources:

Usenet: comp.databases, comp.answers, news.answers=7F

ftp://rtfm.mit.edu/pub/usenet/comp.databases/Approach_Users_Maili
ng_List_FAQ

http://src.doc.ic.ac.uk/usenet/usenet-by-hierarchy/comp/databases
/Approach_Users_Mailing_List_FAQ

Hypertext versions: (Note: These are converted from text to
hypertext by the web sites NOT by me. Both of these services
automatically convert URL references into links, but with
varying degrees of success. The ohio-state document is also
broken up into different pages for each contents item - very
handy.)

http://www.cis.ohio-state.edu/hypertext/faq/usenet/databases/appr
oach-mailing-list-faq/faq.html

How to automatically receive the AUML newsletter containing new
and updated FAQ articles
-----------------------------------------------------------

If you are a subscriber to the Approach Users Mailing List then
do nothing! All the newsletters are posted to the list so soon
as they are produced.

If you are NOT a subscriber to the Approach Users Mailing List,
then send the words SUBSCRIBE AUMLNEWS on the first line of an
email to MAISER@rmgate.pop.indiana.edu

How to get the Archive of Scripts
---------------------------------

Send an email to MAISER@rmgate.pop.indiana.edu with the words
SEND SCRIPTS.DOC in the body of the message.

The archive contains LotusScripts that can be implimented on
Approach v96.

Contributions, comments, corrections, or feedback
-------------------------------------------------

I would appreciate any contributions, comments, corrections, or
feedback that you want to make about the mailing list, the
newsletter, or the FAQ. Post these to the mailing list the same
as any other post or, if you really have to, you can email them
to me at jabrown@unix.infoserve.net.


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

Subject: 4. Our collective wisdom so far...

The latest version
------------------

Approach 96 for Windows 95 is the latest version of Approach. It
was released in December 1995 and contains significant new
features including LotusScript 3.0 - a programming language that
provides a development environment for robust, cross- product
applications. To find out more about it see the following Lotus
Web Page:

http://www.lotus.com/ssuite96/approach.htm

How does Approach compare to other DB's?
----------------------------------------

Articles about how Approach ranks against other databases, as
well as handy hints, and how to optimize your productivity with
Approach and SmartSuite, are available by searching back issues
of PC World:

http://www.pcworld.com

Lotus Web Site and technotes
-----------------------------

Heaps of information including FAQs and hundreds of 'technotes'
on every Lotus product, including all versions of Approach.

http://www.lotus.com

For all the Lotus FAQ's including a number for approach:

http://www.lotus.com/csswww/faqwin.htm

Technotes are Lotus's answer about a whole range of problems
people encounter using Lotus products and answers to frequently
asked questions. They are also available via ftp.

ftp://192.216.79.100/pub/desktop/Approach/technotes/text/00_index

Books
-----

The following books have been recommended in posts to the
Approach Users Mailing List:

   * The manual that come with Approach. Always a good place to
start!

   * Mastering Lotus Approach by James Powell (Sybex / Lotus
Books) (This is the most highly recommended book)

   * Mastering Lotus Approach 96 for Windows 95 by James Powell
(Sybex / Lotus Books) (This is the most highly recommended book,
however, some subscribers have commented that IF YOU ALREADY OWN
THE EARLIER VERSION (for v3.0*, see above) then check it out
carefully before you by this one. They expressesd strong
disappointment that it has only been minimally updated from the
earlier version, and major aspects of Approach 95 are not
covered adequately. It therefore may not be worth the expense.)

   * Building Databases with Approach 3 by Elaine Marmel
("...seems to be an extended, well documented, tutorial on
building a database that is usable in a business environment.")

   * Introduction to Databases" by James T. Townsend  (recommend
for business uses who are learning and        designing
databases. It is about databases in general rather than how to
use Approach.)=7F

   * Approach 3.0 for Dummies

   * Practical Approach version 3.0 by Siegel (MIS Press)

   * Using Lotus Approach 3 by Plotkin (Que)

Other information sources about Approach
----------------------------------------

   * A good way to learn about database structure and what
Approach is capable of is to study the example databases that
come with Approach.

   * Lotus fax - back service (free) (+1) 800-346-3508
           (These are also available from the Lotus Web Page
            as Technotes)

   * PC World articles and tips from current and past issues:
(Site search available)

          http://www.pcworld.com

   * The Approach BBS: (+1) 415-335-2362

   * Do It With Lotus SmartSuite (newsletter)

          http://www.idgnews.com

   * The Approach forum on CompuServe

   * Lotus Forums on Compuserve such as LotusA or LotusB

   * Lotus Business Partners Program (This is for people who
develop desktop applications, including Approach, for others for
a living.  See http://www.lotus.com/bpartbro/bpbroch.htm for
details: "You'll receive a complete collection of Lotus
software, including Notes, Notes Mail, cc:Mail, SmartSuite, and
Lotus Components _ as well as Business Multimedia Products.
These PartnerGold CDs include all the software you need to run
your business, as well as upgrades for as long as you're in the
program. You'll also receive the Lotus Toolkit Collection, a
quarterly CD with all the latest developer tools you need to
start building applications. Plus you get the Inside Edge CD, a
collection of Notes and NotesSuite applications for internal use
only. And Knowledge Base is your source for technical support
information _ online, via the World Wide Web, or on CD. You get
all this plus initial technical support and access to the
Partner Forum, a worldwide discussion database monitored by
technical analysts from Lotus Support." The Business Partners
homepage is http://www.lotus.com/partners/ )

Getting started with joined databases!
--------------------------------------

Understanding relational (or "Joined") databases is a big area,
but is vital to fully utilizing a program such as Approach. It
can be a frustrating time with many new concepts to learn, as
well as all the ins and outs of Approach.

DO NOT START by launching into some major project that you
expect to be fully functional tomorrow. Start small, using some
test data, and test each function before you go onto the next.
Read all you can, and spend time carefully planning what
databases you need and how they should be joined. Once you have
the basic structure of your database set up you can begin
entering or importing you actual data.

But as I said, start by do some reading. Some books are
recommended earlier in the FAQ, but don't forget the Approach
manual either. It briefly takes you though the process of
setting up joined databases. Looking at the example databases
that come with Approach will also help you understand how the
different data in different databases is linked together to
provide many facilities that are not possible in flat databases.

Help me design my database!=7F
---------------------------
(joins, repeating panels, what data in which database etc...)

Apart from simple 'card file' type databases, every piece of
major database software you can think works with relational
databases; Approach, Paradox, Access etc... In order to use any
of these packages efficiently, including Approach, you first
need to understand what a relational database is, and how you
would divide your data into one. If you don't understand this
then you are going to have difficulty using many features of
Approach, or any other database software.

Questions concerning database design are quite common on the
Approach Users Mailing List. However, strictly speaking most of
these questions and answers have little or nothing to do with
Approach. What the people asking these question lack not so much
an understanding of how Approach works, but rather an
understanding of what a relational database is and how they work.

And as it turns out, I am not going to attempt to explain this!
Instead, what I suggest you do is this: Allocate some time and
perhaps money, to do some reading or perhaps a short course in
relational databases. Some books are suggested above. The
Approach manual includes some information, but most people seem
to need a more in-depth look at it before they really grasp what
is happening. The better this ground work the easier and quicker
you will be able to build an efficient database. I won't take
all that long, and the benefits are huge.

Also have a careful took at the example databases supplied with
Approach. Go into design and check out the structure of the
database; the design of the various views; and how each aspect
works.

As you are doing these things keep some notes about how you
might structure your database. When you are ready begin
establishing your database one bit at a time with a small amount
of test data. When it is fully functioning, or at least
developed enough to do all the major functions, import your
data, and begin using it!

Designing and problem solving techniques
----------------------------------------

Try this: Rather than working with you main database, try
setting up and testing out new things or trying to solve
problems on smaller practice databases. This way you minimize
the possibility of you loosing valuable data, or disabling your
main database when you really need to use it. For example, if
your having trouble with an import or export, set up a very
simple flat (i.e. no joins) database and view form. Enter some
junk data into a couple of records and try exporting it as a
text file. If this works, try importing it back in again. When
you can do this, expand this test database so that it has two
databases that are joined, and the data from both database is=7F
displayed on the one form. Then export some data. Add a
repeating panel. Export some more. Import some back in. At every
stage do only one thing at a time and test it. This way you will
easily identify at which point you are getting into trouble.

Once you have managed all of this on your test database you
should be able to solve the problems on you main database.

Stand-alone databases using Approach?
-------------------------------------

No, there is no way of creating fully stand alone' database
using Approach. There are currently no run time compilers for
Approach that I am aware of, but it is rumored that one is under
development. Approach is designed to be a 'Front End'. The idea
is you can take an existing database (dBase, Access, Excel,
Foxpro, Oracle, Paradox, SQL, 123 ...) and create your own Front
End, leaving the original database intact for others. Or you can
develop your own database from scratch. You can also develop a
View files with Macros that give it the appearance of a
full-blown application and distribute to other users, but
everyone who uses it needs their own copy of Approach.
Purchasing site licenses and using Lotus' concurrent licensing
scheme will greatly reduce the costs for businesses.

Using the Customizing Approach chapter in the user's manual you
can be completely customized so that your users can't trash the
database that you build for them. Pay particular attention to
the Setting Preferences, Defining Passwords for Files,
Customizing SmartIcons, and Customizing Menus sections. Also
review procedures for defining basic field settings in the
various views you give the user. You can design your database
using custom pull-down menus and various buttons on forms so
that all the native controls in Approach can be turned off or
hidden from the user.

Approach 96 includes 'LotusScript' which gives you much the same
capabilities of Access Basic. But all is not lost for v2.* and
v3.**! You may be able to write the routines you need in some
other language, such as Visual Basic. These routines could be
activated by a Windows macro (i.e. Recorder).

Can Approach handle big databases?
----------------------------------

Well, it probably depends on what you call big, the design of
your database, and what hardware you have. A number of
subscribers have reported having in excess of 40,000, and as
high as 90,000 records without any difficulties. Nobody has ever
reported reaching a maximum number of records limit in Approach.
In theory it should be able to handle the maximum number of
records you can put in a dbase IV database, which is 4294967296
(4 gig =3D maximum 32 bit number).

In order to maintain the best ratio of speed and stability
delete the .adx files (indexes) on a regular basis (say every
week or two). It may be a good idea to do this after doing a
full back up, and then rebuilding the indexes by opening the
databases one at a time.

It has been suggested that the number of joins has a far greater
impact on the speed and stability of large databases rather than
simply the number of records. One subscriber stated that they
found a database having "80,000 records and approximately 20
tables joined" to be unusably slow, and suggested that a
database of "100,000 records if there are more than 4 or 5
joins" to be a maximum usable size.

Optimizing the speed of a database
----------------------------------

Having the latest version of Approach on a suitably fast system
with plenty of RAM (8 meg or more), fast disk access, and fast
disk caching will of course increase the performance of any
large database, but you may also what to carefully consider your
database design. A badly designed database will perform poorly
in any application... For instance, the more joins you have, and
the more joined database shown on one form, the slower your
database will tend to be, and databases work slower with 'full
record locking' than with 'optimistic record locking'.
Compressing the Databases on a regular basis will also help. If
you are using Approach on a LAN or WAN, also see 'Running
Approach on a network'

Running Approach on a network
-----------------------------

There is no special network version of Approach. When you are
doing the installation you will be asked if it it is to go onto
a desktop or a server. Check the README.WRI that comes with
Approach and the Manual for future information.

However, as with all software (executables) you will notice a
drop in performance running Approach from the LAN server
compared to the running it on a desktop. The general consensus
therefore seems to be that you should run Approach on each
desktop individually, but just store the data and the .vew/.apr
files on the server. With this configuration the speed of your
desktops is more critical than the speed of your server. So if
you have a choice, use an older slower computer as the server,
and your fastest computers as workstations. This configuration
also means you won't be loading your network up with large
executables.

However, unless you have software distribution software, the
"software" person to go nuts if there are 100 workstations to
upgrade at some stage in the future... I guess you'll just have
to way up the pros and cons.

"Could not load infobox dynalink" error
---------------------------------------

This may be caused by not having done a network install of
Approach on the desktop you are using to link it to the Approach
executable on the network. To fix this, reinstall
Approach on the desktop, making sure that you do a network
install. (also see 'Running Approach on a network')

dBase IV limitations
--------------------

Approach can use a number of different database base structures,
but uses dBase IV by default. dBase IV has the following
limitations that you might need to be aware of:

   * maximum number of fields =3D 256
   * maximum size of a record =3D 4000 characters
        =3D all the fields added together
         Memo fields take 10 characters
   * maximum length of a text file =3D 256 characters
   * maximum number of records =3D 4294967296 (4 gig)

"Page Fault... at 0137:0049eb64" in Approach 96
-----------------------------------------------

To avoid getting "APPROACH caused an invalid page fault in
module APPROACH.EXE at 0137:0049eb64" errors when entering new
records in Approach 96, turn off the "In Field" option.

Problems with repeating panels
------------------------------

Firstly, does the repeating panel contain the right fields? If
so, try moving the repeating panel a bit on the form. Do the
fields move with it? If not then they are not really in the
repeating panel. To correct this, select all the fields and cut
them from the form; select the repeating panel so that the panel
edges are highlighted and paste the fields into it. Try moving
it again to check they are now in the repeating panel. If they
still don't move, try deleting both the fields and repeating
panel and setting it up from scratch.

Other things you may need to check are whether or not the data
is actually there in the right database and fields, and that the
database is meaningfully joined to the parent database.

Leading zeros (eg. 00054628)
-------------

To output data with leading zeros create and output a calculated
field containing the formula:

Right(Combine('0000000',DB.NUMBER), 7)

where '7' is the maximum number of digets, and hence the length
of the output string, and 0000000 is a line of zeros of the same
length.

Trailing zeros (eg. 1.400)
--------------

First check that you have that the field definition is numeric
with the right number of decimal places.

While in design mode, select the field and then enter the
Style/Field Format menu (v2.1, other versons may be different)
and either select one of the custom numeric field formats
provided, or enter your own.
For exporting data, create and export a calculated field
containing the formula:

NumToText(DB.NUMBER,'####.00')

where ####.00 is the output forumla you require.

Working with Paradox database files
-----------------------------------

A number of people have reported indexing and General Protection
Fault problems when working with Paradox files. To avoid these
make sure you are using:

  either: Approach v3.02 on MS-Windows 3.1 or (WFWG v3.11)

      or: Approach 96 on MS-Windows 95.

You will find problems if you attempt to use v3.0* on MS-Windows
96, or if you use v3.00 or 3.01 on any operating system.
However, you will probably be better off converting your
database files to dBase iV.

Free Approach 3.02 Upgrade
--------------------------

According to the technotes available at the Lotus web site
http://www.lotus.com, version 3.02 of Approach is available free
to customers experiencing an issue with Approach 3.0. Technotes
on the following topics all contain the phrase, 'This issue has
been addressed in Approach 3.02':

     * Only One Mailing Label Prints Per Page
     * Error: Unable to Build Index Required for XXX
     * Fill Field Option is Grayed Out in Browse/Worksheet
     * Stack Overflow Running Approach 3.0 QuickStart Tutor.
     * Import Table Has Changed, Unable to Import Data
     * Sending the Current View Only Sends All Views
     * General Protection Fault in Module KRNL386.EXE
     * Record Header Size... Bytes Too Small Saving an .APR
     * Form or Report Places Fields on Top of Each Other
     * Repeating Panel Will Not Sort in Approach 3.0
     * Crosstab Repeats the Same Value in Each Row
     * Changing Number to Boolean Returns Incorrect Results
     * Slow Performance using PowerClick for Grand Total
     * Internal Error - Couldn't Load String 1035 with Spell
     * Approach Export Macro to Fixed Length Inserts Spaces
     * Find Using a Wild Card Returns Blank Date Fields
     * Error: No Records Found Finding Times in DB2 Database
     * Error: Out of Disk Space Finding a Date in SQL or DB2
     * Fields Formatted as Zip Code Corrupt Data in Approach
     * Error: Macro Step Not Valid Editing Valid Macro
     * Percent Format Displays Zeros after the Decimal

So, if you are having troubles in any of the above areas it may
pay to check what version of Approach you have (by selecting
Help: About Approach). If you are using 3.00 or 3.01 then you
may be able to get a free upgrade to v3.02 by contacting Lotus.

Calculating ages from birth dates
----------------------------------

The following formula produces a line of text e.g. "Age: 32 yrs
6 mths". If the PEOPLE.BIRTHDATE is empty them it produces an
empty text string i.e..""

If(Isblank(PEOPLE.BIRTHDATE), '', If((Month(Today()) -
Month(PEOPLE.BIRTHDATE)) < 0, Combine('Age: ', (Year(Today()) -
Year(PEOPLE.BIRTHDATE) - 1), ' yrs ', (12 + Month(Today()) -
Month(PEOPLE.BIRTHDATE)), ' mths'), Combine('Age: ',
(Year(Today()) - Year(PEOPLE.BIRTHDATE)), ' yrs ',
(Month(Today()) - Month(PEOPLE.BIRTHDATE)), ' mths')))

The next formula just returns the ages in years and increments
on the exact birthday:

If(Month(Today()) < Month(PEOPLE.BIRTHDATE)  or Month(Today()) =3D
Month(PEOPLE.BIRTHDATE) and Day(Today()) <
Day(PEOPLE.BIRTHDATE),  Year(Today()) - Year(PEOPLE.BIRTHDATE) -
1, Year(Today()) - Year(DOB))

The last forumla gives you the persons exact age in days. You
can then roughly calculate how many years and months, but since
months and years are different lengths the answer is an
approximation, give or take a few days:

If(isblank(PEOPLE.BIRTHDATE),'',Trunc(((today()-
PEOPLE.BIRTHDATE/365.25),0))

Calculating number of weekdays
--------------------------------

The following formula will return the number of weekdays between
the dates contained in DB.STARTDATE and DB.ENDDATE:

If(Year(DB.STARTDATE)<>Year(DB.ENDDATE),DB.ENDDATE-DB.STARTDATE-(
WeekOfYear(DB.ENDDATE)+(Year(DB.ENDDATE
)-Year(DB.STARTDATE)-Year(DB.STARTDATE))*52-WeekofYear(DB.STARTDA
TE))*2,DB.ENDDATE-DB.STARTDATE-(Weekof
Year(DB.ENDDATE)-WeekofYear(DB.STARTDATE))*2)

You can have the formula automatically subtract any public
holidays, if any, by adding the following formula segment for
each possible public holiday:

-If(START <=3D '25/12/96' and FINISH >=3D '25/12/96', 1, 0)

where 25/12/96 is a public holiday. Make sure you use the same
date format as your database.

21st Century Dates
------------------

If you enter the full year (e.g. 10/31/2001) you should not
encounter problems. However, if you want to enter dates like
10/31/01, and you want to add only future dates you can use the
following solution.

Create 3 variable fields, all 2 digit numbers: VYear, VMonth,
VDay. Display these three fields on your form. Enter the dates
using these three fields. Create a calculated field called
VEYear containing the formula If(VYear =3D> 95, (If(VYear <=3D 99,
VYear, VYear + 2000)), VYear + 2000.

Then create another calculated field called, say, DISPLAYDATE
containing the formula: DATE(VMonth,VDay,VEYear). DISPLAYDATE
will contain the full 21st century date.

Dates before 1929
-----------------

Approach 96 assumes that any year less than '30 is in the next
century, i.e., 2029, instead of 1929. To avoid problems that
this may cause you should use full 4 digit year format.

Saving records in their sort order
----------------------------------

Sort the records as you want them, and then export
(ALT-FILE-EXPORT) that database into a new .dbf file. This .dbf
must contain the same files as the old .dbf. Delete the old .dbf
and rename the new .dbf with the old .dbf file name.  e.g. Say
you have a database called PEOPLE.DBF. Sort the records and
export all the fields in PEOPLE.DBF into another database called
TEMP.DBF. Exit Approach. Delete PEOPLE.DBF. Rename TEMP.DBF as
PEOPLE.DBF.

Record numbering
----------------

The record numbers that Approach allocates are used only for its
own administration of your database. When a record is deleted
all the records after it are renumbered etc... The record
numbers are not part of your data. If you want each record to
have a fixed reference number that never changes as long as the
record continues to exist, then you need to create a numeric
'serial number' field which automatically increments each time a
new record is created. See the options associated with numeric
fields.

Header / Footer problems in v2.1
----------------------------------

People have experience problems where the top or bottom half of
the footer or header line is cut off in v2.1. The easiest way to
get rid of this type of problem is to recreate the report from
scratch - this is likely to be quicker and easier than spending
to much time playing with your current one. 1) set the printer
setup to the correct values  2) create a very simple report
using all the default values. Just specify  a couple of fields
from the database the report is based on.  3) immediately insert
and set up the footer and header as required. Do a couple of
test prints to make sure it is right.  4) finally, set up the
report contents the way you want it - insert further fields, do
formatting etc...

Automatically opening files
---------------------------

Make sure .apr (.vew for v2.1) extensions are associated with
Approach. You can do this in the File/Associate menu of File
Manger. Then create a Windows program item with a command line
something like:

c:\path\database.apr (for >v3.0*)

where a:, \path, and database.* are the drive, path, and
filename of your database file.

To automatically open several files use the command line:

c:\approach\approach.exe c:\path\db1.apr c:\path\db2.apr

Use .vew instead of .apr for v2.1 files

Automatically opening a particular form
---------------------------------------

Approach automatically opens the form that you had up on the
screen the last time you saved the .apr file (or .vew in v2.1).
So next time you make some changes make sure the right form is
on the screen when you save it. Alternatively you can set up a
macro to automatically change to that form when the database is
opened. (See "Automatic macro on opening/closing View file")

Automatic macro on opening/closing View file
----------------------------------------------

You can create a macro that runs automatically each time you
open a particular approach view file by assigning it the name
"OPEN". Similarly, if you assign the name "CLOSE" to a macro it
will run each time you close it's associated file.

Deleting the body in summary reports
------------------------------------

Use your mouse to drag the bottom line of the body frame up
above the top line of the body frame. That way the body
disappears and only the summary lines show up on the report.

Preserving found set when switching forms
-----------------------------------------

Make sure all of your forms are based on the one database. When
creating a new database do not create the .vew or .apr based on
that database (or delete it if you do). Instead, create your new
form based on your main database rather than your newly created
one.

Keeping users out of design environment
---------------------------------------

If you are using v3.0* you can set a design mode password by
selecting the Tools/Preferences/Password menu item. Approach
will then not let anyone in design unless they know the password
(even by status bar, icon or key combination).

Read on if you are using v2.1, or if you would also like to
remove the temptation of changing the form:

Menus can be customized so that the design environment option
never appears.  Apparently, custom menus are kept in .APR files
so that no custom files on user PCs would be required.

Icon bars can be customized so the design environment icon never
appears.

The status bar on the bottom of the Approach window will let the
user go into the design environment. The status bar can be
suppressed but this prevents the user from reading useful
information such as current record number and how many records
were found out of how many.  There are no Approach macros and
formulas that support display of such record information on a
form.

In v2.1 there is no way to prevent a user from getting into the
design environment via CTRL-D.

.APR / .VEW files can be made read-only by changing their
properties in File Manager, but if a user gets into the design
environment, Approach won't tell them they cannot save design
changes until they try to save their changes, and they may know
how to change the file properties.

Stopping people accidentally entering data
------------------------------------------

One way of doing this is to create two separate forms: one for
data entry and on for retrieval. Make all the fields on the
retrieval form read-only so that data cannot be entered.

Searches on multipage forms
---------------------------

Make a single page form that contains all of the fields that you
want to search on. Switch to this search form, do the find, and
then switch back to your multipage form. The found set will be
preserved as long as search form must also be based on the same
database as your original multipage form.

MS-Windows 95 printing blank pages (v2 & v3)
-------------------------------------------

To stop Approach from printing out a blank page after every
printed page in Windows 95, make the following change to your
WIN.INI file: Under the [Compatibility] heading change
'Approach=3D0x0004' by putting a ';' in front of it so that it
reads ';Approach=3D0x0004'.

Radio Buttons Field Label=7F
-------------------------

Question: When using radio buttons, why can't I maintain the
field label, as in getting it to appear above / below / etc. /
anywhere in the box?

Page 6-12 of my User's Guide shows an example with a field label
Type of Customer. But I have been unable to generate a similar
example.

Answer: They got tricky. What they did to accomplish that is to
create a Text Box and type the box label Type of Customer. Then
they created their Radio Buttons box (outside of the text box,
clicked the right mouse button, chose ARRANGE, BRING TO FRONT,
and then dragged the radio buttons on top of the text box they
way they wanted it.

Play with it a bit and you'll get the results you're looking for.

Find the previous weeks records
-------------------------------

For the previous week (Sun - Sat): ('date'=3D your date field)

     if(weekofyear(date)=3Dweekofyear(today())-1 and
          year(date)=3Dyear(today()))

If the data is only for one year, then you can leave out the
'and' and all that follows.

For the last 7 days: (including today)

     >=3D@TODAY()&<=3D@TODAY()+7

Or, if you need to find records produced during the last 7 days:

... starting with and including today's date, use the formula:

     >=3D@TODAY()&<=3D@TODAY()+7

Finding records which do not contain *text*
-------------------------------------------

In Find, enter the following formula in any field:

if(NOT like(DBFNAME.fieldname,'*text-in-question*'))

Finds on repeating panels fields
--------------------------------

If any find criteria are put in a repeating panel the find will
return all the records that fulfill the criteria but will not
display any other records in the repeating panel.
A way around this is to set up a view that shows the specific
fields you will be searching. Do your search with that view,
then switch back to your original view with the found set.

Make empty fields disappear
---------------------------
     (... rather than just turn invisible)

It is possible on reports but not on forms.

An alternative is to set up a multipage form and have a separate
page for each field. On the first page, put a check box for each
field set to automatically come on if there is an entry in the
field. Then, when you are browsing through the forms, you look
at the page if you need to.

Running a Macro in a different .apr files
-----------------------------------------

One way of doing this would be to use  the Windows Macros
(Recorder) rather than Approach Macros.

Converting numbers to words 34 to thirty-four?
----------------------------------------------

There isn't such a function built into any version of Approach.
For Approach v96 there is a LotusScript called
ConvertNumberToText in the Archive of Scripts (see section 3.
Notices: How to get the Archive of Scripts) which works for
numbers up to 999 999 999.

If you are using v2.1 or 3.0* you need create another database
with the numbers and related text versions of the numbers, and
join it into your current database to the field that you want to
appear as text.

It might look something like this:

     number     text
       1        one
       2        two
      ..        ...
      27        twenty seven

Making keyboard entries into a formula or macro
------------------------------------------------

To do this, link every record in your database to a single
record in a specially created database. Fields in this record in
the special database can be easily changed by putting the fields
on any form, and can be used to input information required by
any macro or formula. The method for doing this in Approach v2.1
is as follows:

  1) In your existing database (let's call it EBASE) create a
new field called (say) DUMMYLINK, and set the field definition
options so that a '1' is automatically entered into DUMMYLINK
every time a new record is created. Then enter a '1' into the
DUMMYLINK field in all existing records. The end result of this
is that every record in EBASE will always have a '1' in the
DUMMYLINK field. Once it is set up there is no need for
DUMMYLINK to appear on any forms.

  2) Then create a new database (INPUTDBASE), with two fields
INPUTDATA, and DUMMYLINK. Create one (and only one) record and
enter '1' into the its DUMMYLINK field. Then join INPUTDBASE to
EBASE by the DUMMYLINK fields. The data you enter into the
INPUTDATA field is now linked to every record in EBASE.

  3) Put the EBASE:INPUTDBASE field onto any form or forms you
commonly use.

  4) Create the macro or formula using the EBASE:INPUTDBASE
field where you want to input data.

e.g. In an phone book database you could enter your current
location into the INPUTDATA field. This information could then
be used by various formulas to calculate the actual phone
numbers you need to dial from there, including any national, or
international codes, as well as perhaps giving the time and date
at the destination.

(Could somebody please confirm if this procedure is required for
v3.0x)

Sliding fields together on mailing labels
-----------------------------------------

If you are having difficulty getting fields to slide together on
mailing labels (such as first and seconds names), try setting up
a formula that combines the fields in the format you want, and
then print the formula instead of the individual fields. Eg.

Trim(Combine(SALUTATION,if(SALUTATION =3D '','', ' '), FIRST_NAME,
' ', Trim(MIDDLE_INI),if(MIDDLE_INI =3D '','',' '), LAST_NAME))

This routine only inserts spaces where they are required.

Another way of dealing with this problem is to create a text
object and embed the fields in it. The fields are expressed as
<<database.field>> where DATABASE is the name of the database
and field is the name of the field.
e.g. <<database.city>>, <<database.state>> <<database.zip>>


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

Subject: 5. How the Approach Users Mailing List works


It is fairly simple, but before we get into it please remember
one thing: The best source of information about how to use
Approach is the printed manual and the help files that come with
it. If you have access to the World Wide Web you may also want
to check out the technotes and FAQs on the Lotus WWW site:

http://www.lotus.com

The Approach Users Mailing List is a discussion area for anyone
uses or provides user support for the Lotus Approach relational
database. Subscribers also receive an occasional newsletter
summarizing recent additions to the FAQ as well as other
relevant information and notices.

The Approach Users Mailing List is not moderated.  Any message
sent to it is automatically bounced back out to everyone on the
list.  While I in no way want to stifle free discussion, as list
owner I reserve the right to step in if a flame war erupts that
becomes tedious for other subscribers, or if someone becomes
unseemly (excessive use of profanity, for instance).

You may choose to receive the mailing list as normal individual
emails, or to receive all the posts for each day compiled into a
single 'Digest'.

     <<<< =3D=3D VERY IMPORTANT =3D=3D>>>>

Please read the remainder of this section very carefully, as
this mailing list has two different addresses for different
purposes, and errors will occur if you use the wrong one.

     <<<<=3D=3D SAVE THIS MESSAGE NOW =3D=3D>>>>
                              ^^^

You should keep a copy of this section on your computer so that
you can refer to it in the future. This is particularly handy if
you want to know how to subscribe or unsubscribe one day in the
future.

If you have a personal message for someone, please send it to
their address, not the list server address.  Any mail sent to
the APPROACH List Server address will automatically be sent to
everyone on the list.

The list has two addresses. One is the APPROACH List Server
address, and the other is the MAISER Command address:

The MAISER Command address is: MAISER@rmgate.pop.indiana.edu
    --------------

It recognizes the following commands in the body of the message
(NOT THE SUBJECT LINE):

     SUB APPROACH           to subscribe
     UNSUB APPROACH         to unsubscribe
     SET APPROACH DIGEST    to turn on the digest option
     SET APPROACH NODIGEST  to turn off the digest option
     SET APPROACH NOMAIL    to turns off your mail
                              temporarily (say, if you're
                              going away for a few days)
     SET APPROACH MAIL      to turn your mail back on
     HELP                 to receive the MAISER help file
     INDEX                to receive a list of files
     SEND AUML-FAQ.DOC    to receive the Approach Users
                               Mailing List FAQ
     SEND LIST.TXT        to receive a list of other mailing
lists at this site. To subscribe to them send a message to the
MAISER Command address with SUB listname, e.g. SUB INROOTS in
the body of the text.

ALL COMMANDS SHOULD BE SENT TO THE MAISER COMMAND ADDRESS. ENTER
THE COMMAND AS SHOWN BEGINNING WITH THE FIRST LINE OF YOUR
MESSAGE. MULTIPLE COMMANDS CAN BE SENT AT ONCE, ONE PER LINE
WITH NO BLANK LINES BETWEEN THEM.

If your email software automatically adds a signature to your
email, then you should put the word EXIT on the line immediately
after the last command so that the list server knows to ignore
the rest of the message.

The APPROACH List Server address is:
    --------------------
                             APPROACH@rmgate.pop.indiana.edu

Do not send commands to this address! All posts sent to the
APPROACH List Server address will be immediately sent out to
everybody on the list. Make sure you are using the correct
address, and that you have not made any errors or omissions in
your message.

If you have any difficulties with the mailing list please email
me at jabrown@unix.infoserve.net with APPROACH in the subject
line.

Changing Your Subscription Address
----------------------------------

If you want to change the email address you are subscribed from,
simply unsubscribe from you existing account, then subscribe
from the new one. If for some reason you cannot access your old
account to unsubscribe please email me at
jabrown@unix.infoserve.net and I will remove your old
subscription for you.

Question and Answer Posts
-------------------------

I suggest you use the following format:

     a) Summarize your question in one sentence, being as
specific as you can. E.g. 'Is it possible to save records in
alphabetical order?'

     b) In a short paragraph following the question, provide any
further information that people may need in order to fully
understand your question E.g. 'I find it annoying to have to
continually sort the records and it would be easier if I could
just save them in their sorted order. Is this possible?'

     c) In a final paragraph state what version of Approach you
are using (2.1, 3.0,...) as well as any other software or
hardware that you refer to. THIS IS VERY IMPORTANT.

NO, IT IS NOT OK TO ATTACH FILES
----------------------------------

No. It is not OK to attach files. Doing this substantially
increases the size of the email, even if the file is compressed.
There are many different mail system around the world and not
all of them meet the same standards. Attached files can
sometimes be treated different ways which can (and does) cause
problems. Finally, many people around the world pay by the
minute to receive their email, and downloading large and
unwanted files adds unnecessarily to the cost of their service.
Many service providers consider attaching or sending large files
to mailing lists a breach of service and may terminate your
email account for doing it. So don't!
Being on a mailing list gives you privileged access to email
boxes of many people around the world. Be wise about how you
this privilege or it will be revoked. Keep you posts short and
to the point. You can always asked people to email you directly
if they want more information or a .zipped copy of your database.

comp.databases
--------------

There is no official association between the mailing list and
the Usenet newsgroup comp.databases, or any other newsgroup.
However comp.databases is a good forum for discussion. Relevant
information gathered from the newsgroup will be included in the
Newsletter and FAQ. If you do post to comp.databases please
include the word 'APPROACH:' in the subject line, preferably a
the beginning in capital letters, so that it shows up clearly in
a subject line search. e.g. Subject: APPROACH: Can you save
sorted records? Remember, most people on the mailing do not
regularly access comp.databases.

Lotus
-----

There is official no connection between this mailing list and
the Lotus Corporation what so ever. This is a completely
unofficial and unconnected mailing list.

Mail errors after posting to the list
-------------------------------------

You may sometime receive an error message from various mail
servers after you have posted something to the mailing list. If
it contains words like 'undeliverable' or 'unrecoverable' you
should forward them to me at jabrown@unix.infoserve.net. Mail
servers should do this automatically but many do not. Error
messages containing words like 'warming, message could not be
delivered for 4 hours' can generally be ignored, but make sure
you have read it properly first. Error messages from
.rmgate.indiana.edu are usually directed at you personally, and
generally means that you have done something wrong. Email me if
you cannot work out what it is.

Mail storms
-----------

A mail storm is caused by a mail server which incorrectly sends
error messages to the mailing list instead of the Error_to:
address in the message header. When it sends an error message to
the list it sometimes causes the same error to be repeated. This
goes on and on and our inboxes quickly fill up with recurring
error messages. This is what is called mail storm. They are
unusual, but one has occurred in the life of this mailing list.

There is nothing I can do to stop this from happening because it
is caused by an error in the remote system. I am able to remove
the offending address from the mailing list when I log on and
discover the problem, but the mail storm will probably have been
stopped by the mail server before I do this.

I will however make a complaint to the postmaster of the
offending system, asking them to permanently correct the problem.

If this ever occurs again DO NOT SEND ANY MAIL TO THE LIST AS IT
WILL ONLY MAKE THE SITUATION WORSE BECAUSE EVERY ADDITIONAL
MESSAGE SIMPLY INCREASES THE RATE OF THE MAIL STORM.

I will appreciate your understanding and cooperation with this,
as will everybody else on the list.


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

Subject: 6. Acknowledgments


The following people have contributed information to the mailing
list which is contained within this FAQ:

  Mike Armour
  Charles Atkinson
  Anthony Barker
  Michael Attenborough
  'Bigjc6991'
  Bill Bosking
  'CedarsMT'
  Sue Clark
  Tim Cook
  Bruno Dumon
  Elizabeth Durkin
  Rhodri Edwards
  'JoeF3900'
  Richard A. Garrard
  Charles T. Hall
  Cornel Huth
  'Bobj19'
  Vance Jochim
  Fred Kennedy
  Peter M Ladstaetter
  Carl E Lloyd
  Lewis N. Payton
  Donald Ravey
  R M Reddicks
  Bernie Sakel
  Ben Schorr
  Scott 'pccscott'
  Alan Shein
  David Sherman
  Jerry Sikes
  Bill Tarkulich
  Patrick Timmins
  Walter Thompson
  Doug Tymes
  Doug S. Varney
  Bernard Victor
  Mark Wright
  'zarief'

Some information was also gathered from the Lotus Web Site.

If you would like you name added to this esteemed list, please
feel free to post some helpful suggestions and answers to the
Approach Users Mailing List.

This document is provided as is without any express or implied
warranties. The editor and contributors assume no responsibility
for errors or omissions, or for damages resulting from the use
of the information contained herein.

(C) Copyright 1996 by John Brown, all rights reserved.
Permission is given for this document to be distributed in
electronic form (such as email, newsgroups, and the WWW) as long
as it is posted in its entirety including this copyright
statement, and is not distributed for financial gain.

Editor: jabrown@unix.infoserve.net (John Brown)

