Thanks for the post.
I think maybe it's my turn to be slow, but let me ask a couple of questions first before I write something foolish (some would say it's too late for that).
1. If CSE Name is unique, and you're creating a new record with a unique CSE Name, where are you trying to get the CSE phone from? What would this new, unique field be linked to?...and how? (but perhaps that's your question...)
2. If you are duplicating a record to make the new record, isn't the phone# already there? If you're duplicating a random record just for the other 63 fields and then replacing the CSE Name and CSE number, where would you need to look to find the number that you'd like inserted into this record's CSE number field? Another, unrelated, CSE number field?
Again, please forgive me if I'm being blind...I'm just trying to understand what you're after.
I have finally learned how to link two fields together so when one is filled in the other populates with the associated data. Thanks to Rosemary of FileMaker.
Here goes the problem and the solution.
Problem: I have a flat database containing over 4,00 records with over 65 fields. Two of the fields are Engineer names and Engineer phone numbers. I wanted the phone field to auto populate after I entered the engineers name. Self-join does not work. It took a Relationship, but I was missing a very important step.
Solution: I created one new table - an Excel file containing two columns: Engineers and Phone numbers and imported it as a new table "AXA Engineers".
I went to manage database and created a relationship between "Engineer" in the existing table (Main Table) and "Engineer" in the new table (AXA Engineers). This was where I got in trouble. I needed to perform one more step.
I went to the main details page (Browse Mode) as I usually do to view my records and selected Layout mode. I double-clicked on the Engineer phone field and selected, from the pull-down, the new table and then highlighted Phone number and saved. Eureka! It works.
To answer Ninja's qusetion "If you are duplicating a record to make the new record, isn't the phone# already there? ". These are records containing equipment data and who is responsible for the service. Depending on the type of equipment there may be a different engineer. I only use duplicate because over 2/3rds of the data will remain the same. This way I only have to make a few changes to create the new record (i.e. equipment type and who is responsible).
Thanks to all that assisted in my journey Big KUDOS to Rosemary