Museum Collection Solution Question
I am a very basic FileMaker user who is learning as I go. I have been directed to accomplish a complex task and I could use some expert advice.
I am creating a museum collection database for an automotive museum. This database will cover vehicles, photos, artifacts and film/video. My initial approach has been to create separate tables for each item classification (Vehicles, 2D Artifacts, 3D Artifacts, Photography, Film and Video). At the moment, none of these tables are relational, and since I have been directed to not use this database to track exhibits, I am struggling to find a relationship between them all.
One of the relationship ideas I have revolves around using the catalog number to link a master item list to the separate tables. Now, the difficult part. Different category has a letter based prefix describing the category and a numeric number (or series of numbers) tracking the item. This would look something like VEH.00001 for the first vehicle entered into the table, and VEH.00025 for the twenty-fifth. I would like the number to be generated and controlled by Filemaker and show up in a master table, but I am not quite sure how to achieve this.
So, my question(s) to the community are thus: Am I going about this the right way, with multiple 'flat' tables? Is there an easy way to achieve a complex numbering system? Should I be looking at scripting for what I am attempting to achieve?