Question about linking records via relationship but by other fields
I feel like this is a basic question but after using FM Pro 13 for 2 days and reading the missing manual, I don't seem to see this anywhere. Here's what I have in a super basic example with my data from Salesforce. 2 tables:
ID (Comes from Salesforce, Primary Key, looks like CBD00343492A. This is set to auto fill as serial number for new records)
Account Name (Company name, so Jerry's Plumbing for example)
ID (Comes from Salesforce, Primary Key, looks like ODB3482387B. This is also set to auto fill as serial number for new records)
Account_ID (The ID field from Accounts to link the records together)
So I have a relationship set linking Account's ID field to Account_ID in Opportunities. I uploaded both files, and they are linked properly and relate info. If I have a portlet under Account that shows Opportunities and allows the creation of records, creating a record there links it to the account as expected.
Where I'm lost though is if say I wanted to change an opportunity's account after it has been made. The only way I can figure out to do that, is by having "Account_ID" as a field visible on opportunity, and then manually changing it to the correct Account ID. What I'm trying to do is have the field on opportunity that shows "Account Name", reflect the name of the account based on the ID link. That way if I want to change it from "Jerry's Plumbing" I can change it to "Bob's Widgets" in the account field, and it then links them using their IDs.
I tried adding the Account Name field from Account to Opportunity, which I thought worked, but then learned that is just showing that field on the record, meaning if I change the account name this way, it actually modifies the account record. What I'd ideally like is the Account Name field under opportunity to be a text field you type in, that validates info against "Account Name" field in Accounts, so that you can only select an existing account, and then have the option where it fills in as you type.
Not sure why I'm so lost here but mind is blank. Thanks so much!