1 2 Previous Next 21 Replies Latest reply on Jun 18, 2012 3:45 AM by AlastairMcInnes

    Showing data from related records on a layout

    AlastairMcInnes

      Title

      Showing data from related records on a layout

      Post

      I have two tables containing data about books.

      In one, called Titles, is data that is common to all editions - title, author, editor, etc.

      In the other, called Editions, is data specific to a particular format - ISBN, price, hardback/paperback/eBook, etc.

      Some books only exist in hardback, whilst others may have as many as 5 (or more) different formats, each with its own ISBN.

      The Titles table has an ID field which is used to form the relationship to the Editions table.

      What I'd like on a layout is to display the common data at the top with a summary under that of the different editions available - perhaps a list of the ISBNs and the format in a list.

      When the user clicks on one of the ISBNs, I'd like the lower part of the layout to show the data from the relevant record from the Editions table.

      The user would be able to click on a different ISBN and the lower part of the layout would change to show the data from that Editions record, and so on.

      I managed to get the common data onto the layout easily enough and then it seemed that I needed a portal to show the ISBNs and formats but I'm stuck on how to change the lower fields to show the data from the relevant record from the Editions table when the user clicks on one of the lines in the portal.

      Assuming this is possible, I'd be grateful for any pointers on how to do it. I'm reasonably competent generally with IT, but a beginner with Filemaker so please use short words...

      Many thanks.

        • 1. Re: Showing data from related records on a layout
          philmodjunk

          You appear to have this relationship:

          Titles----<Editions

          Titles::TitleID = Editions::TitleID

          If I understand your post, you want to divide the layout into three sections, The Title data, a list of editions (ISBNs) and a "detail" section where data from a selected edition can be displayed.

          In Manage | Database | relationships, make a new table occurrence of Editions by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box as SelectedEdition.

          We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.

          Add it to your relationships like this:

          Titles::SelectedTitleISBN = SelectedEdition::ISBN

          For the detail section of your layout, add fields from SelectedEdition to your layout.

          Now write this script:

          Set Field [Titles::SelectedTitleISBN ; Editions::ISBN]
          Commit Record

          You can either select all the fields in your portal listing records from Editions and use button setup to turn them into a button, or you can add a button to the portal row. Either way, set the button to run this script. When you click the button the ISBN is copied from the clicked portal record into the SelectedTitleISBN field establishing a link to that record so that the detail fields can then display data from that record via the new occurrence of your Editions table.

          • 2. Re: Showing data from related records on a layout
            GuyStevens

            This demo file is an example of this technique.

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

            Where you can select a value in the portal and see the fields change.

            • 3. Re: Showing data from related records on a layout
              AlastairMcInnes

              Thanks very much to both of you.

              Phil - you've got the relationship exactly right, yes. 

              Da Saint - yes, that looks like the sort of thing I'm trying to do.

               

              What I'm going to do now is follow Phil's instructions and see how I get on. It won't be today though - well past my bedtime. I'll post a progress report tomorrow, but in the meantime, thanks again.

              • 4. Re: Showing data from related records on a layout
                AlastairMcInnes

                OK. I've spent some time on this today and have managed to make some progress, I think.

                I followed Phil's instructions and got something that looks quite nifty however, I've just run into the next road block. 

                The layout I created, I've called BrowseTitles - this is the one with the General data on it and the portal showing the ISBNs and the format of specific editions. When you click on one of the portal rows, the non-general fields on my layout update to show the data specific to the ISBN you just clicked on. Great!

                However, how do I add new formats. Say they decide to issue the book for Kindles and they'll want to add the details for that version. It would be nice to have a button under the portal that says "Add New Format" which would create a new record in the Editions table, with blank or default values and then let the user fill in the details as required. 

                However, I can't see any way of doing this - the New Record/Request script step doesn't let me specify which table I want to create a new record in and insists on creating new Title records, which I don't want.

                I see that in DaSaint's example he (she?) opens a new form to entire new billing information, but I'd really like to use the fields on my BrowseTitles layout. I think it would look better and would also save cluttering the database up with another layout.

                So, is what I want to do possible, and if so, how?

                Thanks again.

                • 5. Re: Showing data from related records on a layout
                  GuyStevens

                  I think the only thing you are missing is to check the "Allow creation of records via this relationship" in the relationships settings on the "Edition" side of the relationship.

                  That way you can create new Eiditions straight into the portal.

                  But you do have to make a seperate button to select the edition, because if you use the edition field as a button you can't type any text in it.

                  • 6. Re: Showing data from related records on a layout
                    AlastairMcInnes

                    OK. good point about the "allow creation" box. I've checked it.

                    Now, it looks as if I get a new Editions record whenever I type something into one the fields bound to the Editions table on my BrowseTitles layout. However, that Editions record doesn't have the TitleID field set so it's not being properly matched to the correct Title record. I was hoping that, when the Editions record was created automatically, the fields linking it to the Titles table might be set automatically too.

                    If this were in, say, Access, I could perhaps pick up the OnCreation event and set the ID fields myself but Filemaker doesn't seem to do events so I'm stuck for how to set this.

                    I'm also toiling to see how to create the second and subsequent Edition records for a title. I've put a button on the layout which will, I assume, run a Script but what should be in the Script - there doesn't seem to be a command to create a new record in a random table, only the main one associated with the current layout, which I don't want in this case.

                    I'd really like not to have to switch to a separate layout to do this, but maybe that's the only way to go.

                    Maybe what I'm trying to do is beyond the capabilities of Filemaker - as I said, I'm a beginner with it so I really don't know its limitations as yet.

                    Thanks again, though, for the help - it's really is very much appreciated.

                    • 7. Re: Showing data from related records on a layout
                      GuyStevens

                      I was hoping that, when the Editions record was created automatically, the fields linking it to the Titles table might be set automatically too.

                      This is the case.

                      If you have a relationship between Title::Id and Edition::TitleId

                      And you create a new edition in the portal it's automatically linked to that title.

                      If this were in, say, Access, I could perhaps pick up the OnCreation event and set the ID fields myself but Filemaker doesn't seem to do events so I'm stuck for how to set this.

                      Filemaker does ScriptTriggers but they are not neccesary because the Edition record you create in the portal is autimatically linked to the Title record.

                      You might have done something funky with your relationships or used fields from the wrong table occurence, but filemaker works pretty darn good in that regard.

                      Maybe what I'm trying to do is beyond the capabilities of Filemaker

                      Hehe, believe me as a former Access user when I say: "You aint seen nothing yet!!" You are not even close to reaching the limitation of filemaker with what you are trying to do.

                      I'm also toiling to see how to create the second and subsequent Edition records for a title. I've put a button on the layout which will, I assume, run a Script but what should be in the Script - there doesn't seem to be a command to create a new record in a random table, only the main one associated with the current layout, which I don't want in this case.

                      I don't really understand this one. Why would you make a new record in a random table?

                      The way I see it you have a title and you create multiple editions of this tatle in your portal. I don't see any problems there. But maybe I'm missing something.

                      I'll try and see if I can't make you a little example.

                       

                      • 8. Re: Showing data from related records on a layout
                        GuyStevens

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

                        This should make things a little clearer.

                        I'm guessing you are struggeling with the multiple Table Occurences.

                        Because you do need two table occurences of the Editions table.

                        But I hope the example shows you the light.

                        And shows you that you have not yet reached the limits of filemakers abilities.

                        • 9. Re: Showing data from related records on a layout
                          AlastairMcInnes

                          There is a relationship between Titles::_TitleID and Editions::TitleID

                          I just checked in case I was being stupid.

                          When I press Ctrl+N on my BrowseTitles layout a new, blank set of fields appears. I then fill in details in the fields linked to the Titles table and add a comment and an ISBN to the fields linked to the Editions table. If I click back and forth through the records, Filemaker seems to be able to match these two records and show them on the layout.

                          I've no idea how it's doing it though. I have another layout just for the Editions table and it shows that the TitleID field is empty for that record. There's another record in that table with a blank TitleID and it's never displayed at all, which is what I would expect as there's no parent-child relationship to make it work.

                          On the basis of your "create new records in the portal" statement, I just tried clicking on the top (blank) line of the portal and it does indeed create a new Editions record - I can put some random datat in it and see that data in the Editions layout but, again, there's nothing in the TitleID field. Also, when I click back on the BrowseTitles layout and then forward again, the new Editions record doesn't appear in the portal - again, this doesn't surprise me as there's no entry in the TitleID field.

                          Filemaker does ScriptTriggers but they are not neccesary because the Edition record you create in the portal is autimatically linked to the Title record.

                          You might have done something funky with your relationships or used fields from the wrong table occurence, but filemaker works pretty darn good in that regard.

                          Google tells me that ScriptTriggers come in in version 10 - I've only got 9 here. Before I tell the boss I need to upgrade, I'd have to be sure that it would work. Also, others in the office are also on version 9 so the database, presumably, wouldn't work for them if it relied on ScriptTriggers.

                          Hehe, believe me as a former Access user when I say: "You aint seen nothing yet!!" You are not even close to reaching the limitation of filemaker with what you are trying to do.

                          I'm sure I'm not close to scratching Filemaker's limitations but it does seem to me that where, in Access, you can pretty much do anything you like via VBA, Filemaker limits you very severly to, what, a hundred-off Script commands. Of course, that must be read in the context of me being a beginner. I'm sure it's not as hard as it seems at the moment. I certainly haven't seen anything that Filemaker can do that Access can't though.

                          I don't really understand this one. Why would you make a new record in a random table?

                          I didn't mean totally random - I meant in a specified table. There is a New Record/Request script command, but it only lets you create a record in the current table and I want to create a record in a different table.

                          I've attached a jpg of some of my BrowseTables layout. Bear in mind that it's very much in development at the moment - it'll look nicer when I'm done.

                          On the grey block is data that applies to all editions of a book. On the pale green block, data that only applies to one or other version - the hardback, the paperback or ebook, etc.

                          What I'd like to happen is that the user will search or scroll through the Title records. When they find the one they want, they'll see a list of the available editions in the portal at the bottom of the left hand grey block. The data for the first of these will already be in the green block in the middle. By clicking on the second, third, ... line of the portal, the data in the green block will change to show the data for whatever version has been chosen.

                          When a new Title is added, I would assume that at least one new Edition record will also be required. The portal will, obviously, be blank as there is no ISBN and no format at this stage but that's OK. I can go ahead and fill in all the details - those in the grey block and those in the green block for the first edition we're publishing - usually the hardback.

                          When the time comes to issue the book in paperback or, say, for the Kindle, I'd like to click on the "Add new format" button to blank out the fields in the green box ready to receive the data for this new edition. The next time I scroll onto this record in layout, I'd expect to see the new edition in the portal along with the existing hardback.

                          Hope that makes it clearer. It doesn't seem like a huge problem, but I just don't see what Filemaker expects me to do to create that new Editions record (for the green block) when I click the Add New Format button.

                          Thanks.

                          • 10. Re: Showing data from related records on a layout
                            AlastairMcInnes

                            Our posts got crossed - I'll have a look at what you sent. In the meantime, here's the relationship diagram for my database:

                            The SelectedEdition table is duplicate of the Editions table as per Phil's initial suggestion.

                            • 11. Re: Showing data from related records on a layout
                              GuyStevens

                              Just make sure your portal is based on Edition. And the fields in the portal are from the Edition table.

                              That your fields in the green area are based on the Selected Edition table.

                              And that your button in the portal sets the Edition::ID value to the Titles::SelectedEdition field.

                              You shouldn't be to far of.

                              • 12. Re: Showing data from related records on a layout
                                AlastairMcInnes

                                I checked all those things and they're OK.

                                I hadn't thought about putting buttons on the portal itself - I was seeing it as just a display list but maybe that's what I'll have to do. Your version seems to be reasonably close to what I'm after. It took me a moment to think of clicking on the green blob but that would just be a training issue for the end users.

                                Once again, thanks for all the trouble you've taken with this.

                                • 13. Re: Showing data from related records on a layout
                                  GuyStevens

                                  Maybe you are right in saying that it's not yet entirely what you want. Maybe we can make it a little bit more elegant.

                                  I removed the 'green blob'  :)

                                  And I removed the ability to create new records from within the portal (unchecked the "allow creation of records.." )
                                  That way you don't have that one extra empty record at the botttom.

                                  And I made a button that creates the new edition.

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

                                  The script attached to the button is pretty simple. Because we need to go away from the title layout we need to set the Title ID as a variable (we need to remember it) so we can use it later. A variable always has two settings you need to specify:
                                  - It's value, in this case Titles::ID
                                  - A Name. Always starts with a "$" sign or "$$" for a persistent variable that can be used after the script ends.

                                  Then we need to go to the Editions table but we'll freeze the screen so we don't need to see that.

                                  There we create a new record for the new edition and we set the TitleId variable ($TitleId) in the Editions::TitleIdFk field. That way it's linked to the Title.

                                  Then we need to set the EditionId as a variable because we want to set that one as the selected edition so we can fill in it's data.

                                  So we return to the Title layout and set the $EditionId variable in the SelectedEdition field of the Title table.

                                  Then we go to the SelectedEdition::Edition field so we can immediatly fill in the edition.

                                  Let me know if this is better.

                                  • 14. Re: Showing data from related records on a layout
                                    AlastairMcInnes

                                    I can see why you're called DaSaint - I can't thank you enough for that. It's pretty much exactly as I wanted.

                                    I had thought that I'd have a "Delete format" button next to me "Add format" but (a) it didn't work and (b) I like your idea of having a delete button on each row much better - it's clearer exactly what's going to be deleted.

                                    I had one more niggle. It's important that the ISBN isn't blank. I changed its validation to be "Not Empty" but this screwed up the script (I think at the second Goto Layout step).

                                    What I've done is added steps to set a value of 1 into the field when the record is created and then set it back to blank just before it's shown to the user. This seems to be doing the trick, though at first it allowed the user the option to save the record anyway - changing the options in the Field Validation dialog to prevent the user being allowed to override the validation seems, finally, to have got the thing working the way I want.

                                    Once again, many, many thanks. This is part of my first foray back into IT after many months of unemployment and I was beginning to wonder if I was going to have to turn the job down for lack of Filemaker knowledge. You've basically saved my bacon. 

                                    Laughing

                                    1 2 Previous Next