10 Replies Latest reply on Jun 21, 2012 6:10 AM by AlastairMcInnes

    Showing indirectly related data

    AlastairMcInnes

      Title

      Showing indirectly related data

      Post

      I have a table of book information - title, publisher, etc.

      Each book may have multiple authors and/or editors.

      It seemed to me to be good practice to create a table of Contributors to store the details of these people and then make a link table to tie them to the books with which they are associated.

      So, I have Three tables (in this scenario):

      Titles
      Contributors
      Contributions

      The Contributions table had three fields when I first set the database up - the TitleID, the ContributorID (both numbers and being the foreign keys of the relevant tables) and the Role - a text field.

      What I wanted to do on the layout was have a portal to display the unknown number of contributors - selecting those where the TitleID was the same as the current Title being displayed on the layout.

      I ran straight into a problem - the portal won't let me show the Contributor's name because it's not in the Contribution record - only a reference to it is held there.

      I have now added a fourth field to the Contribution record and made it a Calculation field with the result being:

      Contributors::Full Name

      I can add this field to the portal, no problem. However, when the layout is displayed, only the current line of the portal actually shows the name - if I click on one of the other lines, that name is displayed but the first one vanishes.

      It must be possible to show all the names all the time, but I don't see what else I can do.

      In reality, I'm told that no title will have more than 6 contributors (or perhaps we're just going to ignore the 7th and subsequent) so I could just have 6 fields on the title layout to show them but this seems a little untidy and not very good database practice.

      I've attached a small screen shot to show the portal.

      I'd be grateful for any pointers. 

      Authors.jpg

        • 1. Re: Showing indirectly related data
          GuyStevens

          Hey Alastair.

          You shouldn't have to make this calculation.

          This should be working just fine. You can use the fields from the other table.

          I just happen to have an example file that's rather similar to your file :)

          And I added a little portal to show this technique.

          http://dl.dropbox.com/u/18099008/Demo_Files/Books_EditionsV2.fp7

          Pay attention that I put two fields on top of each other in the portal. That's a handy technique for when you don't want to show an ID field.

          First you have the ContributorIdFk from the Contributions table that's a dropdown getting it's values from the Contributors table. It's field entry is enabled in Browse mode, but not in field mode.

          On top of that, and in the exact same size (just copy the ContributorIdFk field by Ctrl - dragging it) I have the c_FullName field from the Contributors table. It's an edit box and is set to that field entry is disabled in browse mode but enabled in find mode.

          I don't know if this is clearly explained or not, but have a look at the file and let me know if you have any questions.

          • 2. Re: Showing indirectly related data
            AlastairMcInnes

            I just happen to have an example file that's rather similar to your file :)

            That's remarkable - what are the odds...? Surprised

            Anyway - thanks again. It didn't occur to me to plank another field on top of the first one. In the end I used a Merge Field because it doesn't show a cursor when you click on it, giving it the same appearance as the other half of the portal.

            I'm wondering about how to add the authors - obviously a nice drop-list would be elegant but there are likely to be tens of thousands of them in the end and a drop-list isn't remotely feasible.

            The other option is to use auto-complete, but I read somewhere that, when the database is remotely hosted (as this one will be), there's a huge overhead in sending the characters to the server as they're typed and getting the list of possible matches back. This makes sense and suggests that auto-complete is out.

            It would be nice if the users had to select from a list (to avoid spelling mistakes and different people using different formats, etc) but even pulling the list of names down to populate any kind of selection is going to create a big overhead, I'd have thought. Well, not that big - say 200K or so for 10,000 names - but it's a noticable amount. Also, just displaying them all is going to create a problem, though if they're sorted I suppose the users can scroll quickly to the right area.

            I'll need to check with the end users over what they're prepared to put up with - they'll have to use the thing at the end of the day, not me! Maybe they'll be happy just to type the author's name into an edit box and be done with it.

            It occurs to me that, if I were to use a separate form and load all the existing authors into it, there might be a way to take the download hit once and just hide the form rather than closing it. That way, it'd still be there with it's long list if the user wanted to add a second author.

            • 3. Re: Showing indirectly related data
              AlastairMcInnes

               In the end I used a Merge Field because it doesn't show a cursor when you click on it, giving it the same appearance as the other half of the portal.

              Now. That's odd - now, without doing anything different that I recall, an ordinary text field doesn't show a cursor when you click on it but would allow me to use the portal field to enter a search term so I'll go back to your original suggestion of a text box rather than needing a separate "Author Search" functionality.

              • 4. Re: Showing indirectly related data
                GuyStevens

                I'm not entirely following these last posts.  But for adding authors you could go a few ways:

                - Either a dropdown list like the one in my example, you can also type ahead in a dropdown and the first characters you type go to the first name with those characters in that dropdown.

                - Or you could have a button that brings you to a list view of all authors where you could perform a find to limit the amount of records shown. In that list view there could be a button in front of every record that you could click to select that author.

                What the quickest option if over a network is something you'll have to test.

                But if you are showing a list of only names it might still be do-able. Without calculations and conditional formatting this could still work.

                • 5. Re: Showing indirectly related data
                  AlastairMcInnes

                  I think you're right - we'll just need to see what's feasible. One of the offices where this is going to be used has a truly hopeless internet connection (mainly operated by carrier pigeon, I think) but they're looking at upgrades. Not sure about the connection at the American end. That's not my decision but I need to tailor the database to suit.

                  Thanks.

                  • 6. Re: Showing indirectly related data
                    AlastairMcInnes

                    Sorry to drag this up again, but I've run into another road block on something that seems like it ought to be simple.

                    I've separated Publisher's details out into a separate table and used a contruction like the one you've got for the authors in the portal. The publisher relationship is simpler in that there is only one publisher for each book so there's no need for the link table.

                    It mostly works as I want - the drop list displays the names of the publishers, but if you just click in the field, it reverts to showing the publisherID field which is irrelevant for the user - it's just used behind the scenes to tie the tables together.

                    When the drop list is select, the number reappears as well in the text box part of the combo, which, again, is not exactly what I want.

                    Is there some way of getting this field just to show the publisher's name?

                    I can live with it as it is, but it would be nice if the user didn't have to see the underlying structure at all.

                    Thanks.

                    • 7. Re: Showing indirectly related data
                      crtopher

                      Hi Alastair

                      I think your answer is already DaSaints first answer:

                      "Pay attention that I put two fields on top of each other in the portal. That's a handy technique for when you don't want to show an ID field."

                      Make the "bottom" field the ID field, with the dropdown value list, and make it enterable in browse mode. Put a solid field containing the related publishers name, directly on top, making it unenterable in browse mode. When you click on what seems like the one field, your dropdown box will show your value list - when you click out, the publisher's name appears.

                      • 8. Re: Showing indirectly related data
                        AlastairMcInnes

                        Hi Chris,

                        Yes - I've done the same, with the two fields on top of each other and it gives (unsurprisingly) the same behaviour in DaSaint's database - when you click on the field (as opposed to the arrow) it changes to show the underlying serial number (the ID field from the Publishers table). If you click on the arrow on the field, the drop list opens with the list of Publishers' names but the edit-box section of the control still changes to show the ID, rather than the currently selected Publisher's name.

                        What I'd really like is for the serial numbers to be completely hidden from the user - they're only there to tie the tables together and have no meaning or significance for the users. So, it would be nice if the field (whether the edit or the list box) only ever showed the Name, rather than switching backwards and forwards between the ID and the name.

                        Incidentally, is it purely because of the Browse Mode and Find Mode check boxes on the Field Setup dialog that allows you to access the "bottom" control? Normally I'd expect it to be inaccessible because of having the other control on top of it. If they were both set to allow entry in Browse Mode, say, would you ever be able to click into the lower one?

                        Thanks,

                        A

                        • 9. Re: Showing indirectly related data
                          crtopher

                          Hi Alastair - yes you are right. If you have an arrow to show the drop-down value list, if you click on the field, or tab into it, the ID shows, which I agree might be confusing to some. The only way around that is to not have the arrow. When the user clicks or tabs in, the value list drops down, indicating that the user should make a selection. The ID still shows, but it seems less probablematic now that the value list has appeared. Another way around it is to make it a pop-up list with an arrow. This way, if users tab in the only see the name, but if they click in, they get the value list. No ID is displayed. You are also right that it is the disabling of the ability to enter the top box in browse mode that makes the bottom box accesable. Cheers, Chris

                          • 10. Re: Showing indirectly related data
                            AlastairMcInnes

                            Interesting - it look better using a pop-up menu in that you can't see the number appearing but it seems to me that menus are for selecting options rather than entering data - the "correct" control for what I want is a combo box but it seems that Filemaker can't quite get it right.

                            The option without the arrow is, I think, the best compromise - at least with the list of options open the fact that an apparently random number has appeared in the field is less distracting.

                            I wonder if this is something the boffins at Filemaker might ever fix - it's a pretty basic database operation, I'd have thought. The whole point of a relational database is the relationships after all, and these are often made using background serial numbers.

                            Anyway, many thanks for your help, Chris - I feel that another couple of chinks in my ignorance of Filemaker have been filled in now.

                            Alastair