== Below, configuration tested, but as far i remember it never worked whatever the configuration ===
Product and version FMPA 17
OS and version macOS 10.12.6 and Windows 10
Hardware iMac (Retina 5K, 27-inch, Late 2014)
When you set-up auto-enters lookup, they will never work if the record in the source table is an uncommitted new record, that mean Get(OpenRecordState) =1.
It seem really buggy since the workaround, placing a Commit Record just After the New Record step, is working even if the field to lookup is still uncommitted when the lookup occurs.
Also, if you set up an Auto-enter formula with an ExecuteSQL it will work correctly even if the if the record in the source table is in open state 1.
Conclusion : in this trivial case, the FQL engine is more reliable than the FileMaker schema.
How to replicate
Download attached file and push the test Button.
Workaround (if any)
See description above.
I know this bug for a long long time but since one customer of mine opened a ticket for it, i decided to take the time to report it one time for all. This persistant weakness is really perplexing regarding to the actual sophistication of FileMaker platform (IMHO).