script to auto-add new entries to foreign key field in join table ?
This seems like sort of a complicated question to me but I'm hoping someone will be able to understand it:
As part of the database for our architecture firm I have two tables and associated layouts: Showrooms and Products (furniture, materials etc). I wanted to be able to assign multiple showrooms to each product (places where that product is available for purchase), so I created a join table with three fields (__pkJoinTableID, _fkShowroomID and _fkProductID) and put a portal to that join table in the Products layout. The portal contains the _fkShowroomID field in a drop-down list populated by values from two fields: _fkShowroomID and the showroom names from the Showroom table.
So that's the set-up, and the problem is this. Every time I add a new record to the Showroom table, in the Showroom layout, I have to manually add the primary key number of that new showroom to the _fkShowroomID field in the join table to get it to show up in the portal on the Products layout. Can someone help me figure out how to avoid this? I was thinking I should write a script to auto-add new entries to the join table, but maybe there is an easier / better way to resolve it?
Screenshot of the portal attached, not sure if that's helpful.