AnsweredAssumed Answered

Using dropbox result to populate another field

Question asked by docroc67 on Oct 17, 2017
Latest reply on Oct 17, 2017 by SteveMartino

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)

 

Thanks,

- dave

Outcomes