Calculates Value From A Relationship

[MarkWilson]

Is it possible to enter a calculated value based on a relationship?

Number field -> Calculated value...

If( Min(SelfJoin::Quote#)=Quote# and not IsEmpty(Job#) , 1 , 0)

If I define it in a calculation field it returns correctly. If I define it as a number field with the calculated value it will not return 1 when a unique Job# is entered.

Thanks.

[-Queue-]

You can actually shrink the calc and use

SelfJoin::Quote# = Quote# and Length(Job#)

It will return a boolean, as desired.

It sounds like you are defining an auto-enter value, which will not update when the record is changed. Only a calculation or lookup will update.

[MarkWilson]

Correct.

All me to clarify a little further.

Min(SelfJoin::Quote#) - is based on a Job# SelfJoin.

I could have Quote# 6345 (base contract), 6453, and 6534 for Job# 2000.

I want Quote# 6345 to return True, for the base contract, when I enter the Job# in its Job# field.

A few weeks later when I am asked to quote an extra to the job and create Quote# 6453 and/or 6534 I want them to return as False.

In my experiments, I have notice that entering the Job#, in the record that should return True, and using Replace..., before leaving the field, I can trigger the validation. Also, if I use Replace... to reset an already existing Job# in the true record, it will trigger the validation. The third method is to reenter the Job# in the True record.

SelfJoin::Quote# = Quote# and Length(Job#) works, but every value returns True with a Job#.

It appears that a one-to-one relationship will trigger the validation and a one-to-many relationship will not unless used with Replace...

I realize that a calculated result will not update unless the field is reset to blank. The first quote is the awarded base contract amount 90% of the time, so I want to simplify the entry for my users.

[-Queue-]

Min( self::quote ) should return the same as self::quote, assuming the lower numbered quotes would be entered first. FileMaker assumes the first related record to be the first one entered (unless a sort order is specified for the relationship). So if the first one is indeed the lowest number, it should be the same. If a sort order is defined, then your Min may be necessary, although you could duplicate the relationship and remove the sort for your purposes.