>On         Thu, 26 Jan 1995 Linda Tanaka <[log in to unmask]> said:
>
>>Is anyone out there using Filemaker Pro for a database?
>>I would like to know if a look-up field can look up more than one
>alternate
>>catalogue number and can the database be searched on those individual
>numbers?
>...
>>Linda Tanaka
>>Vancouver Museum        British Columbia  Canada
>>[log in to unmask]
>
>I can only answer your question indirectly as it pertains to Filemaker Pro.
> Filemaker Pro, as I understandit is a derivative of or cousin to
>Symantec's Q&A, now virtually abandoned by Symantec.  During discussions
>about its demise on the Q&A bulletin boards many Q&A users indicated that
>they were going to switch over to Filemaker Pro because the features were
>so similar.
>
>I'm going to describe how you can solve this problem using Q&A.  You should
>be able to translate these instructions into the appropriate ones for
>Filemaker Pro.
>
>There are two way to solve your problem in Q&A: 1) create a file just to
>use as a lookup engine into your regular database and 2) use Q&A's
>multi-valued/variable length field attribute (available in Filemaker Pro)
>with its field programming facility (also available in Filemaker Pro).
>I'll describe the second way first:
>
>METHOD TWO:
>
>The easiest way is to place each identification number in a variable-length
>multi-valued field (called a key field). Here you separate each term with a
>semi-colon.  Such fields can be queried and sorted as if each term were
>entrely independant of each other--you can sort and report the database on
>each value in the field.
>
>Were I creating the database, I would use a three field set to register
>these accession numbers.  Field one would be used to hold just your current
>accession number.  Field two would be used to enter and transfer the
>alternate or historical numbers to field three.  Field three, setup as a
>read-only field, could not be edited, but would collect all the numbers
>that had been entered into fields one and two.  You would use field
>programming to accomplish this task:
>
>After entering the official number in field one, programming on exit would
>test it for uniqueness (or you can set the index to accept only unique
>values), if passed the uniqueness test and perhaps a format test, a copy of
>that number would be placed in field three followed by a semi-colon.  Send
>the cursor to field two.  Field two acts as en entry field.  Each number
>entered is passed onto field three followed by a semi-colon, but the cursor
>returns to field two for another entry.  When you press enter in field two
>when field two is empty, send the cursor onto its next task.
>
>Field programming can be used for some interesting tricks.  Note that I
>said that that field three should be made read-only.  That means that it
>can't be edited.  It keeps people from messing with the contents and
>accidentally changing its form.  In databases I set up, I specify that if
>you attempt to enter the same number twice into field two the program asks
>you if you want to delete it.  If you answer Yes (in a 4th field defined
>just to answer this question) then the number entered into field two is
>deleted from field three.  Because I test for the contents of field two in
>field three I prevent field three from accepting duplicates.
>
>If you can identify the source of the alternate numbers you can add yet
>another twist to your program.  Establish a field that contains all the
>alternate sources as potential values.  After entering field two, have the
>cursor go to this "number source" field, choose a source and then, before
>the contents of field two is added to field three, grab the contents of the
>"number source" field and stick it after the number in field three.
>
>The typical contents of field three might look like this:
>
>   95-123.a; a123 (Harvey Coll.); 10432 (Unknown Source); [...].
>
>METHOD ONE:
>
>I'll just outline how you can solve this problem using the first method
>cited above.  This is a less efficient solution of the problem, but
>illustrates the ability of Q&A (or Filemaker Pro) to grab data from
>associated files through lookups.
>
>Establish a file called something like NUMLOOK.  This file needs at least
>three fields.  Field one contains the official accession number of the
>museum's object.  When this is entered you must establish a validity check
>by looking it up in your regular cataloging file.  If it passes, accept it,
>if not, warn the inputter.  Field two is for a single instance of an
>alternate object identification number.  This means that if an object has
>four alternate accession numbers, the  NUMLOOK file will have four records
>for one object record in the catalogue file (five if you want to include
>the official number also in field two).  The third field is empty and will
>be used to display the object data from the catalogue file.  Here the user
>can query on any alternate number and be shown the offical number.  Through
>a lookup based on the official number, specific identification fields for
>the object can be imported through a lookup (and then erased) into field
>three for identification.  Earasing the data after each use keeps the
>NUMLOOK file small and insures that only current data is viewed.  This file
>can be reported on by alternate number and through a lookup in reports can
>import any set of data from the catalogue file.
>
>I would be grateful if any user of Filemaker Pro who reads this can report
>back to me on whether these strategies can be implemented in the program.
>I've heard wonderful reports on the program, on how similar it is to Q&A
>and might consider learning it.
>_____________________________________
>Robert A. Baron
>Museum Computer Consultant
>P.O. Box 93, Larchmont, NY 10538
>[log in to unmask]
 
 
I used FilemakerPro for a collection management system that is now being
converted to 4th Dimension.  Let me see if I can combine Robert's excellent
comments with my experience using FilemakerPro and then some warnings about
flat-file databases in general and Filemaker Pro in specific.
 
We had several different numbering systems in place several of which were
parallel.  We also had situations were an object had what amounted to a
research project number and frequently these objects would wind up in the
collections later, or would be exhibited with items in our collections.
Anyway, we needed to reference all the numbers.  Usually, we could tell by
looking at how the number was constructed what series it was from, but not
always. We also had people not familiar with our numbering systems handling
the objects from time to time.
 
Each object would have only one number of each type.  We would enter each
number in a field set up for each number type.  For example, the museum's
current numbering system, specific research projects that used numbering
systems, the systems of a few museum's whose collections we absorbed, etc.
We also had what amounted to a "mystery number" field for numbering systems
we were unsure of or were unique to the object such as a collector's
number.  This field could accommodate more than one number but each number
was "delimited" or separated from the others with a _standard_ punctuation
mark.  This way we had a one to one correspondence for the number and the
system it was from.
 
We then used a "Calculation" field to combine the numbers, again separated
by a deliminator built into the calculation, into yet another field.
Remember to use "text" as the result or you will get garbage.  This way we
could search by whatever number was easiest to deal with and the person
performing the search did not have to be an expert in our somewhat arcane
numbering systems.  Also if you were working backwards from research and
only had the research number it was easy to find the catalog number.  This
redundancy is especially useful if one number gets lost.
 
I think FilemakerPro is a great product.  It is easy to set up, has an
excellent user environment and is a great forms generator.  It is an
important part of my tool kit.  However, like at all flat file databases it
has some severe limitations.  It is not very efficient in how it uses
memory and it is very limited in how it establishes relationships between
information classes.  These limitations are increasingly apparent the
larger the files get.  Searches, lookups, sorts and other memory intensive
actions get slower and slower.
 
Filemaker will not handle files over 32 megabytes and once you get over
about 25 mbytes it starts crashing far to often.  Also, no matter how much
Claris implies that a look up is a file relation, it is a primitive one at
best.
 
At some point you may want to switch to a more efficient relational
database.  There are many good ones (I like 4-D despite its steep learning
curve) on many platforms.  You can save yourself a lot of headaches in the
future if you pay attention to things like consistently deliminating lists
of attributes in a text field, standardizing terms and generally making
things as consistent as possible.  Some of Filemaker's features make these
thing less important now, but if you ever need to switch platforms a little
care now may save you a lot of hassles later.  The indexed searching
feature, for example makes, it easy to look up a term later instead of
getting it right now.
 
Again, I don't mean to bash Filemaker. I think it is truely great product
just be carefull not try exceed its abilities.
 
 
Peter
 
[log in to unmask]
 
******************************************************************************
Peter Holben Wehr
[log in to unmask] / [log in to unmask] / [log in to unmask]
Museum & Education Technology Consultant
(718) 721-3761 * c/o Debra Parkinson * 28-26 47th St Apt 5 * Astoria NY 11103
****************************************************************************