make sure the value list lists AccountID's and also shows account names as secondary field. Make it a popup. Otherwise you're filling in Account names in the FK, instead of ID's...
Thank you @siplus, so if you don't mind, I'd like to ask a question phrased a little differently:
How can I automatically populate a foreign key within a relationship? I am currently trying to auto-populate the foreign key by looking up the primary key in the related table - but I am not having any success. In a lot of the guides, I see you can manually match the primary key from one table, to the foreign key in another. However, as new records are created, is there a best practice for this?
The technique you are after is called magic key, if you want to search engine for it...
Do it from the context of the parent.
Short version: the realtionship to the child table allows for create in the relationship graph, and when you set ANY field in the child record it will automatically populate the field that is needed to create the relationship which can be the key
Or it is an assign button on the child record which runs a script whicih copied the Id of the parent you chose into the linking field in the child record.
Turns out this worked. You hit the nail on the head.
Only displaying the value from the second field works with the 'popup' list, but not the 'dropdown' list for whatever reason. With the 'dropdown' list, once you select the value, it goes back to displaying the first field value (which is the ID), rather than the second field value (which is the Account name).