Self-Join to sort records that have an empty field

['makerphile]

I have two sets of data:  rankings (Name, Rank) and partner_selections (Name, Name_of_Partner if have one yet).  I want for each person to calculate the best-ranked potential partner among people who do not already have partners (i.e. among records in which Name_of_Partner field does not contain a value, sort for the best associated ranking).  

I can do this by using a calculation field to hold the pairing_status (1 if have partner, 0 if don't), create a global field "Zero_placeholder" that holds the value of 0, and then do a self-join where names are not-equal and Zero_placeholder = pairing_status and sorting for the highest associated ranking. 

I would be interested in knowing whether there are more elegant ways to accomplish what I want without using a self-join and/or a global field. Would I want to create something like a for-loop that goes through each record, tests to see if the partner field is empty, then alters the value of a "highest_ranked_unpartnered_person" field if appropriate?

Thanks

P.S. I solved some problems with indexing in my previous attached examples.

 

 

Zero_placeholder_test_stripped.fp7

Edited July 13, 2018 by 'makerphile
Some issues resolved

[bcooney]

We use a field ONE in every table. It's a number field with this auto-enter calc:

// Ensures that this field always reevaluates to 1

Let ( triggerOnChangesTo = Self ; 1 )

We use it during transactions to create/lock the record, and can also be used on the left side of a relationship to filter.

So, rather than a global 0, set this to be one the left of your relationship, and have it NOT = the pairing_status.

Oh, and sort the relationship by rank desc.