1 2 Previous Next 16 Replies Latest reply on Aug 3, 2015 4:54 PM by MauriceG

    Displaying list in a portal

    MauriceG

      Title

      Displaying list in a portal

      Post

      Hello,

      With great help from this forum, I recently converted my Books DB from flat to relational, putting the authors in their own separate table (Using script to create records in join table) . My db structure is now Books----BooksAuthors----Authors.

      However, one issue  remains unresolved and this makes my db unusable for now. On my book layout, I replaced the Author field with a portal based on BooksAuthors. I'd like to be able to do two things in tnis portal when I register a new book:

      a) if the new book author's name is already in the Authors table, just select it from a rolldown list; or

      b) if the author's name is not already in the Authors table, simply type it in (and create a new entry in the Authors table in the process).

      I spent the last three days trying to resolve this but to no avail. Can anyone explain how?

      Thanks a lot.

       

        • 1. Re: Displaying list in a portal
          GuyStevens

          In your BooksAuthors you should have a AuthorIdFk field where you enter the ID of the author. Preferably by using a dropdown.

          This can also be done inside a portal so you can simply select an existing author.

          The dropdown should be created so that is fills in the ID in the AuthorIdFk field but displays the Authors name in the dropdown.
          I have  a video on how to do that here.

          What you do need there is to have the relationship between Books and BooksAuthors set up so that you are allowed to create new records in the BooksAuthors table through the relationship.

          Then to create a new author you could place a field on the Books table where you could enter an author name (preferably the first and last name in separate fields)

          Then a button could launch a script that could:
          - Perform a find to see if that author already exists in the Authors table.
          - If it does, show the author
          - If multiple authors exist with the same names, show a list to choose from
          - If none is found a new author is created and the name filled in. And the author is related to that book.

          This script can easily be made by writing down the steps that need to happen in order to do all of this. 

          • 2. Re: Displaying list in a portal
            MauriceG

            Thanks Guy,

            I watched your video and I've managed to display the list of authors in the portal field but it behaves strangely. I've put an ID field in the portal and a name field on top of it using the technique shown in the video. When I click on the portal row, what appears first is an ID number and then below that number is a list of the existing authors. If I try to select one author, sometimes it works, sometimes it doesn't. Not sure what is going on.

            To give you more info:

            1. my portal right now is based on the join table BooksAuthors. Is this ok?

            2. I have a value list that uses values from AuthorIDfk and AuthorName (in the table Authors) and shows values only from the second field.

            3. The ID field in the portal is in Find mode only. The Name field is a dropdown list and is in browse mode only.

            What you do need there is to have the relationship between Books and BooksAuthors set up so that you are allowed to create new records in the BooksAuthors table through the relationship.

            4. The relationships between Books and BooksAuthors and between BooksAuthors and Authors are both set up so as to allow for the creation of new records.

            Not sure what else I can add. Maybe you need more info to be able to help. I've been pulling my hair our trying to resolve this.

            • 3. Re: Displaying list in a portal
              GuyStevens

              1. That is correct.

              2. The value list should use the value from Authors::ID and Authors::Name (This will probably be a calculation field that conbines the first and last name into a fullname)

              3. The ID field should be in Browse mode only and be a dropdown value list. The name field should be an edit box that is only there to display the author name from the authors table. It should be in find mode only.

              4. You are only creating records in the BooksAuthors table so that's the only relationship that should have "Allow creation..." checked. But that really doesn't make a difference if the other relationship also has that option checked.

              • 4. Re: Displaying list in a portal
                philmodjunk

                3. is one problem. This should be done in Browse mode. Not Find mode. This will require changing the behavior settings on your ID field to allow Browse Mode access.

                • 5. Re: Displaying list in a portal
                  MauriceG

                  What values should the dropdown value list for the ID field use? AuthorsID only?

                  • 6. Re: Displaying list in a portal
                    GuyStevens

                    Think about it like this:

                    You are entering a value in the AuthorsIdFk field in the AuthorsBooks table. The value that you need in there is an ID. Not a name. 

                    But you don't know the ID's of the authors, you only know them by their names.

                    So the AuthorIdFK field that you are filling in needs the ID value. But for yourself you would like to see the actual Author names. 

                    So the Value List needs to have, from the authors table, as a first field the ID. But then as a second field the Author Name.

                    You can check the box to show only values from the second field so you don't need to see the ID's.

                    You can also type ahead in that dropdown box.

                    If you have your author names in separate first name and last name fields you might want to make a calculation field that combines the First and Last name into a fullname text.

                    • 7. Re: Displaying list in a portal
                      MauriceG

                      Thanks Guy,

                      I have not been able to resolve the issue yet. To help you perhaps figure out what may be the problem, here is a recap of my settings:

                      1. I have the following relationships: Books----BooksAuthors----Authors; Allow creation... checked in both relationships

                      2. on my book layout, an author portal based on the BooksAuthors table

                      3. a value list based on the Authors table (first value is AuthorID, second value is AuthorName, and Show value from second field only is checked)

                      4. in the author portal:

                                    a AuthorID field based on the Authors table with a dropdown value list, in browse mode only;

                                    a AuthorName field based on the Authors table, with edit box, find mode only, set on top of the AuthorID field as shown in yesterday's video;

                      I did more testing and maybe the following info will help you pinpoint the source of the problem:

                      1. if I click on the portal row of an existing record (records created before I converted the db from flat to relational earlier this week), the list of authors does appear. And if I try to replace the author in that existing record by selecting a different one from the list, it doesn't do the change but it does replace, in the BooksAuthors table and the Authors table, the current author ID with the ID of the author I selected. So the result is the two authors now have the same ID. So, in an existing rrecord, selecting a new author from the dropdown list results not in the existing author's name being replaced but in his ID being replaced.

                      2. in the existing records, the list of authors does get displayed although as explained in 1., I cannot effectively select one. However if I create a new record, the author portal is completely frozen in the new record. Nothing happens if I click on it.

                      Does this point to anything in particular?

                      • 8. Re: Displaying list in a portal
                        GuyStevens

                        Hi Maurice. There is a problem in your point 4. 

                        These are your tables:

                        - Books: A list of all the books.
                        - Authors: A list of all authors
                        - BooksAuthors: Where you make the relationship between an author and a book.

                        Your portal is based on the Books Authors table. That's correct. Because there you are relating an author to a book.

                        But you are putting the ID field of the Author table in there. That is not correct.

                        In the portal, based on the BooksAuthors table, you need to fill in the AuthorIdFk field.  

                        There should be a relationship already between the Books::Id field and the BooksAuthors::BookIdFk field. So if you are on the books layout and you create a related record in the BooksAuthors portal Filemaker wil automatically enter the BooksId value. Because the records need to be related. So the only other value you need to enter yourself is the AuthorIdFk.

                        So you need to put the Author IdFk field from the BooksAuthors table in that portal. That field needs to be the one that has the drop down value list we talked about earlier to allow you to select an Author from the Author table. Then you can display the Author name field from the related Author table on top there like the trick in my Youtube video. This allows you to display the name of the author instead of the ID.

                        So if you just put the correct ID field in your portal you should be fine. 

                        Just for testing don't put the fields on top of eachother. Have the ID field and the Name field separate.

                        The only problem in this scenario is that you can't create a new author directly in the authors table from this layout based on the books table because there isn't a direct relationship between Books and Authors. The BooksAuthors table is in between the two.

                        But a temporary field and a script can be used to create new authors and relate them to the book. Like I explained in my first post.

                         

                        • 9. Re: Displaying list in a portal
                          MauriceG

                          Hi Guy,

                          Thanks a million times. The authors' names arre now displayed just fine as a dropdown in the authors field.

                          On my book layout, I also have a portal that displays the author's other books, but sometimes one of these other books has a second author. I'd like to display the name of that second author in the portal but can't manage to do it. That portal is based on an occurrence of the Books table. Any quick explanation that would help me do it?

                          • 10. Re: Displaying list in a portal
                            philmodjunk

                            And this is a portal to which table? The join table or Books?

                            You can probably use ExecuteSQL to get what you need here as you can do a query for another join table record with the same bookID but not the same author ID.

                            • 11. Re: Displaying list in a portal
                              MauriceG

                              Hello Phil,

                              Here is what I put in place to display in this portal all the author's books other than the book on whose record I am.

                              Books----BooksAuthors----BooksAuthors2----Books2. Relationships are the following:

                              between BooksAuthors and BooksAuthors2  BooksAuthors::FkAuthorID = BooksAuthors2::FkAuthorID and BooksAuthors::FkBookID ≠ FkBookID

                              between BooksAuthors2 and Books2 → BooksAuthors2::FkBookID = Books2::BookID.

                               

                               

                              • 12. Re: Displaying list in a portal
                                GuyStevens

                                Hey Phil, I really don't think we should start suggesting Exectute SQL to somebody who is obviously a beginner. Some of the more hardened Filemaker Pro's don't even go near Execute Sql.

                                I think we should keep this as simple as possible. 

                                I just made a little test file and all I added was a simple calculation field in the Books table that would list the Authors:

                                List ( Authors::FullName )

                                The only problem with that is that it gives every author on a new line. 
                                So I added a substitute function to replace the return with a comma:

                                Substitute ( List ( Authors::FullName ) ; ¶ ; ", " )

                                Then in the portal based on the Books Authors 2 I added that List calc field from the Books 2 table.

                                Like you can see in this example

                                It doesn't have the names in two different fields, but I hope this might still work none the less.

                                • 13. Re: Displaying list in a portal
                                  MauriceG

                                  Thanks Guy,

                                  would you mind sending me your example in FM11 format.

                                  • 14. Re: Displaying list in a portal
                                    GuyStevens

                                    Sorry Maurice,

                                    I can't transfer this back to Filemaker 11. 

                                    You can let Filemaker upgrafe .fp7 files to .fmp12 files, but not the other way around.

                                    I would have to rebuild it but I don't have the time right now to do that.

                                    Maybe you could just download a trial version of Filemaker 12, 13 or 14. That way you could have a look at the file.

                                    Greetings Guy

                                    1 2 Previous Next