Self-Join not working as should?

['makerphile]

I have represented a bug in a simple exercise:  a field "previous_serial_number" in a table looks up the auto-entered serial number from the just-previous record in the table.  Record 2 should have 1 in the previous_serial_number field, for example.  Something is not working: record 2 has 2 (it's own serial number) in the previous_serial_number_field. 

I'm doing a self-join, with the match field being the serial number and the relationship being not-equal, sorting the matching records on the serial number in descending order.  Record 2 is not supposed to be matching with itself, but it is.  New records are listing their own serial numbers in the previous_serial_number field.  When the relationship is "<" the previous_serial_number field shows the correct values.  

Is this an issue of using Filemaker Pro Advanced 8 in Windows 10, a bad install, or am I not understanding something basic?

I have attached a stripped down example with one table, two fields, and one defined relationship, both where I have used not-equal and used "<".

Thanks

self_join_only_matching_other_records_in_table_ok.fp7

self_join_only_matching_other_records_in_table_fail.fp7

['makerphile]

The inability to use the self-join with not-= to look up the previous serial number appears to be based on the timing of when the self-join match criterion is evaluated relative to when the serial number field data is committed after creation.  Here is an even simpler example database to exhibit the problem.  The example database consists of one table and one field: serial_number, which is auto-entered upon creation.  A self-join is done where serial_number is not-equal to serial_number from the duplicate table.  Upon creation of the first record, as long as the cursor stays in the serial_number field for that record, a portal showing the serial_number field in the duplicate table shows 1, even though the first record is not supposed to satisfy the match criterion for the first record, which is not-equal.  But when clicking out of the serial_number field, 1 disappears from the portal.  Likewise, when the Nth record is created, which places the cursor in the serial_number field for that record, N shows up among the values in the portal.  When clicking out of the serial_number field, N disappears from the values shown in the portal and only values 1 through N-1 remain.

Oddly, when less-than is used as the match criterion, it is not necessary to click out of the serial_number field.  When record N is created, only values 1 through N-1 show up in the portal.

I have attached the puzzling simple example.

 

self_join_test.fp7

Edited November 10, 2017 by 'makerphile