That ole one company, many locations problem

[Sari]

I was so impressed with the last lot of help I thought I'd try for a bit more! Amongst others, I've got 3 tables: Company, Company location and Contacts. I've attached a pdf file of the relationships.

What I actually want to do is have a layout which will show each contact with the company and the appropriate address. I've managed to create a layout of each company with a portal showing each address but can't go the that last step.

Relationship200105pm.pdf

[RalphL]

NewCenturyData has a demo file called Separation Demo that solves this with a second portal that is tied in with the first. You select a portal row in the first and the data in the second portal changes to match that row. It envoles another table and a coupe of relationships. Try http://www.newcenturydata.com

[Ender]

You should relate Contact directly to Company by CompanyID, keep the relationship between Company and Location, and add a second table occurrence of Location and relate that to Contact by Location Ref.

What this gives you is:

A Company can have many Locations.

A Company has many Contacts.

A Location can be used by many Contacts for a Company.

On the Contact layout, you can choose the Location (use a conditional value list to show just the Locations for that Contact's Company.)

Then using the Location by Contact TO, show the related Location fields on the Contact layout.

[goldcougar]

If you are referring to a portal-in-a-portal type of problem there are 2 ways that I know of to go about it. One is described by RalphL. The other is to have your portal, and then create a regular calculation field, add scroll bars to it, and put it in the portal. Then make a value list that will show only the people in the company. Have the calculation for the field be ValueListItems( "myFileName"; "myValueListName" ). Then you have a portal that scrolls with the addresses for a company, and in each address a field that scrolls with all of the people in that address of the company.

Note: I've had problems with the refresh of the field using ValueListItems, so sometimes its easier to do a scripts that sets the field to ValueListItems( "myFileName"; "myValueListName" )