using formulae in match fields

[robertanderson]

Hi,

I'm trying to set up a relationship between two files, where the match field of related records will be "less than" the match field in the master file. E.g. if the match field in the master record is a date, I'd like to find all related records that have values in the match field that are before that date. The FM5 help says that match fields can contain a calculation formula (the example they give is "Unit price < 100.00") so it seems possible to do this, but I cannot for the life of me work out what it is the help file means! Has someone successfully implemented such a relationship?

Thanks

Rob

[LiveOak]

You can't use a field from another file in the calculation of a match field. You can do this:

In your master file (left side of the relationship), create a field:

Constant (calculation, number, indexed) = 1

In your related file, create fields:

gDate (global, date)

Date (date, indexed)

Index (calculation, number) = Date < gDate

(an "If" is not necessary, this calculates to a logical "1" or "0")

In your master file relate Constant <--> Index (in the related file). You can set gDate using another relationship from your master file.

Your other option is to create a field "LessThanDate" (text) in your master file and fill it with every date before the date you a looking for. For example, if your date of interest is 3/11/2001, set "LessThanDate" to a list of dates separated by carriage returns:

3/10/2001

3/9/2001

3/8/2001

.

.

.

(earliest date of interest)

and relate "LessThanDate" to "Date" in your related file. The relationship will display any records with "Date" less than "LessThanDate". The fill operation can be done with a script or a plug-in.

-bd