You might look into normalizing your relations
http://en.wikipedia.org/wiki/
Dat...e_normalization
in particular:
http://en.wikipedia.org/wiki/
Dat...ation_anomalies
Also, look into a RDBMS that supports SQL, despite it being "seriously flawed". You can use Excel, Access or Openoffice as graphical front ends to MySQL or PostgreSQL.
Thanks for the homework, Lee! I'll definitely do a little reading on this.
I'm sure I checked out FileMaker Cafe the other week (after your suggestion), but now it all seems to be password protected and I can't get on...
Any idea if it's just a temporary thing? I've got a few questions about my own VFX shot tracking db that I want to make changes to....
Love the blog, though - I've been keeping an eye on it for a while now, and am slowly working my way through your "adventures" series....
Awesome! Thanks, Hugh. Yeah - I noticed on a recent visit to FMCafe that it was all different and passwordy. Took me forever to find my old threads, which did not come up in a normal search, as they should have.
If you find out anything, let me know... I haven't investigating signing up for any pay service--but my guess is that this is not a temporary move. 
It's a bit of a shame... I'd never really visited (I might have taken a quick glance when you first mentioned it, but not since then) - it all sounded great and really helpful....
I had an issue recently when I realised that I was linking my shots with notes, deliveries and requirements all by vfx number. Not good, I realised.... I can link them by a unique id easily enough, but I was trying to figure out the best way of doing it in the UI so that the user never has to worry about the unique id and just thinks that they're selecting the vfx number....
I only really got into FMP a couple of months ago (I bought v9 on the day before I should have been eligible to a free upgrade to v10, but due to a typo on one of the pages on their site, they agreed to let me have the upgrade anyway...) - I'm actually surprised how much, seeing as I'm far more of a compositor (with fledgeling supervisory roles) than a producer/coordinator, I've actually been enjoying using it! Should I be worried? 
Ha ha - no... it's normal to love FileMaker (at least I and my geeky friends think so!).
I went into FMcafe yesterday and it let me just log in with my user ID (which I've had for years and was free). Try creating a guest membership. I think it's still free.
In any event, I can explain how to use serial #s as match IDs, but with user interface seeing more meaningful data to select. However, Just curious... don't you import your shot notes from an Excel sheet? Or are all your notes typed directly into the shots database? If you're just creating a new note in a portal, from the shots database, you don't have to even show the match ID. Just enable record creation via this relationship (only on the notes side).
An easy rule of thumb as non-negotiable base fields whenever you create any new table are:
SHOTS FILE:
- primary key, which is a serial number that is auto-entered and not modifiable under any circumstances (I have come to see the wisdom in naming this field like this: keyP_shotID)
- foreign keys (for any child tables to which Shots is related, like notes): keyF_noteID etc.
- creation date
- modification date
- created by (user name)
- modified by (user name)
Before I do anything, I start there.
NOTES FILE:
- keyP_NoteID (serial, auto, not modifyable)
- all the other record creation/modification fields
Your relationship for a many-to-one deal will always be set up (in the case of one shot to many notes, for example) as follows: Shots: keyF_noteID = Notes: keyP_noteID. Make sense?
Commenting by HaloScan
