Find not finding!!! Help!

[MoonShadow]

Vs. 6, WinXP (yeah, boring) -- I have a database of 288,000 lineitems. I have created a selfjoin relationship from Invoice# to Invoice# (text) and created a calculation called FindMax (number) with:

Case(Max(SelfJoin::cRecordID) = cRecordID, 1,0)

Where cRecordID is numeric calculation of: Status(CurrentRecordID)

When placing FindMax on a table layout, it displays a proper 1 on the last lineitem for each invoice and a 0 on the rest. I then placed the field on a null layout and performed a manual find and entering a 1 in the field but it produces all records! I have performed this same find three times with the same results!!!

The purpose of this calculation is to isolate the last of each Invoice#. It is a straight import in exact order required and hasn't been sorted or anything. I have re-checked the data types of all fields involved and verified that, when scrolling through the records, a 0 or 1 appears in the FindMax field. Maybe an indicator I should quit for the night ... strangest thing I've seen! I've used this same process many times with no problem. Is this file wonky or am I?

[ESpringer]

The only thing I can imagine is that you accidentally placed the *related* field on the layout, rather than the "Current File" version of the field. If you had placed the *related* field on your layout, FileMaker would behave just as you describe, no: each record has the 1 tag in the so-related record...

[MoonShadow]

Good grief. I can't believe FM tricked me like that. That's exactly what I did. It, of course, defaults to the last relationship selected and, yeah, the last relationship was the self-join. Okay. Time for a break.

How many of you have ever done that? Raise your hands! Still, an indicator that it's time to stop. Thank you for catching me.