Seemingly simple database question

[dmf109]

First, let me apologize if my question is hard to follow. I'm completely new to FM (and databases in general) and find explaining my question a bit...difficult.

I'm trying to set up a database for my company. We sell a product for use with septic systems that has a PO, an owner of the site the system is for, a designer who designs the system our product is a part of and an installer that installs the system our product is a part of.

I originally set it up with 3 tables:

Table 1 has the main project interface layout that has fields for the PO, the lot owner and their info, and our model and date installed, as well as a tab for the designer company and info and tab for the installer company and info.

Table 2 is for designers, and basically set up as a contacts database where each company is a record with all it's information.

Table 3 is similar to table 2, except it's for installers.

The way I had it setup was that Table 1 had two relationships; 1 for the Designer company field and the other for Installer company field.

I setup portals so that choosing the designer/installer company from a pull down brought in all the information for those entities (address/phone/etc).

This works well.

My problem: I want to condense the 2 tables for designer/installer down into one general contacts table. The reason being that one company may do both designs and installs. Also, some of our project sites are owned by management firms, and so it would be nice to be able to access this firm's company via a pull down like I have with designers/installers. Therefor, I think it best to have all contacts on one table with a check box field showing their discipline. Then I could have that one contact table shared with the project table.

My idea is to still have the tabs on the main project layout and still have the user choose the management/designer/installer company name by choosing from a pull down field under the respective tab. But, my portals no longer work, meaning none of the information is brought into the tab view from the contact table.

I think this is a relationship issue. I used to have the Table 1 fields 'Designer Company Name' and 'Installer Company Name' link to the 'Company Name' field in the 2 respective tables. Now I figure I'll have the 3 fields on Table 1: Management Company Name, Designer Company Name and Installer Company Name, each on it's respective tab. Then each of those 3 fields would have a relationship back to the Company Name field on the Contacts table.

So, the user would go to the respective tab on the Projects layout on Table 1, then with the pulldown choose the correct company, then have all of that company's info brought in.

The end result would be a layout on table 1 showing the Project Number, our model sold, the site information (site address, owner and owner info), a tab showing the management firm (if different from the lot owner), a tab showing the designer company and info, and a tab showing the installer company and info.

What am I doing wrong with this new structure? It seems very simple, especially given that I had it working fine when I had separate tables for each discipline. Perhaps my entire setup is wrong?

I'll try explaining better or include some screen shots if need be.

Thanks in advance.

[Ocean West]

This is a need for supertype/subtype structure for adding the the records to a single table but allow you to have a separate tables for the types - there are a few methods for employing this but this is one I am setting up - I am sure some others will chime in here. too.

types.fp7.zip