Is there a way to reduce the number of table occurrences?

[Dana G]

Attached are two images.

The Contacts.jpg is the relationship graph where I am creating multiple TOs in order to feed their contact information into a layout from another file.

The Layout.jpg is showing what I need the result to be. We enter the contact name into the layout. The company and phone number is pulled from the Contacts TO via the relationship between the contact name field and the assigned Contacts TO.

I'm wondering if there's a better way. My solution is riddled with these Contact TOs. I have to recreate them over and over every time I create a new layout that needs contact data. Can I create a global field somewhere to either (1) reduce the number of contact fields I have to create in every new table or (2) reduce the number of TOs I have to create to pull in the data.

Any help is appreciated.

Thanks,

Dana

[RalphL]

Use one of the SQL plugins & Filtered Portals. I was able cut out at half of the TO's in one of my solutions using SQL. Look at the FQL section of the Fourm.

[Rick Whitelaw]

I would advise against using names as match fields. As well, I'm not sure why you find it necessary to have all those TOs of the same table.

[Fitch]

First of all, why do you need a new TOG (table occurrence group) for a new contact layout?

That said, maybe a structure more like this:

Project -< ROLE >- Contact

The Role table would have the Project ID, Contact ID, and the role (engineering, planning, etc.). That way you wouldn't need all those separate fields and relationships. A project could have multiple roles, and a contact could have multiple roles for multiple projects.

And as Rick said, I'd use IDs, not names, as key fields for relationships. (What if two people have the same name? What if someone changes his or her name?)

[LaRetta]

Hi Dana,

I agree with Rick as well that IDs should be used for all relationships and that you need to change your structure as Tom indicates. The different roles you have hard-coded as fields (in Building Permitting) should be ROLE records. Then a join table will resolve the many-to-many and all of those duplicate TOs can go away with added benefit that your new ROLES table will have only a few fields ( to hold the role) compared to your now Building Permitting table with the (visible) 11 existing fields.

I would also bet that your Building Permitting file contains a lot of other duplicate fields/calcs used in support that can also (by the very nature of a new proper structure) be eliminated. In all, a restructure here would really simplify your solution and your life. That is the power of good relational structure.

ADDED...good for you in checking your structure. Many people get deep into a project before they find out their structure is incorrect and changing structure once it contains data can be more difficult.