You appear to have these relaitonships:
Editions>----Titles>----Imprints (>---- means "many to one" )
If so, you can use the field tool to add a field from Imprints to your edition layout. It will show data from Imprints and be fully editable if you need it to be. There would be no need for any additional fields of any kind in Editions to make this happen.
You've got the relationships correct.
I don't want to add the Imprints table field to the edition layout directly - I just did that to see if one of the links was working the way I thought it would. I need to copy the field from the Imprints table to a field in my Editions table so that, if the user changes it for a particular edition, it won't change the value for every other book that is from the same publisher - they may want to overwrite the terms for a single book.
That's why I made the Editions table field a calculation field, but I think I must have done that incorrectly somehow.
OK, then define your field as a data field (text, number, date...)
and use auto-enter settings to copy the value from the field in the imprints table. YOu can use either looked up value or calculation and it will copy the value from imprints, but then you can edit the value if needed.
I thought that's what I'd done.
I had it as a calculation field but changed it to Lookup. It didn't seem to make any difference though. I'm not, to be honest, sure what I'd expect the difference to be.
I printed out the field definition:
Auto-enter calculation: from Editions, = Imprints::OrcaProductGroup, replaces existing
Maybe that will help.
This won't automatically update any of your existing records but should copy over a value in any new records that you create. Or any time you update the Editions::TitleID field.
Try using Relookup or see this link: Updating values in auto-enter calc fields without using Replace Field Contents for another way to update existing records when you add or change an auto-enter calculation.
OK, I think I see what you mean. I used the replace field contents option and the field was filled in nicely exactly as I wanted.
I guess what I was expecting was that when I changed the Publisher, the field would upate itself automatically but it doesn't look like that's possible. I'm in two minds as to whether I want it to anyway - on the one hand, if the user has filled in a special code, that probably ought to stay even if they realise they've got the publisher wrong. On the other, if the publisher is changed (which probably won't happen in practice) maybe the book ought to be given the new default terms.
I'll need to run that one past the business expert.
I more pressing problem has occurred to me though. When I create a new record (using Ctrl+N) it only creates a new Titles record. The natural order of filling in the information will see the user quite likely to select the publisher before they've created a specific edition of the book, so there won't actually be an Editions record to update at that point. I can fix that by adapting the "Add Edition" script to set the value of the OrcaProductCode field as the new Editions record is created.
BUT, if the user does create the edition before selecting the publisher, I'd expect the (empty) ProductCode field to be updated when the publisher is selected and that doesn't seem to be happening. And that I can't figure out.
The problem as I see it is that you don't select a publisher for an edition--only a publisher for a given title.
Any changes to publisher are changes in the Titles record, not the Editions record so there is no event in the Editions record to trigger the lookup. You may need to figure out a script trigger that updates the fields in all the related editions records when you select a publisher.
I think you're right - there isn't the direct link from the publisher to the edition that I need to make it work as I'd envisioned.
I am just about to download FM11 Advanced and I'm hoping that a script trigger can be used to figure out when/if the publisher code should be updated.
Many thanks, as always.
Actually, it was much simpler than I realised.
When the user want to choose the publisher, they click on a button on the main layout that takes them to a list of publishers. When they click on the publisher's name, it takes them back to the main layout and sets up the ImprintID field. It was very easy, at that point to check if there was a value in an associated Editions record and, if not, to set it.
Similarly, when they click to add a new format, I've modified the script to copy and productcode to the new Editions record as it's being created.
Thanks for all your advice, though.