Having an issue with getting a self-joining relationship lookup to work properly and was hoping someone could add some insight to or maybe have a better way to solve the problem without using the lookup.
First the background:
The input field, 'countryin', is the original input. 'country' is a calculation which checks 'countryin' and corrects/changes it based on rules and related fields. In a large database, 20,000 to 50,000 records, the sort and find can take a long time beause the field ('country' in this example) is a calculation that is not able to be indexed becuase it references a related field. The solution was to create another field 'country2' which does a lookup based on a self-joining relationship. The self-joining relationship is a calculation which is: "Get(RecordID) & country." The recordID would ensure uniqueness and country changing would tirgger the lookup. Then the sort and find would be done on the field 'country2', instead of 'country'. Becuase 'country2' is a simple text field, it would be indexed and therefore the sort and finds would be much faster.
The lookup never finds a exact match.
If the lookup is set to exact match or do nothing, the result is nothing. If the lookup is changed to "next lower" or "next higher" it does the lookup, but from the wrong record. Perhaps it is a timing issue where the lookup is attempted before the calculation is completed and saved?
Any insight or solution?