I have a flag that should be set to yes if a job status is "NA" and the current date is within 15 days of a requested date (Estimated Delivery Date). The flag that I am setting via the calculation is also the "key" field in a relationship to another file where a user can view all exceptions. Any ideas on how to make these two mutually exclusive uses jive?

Thanks

Yes, i speak jive... (/Airplane quote)

When you say "exceptions" i take it you mean all jobs ((whose status is not NA) or (are not within 15 days)) ?

What version of FM do you have?

Jerry

Jerry -

I have FM6. I actually mean all jobs where Status is NA and the due date is within 15 days of the current date. Sorry if that wasn't clear...

JobStatus = "NA" and DateDue - Status(CurrentDate) < 16

is this what you mean?

So you set your calc field to "Yes" if it meets those two criteria (using Queue's calc from above). Have a calc text field in Exceptions which is equal to the value "Yes". Set a relationship between these two fields to access the data in Exceptions whenever this field is set to Yes.

Does this address the issue?

J

The calculation is correct however, the flag does not recalculate every day. For example, if the record is created with a due date that is 18 days away, the flag will be set to "N". Once the current date is 15 days away, I would expect the flag to be set to "Y", but it doesn't. I have to go in and do a copy/paste of the status in order to get it to calculate.

If I set the calculation to unstored, then I can no longer use the flag in the relationship...

You can use the calc as a left-hand side key, just not the right-hand side.