Hi - I'm a FM Pro newbie, bit not a database newbie. I'm working on an older database (in FM Pro 13) which holds book review info for an academic journal. As originally designed, it has only one table - books - with all info about each book. But an equally important entity is reviewers - which, in a normalized database would have its own table. But not in this database.
Because reviewers were never managed before as a separate entity there was no unique ID for reviewer in the books table --just a name on each book record for the reviewer that was chosen. And, by "chosen," I mean typed in by hand each time, with all the spelling inconsistencies that guarantees.
In order to get control of the reviewer data -- I've created a special reviewer table and a related reviewer_history table. In order to ensure true uniqueness of each reviewer record, I assigned a unique numeric ID to each one during a big cleanup I did before loading these new tables into the database. All spelling variances have been removed and I created a related reviewer_history table containing date of each use of that reviewer (and some other stuff).
Here's the problem:
So I changed reviewer (the reviewer name field already in the book record) into a drop-down, getting its data from the reviewer table, allowing the user to browse by reviewer name. This works fine.
But I also created a reviewer_ID field in the Book record, because reviewer_ID is the unique key for reviewers.
When the user chooses a reviewer by name from the reviewer drop-down box, in addition to populating the reviewer field, I want it to put the reviewer_ID into the reviewer_ID field. There are other fields in the current Book record that I might want to populate based on the Reviewer record as well.
I've tried a script trigger using the OnModified event for the reviewer field, but either I'm not sure of the syntax or it just doesn't work. I can stick an arbitrary number into reviewer_ID based on the script trigger, but if I try to reference the reviewer_ID field in the Reviewer table, nothing appears in reviewer_ID in the Book table.
For people who have used ACCESS, I'm trying to duplicate its ability to have drop-down box populate one or more other fields on the layout, once a drop-down choice has been made. In access, the row chosen in the drop-down box can be referenced as can each of the columns in the drop-down's row. I'd like to do whatever is the right approach in FM.
Any help would be greatly appreciated.
I hope I was clear (but not long-winded)