Creating a new record in one table automatically creates in other tables. Or just bad database design.

[notinabottle]

Hello there!

I am working on updating my company's current filemaker pro database with some time saving features. Every project has its own record and I am mainly putting in drop down menus of vendor names that will auto populate several fields with the vendors contact information. We have three types of Vendors, will call them vendors A,B,C. A project can have up to two A vendors, 4 B vendors and 4 C vendors.

To Achieve this I have my main projects table, two tables for the A vendors(1st A Vendors, 2nd A Vendors), 4 tables for the B vendors and 4 tables for the C vendors with all the vendors as separate records. On my main projects table for the 1st A Vendor Name field I have created a relation to the 1st A Vendor Name table and used auto lookups to auto populate the corresponding fields with the contact information. For the 2nd A Vendor Name field I have a relationship with the 2nd A Vendor Name table. The other fields and tables for vendor name follow the same format.

This is where I start having trouble. All of the above work but we use IWP for the database. If there is an new vendor that needs to be added to the 1st B Vendors table I do not want people to have to enter that information on all 4 B Vendor tables.

Is there a way I can make it so that when entering a new record into the 1st B Vendors table it automatically creates new records in the 2nd, 3rd and 4th B Vendors tables??

Thanks you in advance. My efforts scrounging the internet have not proved useful enough for my brain to put together a solution.

[comment]

To Achieve this I have my main projects table, two tables for the A vendors(1st A Vendors, 2nd A Vendors), 4 tables for the B vendors and 4 tables for the C vendors with all the vendors as separate records.

IMHO, you should have one table for all vendors. Then, if you like, create multiple occurrences of the Vendors table, to accommodate multiple relationships between Projects and Vendors. Setting up a join table would probably be a better idea.