value list based on multiple fields...
Hi...I was looking to see if it was possible to create a value list based on multiple fields.
I have a table I call People...with fields called PeopleID, LMID, Name, Services (repeating field), etc. I have a second occurance of the people table called LMID, which is related by People::PeopleID = LMID::LMID.
The reason I have this relationship is because on a layout based on people...I have a field where you can select the persons manager (LMID). There could be several people associated to a manager. There is also a portal (LMID) on this layout which shows all the staff associated to the manager if your viewing their record. All works fine.
What I'm trying to do is create a value list which populates the Services and all the Names of people associated to a manager. For example, in case this is confusing...five pepole records have John Doe picked as their manager. When on John's record I see those five people listed in the LMID portal. I also have three different services listed in the repeating Service field.
On a different layout (which is related to the People table) I want to be able to use a value list which shows the five names and three services. I can do it successfully with separate lists but is there a way to combine the two to one list?
A repeating field is rarely an optimum design choice and this is an example of that.
To get such a value list, I'd use a single table to list both people and services as separate records in that same table. Then a basic conditional value list could list just the people and services for a given manager.