Unique field based on certain criteria

[S Molly T]

Fields:

client

[Helpful Harry]

When using related fields in calculations only the first related value is ever used (unless you're using a Portal on the layout, in which case the values from the "current row" are used), so for your example "Value4" is going to be the one used since that's what is first.

What you can do is set the relationship to sort on the Status field instead. That way "Value1" gets sorted above "Value4" and it will be this top-sorted related status "Value1" that's used in your calculations.

If the values don't sort naturally with "Value4" at the bottom (or at least not the top) you can create a value list that can be used only for the sort order in the relationship definition.

By the way, "not equal" can either be typed as [color:"green"]<> (both here and in FileMaker) or by typing [color:"green"] [ ne ] (without the spaces) in your message here.

[S Molly T]

Interesting.

I sort of understand what you are telling, but I am not really sure I follow.

While troubleshooting this thing, I have noticed that always the same value is found in the lookup with regards to status, I was not really sure why this is, but I what you say makes more sense now.

I will try what you say.

Thanks.

[Helpful Harry]

Sorry, you obviously read my first reply while I was still editing it to read more clearly. Try re-reading it now and see if it makes more sense - if not just ask.