Using FM7pro and server. I am trying to achieve what I thought was easy at first. It even worked for a few days!!

I have a SITE database and a SERVICE database which has a Unique ID field called(ServiceID). In the Site Database I have a field (ID0) and a field (ID1) and a Calculation Field (ID) When the date for a 'service change' is equal to 'today' or older I want the (ID) field to display (ID1) else (ID0) This is the working calculation here -

"If ( (Service Change Date > Current Date) or (Service Change Date = ""); Service ID0 ; Service ID1 )"

BUT the relationship needs to use the Calculation field (ID) so that the service will change AUTOMATICALLY when the date has past BUT when I setup a relationship using the calculated field it fails saying "The relationship is invalid"

Please help me - maybe there is a better way to have a lookup automactically change when a DATE goes into the past.

If its any help the relationship is working if I use it in a lookup field rather than a related field.

the relationship just says (SITE::ID = SERVICES::ServiceID)

Thanks in advance.

Hmm, this looks like your 7th post.

Anyway, you cannot use an unstored calc as a match field. Unstored calcs are those that reference a global, related field, or get() function.

Before giving suggestions, I think we need to know why you'd use two different Service ID fields.