13 Replies Latest reply on Dec 6, 2011 10:41 AM by SarahGaby

    Relational Field Assistance Needed

    SarahGaby

      Title

      Relational Field Assistance Needed

      Post

      Hello Filemaker community,

       

      I have built a relational database which I have taken several pictures of below. My issue is that I want to limit the field options in the drop down menu to contain only those which match based on the city. In other words, in the events drop down menu I would like to see only events that occurred in Tampa, for instance. I have no idea how to make this work. I have tried creating duplicate tables and linking them to the city table but that does not work. Any assistance would be awesome. Someone on here gave me the code to make my portal behave this way (IsEmpty ( Events::Selected_City ) or Individual_Event::City = Events::Selected_City) Can I do something like this with this with the fields in the table? If I can send more information or take screen shots of some other part of the database, please let me know. Thanks again!

       

      Screen_shot_2011-11-03_at_8.10.17_PM.png

        • 1. Re: Relational Field Assistance Needed
          LaRetta_1

          Hi Sarah,

          What you seek is a conditional value list.  Here are a few links, both by Comment (one of the top developers in this business).  Both of these examples are self clearing:

          http://fmforums.com/forum/topic/40834-clear-related-value-lists/page__p__190602#entry190602

          http://fmforums.com/forum/topic/52209-conditional-value-list-with-self-clearing-fields/page__p__330569#entry330569

          Added:  Self clearing means that if a User changes their mind on their first selection, the secondary field clears. 

          In fact, if you want a link to the best set of demos available in FileMaker today on how to handle many of the specific problems that we all face, take full advantage of all the demos he has presented here:

          http://attic.fmforums.com/forum/userpage.php?uid/21152/

          • 2. Re: Relational Field Assistance Needed
            SarahGaby

            Thanks! I will work on getting this up and running and follow up if I have more questions.

            • 3. Re: Relational Field Assistance Needed
              SarahGaby

              Alright, I have been working on this and have made some progress. I can make the first field match, that is name of person is now depenedent on city, but I can not make the event and episode fields match. I simply get a "no values defined" response. Does this have to do with my relationships? I built a values list for actor name which says to take values from the table with individuals and use related values from "individual event," which is the current table. When I follow the same proceedure for the event, i get the error. Please let me know how I can do this. I see it working in the examples on the blogs you shared but I can't figure out what I am missing to get it working in my own databse. Thank you again!

              • 4. Re: Relational Field Assistance Needed
                philmodjunk

                Your first screen shot shows a layout named "Individual Event" and the second, in the background, shows one labeled "Organization_Event". Do these correspond with the table occurrence boxes of the same name in your Relationships Graph that you've posted?  I would assume so, but need to be sure.

                Since each of these layouts refer to different tables, what works on one may not work on the other.

                It looks like you need two relationships to the same table. One to show related data for a selected organization or individual and one for each layout that serves as the relaitonship for your city based conditional value list.

                You have this relationship:

                Organization_Event::City = Organization::City

                which works for a conditional value list.

                You need to add:

                Organization_Event::OrganizationID = OrganizationByID::OrganizationID

                To create the second relationship, you select Organization in Manage | Database | relationships and then click the duplicate button (button with two green plus signs). You can then double click this new occurrence of the Organization table to bring up a dialog box where you can rename it as I show in my example.

                Then you can use a conditional value list based on the first relationship to select an OrganizationID, but the value you select then matches to an organization record via the second relationship.

                If "table occurrence" is a new concept, you may want to read/do this tutorial: Tutorial: What are Table Occurrences?

                • 5. Re: Relational Field Assistance Needed
                  mgores

                  Are you trying to use City as the factor for Events?  You said it works for individuals but not events.  From your diagram Individual_Events is tied to Individuals by City, but it is tied to Events by _keyEvent.  If you are trying to use City for your conditional value list you may need another occurance of Events linked to Individual_Events by city and base the list on that relationship.

                  • 6. Re: Relational Field Assistance Needed
                    SarahGaby

                    Hi Mark, 

                     

                    That is what I am trying to do. I would like the individual/organization and the event to both be lists specified to a city. I created a duplicate relationship (see attached) but it is still giving me "no values defined" for the event name. The values list I created as follows:

                    use values from events first field "Event_name" second field "city." Include only values starting from "individual_event." Is that the right table to choose? I tried all the others, too but none made the match pull a list. Thanks for the help with this!

                     

                    Sarah

                    • 7. Re: Relational Field Assistance Needed
                      SarahGaby

                      Hi again filemaker community,

                       

                      I now have the conditional values lists working, but the portals I am using to displace the actors at an event are no longer working. When I build the relationship to city in order to have a conditional values list, the portal then pulls in any individual that is set to be in that given city. What I need to do is have a conditional values list that gives me the options for individuals based on the city match, but then have those actors who are at an event that matches the event name in the table pull in to that portal. Does that make sense? Please let me know what to do in order to have conditional requirements be different for two different parts of the database. I tried building two relationships between event and individual event as above, but that returns the no values message again on the event name. Any help greatly appreciated.

                       

                      Sarah

                      • 8. Re: Relational Field Assistance Needed
                        philmodjunk

                        You'll need two sets of relationships between the same tables. One set for your conditional value list and one for your portal as they display different subsets of related data if my skimming of this thread didn't miss a detail. You can create multiple relationships between the same two tables by creating extra table occurrences in Manage | Database | Relationships. You click a table occurrence box to select it, then click the duplicate button (two green plus signs) to make a duplicate of that table occurrence. This doesn't duplicate your data, it just creates a new way to refer to the same table. You can double click such a newly created table occurrence in order to change its name to something more descriptive than the original occurrence name plus a number.

                        You may find this tutorial useful in learning more about table occurrences and how they control the function of a FileMaker database:  Tutorial: What are Table Occurrences?

                        • 9. Re: Relational Field Assistance Needed
                          SarahGaby

                          I have tried to do this using the database design below. Nothing is pulling into my portal. The portal is set up as follows:

                           

                          Show records from: Individual Events 2

                          Filter portal records: IsEmpty ( Events::Selected_City ) or Individual_Event 2::City = Events::Selected_City

                          Then I have the field for Name of person in the portal with the following set up:

                          Individual_Event2::Name of Person

                           

                          I must be missing some link, but I'm not sure what it is. Thanks!

                          • 10. Re: Relational Field Assistance Needed
                            philmodjunk

                            Is the portal based on Individual_event 2 and the layout based on Events?

                            If you temporarily disable the portal filter, do you see any related records?

                            • 11. Re: Relational Field Assistance Needed
                              SarahGaby

                              The portal is located in the events layout, and is based on individual_Events2. 

                              If I disable the protal filter I do not see related records. If I remove the second copy of the table and just use the relationship between sity - I see all of the individual_names in every event.

                              • 12. Re: Relational Field Assistance Needed
                                philmodjunk

                                That indicates that you do not have matching data in the _keypEvents fields in the two tables. They should both be the same data type (usually number) and must store exactly the same data to match. You may need to examine the two tables on other layouts to see what data exists in these two fields to figure out why the records aren't linking by matching values in these two fields.

                                • 13. Re: Relational Field Assistance Needed
                                  SarahGaby

                                  Thank you so much! I Think i fixed it!