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

    Relationships, Globals, and 'or'



      Relationships, Globals, and 'or'


      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.







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

          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'

            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.




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

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

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

                Just a single date.

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

                  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