1 2 Previous Next 16 Replies Latest reply on Sep 29, 2011 1:14 PM by philmodjunk

    Insert one db into another. Help needed.

    MEh

      Title

      Insert one db into another. Help needed.

      Post

      Hi everyone,

       

      I'm currently using Bento but I am making the switch to Filemaker Pro now. I just started exploring FMP and I am trying to re-create a database I made using Bento.

      it's a db of my fav soccer team. I have one db with all players and their data and I got one db with all matches of the team with results.

      In Bento I included the matches db into the players db so that on every single player record I can see all matches played. If a player scored a goal in that particular match, I can put a mark next to the match he scored a goal. In Bento I used a so called "related records" field. The matches show up as a sheet and I can set which columns I want to show and hide. Because I don't need all columns from the matches db, I 've hidden the ones I don't need. Besides that I can add custom columns to the sheet (which are not fields in the matches db) so this is where I mark how many goals the particular player has scored in that particular match.

      Now I've created both a players db and a match db in Filemaker but I can't seem to figure out how to make the matches show up in each player record. That's the first step. The ssecons one will be adding fields to mark if a player has scored in the particular match.

       

      I hope I made myself clear (English is not my native language) and I hope someone can point me in the right direction.

      What I already tried is Insert - Portal - Show related reccords from.... Because the matches db is an unrelated table (there's no relation between the players db and the matches db), I can't select it (all unrelated tables are greyed out). Then I did a little test with a related db but then I can't find how I can get the fields of the db to show up in my records. But maybe Insert - Portal isn't the right way to accomplish what I want.

      So some help is needed here. I hope someone is willing to help me out. I figured if it's possible in Bento, it should be in Filemaker Pro too.

      Thanks for your help

      Meh

        • 1. Re: Insert one db into another. Help needed.
          philmodjunk

          You need to use Manage | Database | relationships to define a relationship between the two tables. I must presume that Bento did this step for you as you refer to "related records" in your description of that process. In FileMaker you must set up this relationship yourself.

          Here's the most basic relationship you might set up, other options are also possible:

          Players::PlayerID = Matches::PlayerID

          PLayers::PlayerID would be defined as an auto-entered serial number so that all players have a unique Id number. Matches::playerID would be a number field with no auto-enter settings.

          In Manage | database | Relationships, you'd drag from PlayerID in Players to PlayerID in Matches to create this relationship. If you then double click the line this action creates, you can specify "Allow creation of records via this relationship" for Matches. With this relationship, you can now add a portal to matches on your Players layout. with the "allow creation" option enabled, you can create new records in Matches for this player by entering data in the bottom blank row of this portal.

          Portals and relationships are basic features of FileMaker that are used a lot. Reading up on them in Filemaker help and in any training materials you might have would be a very good idea.

          • 2. Re: Insert one db into another. Help needed.
            MEh

            Thx for your answer. But like I said there's no relation between the players db and the matches db. The players db has info on the players, the mayches db has info on the matches. What I want is to see is some kind of list of all matches that appear in the matches db to show up in each record of the players db. Then I want to be able to mark each match the particular player has scored one or more goals.

             

            To clarify:

            Players db fields:

            -first name

            -last name

            -fieldposition

            Matches db fields:

            -date of match

            -opponent

            -home / away

            -result

            So there's no way to point out a relation between these two databases.

            • 3. Re: Insert one db into another. Help needed.
              philmodjunk

              You'll need to define a relationship. There are many options for this. I've simply suggested the simplest one as an example of how this works.

              If you want to see the same list of all matches no matter which player record you have up on your player layout, go back to Manage | Database | Relationships and double click this relationship line that was created by the first relaltionship example. This brings up a dialog box where you can change the operator from the default = to X and now all matches records will be listed in your portal. You lose, however, the ability to add new records in the portal. If you need to do that, you'll need to add a button with a script that creates a new matches record in the matches table.

              • 4. Re: Insert one db into another. Help needed.
                MEh

                Here's a picture of how this looks in Bento. The left part of the sheet shows the date, Oponent, Home/away, Goals hometeam, goals away team. The right part shows the number of goals scored by the player who's record is shown. he left part of the sheet is the same on every player-record, the right part is different for each player-record.

                My first goal is to include the left partof this sheet (the static data of each match) into each player record of my Filemaker Pro database. Then I want (if possible) to add the left part of this sheet.

                • 5. Re: Insert one db into another. Help needed.
                  philmodjunk

                  That requires the X based relationship I just described. To get the list of goals down the right, you'll need another portal to another table where you record the goals for the current player for each game. This relationship would use the PlayerID type relationship I first described, but to a different table than "matches".

                  • 6. Re: Insert one db into another. Help needed.
                    MEh

                    If I want to create the relationship you suggested, I have to include all players that participated in each match in my match-database I assume? I see no other way to create a relationship between the two databases. Or is there any other way.

                    • 7. Re: Insert one db into another. Help needed.
                      MEh

                      The list of goals in my screendump is filled out manually. That's exactly how I want it.

                       

                      I simply can't accept that what I want is poosible in Bento but not in Filemaker. Can't this be done without a relationship? I only want to show data from one db into another db. I don't want to use the data in any way except for displaying it.

                      • 9. Re: Insert one db into another. Help needed.
                        philmodjunk

                        It's also done in Bento with a relationship. It's just done for you behind the scenes.

                        FileMaker is a much more sophisticated system that can do many things Bento cannot. It is not, however, set up to manage this kind of set up automatically. You have to understand how the system works and make settings manually during the design phase that bento did for you automatically.

                        You must use relationships and two related tables if you want to do this in FileMaker.

                        • 10. Re: Insert one db into another. Help needed.
                          MEh

                          When using a linked data field in Bento this only links two databases so you can display data from the one database into the other. It does not create a relationship. Like I said, these are the fields of the two databases in Bento:

                          Players db fields:

                          -first name

                          -last name

                          -fieldposition

                          Matches db fields:

                          -date of match

                          -opponent

                          -home / away

                          -result

                           

                          As you can see there are no matching fields, so there's no way that there is a relationship between the two databases in Bento. So using relationships is no necessity to do what I want in Bento. Either Filemaker can't do what Bento can or you don't know how to do this.

                          • 11. Re: Insert one db into another. Help needed.
                            philmodjunk

                            Bento does create a relationship or this would not work for you. It's just hidden from you.

                            Please read my earlier posts again. When you use the X operator, you do not need to have any fields with matching values to get the all the records to display in the portal. You can, infact select any field in one to match to any field to the other.

                            • 12. Re: Insert one db into another. Help needed.
                              MEh

                              I did as you said. Now I have all matches displayed in each player record. Now my second wish: I want to add the number of goals the particular player has scored to each game. If possible I want to add it to the portal. Like I said, in Bento I can add a field to the sheet and use the field to put in the number of goals manually. Do you have a suggestion how to do this in Filemaker?

                               

                              Thanks so far for your assistance. It's really appreciated.

                              • 13. Re: Insert one db into another. Help needed.
                                philmodjunk

                                The problem here, is that the goals are specific to the player where the matches records are not. You don't want to record a goal by "John Smith" and then see that same goal for the same match when you change to the player record for "Mark Jones".

                                My first suggestion is the simplest for you to set up, but is also the least flexible option.

                                In your portal, disable the scroll bar option--this is where "simple" may not work for you here, but if it does this becomes a workable option.

                                Add a field for goals to your Player table. When you add it to your layout, position it next to your portal, and format it as a repeating field with the same number of repetitions as you have rows in your portal. This will look like it's part of the portal, but you'll have different goal statistics for each player. The key limitation here is that you can work with only as many matches as you can display at one time in your portal. If you added a scroll bar option to the portal, you can now show more matches, but scrolling the portal does not also scroll the goals total and thus they won't stay aligned with each other.

                                FileMaker pursists will tell you not to use a repeating field like this and I would agree with them. But it has the advantage here of being simple to set up. The better, but more complicated alternative is to use a portal to a related table of goals instead of the repeating field.

                                It is also possible to set this up where the goals recorded are a field that is located inside the portal. And this makes it possible to use a scroll bar with your portal. This requires, however, a much more sophisticated relationship to set up. Since you are new to how FileMaker works, I've suggested something simpler to get you started. If you want to try out a more sophisticated option, let me know.

                                • 14. Re: Insert one db into another. Help needed.
                                  MEh

                                  Thx. I will look into this tomorrow (it's evening here now and an important soccer match is due in a few moments). I will get back to you. Thx very much.

                                  1 2 Previous Next