Hi all-

I feel sort of stupid having to ask this but it is a rather new
situation for me and I find myself stumped.

Here is the relationship:

People --> Orgs --> PostalCodes

In the solution in which I've worked for many years, it started in the
old days so almost every table is in a separate file. In those old
days as many of you know, we had to create calculated fields in the
Org table to allow us to get the city and state of the org from the
people table. (FM couldn't handle data 2 or more relationships away)

But now I have this new project I am working on and I am putting the
above tables into a single file. I have the above relationship mapped
in the relationship graph and all relationships work fine in layouts.
I rather like not having to create those redundant calculated fields.

But yesterday I tried to export some people data. I wanted to include
their org, and their org's address. So I included in the export field
list the appropriate related fields.

But the export included very very much extra data, like every postal
code record several times.

Can anyone point me in the right direction? I bet I have a
relationship wrong, but they all look good to me. I relate the people
record's orgID field to the orgID of the org, then the postalcode
field of the org to the postalfield of the postalcode table, easy
right? But apparently not!

Thanks

Paul Bruneau wrote:
> Hi all-
> 
> I feel sort of stupid having to ask this but it is a rather new
> situation for me and I find myself stumped.
> 
> Here is the relationship:
> 
> People --> Orgs --> PostalCodes
> 
> In the solution in which I've worked for many years, it started in the
> old days so almost every table is in a separate file. In those old
> days as many of you know, we had to create calculated fields in the
> Org table to allow us to get the city and state of the org from the
> people table. (FM couldn't handle data 2 or more relationships away)
> 
> But now I have this new project I am working on and I am putting the
> above tables into a single file. I have the above relationship mapped
> in the relationship graph and all relationships work fine in layouts.
> I rather like not having to create those redundant calculated fields.
> 
> But yesterday I tried to export some people data. I wanted to include
> their org, and their org's address. So I included in the export field
> list the appropriate related fields.
> 
> But the export included very very much extra data, like every postal
> code record several times.
> 
> Can anyone point me in the right direction? I bet I have a
> relationship wrong, but they all look good to me. I relate the people
> record's orgID field to the orgID of the org, then the postalcode
> field of the org to the postalfield of the postalcode table, easy
> right? But apparently not!
> 
> Thanks


Why not just create  a  OrgID field in the People table, and hard code 
the values. You are going to have to do that in the final normalised 
version anyway.

Having address and org name in People is arguably 
redundant/duplicative/non-normalised anyway.


If you want the org/address data in People export for reassurance 
crosschecking create the required new fields in people and hard code in 
the related data,  before exporting.

On Mar 29, 7:18=A0pm, Chris Brown <cbr...@medicine.adelaide.edu.au>
wrote:
> Paul Bruneau wrote:
> > Hi all-
>
> > I feel sort of stupid having to ask this but it is a rather new
> > situation for me and I find myself stumped.
>
> > Here is the relationship:
>
> > People --> Orgs --> PostalCodes
>
> > In the solution in which I've worked for many years, it started in the
> > old days so almost every table is in a separate file. In those old
> > days as many of you know, we had to create calculated fields in the
> > Org table to allow us to get the city and state of the org from the
> > people table. (FM couldn't handle data 2 or more relationships away)
>
> > But now I have this new project I am working on and I am putting the
> > above tables into a single file. I have the above relationship mapped
> > in the relationship graph and all relationships work fine in layouts.
> > I rather like not having to create those redundant calculated fields.
>
> > But yesterday I tried to export some people data. I wanted to include
> > their org, and their org's address. So I included in the export field
> > list the appropriate related fields.
>
> > But the export included very very much extra data, like every postal
> > code record several times.
>
> > Can anyone point me in the right direction? I bet I have a
> > relationship wrong, but they all look good to me. I relate the people
> > record's orgID field to the orgID of the org, then the postalcode
> > field of the org to the postalfield of the postalcode table, easy
> > right? But apparently not!
>
> > Thanks
>
> Why not just create =A0a =A0OrgID field in the People table, and hard cod=
e
> the values. You are going to have to do that in the final normalised
> version anyway.

I'm not sure I am following what you are saying.

I already have orgID in the people table, in order to support the
relationship between a person and his org.

I'm not sure what you mean by final normalized version, it is already
normalized. But I am trying to export people, along with some related
information, to an excel file to provide to a mailing service.

> Having address and org name in People is arguably
> redundant/duplicative/non-normalised anyway.

Yes, that's why I don't have them in there. I must not have been
clear. Sorry about that.

> If you want the org/address data in People export for reassurance
> crosschecking create the required new fields in people and hard code in
> the related data, =A0before exporting.

I'm sorry, I must have really not described things well.

My solution is well normalized, but when I try to export the data, I
get weird results. I get many multiple records consisting just of
postal code information, for no apparent reason.

Paul Bruneau <paul@ethicalpaul.com> wrote:

> I wanted to include
> their org, and their org's address. So I included in the export field
> list the appropriate related fields.
> 
> But the export included very very much extra data, like every postal
> code record several times.
> 
> Can anyone point me in the right direction? I bet I have a
> relationship wrong, but they all look good to me.

Make sure you're on the correct layout when exporting, and check on
which TO this layout is based on.
-- 
http://clk.ch

ethicalpaul@gmail.com wrote:
> On Mar 29, 7:18 pm, Chris Brown <cbr...@medicine.adelaide.edu.au>
> wrote:
>> Paul Bruneau wrote:
>>> Hi all-
>>> I feel sort of stupid having to ask this but it is a rather new
>>> situation for me and I find myself stumped.
>>> Here is the relationship:
>>> People --> Orgs --> PostalCodes
>>> In the solution in which I've worked for many years, it started in the
>>> old days so almost every table is in a separate file. In those old
>>> days as many of you know, we had to create calculated fields in the
>>> Org table to allow us to get the city and state of the org from the
>>> people table. (FM couldn't handle data 2 or more relationships away)
>>> But now I have this new project I am working on and I am putting the
>>> above tables into a single file. I have the above relationship mapped
>>> in the relationship graph and all relationships work fine in layouts.
>>> I rather like not having to create those redundant calculated fields.
>>> But yesterday I tried to export some people data. I wanted to include
>>> their org, and their org's address. So I included in the export field
>>> list the appropriate related fields.
>>> But the export included very very much extra data, like every postal
>>> code record several times.
>>> Can anyone point me in the right direction? I bet I have a
>>> relationship wrong, but they all look good to me. I relate the people
>>> record's orgID field to the orgID of the org, then the postalcode
>>> field of the org to the postalfield of the postalcode table, easy
>>> right? But apparently not!
>>> Thanks
>> Why not just create  a  OrgID field in the People table, and hard code
>> the values. You are going to have to do that in the final normalised
>> version anyway.
> 
> I'm not sure I am following what you are saying.
> 
> I already have orgID in the people table, in order to support the
> relationship between a person and his org.
> 
> I'm not sure what you mean by final normalized version, it is already
> normalized. But I am trying to export people, along with some related
> information, to an excel file to provide to a mailing service.
> 
>> Having address and org name in People is arguably
>> redundant/duplicative/non-normalised anyway.
> 
> Yes, that's why I don't have them in there. I must not have been
> clear. Sorry about that.
> 
>> If you want the org/address data in People export for reassurance
>> crosschecking create the required new fields in people and hard code in
>> the related data,  before exporting.
> 
> I'm sorry, I must have really not described things well.
> 
> My solution is well normalized, but when I try to export the data, I
> get weird results. I get many multiple records consisting just of
> postal code information, for no apparent reason.
> 

Hi Paul

had the wrong end of the stick...

I just tried on a simple 2 table medico and related postcode suburbs, 
and know what you are experiencing. 100 (medico) records results in 307 
medico+postcode|suburb records exported; the starting point is a one to 
many rel (based on postcode|state), and the source of the problem.


I recall experiencing something like this 5 years ago, I think I created 
a tag field in the related tables, valued it using the existing (1:m) 
rel (or variant), then created  a new rel to replicate the existing plus 
the extra constant::tag thereby creating a 1:1, then used that for export

On Apr 1, 4:17=A0am, Chris Brown <cbr...@medicine.adelaide.edu.au>
wrote:
> ethicalp...@gmail.com wrote:
> > On Mar 29, 7:18 pm, Chris Brown <cbr...@medicine.adelaide.edu.au>
> > wrote:
> >> Paul Bruneau wrote:
> >>> Hi all-
> >>> I feel sort of stupid having to ask this but it is a rather new
> >>> situation for me and I find myself stumped.
> >>> Here is the relationship:
> >>> People --> Orgs --> PostalCodes
> >>> In the solution in which I've worked for many years, it started in th=
e
> >>> old days so almost every table is in a separate file. In those old
> >>> days as many of you know, we had to create calculated fields in the
> >>> Org table to allow us to get the city and state of the org from the
> >>> people table. (FM couldn't handle data 2 or more relationships away)
> >>> But now I have this new project I am working on and I am putting the
> >>> above tables into a single file. I have the above relationship mapped
> >>> in the relationship graph and all relationships work fine in layouts.
> >>> I rather like not having to create those redundant calculated fields.
> >>> But yesterday I tried to export some people data. I wanted to include
> >>> their org, and their org's address. So I included in the export field
> >>> list the appropriate related fields.
> >>> But the export included very very much extra data, like every postal
> >>> code record several times.
> >>> Can anyone point me in the right direction? I bet I have a
> >>> relationship wrong, but they all look good to me. I relate the people
> >>> record's orgID field to the orgID of the org, then the postalcode
> >>> field of the org to the postalfield of the postalcode table, easy
> >>> right? But apparently not!
> >>> Thanks
> >> Why not just create =A0a =A0OrgID field in the People table, and hard =
code
> >> the values. You are going to have to do that in the final normalised
> >> version anyway.
>
> > I'm not sure I am following what you are saying.
>
> > I already have orgID in the people table, in order to support the
> > relationship between a person and his org.
>
> > I'm not sure what you mean by final normalized version, it is already
> > normalized. But I am trying to export people, along with some related
> > information, to an excel file to provide to a mailing service.
>
> >> Having address and org name in People is arguably
> >> redundant/duplicative/non-normalised anyway.
>
> > Yes, that's why I don't have them in there. I must not have been
> > clear. Sorry about that.
>
> >> If you want the org/address data in People export for reassurance
> >> crosschecking create the required new fields in people and hard code i=
n
> >> the related data, =A0before exporting.
>
> > I'm sorry, I must have really not described things well.
>
> > My solution is well normalized, but when I try to export the data, I
> > get weird results. I get many multiple records consisting just of
> > postal code information, for no apparent reason.
>
> Hi Paul
>
> had the wrong end of the stick...
>
> I just tried on a simple 2 table medico and related postcode suburbs,
> and know what you are experiencing. 100 (medico) records results in 307
> medico+postcode|suburb records exported; the starting point is a one to
> many rel (based on postcode|state), and the source of the problem.
>
> I recall experiencing something like this 5 years ago, I think I created
> a tag field in the related tables, valued it using the existing (1:m)
> rel (or variant), then created =A0a new rel to replicate the existing plu=
s
> the extra constant::tag thereby creating a 1:1, then used that for export

Thanks Chris, I think I know what you are saying.

I used to have problems like this when I was working with sql
sometimes but it's in my foggy memory.

How is this issue not all over the online forums I wonder? I think I
have set everything up as FM says it should be set up. Hmm..

anyway I'll work on trying tricks to change the relationship type.
Thanks again.

On Mar 31, 7:54=A0am, c...@tele2.ch (Christoph Kaufmann) wrote:

> Make sure you're on the correct layout when exporting, and check on
> which TO this layout is based on.
> --http://clk.ch

Thanks Christoph. Those are good ideas but I had already had my eye
out for those "gotchas".

I do find maneuvering around to be on the correct layout for what I
want to do one of the more annoying aspects of the multi-table per
file mode. I never had to worry about it before.

On Mar 28, 10:59=A0am, Paul Bruneau <p...@ethicalpaul.com> wrote:
> But yesterday I tried to export some people data. I wanted to include
> their org, and their org's address. So I included in the export field
> list the appropriate related fields.
>
> But the export included very very much extra data, like every postal
> code record several times.
>


Have you tried sorting by people ID and then grouping the export by
People ID?

G

>>
>> I just tried on a simple 2 table medico and related postcode suburbs,
>> and know what you are experiencing. 100 (medico) records results in 307
>> medico+postcode|suburb records exported; the starting point is a one to
>> many rel (based on postcode|state), and the source of the problem.
>>
>> I recall experiencing something like this 5 years ago, I think I created
>> a tag field in the related tables, valued it using the existing (1:m)
>> rel (or variant), then created  a new rel to replicate the existing plus
>> the extra constant::tag thereby creating a 1:1, then used that for export
> 
> Thanks Chris, I think I know what you are saying.
> 
> I used to have problems like this when I was working with sql
> sometimes but it's in my foggy memory.
> 
> How is this issue not all over the online forums I wonder? I think I
> have set everything up as FM says it should be set up. Hmm..
> 
> anyway I'll work on trying tricks to change the relationship type.
> Thanks again.

Hi Paul,

I tried the suggested sort by parentID (medico), and exported Grouped by 
parentID (medicoID) with the related PostCode fields included in the 
export; works for me.

Thanks Grip and Chris. I'll try that. I had looked at that setting but
of course for my past, I had always used it exclusively for grouping
sorted fields for summaries and so I ruled it out. I look forward to
trying it, thanks again.