Relationship based on several date fields

[Enigma20xx]

I have,

 

Table A:
__pk_TableAID
cToday     Calculation (Date)     Unsorted, from TableA, = Get ( CurrentDate )

 

Table B:
_fk_TableAID
StartDate     Date     Indexed
EndDate     Date     Indexed

 

TO Relationship:
__pk_TableAID = _fk_TableAID
cToday ≤ EndDate
cToday ≥ StartDate
 

What I try to accomplish is to see in Table A, through a portal of Table B, the actual active records of Table B.

 

Let’s say customers' contracts. An active contract has a start date but not an end date. This relationship only works if EndDate has a date into it, but I need to leave it empty until I have an ending date.

 

Any advise?

[IT_User]

Hello Enigma,

 

You could make a calculation field for the end date where if the end date field has a date in it, the calculation would copy.  If the end date field does not have a date, then the calculation would fill in the date as 99/99/9999 or something.

 

Then replace the cToday ≤ EndDate with cToday ≤ EndDateCalculationField in the relationship.

 

HTH

[comment]

the calculation would fill in the date as 99/99/9999 or something.

 

Filemaker's dates go up to December 31, 4000.

 

 

 

What I try to accomplish is to see in Table A, through a portal of Table B, the actual active records of Table B.

 

Let’s say customers' contracts.

 

How many contracts - active or not - will a customer have overall? If it's not too many, perhaps you could use portal filtering instead.

[Enigma20xx]

Thank you guys. I was thinking on that comment (portal filtering). Maybe IsEmpty ( EndDate ) will work. TO relationship IDs only. I will try when I'll get back home. Thanks again.

[comment]

Maybe IsEmpty ( EndDate ) will work.

 

Actually, it should be:

Contracts::StartDate ≤ Get ( CurrentDate ) and  ( Get ( CurrentDate ) ≤ Contracts::EndDate or IsEmpty ( Contracts::EndDate ) )

 

 

[Enigma20xx]

Wow comment, your advise covers all possibilities.

 

At least I had the IsEmpty ( Contracts::EndDate ) part, that for a newbie is not that bad, lol.

 

Thank you very much.