2 Replies Latest reply on Mar 7, 2013 3:12 AM by RayCologon

    1:n relationship: select related record to display

    danielangst

      Hello Folks,

       

      Fitrst, and i am sorry if this question has been answered here before, i think i might lack the proper search terms...

       

      i have a basic question about 1:n relationships in fm12 related to printing (and showing) related records.

      I have a table containing contacts (e.g. ID, Name) and a table containing adresses (e.g. ID, AdressLine1,ZIP,Town, Type, main) linked by ID. So this is a 1:n relationship, as every contact can have multiple adresses (home, business, etc.). Type denotes what kind of adress it is, and main is 1 when this is the main adress of the contact.

       

      now to the problem: When printing an adresslist or adresslabels, how do I choose which adress to print?

       

      1. Display adresses in a layout based on the adress table, perform find to only show Adress of Type

       

      but, this will only show contacts that have an adress of type (i 'fixed' this by adding a field denoting which adress is the main adress for a given Contact and display only those). Another problem is that when going from a selection of contacts to this layout the selection is lost if one does not use GTRR match found set, but when i then exclude the adresses i don't want to show (i.e. where main != 1), the found set is reset.

       

      2. Link the two tables by ID and main. this reduces the relationship to a 1:1 relationship.

       

      but, then i have only the one adress to display.

       

      3. Using portals with filters

       

      works great in layouts, but not so much for printing (sliding problems etc.)

       

      4. Create calculated fields in the Contacts table that represent the main adress

       

      like this: ExecuteSQL("SELECT AdessLine1 FROM Adresses WHERE ID=? and main=1";"";"";ID), for each column in adresses.

       

      Am I missing something? I figure this is a very common thing one would want to do in databases (i am pretty new at this) so there should be a more elegant solution, no?.

      At the moment I tend toward solution 4, but doesn't creating fields in the parent table defeat the point of having a database?

       

      thanks for your help in advance!

        • 1. Re: 1:n relationship: select related record to display
          danielangst

          another solution i figured out is to specify sort order in the relationship between contacts and adresses to sort adresses by the main field (in descending order). Like that FM will display the main record (of which there is only one) when i just add e.g. <<Adress:Adressline1>> or the field.

          but this lacks flexibility (like sol. 4 above)

          • 2. Re: 1:n relationship: select related record to display
            RayCologon

            Hi danielangst,

             

            There are a number of ways you might solve this, and you've alluded to a couple of them.

             

            However, bear in mind that you can have more than one relationship between Contacts and Addresses, and a GTRR can use one relationship (ie target TO) while showing the resulting records in a layout based on a different TO - providing both TOs represent the same base table.

             

            So another option you don't appear to have considered would be to have a relationship to a second TO that's based on ID and main, and use that TO as the source of related records for your GTRR, while showing the results in your existing layout based on the Address table.

             

            Failing that, you could use a variation on the approach you've mentioned as your option 1, but after the GTRR, use the Constrain Found Set[ ] command to reduce the displayed records to include only main addresses. This will retain addresses for the selected group of contacts only (as per the found set on Contacts that you performed the GTRR from) but leave only one (main) address per contact.

             

            There are numerous other options, but the above two should give you a place to start.

             

            Regards,

            Ray

            ------------------------------------------------

            R J Cologon, Ph.D.

            FileMaker Certified Developer

            Author, FileMaker Pro 10 Bible

            NightWing Enterprises, Melbourne, Australia

            Email: cologon@nightwingenterprises.com

            http://www.nightwingenterprises.com

            ------------------------------------------------