9 Replies Latest reply on Jul 27, 2012 3:51 AM by AlastairMcInnes

    Indirectly linked tables

    AlastairMcInnes

      Title

      Indirectly linked tables

      Post

      Hello,

      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:

      Imprints::OrcaProductGroup

      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.

      Alastair

        • 1. Re: Indirectly linked tables
          philmodjunk

          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.

          • 2. Re: Indirectly linked tables
            AlastairMcInnes

            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.

            Thanks, though.

            Alastair

            • 3. Re: Indirectly linked tables
              philmodjunk

              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.

              • 4. Re: Indirectly linked tables
                AlastairMcInnes

                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
                value

                Maybe that will help.

                Alastair

                • 5. Re: Indirectly linked tables
                  philmodjunk

                  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.

                  • 6. Re: Indirectly linked tables
                    AlastairMcInnes

                    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.

                    Alastair

                    • 7. Re: Indirectly linked tables
                      philmodjunk

                      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.

                      • 8. Re: Indirectly linked tables
                        AlastairMcInnes

                        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.

                        Alastair

                        • 9. Re: Indirectly linked tables
                          AlastairMcInnes

                          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.

                          Alastair