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]