5 Replies Latest reply on Jun 27, 2010 7:52 AM by comment_1

    Relationships, Globals, and 'or'

    daveslc

      Title

      Relationships, Globals, and 'or'

      Post

      I need to create a relationship on two tables - Events and SummaryInfo

      The first has events with a start date.

      But, sometimes the field with the start date is empty (e.g. an ongoing event).

       

      I need a relationship that includes all events with dates in a given date range or events that have a blank "" start date and allows me to access related records from Events in the SummaryInfo table occurence.

       

      I created a calculation field (EventRangeCalc) that returns a 1 or a 0 depending on the above condition.

       

      But, since I am using a global for the start and end date and am using this in the calculation, I cannot establish a relationship between the two tables.  (The crows feet don't connect.)

       

      The relationship I am using is SummaryInfo::One to Events::EventRangeCalc  where One is a global that is set to 1.

       

      Are there any tricks to circumventing this?

       

      The problem, I think is that I have a global on one side and a calculation that uses globals on the other side.  But, I don't see a way to circumvent this.

       

      I may try a script instead, but a calculation would be better if it's possible.

       

      thanks

      Dave

       

       

       

        • 1. Re: Relationships, Globals, and 'or'
          comment_1

          Try defining a calculation field in the Events table (result is Date) =

           

          Max ( StartDate ; 1 )

           

          Use this field for the relationship instead of StartDate.

           

          • 2. Re: Relationships, Globals, and 'or'
            daveslc

            This gets me a calculation in Events that returns a 1 if the event date is "" or the date.  This is a start.

             

            But, I need to get related records from Events to the SummaryInfo table that are within a global date range and also those records that have "" (no date).

             

            I don't see how to use this new calculation field for the relationship.  Filemaker relationships only seem to support 'and', but not 'or'.

             

            Please let me know if I am missing something.

             

            thanks,

            Dave

            • 3. Re: Relationships, Globals, and 'or'
              comment_1

              Do your events have a StartDate and an EndDate? Or just a single date?

              • 4. Re: Relationships, Globals, and 'or'
                daveslc

                Just a single date.

                • 5. Re: Relationships, Globals, and 'or'
                  comment_1

                  Something is amiss here: if there's no end date, why is there a start date?

                   

                   

                  Assuming you want the relationship to include events whose start date is within a given range AND events whose start date is empty:

                  1. In the Events table,  define a calculation field cStartDate (result is Date) with the above calculation =

                  Max ( StartDate ; 1 )


                  2. In the SummaryInfo table, define a repeating calculation field cRange (result is Date) =

                  Let ( [
                  i = Get ( CalculationRepetitionNumber ) ;
                  n =   Extend ( gEndDate ) - Extend ( gStartDate ) + 1 ;
                  d = Extend ( gStartDate ) + i - 1
                  ] ;
                  Case (
                  i ≤ n ; d ;
                  i = n + 1 ; 1
                  )
                  )

                  Set the number of repetitions to the maximum expected days in a range + 1.


                  3. Define the relationships as:

                  SummaryInfo::cRange = Events::cStartDate