I have a table for a museum's exhibitions and one for Items within an exhibition, which I have inherited as part of a migration from FM11 to FM16. The Exhibition table was never utilised over 9 years as it was not made accessible to the users and I would like to utilise it now for increasing data integrity.
I would like to add a look-up list in the Items record, to set the correct exhibition title while at the same time setting the Exhibition's primary key into the Item's record. If the Item is then associated with a different exhibition, I would like the updated Exhibition ID to be recorded in the Item's record. Many many years ago I recall using the either the Set Field, Calculated value or Look-up value for this. However I am missing something and nothing happens. The exhibition title is recorded in the Item record but the Exhibition ID is not.
The current relationship between the two tables are Item:Exhibition Title = Exhibition:Exhibition Title and Item:Exhibition ID = Exhibition:Exhibition ID. The Exhibition also has 'Allow creation in this table via this relationship'.
Any thoughts on on currently implemented errors or possible approaches most appreciated.