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.

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, 200718 yr by Guest
Flipped my inequality

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, 200718 yr by Guest
I meant greater than or equal to, too!

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

Ender,

Thank you for your help.

Have a good day.