5 Replies Latest reply on Oct 19, 2011 9:49 AM by philmodjunk

    Research Project Design



      Research Project Design


      I have a very complecated schematic for my database with the purpose of studying the way that several things connect together. I have having a few issues. The most pressing issue is how to create a portal in a table of "episodes" (a period of time in which many events occur) that pull events from an events table (one event/table) and matches the events in the portal based on the city field that appears in the events and episodes table. In other words, I want to be able to look at all the events in a given episode for Jacksonville, Florida. I tried using the sort function to accomplish this within the portal but no luck. I need to do a similar thing with actors at a given event. Any advice on how to do these sorts of complex filters would be greatly appreciated.

        • 1. Re: Research Project Design

          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:

          Commit Record
          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.

          • 2. Re: Research Project Design

            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.

            • 3. Re: Research Project Design

              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.

              • 4. Re: Research Project Design

                 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! 

                • 5. Re: Research Project Design

                  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.