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]