Auto-Populate Other Fields from a Value List
I would like to auto-populate other fields from a value list in a drop-down. Not too complicated, right?
My issue is that I have three different sets of similar data that may or may not be necessary on each record.
PI - PI Phone - PI Dept - PI School
Co-PI 1 - Co-PI 1 Phone - Co-PI 1 Dept - Co-PI 1 School
Co-PI 2 - Co-PI 2 Phone - Co-PI 2 Dept - Co-PI 2 School
When I select the PI or Co-PI's name from a value list, I want the information that I have already set up in another table to populate, so we don't have to mess with phone numbers, etc. The problem is that I can't get the exact same data to be available for each of the PI fields. It is very possible that on one record I want Joe in the PI field, and on another one he should be Co-PI 1.
The closest I got to getting this was when I select PI, every other field fills in.... even the Co-PI fields, minus the names, which are not set to lookup.
I have tried making more than one relationship between the tables, but no such luck.