Many Too Many Problems

[glparker389]

Long-time reader, first-time poster …

I’ve been struggling for days on this one:

I have two main tables, CLIENTS and ORGS. A third table, RELATIONSHIPS, defines relations (memberships) between CLIENTS and ORGS. A fourth table, ADDRESSES, supplies contact info for all of the above.

Seems straightforward. But, CLIENTS have their own addresses. So do ORGS. When a CLIENT becomes a member of an ORG, I need to display both the CLIENT addresses and the ORG addresses – from the CLIENTS layout. I’ve been able to accomplish this through a portal. So far, so good.

The problem is when I try to tag one of the ADDRESSES as preferred and display this information in the portal. I’ve tried to do this by matching the ADDRESS serial number with a “preferred” field in CLIENTS. Because the portal is from the ADDRESSES table, and because it is linked via many-to-many relationships to the CLIENTS table, there’s no way to do so.

Any way around this?

Diagram attached. I’m happy to clarify.

Thanks,

glparker389

FMP_diagram.pdf

[CobaltSky]

...because it is linked via many-to-many relationships to the CLIENTS table, there’s no way to do so.

Hi glparker389,

There are a variety of ways of achieving this - though which method suits you best may depend on how your relationships are structured and how your interface works.

Attached are a couple of files showing examples of a couple of different methods. Hopefully thay will give you some ideas to work with. :wink2:

Addresses.zip

[glparker389]

Brilliant! The second example works perfectly. I didn't think to use a global variable -- guess that's why I'm an amateur!

Thanks a ton. My nonprofit is forever in your debt.

glparker389