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

# Relationships, Globals, and 'or'

### 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'

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.

thanks,

Dave

• ###### 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