1 2 3 Previous Next 42 Replies Latest reply on Oct 17, 2013 11:39 AM by philmodjunk

    Take a find to a next step

    appt

      Title

      Take a find to a next step

      Post

            

           I have a perform find set up and working fine but I need to take it one step further.

           Lets say that you perform the find (in my case you are finding members of a particular cricket team). The find works and you can click backwards and forwards through the found set. 

           However what i want to be able to do is have a drop down list on a (coaching plan) layout that is populated after the find is performed with just the names of the players that are in that team.

           At the moment when I turn it into a pop up/drop down menu the only option seems to be to populate it with all records rather than just the found set.

           I need it to work like this. (I can do 1 - 3)

           1. Select a team. 

           2. Find only the players on that team.

           3. Go to a layout

           4. On that layout there are 5 different drop downs that must only be populated with the found names.

           5. The coach then selects one player per drop down so that you have 5 different players shown on the layout who are going to practice bowling during that session.

           Next session, you might choose a different 5. There is no reason to store that data beyond the session on the day.

           Hope someone can help as it is driving me nuts.

        • 1. Re: Take a find to a next step
          philmodjunk

               Do you have a field that identifies what team a player is on? Are players only members of a single team?

               You can set up a relationship that either matches a record in a Team table (one record for each team) to your players table or one that matches the current record in your Players table to another occurrence of the Players table such that it matches to all Players from the same team.

               Either relationship can be used to set up a conditional value list that only lists players from that team.

               4. I'd create just one drop down for that but put it in a 5 row portal such that selecting a player creates a record in a related table instead of setting up 5 individual fields for that.

               Here are my links for how to set up a conditional value list.

               There's a lot of overlap between the first two links so you can read one and skim the other. The demo file gives you several examples of different conditional value lists so you can examine them to see how they are set up. If you are using FileMaker 12, you can open this demo file from your File menu to get a converted copy of the demo file that you can examine in FileMaker 12.

               The last link discusses how to set up a chain of conditional value lists where the value selected in each conditional value list controls the values listed in the next value list.

               Forum Tutorial: Custom Value List?

               Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

               Demo File: https://www.dropbox.com/s/j6qf0z9fnem3uxd/ConditionalValueListDemo.fp7

               Hierarchical Conditional Value lists: Conditional Value List Question

               Feel free to post follow up questions here if those links aren't enough to get your value lists working correctly.

          • 2. Re: Take a find to a next step
            appt

                 Many thanks Phil, I will work my way through that lot today. 

                 I tried a million different ways of setting up relationships and tables last night all to no avail. 

                 To answer your questions

                 each player is a member of only one team and each team can have many players (I know how to set that bit up)

                 I want a third table called Practice, where details can be entered for a practice session. Each practice session can have many players but only from one specific team. But also each player or team can have many practice records. So the layout needs to allow me to select a team then select players from that team. I like the idea of a portal but when I tried it last night I could not get it to work. 

                 I know the issue is my lack of understanding of setting up relationships (for example will i need join tables to allow many to many relationships - I never seem to be able to get those to work properly) and my lack of understanding as far as setting up value lists is concerned. 

                 I know it's a learning curve so I will keep at it but I feel it ought to be easier than I'm finding it :/

                  

            • 3. Re: Take a find to a next step
              philmodjunk
                   

                        each player is a member of only one team and...

                   Good, then my advice applies without modification. If this had been a Many to Many relationship where a player can be on more than one team, the details get more complex due to the need for join tables.

                   

                        I want a third table called Practice, where details can be entered for a practice session.

                   I suggest that you set up two more tables: Practice and PracticeParticipation to produce a data model similar to this:

                   Players>----Team------<Practice------<PracticeParticipation>-----Players|Practice

                   Team::__pkTeamID = Players::_fkTeamID
                   Team::__pkTeamID = Practice::_fkTeamID
                   Practice::__pkPracticeID = PracticeParticipation::_fkPracticeID
                   Players|Practice::___pkPlayerID = PracticeParticipation::_fkPlayerID

                   Players and Players|Practice would be two Tutorial: What are Table Occurrences? with the same data source table.

                   You'd put a portal to PracticeParticipation on the Practice layout and format PracticeParticipation::_fkParticipationID in that portal with your conditional value list of players. You'd define the value list to list __pkPlayerID and PlayerName from Players, but specify the "include only related values starting from Team" option.

                   For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

              • 4. Re: Take a find to a next step
                appt

                     Couple of questions then, as I start to set this up. The ID fields are they all serial/no modification?

                     which way around do I want the allow creation of records, I think I know but would like clarification.

                     do I need then to connect the player|practice table to the player table using a playerID?

                • 5. Re: Take a find to a next step
                  appt

                       Apologies, have just realised what ok means is ignore first question!

                  • 6. Re: Take a find to a next step
                    philmodjunk
                         

                              The ID fields are they all serial/no modification?

                         They should be. That describes the basic requirements for a good primary key. (unique values that are never, ever changed)

                         

                              which way around do I want the allow creation of records, I think I know but would like clarification.

                         You don't have to allow this for any of the tables, it's an option. Add it anywhere that you need for a table occurrence that is used as the data source for a portal if you want to be able to enter data directly into the portal to create new related records. A typical example would be to set up a portal to players on the teams layout. Then you might want to enable this option for the table occurrence of players on which you base the portal.

                         

                              do I need then to connect the player|practice table to the player table using a playerID?

                         If you take another look at what I suggested, you'll find that I have mapped out doing exactly that:

                         Players|Practice::___pkPlayerID = PracticeParticipation::_fkPlayerID

                         Keep in mind that Player|Practice is just another reference to your Player table via an added Tutorial: What are Table Occurrences?.

                    • 7. Re: Take a find to a next step
                      appt

                           PracticeParticipation::_fkParticipationID is not mentioned in the layout diagram, it's a new field? Not linked to anything?

                      • 8. Re: Take a find to a next step
                        philmodjunk

                             Players>----Team------<Practice------<PracticeParticipation>-----Players|Practice

                             PracticeParticipation is a new table and _fkParticipationID is a match field defined in that table, used to match it to a record in the Practice table. This makes it possible to set up a portal to PracticeParticipation with one record for each player that is set up to participate in that practice.

                        • 9. Re: Take a find to a next step
                          philmodjunk

                               Good thing that I can read upside down! surprise

                               The primary keys should be a number. That's what I have specified for this from the beginning.

                               The field won't automatically receive a value. You'll need to put a value in that field when creating new records on this layout based on players. The typical way to do that is to use a drop down list or pop up menu with a value list that lists ID numbers from Teams in the first field and team names in the second.

                               There are also other options than that, but this is the simplest to set up and works fairly well for modest numbers of values.

                               BTW, there are ways to capture a screen shot without using your phone to take a picture. There's a print Scrn button for that on Windows and a three key combination for that in Mac systems. In windows, the image is copied to the clipboard so that you can open a graphics program such as paint and paste the image into it. In Macs, if  they haven't changed it, this generates a file on your hard drive that you can upload or edit and upload.

                          • 10. Re: Take a find to a next step
                            appt

                                 Cheers Phil, I'm so close I can almost smell it! 

                                 Got the pop up working so that when creating players you can assign a team, used the second value option and only display that value (hope tha was right). 

                                 Now on the team layout I have a portal that shows the team members! Yee ha, never managed to get that working before! Never understood the examples. 

                                 So the final step is the practice layout which at the moment  has a portal that refuses to be selectable or show any info. Ned to re-read all the stuff you have given me so far.

                                 oh and apologies for the screen shot, it wasn't upside down on my ipad!

                            • 11. Re: Take a find to a next step
                              appt

                                   Can you give me one last push and tell me what is missing from my table so that I can connect _fkParticipationID to a field that I think I haven't actually created yet?

                              • 12. Re: Take a find to a next step
                                philmodjunk
                                     

                                          it wasn't upside down on my ipad!

                                     it was good for a laugh. I get that all the time on my iPhone, the camera app automatically rotates the display so that your image is not upside down on the screen when the device is held upside down so you don't notice until you go to post or text it....

                                • 13. Re: Take a find to a next step
                                  appt

                                       Just one last push?

                                  • 14. Re: Take a find to a next step
                                    philmodjunk

                                         You have a table where you should have a table occurrence. Delete PlayersPractice from the Table tab in Manage | Database.

                                         Select Players on your Relationships tab and click the button with two green plus signs to make a duplicate table occurrence. Double click the new TO and rename it as PlayersPractice and link it to PracticeParticipation.

                                         This does not create a new table in your database. It creates a new reference to your Players table that you can set up in a different relationship than the original players to teams relationship.

                                    1 2 3 Previous Next