3 Replies Latest reply on Sep 9, 2012 10:42 AM by philmodjunk

    Relationship Selection Question (from an old FP6 developer)



      Relationship Selection Question (from an old FP6 developer)


           Greetings - it's been a long time since I used FPro and I'm likely to have a project on it soon so I've been experimenting with the trial.  I've searched the forums/online but likely been using out of date keywords for the below (which I used to remember how to do...and somewhat remember...but I'm hoping there's an easier way  :)  ).

           Basic structure below

             Table 1: People

             Table 2: Animal

             Table 3: Adoption

           Table 1 & Table 2 will have the normal name and other info fields as well as a sequence number.  Table 3 is the join between the two tables to show who adopted which animal (and has a date of the adoption). I want the relationship to be based off of the sequence number per usual (e.g. allow duplicate animal names).

           User goes to the adoption table, creates a new record, and "picks" the person and the animal and the sequence numbers are hooked up behind the scenes.

           I vaguely recall having to do some complex scripting which involved layout switching, perform finds, copy (to get the sequence number), paste it back into another hidden layout (via freeze screen), and then switching back to the original Adoption Layout which can now display the related information in a portal.  Is this still the main approach to abstracting away unique identifiers from the end-users or has this simplified over the years?

           Thank you



        • 1. Re: Relationship Selection Question (from an old FP6 developer)

               You are describing a classic many to many relationship. No scripting then or now is absolutely necessary for such a relaitonship, though a few scripts sometimes help make the process a  bit more userfriendly or trap for errors.


               People::__pkPeopleID = Adoption::_fkPeopleID
               Animal::__pkAnimalID = Adoption::_fkAnimalID

               IF the above notation is unfamiliar and you can't figure parts of it out, see this thread: Common Forum Relationship and Field Notations Explained

               The above setup, however, isn't needed if an animal only needs to be linked to one People record. (I don't know if this is an animal shelter adoption or a zoo or wild animal "adoption".)

               Assuming that you need this structure, you can enable "Allow creation of records via this relationship" for Adoption in the People to Adoption relationship and then you can place a portal to Adoption on your People layout and use a drop down list or Pop up menu of Animal ID's and Names to select an animal to adopt. Selecting an animal in this field creates a new record in the adoption table and automatically links it to the current People record.

               To see a demo file of many to many relationships that illustrates this set up as well as some more sophisticated approaches, download this demo file: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7

               This is a .fp7 file so if you are using FileMaker 12, open it from the FileMenu instead of by doubleclicking it and FMP 12 will create a converted copy of the file and open it.

          • 2. Re: Relationship Selection Question (from an old FP6 developer)

                 I was familiar with the above - the challenge is the # of records will likely exceed what is reasonable to use in a drop-down or pop-up menu (and I would want the pop-up/drop-down to be able to apply filters - and last time I checked the standard value-lists don't allow conditionals although I might be able to overload that using the "start from a relationship" option).  So the scripting/etc. falls into the category of "make it easier" which is essentially a wizard (although if I can use scripts to define a value list on activation of a field...then that would probably work).

                 It is a resuce organization and prefer to use a true many to many vs. repeating fields/lookups as an animal hopefully has only a single adoption record - however sometimes the adoptions do not work out/etc.

                 There will be an outer set of tables  as well (it will actually be people->application->adoption vs. people->adoption) as a person can have multiple applications, applications can be rejected or not and an adoption is actually the result of a decision upon an application.

                 Does the demo above include examples related to the "wizard" style/more user-friendly scenario as well?

                 Thank you




            • 3. Re: Relationship Selection Question (from an old FP6 developer)

                   I don't see any need for a "wizard" here, though it is an option that can be made to work. (Just seems way more complex than you really need.)

                   The previous demo file does show one way to list choices in a portal where you click a choice to establish a link to that record. Such a portal can be filtered to list animals that are members of a particular category.

                   There are a number of other methods that can be used to trim large value lists down to size.

                   Conditional value lists are one option. By selecting a "category" value in one field, the value list can then display only animals from that category. Thus, the user can select "Large Dog" and only see Large Dogs that are currently listed for adoption. This can even be a hierarchical process where maybe you select "large Dog" in one field, then select from a list of breeds that updates to only list large dog breeds and then you get a list of dogs of the selected breed in the third field.

                   Forum Tutorial: Custom Value List?

                   In the preceding tutorial, take a look at "option 1" as a way that you can have your list of animals automatically limit to only those currently available for adoption.

                   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.

                   There are also other options that can be used to make large value lists more manageable.

                   This demo file demonstrates several such "enhanced" approaches: https://www.dropbox.com/s/0pm1gdqcfi2ndpv/EnhancedValueSelection.fp7