Equijoin with calculated date field

[UTexas80]

I would appreciate any suggestions regarding how to create a relationship between a calculated date field [Get(CurrentDate) + 60] to a corresponding date field in another table. I am interested in finding all records that have an expiration date that is effective 60 days from today. Does the calculated date field have to be formatted as a global field in order for the relationship to work?

Thank you.

[Ender]

Hey Tex,

The calculate date field would need to be unstored (should be by default) and would be on the parent side of the relationship. If you use an = relationship, you would only find those records that expire on that date. This could be a problem if there's no one around on the weekend to check for soon-to-be-expired records.

I'd suggest using a range relationship instead. Maybe:

Parent <=> Child =

Parent::cTodayPlus60 >= Child::ExpirationDate

This would show records that expired on or before 60 days into the future.

Edited August 13, 2007 by Guest
Flipped my inequality

[bcooney]

It would be helpful to know the names of the two tables, but it doesn't matter. It does matter whether the expiration date is a stored date in table2. If it isn't, a relationship is not possible, since unstored calcs cannot be used on the child side of a relationship.

Anyway, you have two choices, use a relationship as you requested or use a script that performs a Find.

For a relationship, create an UNSTORED calc field:

SixtyDaysOut = get (currentdate) + 60 result is Date

relate it to table2's expiration date. Perhaps show the records in a portal on table 1 using this relationship to table2.

However, I'd suggest a greater than or equal to relationship, because you won't see the records if you miss a day of looking.

For a Find script in table2, just

Go to a layout based on table2

Enter Find Mode

SetField expirationDT to ">=" & get (currentdate) +60

Perform Find

Edited August 13, 2007 by Guest
I meant greater than or equal to, too!

[UTexas80]

Thank you for the explanation. Table1, Financial Data is additionally defined as a self-join relationship. Table 2, dateTable contains associated reporting dates.

[UTexas80]

Ender,

Thank you for your help.

Have a good day.