AnsweredAssumed Answered

1:n relationship: select related record to display

Question asked by danielangst on Mar 7, 2013
Latest reply on Mar 7, 2013 by RayCologon

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!