Indirectly linked tables
I have a table of book data called Titles. This contains information common to all editions of a book. It is linked to another table, Editions, which contains information specific to, say, the paperback or kindle version.
These are linked on:
Editions::fkTitleID = Titles::TitleID
I have another table of Publisher information linked to the Titles table on:
Titles::fkImprintID = Imprints::ImprintID
What I'd like to be able to do is transfer a copy of one of the fields in the Imprints table to the Editions table when the user selects the publisher for the book.
I put a temporary field on the layout linked which shows the code from the Imprints table, but I need the user to be able to modify it for a particular edition of a book so I need to store a copy with the edition data.
To this end, I defined the field in the Editions table to be a calculated value with the calculation being:
There is a field on the layout to show this value as well.
The temporary field shows the correct code when you select the publisher but the Editions field is never populated even when I know that there's a ProductGroup code for that particular publisher (they don't all have one).
Is what I want to do actually possible - I mean the imprints and editions tables are only linked indirectly through the titles table so perhaps what I want is actually ambiguous, though I don't think it is.
Any pointers would be gratefully received.