1 Reply Latest reply on Oct 27, 2010 11:29 PM by sunmoonstar.13

    Using a constant in a relationship



      Using a constant in a relationship


      I am wondering if there is an easier and cleaner solution than the one I am using for the following problem.

      I need to be able to create a relationship between tables where one of the fields is equal to a constant rather than a field in the other table.


      I have a table of Events.

      Each event in the table has a Day Of Week field (Monday, Tuesday, etc.)

      I have a Layout based on the Events table.

      I need to create a portal that shows just Monday events, another that shows just Tuesday events, etc.

      So, I have created a relationship (self-join in this case) for each day's events.

      The only way I can make this work is for EVERY record to have seven Auto-Enter fields which contain the names of the days of the weeks.

      Then, my seven relationships are like the following:    Events::Monday = Monday Events::Day Of Week

      It seems to me there should be a better way.  Is there one and I just haven't found it?

      If there is, I could eliminate dozens of mostly useless Auto-Enter fields from my database.

        • 1. Re: Using a constant in a relationship

          If you have FMP v11, you can use a single self-join relationship and portal filters, with no need for extra fields.

          In the Relationship Graph, create another occurence of your Events table and call it Events_2.

          Then set up a self-join relationship like so:


          Events::Day Of Week >----X----< Events_2::Day Of Week


          The X is a "cartesian" join, not the default = sign.

          Set up each portal to show related records from the Events_2 table.

          Then set up a filter on each portal:


          Specify first portal filter as Events_2::Day Of Week = "Monday"

          Specify second portal filter as Events_2::Day Of Week = "Tuesday"

          and so on...