Portals with children next to portals whith grandchildren
I have a database with three tables: 'Days', 'Shows' to record performances given on various days and 'Acts' to record the various acts making up each performance. There is a one-to-many relationship from Days to Shows and another one-to-many relationship between Shows and Acts.
I need a single layout in which I could:
1) enter a new Show without manually specifying the primary key of the corresponding Day
2) enter a new Act without manually specifying the primary key of the corresponding Show
So I created a ‘Data entry’ layout showing records from ‘Days’. In it, I added a portal (A) showing related records from ‘Shows’. This satisfies requirement 1.
To satisfy requirement 2, I created in ‘Data entry’ another portal (B) showing related records from ‘Acts’. In the filtering options of that portal, I specified that the foreign key of an Act be equal to the primary of a Show.
I was hoping that a new record in portal A would automatically show corresponding records in portal B and, more importantly, make B add the right primary key to any new record added within it. Unfortunately, whatever I select in A, all records that I enter in B have the same foreign key, which is the primary key of the first record in A.
Any help to solve this situation would be greatly appreciated. W.