First, we need to be careful with terminology. You can create a portal to Events on a layout based on Episodes. You cannot create a portal to Events in the Events table. This is an important distinction to keep in mind as you learn how FileMaker functions.
You need a relationship like this:
Episodes::EpisodeID = Events::EpisodeID
EpisodeID should be an auto-entered serial number in Episodes and a number field in events. You create this in Manage | Database | Relationships by dragging from EpisodeID in one of these two occurrence boxes to EpisodeID in the other. If you want, you can then double click this relationship line and enable "Allow creation of records via this relationship". If you do this, you can use the portal we are about to create to add new Events records and have them be automatically linked to the current Episodes record.
Now, if you have FileMaker 11, we can set up a filter for this portal so that you can select pr emter a City from into a field and see only the events related to the current episode record that show that City that you selected/entered.
Add a field to Episodes, SelectedCity, you can give it global storage if you want--this is a good idea if this database might be shared over a network.
go to the Episodes layout and use the portal tool to add a portal to events on that layout. In the portal setup dialog select Portal filter and enter this expression:
IsEmpty ( Episodes::SelectedCity ) or Events::City = Episodes::SelectedCity
Finish setting up your portal and then add the SelectedCity field to your layout. Use a script trigger set on this field to perform this short script to force the portal to update:
Refresh Wincow [Flush Cached join results]
The specific trigger to use depends on the format of your selectedCity field. Edit boxes and drop down lists should use OnObjectSave. Radio button and pop up menus should use onObjectModify.
Thank you for the response. I have set all of these pieces in place but no information is populating the portal. Perhaps something is wrong with the design? Here are the screens that create this portal.
What I see looks correct so the trouble lies elsewhere in parts that I cannot see.
First check to see what you get if you remove the portal filter. Do you see all the related event records for a given episode if you remove the portal filter expression by clearing its check box?
If so, the issue lies in the filter expression. make sure that the two city fields are both text.
If not, the issue lies in the relationship, make sure that the two fields used as match fields in the relationship are both the same type--usually number.
Thank you for the continued help. I really appreciate it. The portal does not pull without the filter, which must mean there is an issue with the relationships. I have built the relationships as follows:
Episodes is the parent for event (which is events 3 in the relationships visulaization because of other relationships). The two are connected by the primary key for episodes which is a generated, indexed serial number created on creation of a record. The foreign key for episodes (which is in the events table) is just indexed but both fields are set as "numbers." I can take photos of all this if that is helpful. Thanks again!
Two things to check:
1) make sure that both fields are the same type. Auto-entered serial numbers are usually number, but the records will match correctly as long as both are the same type.
2) confirm that the Event records have values that actually match the parent Episode records. Even a single character difference, such as a space character, could keep them from matching. You may want to set up a table view of Events and check this field--possibly performing a find for specific serial numbers to see if they can be found.