Remove those fields from your transactions table. Keep just the fkContacts field formatted with your value list. On your transactions layout, add these fields from your contacts layout.
On each of these fields define them to use the looked up value auto-enter option to set them to copy fro the corresponding fields in Contacts.
Business rules will determine which option is best. The second option will not automatically update fields in Transactions if one of these name fields is modified in contacts.
Thank you Philmodjunk, I'm not entirely clear with what Option 1 looks like. Let me further explain what I'm trying to achieve and perhaps you or some of the others here can help point me in the right direction.
The list of processors that I'm hoping to add to the transaction table should grow as we use the system. The first time we click into the list it would not have that person as a contact and we would add them, the next time we started to type them we would see they are there already and it would simply copy over the relevant fields to this transaction.
Does that help point me in one direction over the other?
It does not. The difference is in what should happen to the data shown in transactions when data for an existing contact is changed in your contacts table. If the data should always be exactly the same in both tables, use option 1 which actually only stores this info in the contacts table.
Here's an example: say one of your contacts changes their name, do you want the new name to appear on all past transactions associated with that processor?
More on option 1:
I am assuming this relationship:
contacts::pkContactID = Transactions::fkContactID
pkContactID should be defined to auto-enter a serial number or UUID. fkContactID would be formatted with a drop down list or pop up menu that lists names but enters the pk from Contacts.
With that set up, you can enter layout mode on your transactions layout and use the field tool or field picker to add fields to your layout. You can select "contacts" from the drop down at the top of the specify field dialog or the field picker, then select the email field from contacts to add it to the layout.
This field will be empty when you create a new transaction until you select a contact in fkContactID field. Then the email field displays the email from the selected contact. If you edit this email on your transactions layout all transactions for that contact will show the new email address.
I think your wish is a bit flawed.
Let me explain.
(Italics represent ideal entities that don't exactly coincide with your real data but help understanding the goal).
If I correctly understood your wish, you want to create a new patient in a patient table by choosing values while sitting on a analysis table, and you want to do this by displaying existing values from the patient table. Obviously this won't always work, because your choices will always be limited to existing values.
Moreover, I must say that people (and I include both end users and programmers in that bag) seem to overestimate the value of choosing a value from a list, no matter how long it is, over concrete input from the keyboard. Well, let me tell you something: I watched how people use our software. We made it possible for them to have an almost instant feedback in a selection portal so that they can type ber gio 63 and have - among others - Bernasconi Giovanni 15.4.1963 as a result. Well, they don't do it. They type the whole Bernasconi family name and only then they lift their eyes and look at the found set.
As I looked at them work, I used to shout "stop typing, you're already there, only 1 choice, u wasting time" but they separate concepts in chunks and a name is a chunk so they won't stop.
What was ideal, not negotiable feature and non plus ultra on paper turns to be useless in the real world.
It's important to know your clients. Watch them closely work, don't take as granted the needs they express by words, look at how they really work and build around that. And don't think that your clients are special: often the way they act is the way everybody acts, because, well, you know, it's how human brain and habits work...
Also remember that raising hand from keyboard, going to the mouse, pointing, if not avail scrolling, is a false feature: it seems to focus on the choice problem, but - all times added - it takes longer and is less efficient in the long run.
As software developers we can - and have to - lead people towards efficiency, with our experience. When the dice stop, it's all about it, but it's sometimes hard to bring the concept to the ones that pay thus want, no matter if it's efficient or not.