Extra records generated in related table
I am having a problem with extra records being generated via portal which I think may be caused by the structure of the database, but I can’t see what exactly is causing the problem or how to fix it.
It’s an event management database with ? tables, and a connection to a contacts database. The first table has event details and is the ‘home’ table for the data entry layout. Connected directly to this table (with an event ID match field) are four tables with things like agenda, catering etc. They are all accessible in the data entry layout via portals (in tabs). These are all working correctly.
The second table is a Participations table, which connects to the events table through the event ID match field, and to the external contacts database through a contact ID match field. It also generates a unique ID number for each record of an individuals participation in an event. This table is also viewable/editable in the data entry layout through a portal.
The participation IDs are used as the match fields for two related tables, flights and accommodation. For any event, any given participant may have more than one associated flight or stay at more than one hotel, so each record in these tables has a trip ID or stay ID in addition to the participation ID (used as the match field), the event ID and the contact ID. Both are also accessible in the data entry layout using portals.
My problem is this: when I manually add a record to the flights or accommodation portal, a new, blank record is generated in the Participants table. The participation ID in the new Flights/Accommodation record matches correctly with an existing record in the Participants table (and finds the correct event and contact IDs based on this relationship) so it’s not the case that it’s generating a new record because it can’t find the appropriate one to relate to. Also, when I generate records via a script which goes to the basic layouts of the relevant tables to generate the records rather than doing it in the portal in the data entry layout, the problem does not occur, so I think it must be related to the way the portals are set up. Like the other portals, this one is accessible in a tab on a layout based in the events table (just to be clear, the relationships are: events – participants – flights).
Could anyone explain why I’m having this problem, and what I could do to fix it?
Thanks so much if you’ve read this far! I really appreciate your help.