3 Replies Latest reply on Jul 11, 2012 3:49 PM by philmodjunk

    Portal relationship to global field

    VeronicaDaigle

      Title

      Portal relationship to global field

      Post

      I am creating a layout which shows any records that have been modified in a date range specified by the user. Since I want to show a summary for multiple tables in one spot I figured the easiest way to do it would be to make a portal for each table, and each table has a field called "InRange" which is a calculation field that is equal to "Yes" when the ModificationDate (an autoentered calculation field for each record) is within the date range selected by the user. This part of the solution is working perfectly. 

      So then I made a new table called Activity which my summary layout is based on. The only field in this table is a global field called "InRange" which is a calculation field set to always equal "Yes". I then made a relationship between this global InRange field and each of the InRange fields in the tables I want to display the modified records from. So theoretically, whenever the user selects a date range, all of the records that were modified in that date range become related to this global field, and show up in the portals on the Activity summary layout. But right now nothing is displayed in the portal, even though I have checked that the modified records do indeed have a value of "Yes" in their InRange field.

      I figure it must have something to do with the relationship being based on a global variable, or the way the calculaion fields are stored. I tried creating a record in the Activity table, and making the InRange field not global (but still always equal to "Yes" to see if that was the problem, and that didn't work. I tried to make the InRange fields in each table into a stored calculation, but it said I could not because it made reference to a global field (the user defined date range). I figured all of the InRange fields in the tables needed to be calculation fields so that they would automatically update, and when I try making them into a text field with a calculation they do not update correctly as I suspected.

      Can anyone see offhand where I am going wrong, or is there another approach I haven't thought of that might work better?

      Thanks

        • 1. Re: Portal relationship to global field
          philmodjunk

          You are on the right track as to why it doesn't work. Your InRange calculation must be stored and therfore indexable or they can't work on the "many" or "chlid" side of a relationship.

          You'll need to modify your approach.

          If you were to define two global date fields for specifying the beginning and end of the date range, your relationships could match these global fields to the modification date field--which is stored and indexed and thus will work:

          Activity::gDate1 < YourTable::ModificationDate AND
          Activity::gDate2 > YourTable::ModificationDate

          You can edit the details of a relationship by double clicking the relationship line linking your two table occurrences.

          You may find that it takes longer than acceptable to get your screen to update when the user edits the date range. If so, you can use an alternative approach to specify the date range as a return separated list of dates and use relationshps based on this pattern:

          Activity::gDateList = YourTable::ModifictionDate

          Either a script or a custom function can be used to take the date range entered by the user and put the list of dates into gDateList.

          • 2. Re: Portal relationship to global field
            JimMac

            Your date list idea sounds nice.  How does "Activity::gDateList = YourTable::ModifictionDate" check the range in the two dates separated by returns?  Or does the script do the checking?

            Jim...

            • 3. Re: Portal relationship to global field
              philmodjunk

              That's a very good question.

              If the user enters 1/1/2012 in gDate1 and 1/13/2012 in gdate2

              gDateList would need to contain:

              1/1/2012
              1/2/2012
              1/3/2012
              1/4/2012
              1/5/2012
              1/6/2012
              1/7/2012
              1/8/2012
              1/9/2012
              1/10/2012
              1/11/2012
              1/12/2012
              1/13/2012

              That why you need a script or custom function to generate the list of dates.

              This works because a list of values acts like a limited "join" table when used as a key in a relationship. Any one value in the list can match to the value on the other side of the relationship. If you really want to go off the wall with this concept, you can use lists of values on both sides of the relationship and then any one value in one list can match to any one value in the other. A kind of "OR" logic to the relationship. Note that a set of checkboxes can also produce a list of values separated by returns so this is sometimes useful in FileMaker.

              I do not, however, recommend that such a list of values be used as a substitue for a full up join table except in very limited cases such as this one where we need to match against a discrete range of values in a relationship.